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

实验7 数据库设计一、实验目的使学生能够对给定的实际需求，选择一种编程语言或开发工具，完成数据库设计，并实现数据的查询等操作，从而掌握数据库应用系统的设计方法和步骤。熟悉大型数据库管理系统的结构与组成；熟悉数据库应用系统的设计方法和开发过程；掌握一种大型数据库管理系统(SQL SERVER或ORACLE)的应用技术和开发工具的使用；熟悉数据库设计工具的使用；熟悉数据库安全的相关知识和技术；熟悉数据库系统的管理和维护。二、实验时数 4学时三、实验内容完整实践数据库应用系统设计的工程过程:需求分析概念设计逻辑设计物理设计实施编写应用程序：(1)需求分析:选定一个应用，对所设计的数据库系统有一定的调研分析,其结果应该以简单的需求分析说明书数据字典和数据流图(2级数据流即可)表示；(2)概念分析:使用E-R图作为描述工具.描述出局部和全局的E-R图；(3)逻辑设计:将E-R图转化为相应的关系模式,并根据范式理论进行优化分析，关系模式不少于3个；(4)物理设计:根据需要设计必要的索引等结构；(5)实施:以某种DBMS为工具创建出完整的数据库.在数据库中要表现出的知识点包括:设置数据完整性(参照完整性、实体完整性、自定义完整性(包括触发器)。设置用户,并相应设计用户的权限和角色(用户设计至少3个，有不同的权限) 。建立存储过程完成相关操作至少2个,以文件方式保存。(6) 编写应用程序：利用开发工具C+、 JAVA、.NET平台或其它动态网页开发工具编写应用程序；(7)以上内容,请详细描述,并有必要抓图；(8) 提交系统的源码程序、设计性实验报告。表名中文名MerchInfo商品信息表Factory供货表Provide供货商表Supermaket超市表Sale销售表Users顾客表Member会员表 商品信息表 (MerchInfo) 字段名字段类型长度字段值约束主/外键对应中文名MerchIDInt4Not nullF商品编号MerchNamevarchar50Not null商品名称MerchPricevarchar50Not null商品价格BarCodemoney4Not null条形码顾客表 （User）字段名字段类型长度字段值约束主/外键对应中文名UserIDvarchar10Not nullF顾客编号UserNamevarchar25Not null顾客名称UserPWvarchar50Not null顾客密码UserStyleint2Not null顾客类型会员表 (Member)字段名字段类型长度字段值约束主/外键对应中文名MemberName varchar10Not nullF会员姓名MemberCardvarchar20Not null会员卡号TotalCostMoney4Not null累计金额RegDateDatetime8Not null注册日期销售表 （Sale）字段名字段类型长度字段值约束主/外键对应中文名SaleIDvarchar10Not nullF流水账号MerCHID varchar10Not nullP商品编号SaleDateDatetime8Not null销售日期SaleNumInt4Not null销售数量SalePriceMoney4Not null销售金额UserIDvarchar10Not nullP顾客编号供货表 （Factory）字段名字段类型长度字段值约束主/外键对应中文名FactoryIDvarchar10Not nullF供货编号FactoryDatevarchar Not null供货日期FactorySLvarchar4Not null供货数量MerCHIDvarchar10Not nullP商品编号ProvideIDvarchar10Not nullP供货商号供货商表 （Provide）字段名字段类型长度字段值约束主/外键对应中文名ProvideIDvarchar10Not nullF供货商号ProvideNamevarchar50Not null供货商名称ProvideAddressvarchar250Not null供货商地址ProvidePhonevarchar25Not null供货商电话仓库表 （Supermaket）字段名字段类型长度字段值约束主/外键对应中文名SupermaketIDint10Not nullF仓库编号SupermaketNamevarchar50Not null仓库名称SupermaketAddressvarchar250Not null仓库地址库存表字段名字段类型长度字段值约束对应中文名KCLvarchar10Not null库存量SPBHvarchar10Not null商品编号CKBHvarchar10Not null仓库编号创建数据库create database SuperMarketdb on primary ( name=SuperMarketdb , filename=C:Program FilesMicrosoft 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 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 TABLE 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 /*创建会员表*/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,SalePrice 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,德芙巧克力,20,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, 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 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, MemberCard , 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(SaleID,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,001,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,伊利,太原,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,二号超市,光明大街);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 (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 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 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 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.ProvideID GO -1.查询顾客表姓名为张一的信息 select *from userswhere username=张一;-2.查询供货商编号为的货物信息select *from Providewhere ProvideID=003-3.为供应商表添入一条数据insert into providevalues(007,豆奶,邯郸,89898989)-4.创建角色Super并对顾客修改,查询和插入的功能赋给角色Supercreate role Supergrant update,select,inserton Usersto Super-5.收回角色Super顾客表插入的功能revoke inserton Usersfrom Super-6.删除角色Superdrop role Super -7.使用exists语句查询供货商地址为唐山的货物编号,货物名称和供货商电话select ProvideID,ProvideName,ProvidePhonefrom Provide where exists (select * from Provide where Provide.Providename=唐山 )-8.建立触发器，当向供应商表插入新信息后，打印出:添加一条供应商信息字样create trigger provide_inserton provide after insertas begin print 添加一条供应商信息endgoinsert into providevalues(008,芝麻糊,石家庄,67676767)-9.创建查看一个超市信息的存储过程create procedure pro_Supermaketas select SupermaketID, SupermaketName, SupermaketAddress from Supermaket;exec pro_Supermaket