數(shù)據(jù)庫-超市信息管理數(shù)據(jù)庫設(shè)計(jì)

鄭州輕工業(yè)學(xué)院計(jì)算機(jī)與通信工程學(xué)院《數(shù)據(jù)庫系統(tǒng)及應(yīng)用》期末作業(yè)報(bào)告專業(yè)：班級：學(xué)號：姓名：_卓培瑩題目名稱：超市信息管理數(shù)據(jù)庫設(shè)計(jì)完成日期：2015-06-03

一、概念結(jié)構(gòu)設(shè)計(jì)*******************************************E-R關(guān)系圖********************************************進(jìn)貨時間進(jìn)貨編號供給商編號供給商地址商品編號商品價(jià)格進(jìn)貨時間進(jìn)貨編號供給商編號供給商地址商品編號商品價(jià)格供給商進(jìn)貨商品nm供給商進(jìn)貨商品進(jìn)貨數(shù)量進(jìn)貨總價(jià)聯(lián)系供給商名稱商品名稱商品類別進(jìn)貨數(shù)量進(jìn)貨總價(jià)聯(lián)系供給商名稱商品名稱商品類別部門辦公室部門負(fù)責(zé)人n部門辦公室部門負(fù)責(zé)人屬于部門貨品管理1屬于部門貨品管理部門編號部門名稱部門編號部門名稱員工姓名員工編號員工姓名員工編號員工員工員工員工職稱員工員工職稱二、邏輯結(jié)構(gòu)設(shè)計(jì)*************************將E-R圖轉(zhuǎn)化為系統(tǒng)關(guān)系模型**************************************商品〔商品編號，商品名稱，商品價(jià)格，商品類別，部門編號，部門名稱〕供給商〔供給商編號，供給商名稱，供給商聯(lián)系，供給商地址〕進(jìn)貨〔進(jìn)貨編號,進(jìn)貨時間,商品編號,商品名稱,進(jìn)貨數(shù)量,進(jìn)貨總價(jià),供給商編號,供給商名稱〕部門〔部門編號，部門名稱，部門負(fù)責(zé)人，部門辦公室〕員工〔員工編號，員工姓名，員工職稱，員工，所屬部門編號，所屬部門名稱〕三、系統(tǒng)實(shí)現(xiàn)1.數(shù)據(jù)表的創(chuàng)立語句〔最好有注釋〕-----創(chuàng)立SUPERMARKET數(shù)據(jù)庫-----CREATEDATABASESUPERMARKETGO-----創(chuàng)立表GongYingShang供給商-----USESUPERMARKETCREATETABLEGongYingShang(G_IDCHAR(50)PRIMARYKEY,G_NAMEVARCHAR(50)NOTNULL,G_PHONEVARCHAR(50),G_ADDRESSVARCHAR(50))〔表--供給商〕-----創(chuàng)立表BuMen部門-----USESUPERMARKETCREATETABLEBuMen(B_IDCHAR(50)PRIMARYKEY,B_NAMEVARCHAR(50)NOTNULL,B_OFFICERVARCHAR(50),B_OFFICE_ADDRESSVARCHAR(50))〔表—-部門〕-----創(chuàng)立表ShangPin商品-----USESUPERMARKETCREATETABLEShangPin(S_IDCHAR(50)PRIMARYKEY,S_NAMEVARCHAR(50)NOTNULL,S_PRICEVARCHAR(5000),S_CLASSVARCHAR(50),B_IDCHAR(50),B_NAMEVARCHAR(50),FOREIGNKEY(B_ID)REFERENCESBuMen(B_ID))〔表—-商品〕-----創(chuàng)立表JinHuo進(jìn)貨-----USESUPERMARKETCREATETABLEJinHuo(J_IDCHAR(50),J_TIMEVARCHAR(50)NOTNULL,S_IDCHAR(50),S_NAMEVARCHAR(50),J_NUMBERVARCHAR(50),J_PRICEVARCHAR(50),G_IDCHAR(50),G_NAMEVARCHAR(50),FOREIGNKEY(S_ID)REFERENCESShangPin(S_ID),FOREIGNKEY(G_ID)REFERENCESGongYingShang(G_ID))〔表—-進(jìn)貨〕-----創(chuàng)立表YuanGong員工-----USESUPERMARKETCREATETABLEYuanGong(Y_IDCHAR(50)PRIMARYKEY,Y_NAMEVARCHAR(50)NOTNULL,Y_ZHICHENGVARCHAR(50),Y_PHONEVARCHAR(50),B_IDCHAR(50),B_NAMEVARCHAR(50),FOREIGNKEY(B_ID)REFERENCESBuMen(B_ID))〔表—-員工〕2.視圖的創(chuàng)立語句〔最好有注釋〕-----創(chuàng)立view_ShangPin_YuanGong視圖〔商品信息以及負(fù)責(zé)管理該商品的員工信息〕-----USESUPERMARKETGOCREATEVIEWview_ShangPin_YuanGongASSELECTShangPin.*,YuanGong.Y_ID,YuanGong.Y_NAME,YuanGong.Y_ZHICHENG,YuanGong.Y_PHONEFROMShangPin,YuanGongWHEREShangPin.B_ID=YuanGong.B_ID(視圖--商品-員工)-----創(chuàng)立嵌套視圖----------創(chuàng)立view_GongYingshang_JinHuo視圖〔商品的供給商信息和商品編號〕-----USESUPERMARKETGOCREATEVIEWview_GongYingshang_JinHuoASSELECTGongYingshang.*,JinHuo.S_IDFROMGongYingshang,JinHuoWHEREGongYingshang.G_ID=JinHuo.G_ID(視圖--供給商-進(jìn)貨)-----創(chuàng)立視圖〔商品信息以及該商品的供給商信息〕-----USESUPERMARKETGOCREATEVIEWview_ShangPin_GongYingshangASSELECTShangPin.*,view_GongYingshang_JinHuo.G_ID,view_GongYingshang_JinHuo.G_NAME,view_GongYingshang_JinHuo.G_PHONE,view_GongYingshang_JinHuo.G_ADDRESSFROMShangPin,view_GongYingshang_JinHuoWHEREview_GongYingshang_JinHuo.S_ID=ShangPin.S_ID(視圖—商品-供給商)3.存儲過程的創(chuàng)立語句〔最好有注釋〕-----1.創(chuàng)立存儲過程實(shí)現(xiàn):查詢某工號的員工所負(fù)責(zé)管理的商品類別有哪些-----USESUPERMARKETGOCREATEPROCEDUREPROC_YuanGong_CLASSASSELECTview_ShangPin_YuanGong.Y_ID,view_ShangPin_YuanGong.Y_NAME,view_ShangPin_YuanGong.S_CLASSFROMview_ShangPin_YuanGongWHEREview_ShangPin_YuanGong.Y_ID='201'EXECPROC_YuanGong_CLASS〔存儲過程--1〕-----2.創(chuàng)立存儲過程實(shí)現(xiàn):查詢某部門所負(fù)責(zé)管理的所有商品的供給商名稱-----USESUPERMARKETGOCREATEPROCEDUREPROC_BuMen_GongYingShangASSELECTview_ShangPin_GongYingshang.B_NAME,view_ShangPin_GongYingshang.G_NAMEFROMview_ShangPin_GongYingshangWHEREview_ShangPin_GongYingshang.B_NAME='物資管理部'EXECPROC_BuMen_GongYingShang〔存儲過程--2〕-----3.創(chuàng)立存儲過程實(shí)現(xiàn):查詢某供給商所供給的商品信息和該商品所屬部門的經(jīng)理姓名-----USESUPERMARKETGOCREATEPROCEDUREPROC_ShangPin_GongYingshang_OFFICERASSELECTview_ShangPin_GongYingshang.S_ID,view_ShangPin_GongYingshang.S_NAME,view_ShangPin_GongYingshang.S_PRICE,view_ShangPin_GongYingshang.S_CLASS,BuMen.B_OFFICERFROMBuMen,view_ShangPin_GongYingshangWHEREview_ShangPin_GongYingshang.G_NAME='廣東廣州供給商'ANDview_ShangPin_GongYingshang.B_NAME=BuMen.B_NAMEEXECPROC_ShangPin_GongYingshang_OFFICER〔存儲過程--3〕插入數(shù)據(jù)-----向表GongYingShang供給商中插入數(shù)據(jù)-----USESUPERMARKETINSERTINTOGongYingShangVALUES('11111','上海浦東供給商','123456789','上海浦東新區(qū)')USESUPERMARKETINSERTINTOGongYingShangVALUES('22222','福建泉州供給商','223456789','福建泉州豐澤區(qū)')USESUPERMARKETINSERTINTOGongYingShangVALUES('33333','廣東廣州供給商','323456789','廣東廣州開發(fā)區(qū)')USESUPERMARKETINSERTINTOGongYingShangVALUES('44444','內(nèi)蒙古伊利供給商','523456789','內(nèi)蒙古畜牧區(qū)')-----向表BuMen部門中插入數(shù)據(jù)-----USESUPERMARKETINSERTINTOBuMenVALUES('10','物資管理部','王晶晶','總部9#101')USESUPERMARKETINSERTINTOBuMenVALUES('20','質(zhì)量監(jiān)督部','陳貝貝','總部9#502')USESUPERMARKETINSERTINTOBuMenVALUES('30','經(jīng)營銷售部','劉裕','總部9#301')-----向表ShangPin商品中插入數(shù)據(jù)-----USESUPERMARKETINSERTINTOShangPinVALUES('10001','格力空調(diào)','2999','家用電器','10','物資管理部')USESUPERMARKETINSERTINTOShangPinVALUES('10002','伊利桶裝純牛奶','255','食品','20','質(zhì)量監(jiān)督部')USESUPERMARKETINSERTINTOShangPinVALUES('10003','華碩手提電腦W519L','2899','數(shù)碼電器','30','經(jīng)營銷售部')USESUPERMARKETINSERTINTOShangPinVALUES('10004','天堂太陽傘','50','居家用品','10','物資管理部')-----向表JinHuo進(jìn)貨中插入數(shù)據(jù)-----USESUPERMARKETINSERTINTOJinHuoVALUES('1','201501','10001','格力空調(diào)','1','2999','33333','廣東廣州供給商')USESUPERMARKETINSERTINTOJinHuoVALUES('2','201504','10002','伊利桶裝純牛奶','10','2550','44444','內(nèi)蒙古伊利供給商')USESUPERMARKETINSERTINTOJinHuoVALUES('3','201506','10003','華碩手提電腦W519L','1','2899','11111','上海浦東供給商')USESUPERMARKETINSERTINTOJinHuoVALUES('4','201501','10004','天堂太陽傘','100','5000','22222','福建泉州供給商')-----向表YuanGong員工中插入數(shù)據(jù)-----USESUPERMARKETINSERTINTOYuanGongVALUES('101','王晶晶','經(jīng)理','123123123','10','物資管理部')USESUPERMARKETINSERTINTOYuanGongVALUES('102','張曉虹','員工','121212121','10','物資管理部')USESUPERMARKETINSERTINTOYuanGongVALUES('103','楊花花','員工','131313131','10','物資管理部')USESUPERMARKETINSERTINTOYuanGongVALUES('201','陳貝貝','經(jīng)理','131312031','20','質(zhì)量監(jiān)督部')USESUPERMARKETINSERTINTOYuanGongVALUES('202','徐長春','員工','14572