实验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 会员表.会员编号=联系方式表.会员编号
 

展开全部
一、数据库系统的三636f707962616964757a686964616f31333433616139级模式：外模式、模式、内模式。
1、模式(逻辑模式、概念模式)：实际上是数据库数据在逻辑级上的视图。描述的是全局逻辑结构。一个数据库只要一个模式。模式是数据库的中心与关键，它独立与其他层次。设计数据库模式结构时应首先确定数据库的逻辑模式。DBMS提供模式描述性语言来严格定义模式。
2、外模式(子模式、用户模式)：数据库用户能够看见和使用的局部数据的逻辑结构和特征，是数据库用户的数据视图。描述的是局部逻辑结构。是模式的一个子集。一个数据库可以有多个数据库内模式。每个用户只能看见和访问对应的外模式中的数据，数据库中的其余数据是不可见的。
是保证数据库安全性的有力措施。同一外模式也可以为用户的多个应用程序使用，但一个应用程序只能使用一个外模式。它定义在逻辑模式之上，但独立于存储模式和存储设备。当应用需求发生较大变化，相应外模式不能满足其视图要求，该外模式就应该做出相应的改变。
设计外模式的时候应充分考虑到应用的扩展性。DBMS提供子模式描述性语言来严格定义子模式。
3、内模式(存储模式)：一个数据库只有一个内模式。它是数据物理结构和存储方式的描述，是数据库内部的表示方法。比如记录什么存储方法存储，索引按照什么方式组织。数据是否压缩存储，是否加密。数据库存储记录结构有何规定。
它依赖于全局逻辑结构，但独立于数据库的用户视图和存储设备。它将全局逻辑结构中所定义的数据结构及其联系按照一定的物理存储策略进行组织，以实现达到较好的时间与空间效率的目的。DBMS提供内模式描述性语言来严格定义内模式。
二、二级映像：
1、外模式/模式映像：数据库系统都有一个外模式/模式映像。它定义了该外模式与模式之间的对应关系。这些映像通常包含在各自外模式的描述中。
当模式改变时(增加新的关系、新的属性、改变属性的数据类型)，需要数据库管理员对各个外模式/模式的映像做相应改变，也可以是外模式保持不变。应用程式是依据数据的外模式编写的。因此应用程序也不必修改，这保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

在一个关系数据库应用系统中，构成该系统的关系数据库的全局逻辑结构的基本表全体，称为该数据库应用系统的关系数据库模式。
数据冗余导致的问题：

冗余存储：信息被重复存储，导致浪费大量存储空间；
	
更新异常：当一个副本被修改，所有副本必须修改；
	
插入异常：只有当一些信息事先已经存放在数据库中时，另外一些信息才能存入数据库中；
	
删除异常：删除某些信息时，可能会丢失其他信息。
模式分解存在的问题：

有损分解：两个分解后的关系通过连接运算还原得到的信息与原来关系的信息不一致；
	
依赖关系丢失
一个好的关系模式应该是：

数据冗余尽可能少；
	
不发生插入异常，删除异常，更新异常等问题；
	
模式分解时，分解后的模式应具有无损连接，保持依赖等特性。
规范化：规范化理论通过分解关系模式来消除其中不合适的数据依赖，已解决数据冗余导致的问题。

 

 

数据库的三种模式分别为外模式、模式、内模式。

       模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。对应与同一个模式可以有任意多个外模式。在数据库中提供两级映象功能，即外模式/模式映象和模式/内模式映象。对于每一个外模式，数据库系统都有一个外模式/模式映象它定义了该外模式与模式之间的对应关系。这些映象定义通常包括在各自外模式的描述中，当模式改变时，由数据库管理员对各个外模式/模式的映象做响应改变，可以使外模式保持不变，从而应用程序不必修改，保证了数据的逻辑独立性。数据库中只有一个模式，也只有一个内模式，所以模式/内模式映象是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系。当数据库的存储结构改变了，由数据库管理员对模式内模式映象做响应改变，可以使模式保持不变，从而保证了数据的物理独立性。