连锁超市数据库及代码

连锁超市数据库及代码 The Standardization Office was revised on the afternoon of December 13, 2020

实验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)

顾客表（User）

会员表 (Member)

销售表（Sale）

供货表（Factory）

供货商表（Provide）

仓库表（Supermaket）

库存表

创建数据库

create database SuperMarketdb

on primary(

name=SuperMarketdb ,

filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\', size=100MB,

maxsize=200MB,

filegrowth=20MB )

log on(

name=SuperMarketlog,

filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\', 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','');

INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode) VALUES('002','上好佳','2','');

INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode) VALUES('003','火腿肠','5','');

INSERT INTO MerchInfo(MerchID,MerchName,MerchPrice,BarCode) VALUES('004','面包','4','');

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','蒙牛','内蒙古','');

INSERT INTO provide(ProvideID,ProvideName,ProvideAddress,ProvidePhone) VALUES('004','伊利','太原','');

INSERT INTO provide(ProvideID,ProvideName,ProvideAddress,ProvidePhone) VALUES('005','飞鹤','唐山','');

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_MerchInfo

AS

SELECT MerchID as商品编号,

MerchName as商品名称,

MerchPrice as商品价格,

BarCode as条形码

FROM MerchInfo

GO

创建用于查询销售明细记录的视图

CREATE VIEW v_Sale

AS

SELECT as商品名称,

as条形码,

as商品价格,

as销售金额,

as销售数量,

as销售日期

FROM Sale INNER JOIN

MerchInfo ON=

GO

创建用于供货情况的视图

CREATE VIEW v_Factory

AS

SELECT as供货数量,

as供货日期,

as供货商,

as供货地址,

as供货电话

FROM Factory INNER JOIN

Provide ON=

GO

--1.查询顾客表姓名为张一的信息

select*

from users

where username='张一';

--2.查询供货商编号为的货物信息

select*

from Provide

where ProvideID='003'

--3.为供应商表添入一条数据

insert

into provide

values('007','豆奶','邯郸','')

--4.创建角色Super并对顾客修改,查询和插入的功能赋给角色Super

create role Super

grant update,select,insert

on Users

to Super

--5.收回角色Super顾客表插入的功能

revoke insert

on Users

from Super

--6.删除角色Super

drop role Super

--7.使用exists语句查询供货商地址为唐山的货物编号,货物名称和供货商电话

select ProvideID,ProvideName,ProvidePhone

from Provide

where exists

(select*from Provide

where='唐山')

--8.建立触发器，当向供应商表插入新信息后，打印出:添加一条供应商信息字样

create trigger provide_insert

on provide after insert

as begin print'添加一条供应商信息'

end

go

insert into provide

values('008','芝麻糊','石家庄','')

--9.创建查看一个超市信息的存储过程

create procedure pro_Supermaket

as

select SupermaketID, SupermaketName, SupermaketAddress from Supermaket; exec pro_Supermaket