DB 小組專題報告

DB小組專題報告 第四組組長：1101137238游長茂組員：1101137213黃偉綸 1101137222劉得暘 1101137226 羅世平 1101137231 張有銘 1100131136 林昀逵

專案背景 本專案訪談商家億客來生鮮超市。這家超市主要從事流通零售業，銷售生鮮食品、日常用品、五金百貨等。

專案目標 億客來生鮮超市商品進銷及員工管理資訊系統。

專案範圍

功能架構 • 員工管理 • 進貨管理 • 存貨管理 • 銷貨管理

企業規則(Business Rule) • 員工分為一般員工、員工主管、副店長及店長；員工資料必須記錄員工編號、姓名、性別、年齡、住家電話、行動電話、學歷、曾任職務、身份證字號。 • 此商家不記錄顧客資料；所有的銷售一律為店內(現金銷售)銷售，不接受訂貨。 • 百貨類商品可於7天內憑發票退(換)貨，生鮮蔬果類商品須於3天內憑發票退(換)貨。 • 每家供應商可提供很多商品但每件商品只有一家供應商；供應商資料須記錄供應商編號、名稱、電話、地址；商品資料須記錄商品編號、名稱、單價、庫存量、種類、來源。 • 由店長決定優惠期間、優惠商品，以及優惠價格。 • 銷售狀況較不好的商品會記錄安全庫存，以控制進貨數量。

企業規則(Business Rule) • 所有員工，包含員工主管、副店長及店長皆需負責櫃台結帳等一般日常作業；員工主管須負責一般員工管理；店長或一般工作人員皆須負責進貨；結帳時，會產生發票作為銷售記錄，每張發票都有發票編號、統一編號、門市電話、門市地址、日期、時間、店別、收銀機號、銷售明細、收錢、合計、找錢，每筆結帳明細資料皆有商品名稱、銷售數量、小計。 • 會不定時盤點商品數量，以決定是否進貨。進貨前要先填寫進貨訂單，然後傳真一份給該供應商，此訂單須記錄供應商編號、供應商名稱、商品名稱、商品編號、進貨數量、進貨單價、進貨人姓名。可接受分批進貨(數量分批、項目分批或是都有可能的組合皆可以接受)，但要事先告知，並且先行紀錄才算數，否則不予接受。

資料模型 DATA MODEL

User Views PART 1：員工主管－員工管理功能

User Views PART 2：進貨人員－進貨管理功能

User Views PART 3：倉管人員－庫存管理功能

User Views PART 4：銷售人員－銷售管理功能

Conceptual Schema

Logical Schema • EMPLOYEE_T ( E_ID, E SupervisorID, JP_ID , E_Name, E_Age , E_IdNum , E_Gender, E_Telephone , E_Cellphone, E_EducationBackground, E_JobBackground, E_EmployDate , E_Portrait, E_Notation , E_Salary ) • JOB_POSITION_T ( JP_ID, JP_Name , JP_Description)

Logical Schema • ORDER_T ( O_ID , E_ID , O_DateTime , O_Notation , O_Status, O_Total ) • ORDER_LINE_T ( OL_ID , P_ID , O_ID , OL UnitCost , OL_Quantity) • RECEIVE_T (GR_ID , GR_ReceiveDateTime , GR_Notation) • RECEIVE_RECORD_T (RR_ID , OL_ID , GR_ID , RR_Quantity)

Logical Schema • SUPPLIER_T ( S_ID, S_Name , S_Telephone , S_ContractDate, S_Address , S_Notation ) • PRODUCT_T ( P_ID, K_ID , SC_ID, S_ID , P_Name , P_SalePrice, P_ AverageCost, P_StockQuantity, P_SafeQuantity , P_Barcode, P_Picture , P_Notation) • KIND_T ( K_ID , K_Name , K_Notation ) • SOURCE_COUNTRY_T ( SC_ID , SC_Name , SC_Notation)

Logical Schema • SALES_RECORD_T ( SR_ID , P_ID, SA_ID , SR_Quantity ) • SALES_T ( SA_ID, SA_DateTime , SA_Total ) • PROMOTION_RECORD_T ( PR_ID , SP_ID , P_ID , PR_Discount) • SALES_PROMOTION_T ( SP_ID , SP_Name , SP_StartDateTime, SP_EndDateTime, SP_Description)

資料字典

系統界面架構

資料庫使用介面與SQL 登入畫面 SELECT E_ID,E_IdNum FROM EMPLOYEE_T; WHERE E_ID=’xxxx’ AND E_IdNum=’xxxx’;

資料庫使用介面與SQL 人事管理_員工_查詢 SELECT JP_Name//抓出職位 FROM JOP_POSITION_T; SELECT JP_Name//抓出主管 FROM EMOPLYEE_T; SELECT A.E_ID,E_Name,E_Telephone,E_Cellphone,E_EducationBackground, E_JobBackground, E_EmployDate, B. JP_Name,E_Salary, B.E_Name FROM EMPLOYEE_T A INNER JOIN EMPLOYEE_T B ON A. SupervisorID = B.E_ID INNER JOIN JOB_POSITION_T C ON A.JP_ID=C.JP_ID, WHERE E_ID=xxxx AND E_ Name LIKE’%xxxx%’ AND E_ Cellphone LIKE’%xxx%’ AND JP_Name =’xxx’ AND E_Salary BETWEEN xxx AND xxxxx AND B.E_Name=’xxx’;

資料庫使用介面與SQL 人事管理_員工_查詢結果人事管理_員工_查詢結果(刪除) DELETE FROM EMPLOYEE_T WHERE E_ID = xxx;

資料庫使用介面與SQL 人事管理_員工_修改 SELECT JP_Name//抓出職位 FROM JOP_POSITION_T; SELECT JP_Name//抓出主管 FROM EMOPLYEE_T; SELECT A.E_ID,E_Name,E_Age,E_IdNum, E_Telephone,E_Cellphone,E_EducationBackground, E_JobBackground, E_EmployDate,E_Notation,B.E_Name,C.JP_Name,E_Salary FROM EMPLOYEE_T A INNER JOIN EMPLOYEE_T B ON A. SupervisorID = B.E_ID INNER JOIN JOB_POSITION_T C ON A.JP_ID=C.JP_ID, UPDATE FROM EMPLOYEE_T A SET A.E_Name = ‘XXX’, A.E_Age=X, A.E_IdNum=’XXXX’, A.E_Gender=’x’, A.E_Telephone=’xxxxxxx’, A.E_Cellphone=’XXXX’, A.E_EducationBackground = ‘xxx’, A.E_JobBackground = ‘xxx’, A.E_EmployDate = ‘xxxx/xx/xx ‘ A.E_Notation=’XXX’, A.E_B.E_ID=XXX, A.JP_ID=xxx, A.E_Salary=’ffff’ WHERE A.E_ID=XXX;

資料庫使用介面與SQL 人事管理_員工_新增 INSERT INTO EMPLOYEE_T (E_Name, E_Age, E_IdNum, E_Gender, E_Telephone, E_Cellphone, E_EducationBackground, E_JobBackground,E_ EmployDate,E_Picture, E_Notation,E_Salary,SupervisorID,JP_ID) VALUES(‘XXX’,’XX’,DDD’, ’D’,’N’,’FFF’,’FFFFF’,’SSSSS’,NOW( ),'xxx.gif',’FFFFFF’,’FFF’.’FFF’,’FFFF’);

資料庫使用介面與SQL 人事管理_員工_查詢 SELECT JP_Name//抓出職位 FROM JOP_POSITION_T; SELECT JP_Name//抓出主管 FROM EMOPLYEE_T; SELECT A.E_ID,E_Name,E_Telephone,E_Cellphone,E_EducationBackground, E_JobBackground, E_EmployDate, B. JP_Name,E_Salary, B.E_Name FROM EMPLOYEE_T A INNER JOIN EMPLOYEE_T B ON A. SupervisorID = B.E_ID INNER JOIN JOB_POSITION_T C ON A.JP_ID=C.JP_ID, WHERE E_ID=xxxx AND E_ Name LIKE’%xxxx%’ AND E_ Cellphone LIKE’%xxx%’ AND JP_Name =’xxx’ AND E_Salary BETWEEN xxx AND xxxxx AND B.E_Name=’xxx’;

資料庫使用介面與SQL 人事管理_職位_查詢結果人事管理_職位_查詢結果(刪除) DELETE FROM JOB_POSITION_T WHERE JP_ID = xxx;

資料庫使用介面與SQL 人事管理_職位_修改 1 SELECT JP_ID,JP_Name,JP_Descrption FROM JOB_POSITION_T WHERE JP_ID=xxxx; 2 UPDATE JOB_POSITION_T SET JP_Name = ‘xxx’, JP_Description =’ xxx’ WHERE JP_ID = xxx;

資料庫使用介面與SQL 人事管理_職位_新增 INSERT INTO JOB_POSITION_T(JP_Name,JP_Notation) VALUES(‘XXX’,’XXXXXX’);

資料庫使用介面與SQL 進貨管理_訂單_查詢 SELECT A.O_ID, O_DateTime,D.Name,O_Notation,O_Status,O_Total FROM ORDER_T A INNER JOIN ORDER_LINE_T B ON A.O_ID = B.O_ID INNER JOIN PRODUCT_T C ON B.P_ID=C.P_ID INNER JOIN SUPPLIER_T D ON C.S_ID=D.S_ID INNER JOIN EMPLOYEE E ON ON E.E_ID=O.E_ID WHERE A.O_ID=X AND O_DateTime=’XXX’ AND O_Status=’F’ AND D.S_Name AND E.E_Name;

資料庫使用介面與SQL 進貨管理_訂單_查詢結果進貨管理_訂單_查詢結果(刪除) DELETE FROM OREDR_T WHERE O_ID=XX;

資料庫使用介面與SQL 進貨管理_訂單_修改 SELECT O_ID,O_DateTime,O_Notation,O_Total,PR.E_ID FROM ORDER_T AS PR SELECT S_ID,S_Name FROM SUPPLIER_T SELECT E.E_ID,E_Name FROM EMPLOYEE E UPDATE ORDER_T SET O_DateTime= ‘xxxx/xx/xx yy:yy:yy’, O_Notation=’fffff’, O_Total=’zzzz’ UPDATE PRODUCT_T SET P_ID=XX;

資料庫使用介面與SQL 進貨管理_訂單_新增 SELECT S_Name FROM SUPPLOER_T SELECT E_Name FROM EMPLOYEE_T SELECT P_Name FROM PRODUCT_T 1資料寫進ORDER_T INSERT INTO ORDER_T (E_ID, O_DateTime, O_Notation, O_Status, O_Total)VALUES(x,NOW ( ),x,x,x); 2資料寫進ORDER_LINE_T INSERT INTO ORDER_LINE_T (P_ID, O_ID, OL_ UnitCost, OL_Quantity)VALUES(X,X,X,X);

資料庫使用介面與SQL 進貨管理_供應商_查詢 SELECT S_ID,S_Name,S_ContractDate FROM SUPPLIER_T WHERE S_ID=xx AND S_Name LIKE ‘%XX%’ AND S_Telepone LIKE’%gg%’;

資料庫使用介面與SQL 進貨管理_供應商_查詢結果進貨管理_供應商_查詢結果(刪除) DELETE FROM SUPPLIER_T WHERE S_ID = XX;

資料庫使用介面與SQL 進貨管理_供應商_修改 UPDATE FROM SUPPLIER_T SET S_Name=’xxx’, S_Telephone=’xxxx’, S_ ContractDate= ’yyyy/mm/ddyy:yy:yy‘, S_Address=’ddddd’, S_Notation=’dffffffffffffff’

資料庫使用介面與SQL 進貨管理_供應商_新增 INSERT INTO SUPPLIER_T (S_Name,S_Telephone,S_ContractDate,S_Address,S_Notation)VALUES(X,X,NOW( ),X,X);

資料庫使用介面與SQL 進貨管理_收貨單_查詢 SELECT * FROM GOODS_RECEIVE_T WHERE GR_ID=XX AND ANDGR_DateTime BETWEEN ‘XX’ AND ‘XXXX’;

資料庫使用介面與SQL 進貨管理_收貨單_查詢結果進貨管理_收貨單_查詢結果(刪除) DELETE FROM RECEIVE_T WHERE GR_ID=XX;

資料庫使用介面與SQL 進貨管理_收貨單_修改 SELECT E.GR_ID,E.GR_DateTime,GR_Notation,A.O_ID,C.P_ID,C.P_Name, B.OL _Quantity,D.RR_Quantity FROM ORDER _T A INNER JOIN ORDER_LINE_T B ON A.O_ID=B.O_ID INNER JOIN PRODUCT_T C ON B.P_ID =C.P_ID INNER JOIN RECEIVE_RECORD_T D ON D.OL_ID =B.OL_ID INNER JOIN GOOD_RECEIVE_T E ONE.GR_ID =D.GR_ID WHERE D.GR_ID=XXX; 1(修改日期和註解) UPDATE GOODS_RECEIVE_T SET GR_Notation=XX, GR_DateTime=‘xxxx/xx/xx yy:yy:yy’, WHERE GR_ID=XX; 2(新增RECEIVE_RECORD_T) INSERT INTO RECEIVE_RECORD_T (OL_ID,GR_ID,RR_Quantity) VALUES(X,X,X);

資料庫使用介面與SQL 進貨管理_收貨單_新增 SELECT P_ID,P_Name FROM PRODUCT_T INSERT INTO GOODS_RECEIVE_T(GR_DateTime,GR_Notation)VALUES(X,X); INSERT INTO RECEIVE_RECORD_T(OL_ID,GR_ID,RR_Quantity)VALUES(X,X,X);

資料庫使用介面與SQL 庫存管理_商品_查詢 SELECT A.P_ID,P_Name,P_SalePrice, E.PR_Discount , P_SalePrice - E.PR_Discount AS 折扣後單價 ,P_StockQuantity,P_SafeQuantity, P_AverageCost FROM PRODUCT_T A INNER JOIN KIND_T ON B ON A.K_ID = B.K_ID INNER JOIN SOURCE_COUNTRY_T C ON A.SC_ID=C.SC_ID INNER JOIN SUPPLIER_T D ON A.S_ID =D.S_ID INNER JOIN PROMOTION RECORD_T AS E ON A.PR_ID = E.PR_ID WHERE A.P_ID =’SSSS’ AND P_Name LIKE’%XXXXX%’ AND P_SalePrice BETWEEN AAA AND CCC AND P.K_ID=’FFFF’ AND A.SC_ID=’FFFFF’ AND A.S_ID=’DFFFFF’;

資料庫使用介面與SQL 庫存管理_商品_查詢結果庫存管理_商品_查詢結果(刪除) DELETE FROM PRODUCT_T WHERE P_ID=XX;

資料庫使用介面與SQL 庫存管理_商品_修改 SELECT A.P_ID,P_Name,P_SalePrice,P_StockQuantity, P_SafeQuantity,P_Barcode,P_Picture,P_Notation, B.SC_Name, C.K_Name, D.S_Name FROM PRODUCT_T A INNER JOIN SOUCRE_COUNTRY _T B ON A.SC_ID=B. SC_ID INNER JOIN KIND_T C ON C.K_ID=A.K_ID INNER JOIN SUPP;IER D ON A.S_ID=D.S_ID WHERE A.P_ID=XXX UPDATE PRODUCT_T SET K_ID=XX, SC_ID=XX, S_ID=XX, P_Name=XX, P_SalePrice=XX, P_AverageCost=xx, P_StockQuantity=xx, P_SafeQuantity=xx, P_Barcode=xx, P_Notation=xx WHERE P_ID=XX;

資料庫使用介面與SQL 庫存管理_商品_新增 INSERT INTO PRODUCT_T (K_ID, SC_ID, S_ID, P_Name, P_SalePrice, P_AverageCost, P_StockQuantity, P_SafeQuantity, P_Barcode, P_Picture, P_Notation) VALUES (X,X,X,X,X,X,X,X,X,X);

資料庫使用介面與SQL 庫存管理_種類_查詢 SELECT K_ID,K_Name FROM KIND_T WHERE K_ID=XX AND K_Name LIKE ’%FFF%’;

資料庫使用介面與SQL 庫存管理_種類_查詢結果庫存管理_種類_查詢結果(刪除) DELETE FROM KIND_T WHERE K_ID=XX;

資料庫使用介面與SQL 庫存管理_種類_修改 UPDATE KIND_T SET K_Name=XX, K_Notation=XX WHERE K_ID=XX;

資料庫使用介面與SQL 庫存管理_種類_新增 INSERT INTO KIND_T VALUES(XX,XX);

DB 小組專題報告

DB 小組專題報告

Presentation Transcript