连锁超市--数据库及代码

1、实验7 数据库设计一、实验目的使学生能够对给定的实际需求，选择一种编程语言或开发工具，完成数据库设计，并实现数据的查询等操作，从而掌握数据库应用系统的设计方法和步骤。熟悉大型数据库管理系统的结构与组成；熟悉数据库应用系统的设计方法和开发过程；掌握一种大型数据库管理系统(SQL SERVER或ORACLE)的应用技术和开发工具的使用；熟悉数据库设计工具的使用；熟悉数据库安全的相关知识和技术；熟悉数据库系统的管理和维护。二、实验时数 4学时三、实验内容完整实践数据库应用系统设计的工程过程:需求分析概念设计逻辑设计物理设计实施编写应用程序：(1)需求分析:选定一个应用，对所设计的数据库系统有一定的调

2、研分析,其结果应该以简单的需求分析说明书数据字典和数据流图(2级数据流即可)表示；(2)概念分析:使用E-R图作为描述工具.描述出局部和全局的E-R图；(3)逻辑设计:将E-R图转化为相应的关系模式,并根据范式理论进行优化分析，关系模式不少于3个；(4)物理设计:根据需要设计必要的索引等结构；(5)实施:以某种DBMS为工具创建出完整的数据库.在数据库中要表现出的知识点包括:设置数据完整性(参照完整性、实体完整性、自定义完整性(包括触发器)。设置用户,并相应设计用户的权限和角色(用户设计至少3个，有不同的权限) 。建立存储过程完成相关操作至少2个,以文件方式保存。(6) 编写应用程序：利用开发

3、工具C+、 JAVA、.NET平台或其它动态网页开发工具编写应用程序；(7)以上内容,请详细描述,并有必要抓图；(8) 提交系统的源码程序、设计性实验报告。表名中文名MerchInfo商品信息表Factory供货表Provide供货商表Supermaket超市表Sale销售表Users顾客表Member会员表 商品信息表 (MerchInfo) 字段名字段类型长度字段值约束主/外键对应中文名MerchIDInt4Not nullF商品编号MerchNamevarchar50Not null商品名称MerchPricevarchar50Not null商品价格BarCodemoney4Not n

4、ull条形码顾客表 （User）字段名字段类型长度字段值约束主/外键对应中文名UserIDvarchar10Not nullF顾客编号UserNamevarchar25Not null顾客名称UserPWvarchar50Not null顾客密码UserStyleint2Not null顾客类型会员表 (Member)字段名字段类型长度字段值约束主/外键对应中文名MemberName varchar10Not nullF会员姓名MemberCardvarchar20Not null会员卡号TotalCostMoney4Not null累计金额RegDateDatetime8Not null注册

5、日期销售表 （Sale）字段名字段类型长度字段值约束主/外键对应中文名SaleIDvarchar10Not nullF流水账号MerCHID varchar10Not nullP商品编号SaleDateDatetime8Not null销售日期SaleNumInt4Not null销售数量SalePriceMoney4Not null销售金额UserIDvarchar10Not nullP顾客编号供货表 （Factory）字段名字段类型长度字段值约束主/外键对应中文名FactoryIDvarchar10Not nullF供货编号FactoryDatevarchar Not null供货日期Fa

6、ctorySLvarchar4Not null供货数量MerCHIDvarchar10Not nullP商品编号ProvideIDvarchar10Not nullP供货商号供货商表 （Provide）字段名字段类型长度字段值约束主/外键对应中文名ProvideIDvarchar10Not nullF供货商号ProvideNamevarchar50Not null供货商名称ProvideAddressvarchar250Not null供货商地址ProvidePhonevarchar25Not null供货商电话仓库表 （Supermaket）字段名字段类型长度字段值约束主/外键对应中文名Su

7、permaketIDint10Not nullF仓库编号SupermaketNamevarchar50Not null仓库名称SupermaketAddressvarchar250Not null仓库地址库存表字段名字段类型长度字段值约束对应中文名KCLvarchar10Not null库存量SPBHvarchar10Not null商品编号CKBHvarchar10Not null仓库编号创建数据库create database SuperMarketdb on primary ( name=SuperMarketdb , filename=C:Program FilesMicrosoft

8、SQL ServerMSSQLDataSuperMarketdb.mdf, size=100MB, maxsize=200MB,filegrowth=20MB ) log on ( name=SuperMarketlog, filename=C:Program FilesMicrosoft SQL ServerMSSQLDataSuperMarketdb.ldf, size=60MB,maxsize=200MB, filegrowth=20MB ) go/*创建商品信息表*/CREATE TABLE MerchInfo ( MerchID int Primary key , MerchName

9、 varchar(50) NOT NULL , MerchPrice money NOT NULL , BarCode varchar(20) NOT NULL , ) GO /*创建供货表*/CREATE TABLE Factory ( FactoryID varchar(10) Primary key , FactoryDate Datetime NOT NULL , FactorySL Char(4) NOT NULL , MerCHID varchar(10) NOT NULL,ProvideID varchar(10) NOT NULL ) GO/*创建供货商表*/CREATE TA

10、BLE Provide ( ProvideID varchar(10) Primary key , ProvideName varchar(50) NOT NULL , ProvideAddress varchar(250) NOT NULL , ProvidePhone varchar(25) NOT NULL ) GO/*创建仓库表*/CREATE TABLE Supermaket ( SupermaketID int Primary key, SupermaketName varchar(10) NULL , SupermaketAddress varchar(10) NULL ) GO

11、 /*创建会员表*/CREATE TABLE Member ( MemberName varchar(10) Primary key , MemberCard varchar(20) NOT NULL , TotalCost money NOT NULL , RegDate datetime NOT NULL ) GO /*创建购买表*/CREATE TABLE Sale ( SaleID int Primary key ,MerCHID varchar (10) NOT NULL,SaleDate datetime NOT NULL ,SaleNum int NOT NULL,SalePri

12、ce money NOT NULL,UserID varchar(10) NOT NULL)GO /*创建顾客表*/CREATE TABLE Users( UserID varchar(10) Primary key , UserName varchar(25) NOT NULL , UserPW varchar(50) NOT NULL , UserStyle int NOT NULL , ) GO/*-添加表信息-*/MerchInfo：INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode)VALUES(001,德芙巧克力,2

13、0,11112222);INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode)VALUES(002,上好佳,2,11113333);INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode)VALUES(003,火腿肠,5,11114444);INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode)VALUES(004,面包,4,11115555);Users：INSERT INTO Users(UserID,

14、 UserName, UserPW, UserStyle)VALUES(001,张一,985211,1);INSERT INTO Users(UserID, UserName, UserPW, UserStyle)VALUES(002,王二,211985,2);INSERT INTO Users(UserID, UserName, UserPW, UserStyle)VALUES(003,朱三,369852,2);INSERT INTO Users(UserID, UserName, UserPW, UserStyle)VALUES(004,郭四,456321,1);Member：INSERT

15、 INTO Member(MemberName, MemberCard , TotalCost,RegDate)VALUES(张一,001,200,2014-12-1);INSERT INTO Member(MemberName, MemberCard , TotalCost,RegDate)VALUES(王二,002,100,2014-11-1);INSERT INTO Member(MemberName, MemberCard , TotalCost,RegDate)VALUES(朱三,003,350,2014-9-3);INSERT INTO Member(MemberName, Mem

16、berCard , TotalCost,RegDate)VALUES(郭四,004,700,2014-8-1);Sale：INSERT INTO sale(SaleID,MerCHID, SaleDate, SaleNum, SalePrice ,UserID)VALUES(0000,001,2014-12-28,30,750,002);INSERT INTO sale(SaleID,MerCHID, SaleDate, SaleNum, SalePrice ,UserID)VALUES(0001,002,2014-12-16,10,500,001);INSERT INTO sale(Sale

17、ID,MerCHID, SaleDate, SaleNum, SalePrice ,UserID)VALUES(00002,003,2014-12-15,15,400,003);Factory:INSERT INTO factory(FactoryID,FactoryDate,FactorySL, MerCHID,ProvideID)VALUES(005,2014-12-1,500,002,010);INSERT INTO factory(FactoryID,FactoryDate,FactorySL, MerCHID,ProvideID)VALUES(006,2014-12-11,200,0

18、01,012);INSERT INTO factory(FactoryID,FactoryDate,FactorySL, MerCHID,ProvideID)VALUES(007,2014-12-24,500,003,011);Provide:INSERT INTO provide(ProvideID,ProvideName,ProvideAddress,ProvidePhone)VALUES(003,蒙牛,内蒙古,36363636);INSERT INTO provide(ProvideID,ProvideName,ProvideAddress,ProvidePhone)VALUES(004

19、,伊利,太原,56565656);INSERT INTO provide(ProvideID,ProvideName,ProvideAddress,ProvidePhone)VALUES(005,飞鹤,唐山,78787878);Supermaket：INSERT INTO Supermaket (SupermaketID, SupermaketName, SupermaketAddress)VALUES(01,一号超市,中华大街);INSERT INTO Supermaket(SupermaketID, SupermaketName, SupermaketAddress)VALUES(02,二

20、号超市,光明大街);INSERT INTO Supermaket(SupermaketID, SupermaketName, SupermaketAddress)VALUES(03,三号超市,人民路);/*-创建表间约束-*/*供货表中商品编号、供应商号分别与商品信息表、供应商表之间的外键约束*/ ALTER TABLE Factory ADD CONSTRAINT FK_Factory_MerCHID FOREIGN KEY (MerCHID) REFERENCES MerchInfo (MerCHID), CONSTRAINT FK_Factory_Provide FOREIGN KEY

21、(ProvideID) REFERENCES Provide (ProvideID) /*销售表中商品编号与商品信息表之间的外键约束*/ ALTER TABLE Sale ADD CONSTRAINT FK_Sale_MerchInfo FOREIGN KEY ( MerChID ) REFERENCES MerchInfo (MerchID) ON DELETE CASCADE/*入库表中商品编号与商品信息表之间的外键约束*/ ALTER TABLE Stock ADD CONSTRAINT FK_Stock_MerchInfo FOREIGN KEY( MerchID)REFERENCES

22、 MerchInfo (MerchID)ON DELETE CASCADE GO/*-创建索引-*/-在顾客表上建立一个以顾客编号、顾客密码为索引项的非聚集索引CREATE nonclustered INDEX IX_Users ON Users(UserID, UserPW) GO -在商品信息表上建立一个以商品编号为索引项的非聚集索引CREATE nonclustered INDEX IX_MerchInfo ON MerchInfo(MerchID) GO -在购买表上建立一个以购买编号、购买日期为索引项的非聚集索引CREATE nonclustered INDEX IX_Sale ON

23、 Sale(SaleID, SaleDate) GO -在供货商表上建立一个以供货商号、供货商名称为索引项的非聚集索引CREATE nonclustered INDEX IX_Provide ON Provide(ProvideID, ProvideName) GO/*-创建视图-*/-创建用于查看商品信息情况的视图CREATE VIEW v_MerchInfoAS SELECT MerchID as 商品编号, MerchName as 商品名称, MerchPrice as 商品价格, BarCode as 条形码FROM MerchInfoGO创建用于查询销售明细记录的视图CREATE

24、VIEW v_Sale AS SELECT MerchInfo.MerchName as 商品名称, MerchInfo.BarCode as 条形码, MerchInfo.MerchPrice as 商品价格, Sale.SalePrice as 销售金额, Sale.SaleNum as 销售数量, Sale.SaleDate as 销售日期FROM Sale INNER JOIN MerchInfo ON Sale.MerChID = MerchInfo.MerchID GO 创建用于供货情况的视图CREATE VIEW v_FactoryAS SELECT Factory.FactorySL as 供货数量, Factory.FactoryDate as 供货日期, Provide.ProvideName as 供货商, Provide.ProvideAddress as 供货地址, Provide.ProvidePhone as 供货电话FROM Factory INNER JOIN Provide ON Factory.ProvideID=Provide