SQLServer数据库实验——实验10 数据库系统的逻辑设计与物理设计

实验10 数据库系统的逻辑设计与物理设计

一、实验目的
1．能够将E-R图转换为对应的关系模式。
2．能够对关系模式进行规范化的分析和验证。
3．能够在业务需求发生变化时正确调整关系模式。
4．能够将关系模式图转换为相关数据库管理系统的DDL语句。
5．能够向建立好的数据库中添加测试数据。
6．能够根据业务需求建立相关的视图。

二、实验内容
根据概要设计所得的全局E-R图，完成如下实验。
1． 根据已经绘制的全局E-R图，通过E-R图到关系模式的转换方法，将全局E-R图转换为关系模式，并注明每个模式的主键和外键。

（1）	商品表（商品编号，商品名称，商品价格，详情信息，商品图片）主键：商品编号
（2）	类别表（类别号，类别名称）主键：类别号
（3）	属于表（商品编号，类别号）主键：商品编号，类别号 外键：商品编号，类别号
（4）	会员表（会员编号，会员姓名，会员性别，会员邮箱）主键：会员编号
（5）	订购表（订单编号，商品编号，会员编号，数量，提交时间）   
主键：订单编号  外键：商品编号，会员编号
（6）	状态表（状态号，状态名）主键：状态号
（7）	操作表（订单编号，状态号）主键：订单编号，状态号 外键：订单编号，状态号
（8）	联系方式表（电话，邮编，地址）主键：电话
（9）	联系表（会员编号，电话）外键：会员编号，电话
（10）	厂家表（厂家编号，厂家名称）主键：厂家编号
（11）	生产表（商品编号，厂家编号，上架时间）
主键：商品编号，厂家编号 外键：商品编号，厂家编号

2． 对转换后的关系模式进行优化。

（1）	商品表（商品编号，商品名称，商品价格，详情信息，商品图片，类别号）
（2）	类别表（类别号，类别名称）
（3）	订购表（订单编号，商品编号，会员编号，数量，提交时间，状态号）
（4）	状态表（状态号，状态名）
（5）	会员表（会员编号，会员姓名，会员性别，会员邮箱，电话）
（6）	联系方式表（电话，邮编，地址）
（7）	厂家表（厂家编号，厂家名称）
（8）	生产表（商品编号，厂家编号，上架时间）

3． 使用数据规范化分析方法，分析转换后的模式属于第几范式。

3NF

4．在与客户进行数据库的确认工作时，商品管理部门发现现有设计中遗漏了商品的库存信息。需要在现有商品中添加库存信息。添加库存后的商品记录如图6-29所示。请修改现有E-R图，并调整转换后的关系模式。

                    图6-29  添加库存后的商品记录信息

（1）	类别表（类别号，类别名称）
（2）	商品表（商品编号，商品名称，商品价格，详情信息，商品图片，类别号）
（3）	会员表（会员编号，会员姓名，会员性别，会员邮箱）
（4）	联系方式表（会员编号，电话，邮编，地址）
（5）	状态表（状态号，状态名）
（6）	厂家表（厂家编号，厂家名称）
（7）	生产表（商品编号，厂家编号，上架时间）
（8）	仓库表（商品编号，库存量）
（9）	订购表（订单编号，商品编号，会员编号，数量，提交时间，状态号）

根据数据库系统逻辑设计所得的关系模式，完成如下实验。
4． 以SQL Server 2008为系统将要部署的数据库管理系统，把逻辑设计所得的关系模式转换成数据库系统的DDL语句，具体包括：数据库创建的DDL、各种实体创建的DDL和多对多联系创建的DDL等。

（1） 创建一个Test数据库

CREATE DATABASE Test;

（2） 创建各个数据表

创建类别表：
CREATE TABLE 类别表
( 类别号 char(10) primary key,                 
 类别名称 varchar(10));

创建商品表：
CREATE TABLE 商品表
( 商品编号 char(10) primary key,                 
 商品名称 varchar(10),  
 商品价格 int, 
 详情信息 varchar(20),
 商品图片 image,
 类别号 char(10)
primary key(商品编号,类别号),
FOREIGN KEY(类别号) REFERENCES 类别表(类别号)
);

创建会员表：

CREATE TABLE 会员表
( 会员编号varchar(10)  primary key,                 
 会员姓名varchar(10),  
 会员性别char(4),
 会员邮箱varchar(20),
);

创建联系方式表：

CREATE TABLE 联系方式表
(会员编号 varchar(10) primary key,
电话 varchar(20),                 
  邮编 varchar(10),  
地址 char(20)
FOREIGN KEY(会员编号) REFERENCES 会员表(会员编号)

创建状态表：

CREATE TABLE 状态表
( 状态号 char(10)primary key,                 
 状态名 varchar(10));

创建厂家表：

CREATE TABLE 厂家表
( 厂家编号 char(10) primary key,                 
 厂家名称 char(10));

创建生产表：

CREATE TABLE 生产表
(
商品编号 char(10),
厂家编号 char(10),
上架时间 date,
primary key(商品编号,厂家编号),
FOREIGN KEY(商品编号) REFERENCES 商品表(商品编号),
FOREIGN KEY(厂家编号) REFERENCES 厂家表(厂家编号));

创建仓库表：

CREATE TABLE 仓库表
( 商品编号 char(10)primary key,
	库存量 int,
	FOREIGN KEY(商品编号) REFERENCES 商品表(商品编号)
);

创建订购表：

CREATE TABLE 订购表
(订单编号  varchar(10),
会员编号 varchar(10),
商品编号 char(10),
数量 int,
提交时间 date,
状态号 char(10),
primary key(订单编号,会员编号,商品编号,状态号),
FOREIGN KEY(会员编号) REFERENCES 会员表(会员编号),
FOREIGN KEY(商品编号) REFERENCES 商品表(商品编号),
FOREIGN KEY(状态号) REFERENCES 状态表(状态号));

5． 向已经创建好的数据中添加测试数据，添加记录的数量不限，只需有代表性即可。
类别表：

商品表：

会员表：

联系方式表：

状态表：

生产表：

厂家表：

仓库表：

订购表：

7．创建视图，显示每个订单的总价。

CREATE VIEW 订单总价
 AS
  SELECT
    订单编号,商品名称,商品价格,数量,会员姓名,电话,会员邮箱,地址, 商品价格*数量 As 总价
  FROM
    订购表,商品表,会员表,联系方式表
  WHERE 订购表.商品编号=商品表.商品编号 AND 订购表.会员编号=会员表.会员编号 AND 会员表.会员编号=联系方式表.会员编号