实验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\SuperMarketdb.mdf', size =100MB , maxsize =200MB , filegrowth =20MB ) log on (
name =SuperMarketlog ,
filename ='C:\Program Files\Microsoft SQL Server\MSSQL\Data\SuperMarketdb.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_MerchInfo AS
SELECT MerchID as 商品编号, MerchName as 商品名称, MerchPrice as 商品价格, BarCode as 条形码 FROM MerchInfo GO
创建用于查询销售明细记录的视图
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_Factory
AS
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 users
where username=' 张一' ;
--2. 查询供货商编号为的货物信息
select *
from Provide
where ProvideID='003'
--3. 为供应商表添入一条数据
insert
into provide
values ('007' , ' 豆奶' , ' 邯郸' , '89898989'
)
--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 Provide. Providename =' 唐山' )
--8. 建立触发器,当向供应商表插入新信息后,打印出:添加一条供应商信息字样
create trigger provide_insert
on provide after insert
as begin print ' 添加一条供应商信息'
end
go
insert into provide
values ('008' , ' 芝麻糊' , ' 石家庄' , '67676767'
)
--9. 创建查看一个超市信息的存储过程
create procedure pro_Supermaket
as
select SupermaketID, SupermaketName, SupermaketAddress from Supermaket;
exec pro_Supermaket
实验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\SuperMarketdb.mdf', size =100MB , maxsize =200MB , filegrowth =20MB ) log on (
name =SuperMarketlog ,
filename ='C:\Program Files\Microsoft SQL Server\MSSQL\Data\SuperMarketdb.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_MerchInfo AS
SELECT MerchID as 商品编号, MerchName as 商品名称, MerchPrice as 商品价格, BarCode as 条形码 FROM MerchInfo GO
创建用于查询销售明细记录的视图
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_Factory
AS
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 users
where username=' 张一' ;
--2. 查询供货商编号为的货物信息
select *
from Provide
where ProvideID='003'
--3. 为供应商表添入一条数据
insert
into provide
values ('007' , ' 豆奶' , ' 邯郸' , '89898989'
)
--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 Provide. Providename =' 唐山' )
--8. 建立触发器,当向供应商表插入新信息后,打印出:添加一条供应商信息字样
create trigger provide_insert
on provide after insert
as begin print ' 添加一条供应商信息'
end
go
insert into provide
values ('008' , ' 芝麻糊' , ' 石家庄' , '67676767'
)
--9. 创建查看一个超市信息的存储过程
create procedure pro_Supermaket
as
select SupermaketID, SupermaketName, SupermaketAddress from Supermaket;
exec pro_Supermaket