1、实验目的
掌握数据库设计基本方法及数据库设计工具。
2、实验内容及要求
掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计、物理结构设计、数据库模式SQL语句生成。
3、实验步骤
设计一个采购、销售和客户管理应用数据库。其中，一个供应商可以供应多种零件，一个零件也可以有多个供应商。一个客户订单可以订购多种供应商供应的零件。客户和供应商都分属不同的国家，而国家按世界五大洲八大洋划分地区。请设计该数据库。
（1）需求分析
要求实现一个销售应用数据库。在此数据库中7个实体:零件、供应商、顾客、订单、订单明细、国家、地区。
此系统的业务要求是：
其中，一个供应商可以供应多种零件，一个零件也可以有多个供应商。一个客户订单可以订购多种供应商供应的零件。客户和供应商都分属不同的国家，而国家按世界五大洲八大洋划分地区。
该系统的基本信息是：
地区表：地区编号，地区名称，备注
国家表：国家编号，国家名称，地区编号，备注
零件表：零件编号，零件名称，制作商，品牌，零售价格，备注
顾客表：顾客编号，地址，姓名，国家编号，电话，账户余额，备注
供应商表：供应商编号，供应商名称，地址，国家编号，电话，备注，供应商账余额
订单表：订单编号，订单总价，订单状态，订单日期，订单优先级，记账员，备注，顾客编号
订单明细表：订单的详细内容的报表，内容包括：零件编号，订单编号，供应商编号，零件数量，订单明细价格，折扣，备注
还需要产生报表：
零件供应商联系表：供应商与零件联系的表。内容包括零件编号、可用数量、供应价格、供应商编号、备注
（2）数据库概念结构设计
设计E-R图
根据需求分析的结构可知，该E-R模型中各实体所包含的属性如下：
1. 地区表：地区编号，地区名称，备注
2. 国家表：国家编号，国家名称，地区编号，备注
3. 顾客表：顾客编号，地址，姓名，国家编号，电话，账户余额，备注
4．供应商表：供应商编号，供应商名称，地址，国家编号，电话，备注，供应商账余额
5. 订单表：订单编号，订单总价，订单状态，订单日期，订单优先级，记账员，备注，顾客编号
6．订单明细表：订单的详细内容的报表，内容包括：零件编号，订单编号，供应商编号，零件数量，订单明细价格，折扣，备注
7. 零件表：零件编号，零件名称，制作商，品牌，零售价格，备注
8.零件供应商联系表：零件编号、可用数量、供应价格、供应商编号、备注
（3）数据库逻辑结构设计
设计关系模式
把上面的E-R图转换为关系模型
1. 地区表(地区编号，地区名称，备注)
主码:地区编号
2. 国家表(国家编号，国家名称，地区编号，备注)
主码：国家编号
外码：地区编号 为引用地区表的地区编号的外码
关系：国家表属于地区表
3. 顾客(顾客编号，地址，姓名，国家编号，电话，账户余额，备注)
主码：顾客编号
外码：国家编号，为引用国家表的国家编号
关系：国家拥有顾客
4．供应商(供应商编号，供应商名称，地址，国家编号，电话，备注，供应商账余额)
主码：供应商编号
外码：国家编号，为引用国家表的国家编号
关系：国家拥有供应商
5. 订单表(订单编号，订单总价，订单状态，订单日期，订单优先级，记账员，备注，顾客编号)
主码：订单编号
外码：顾客编号，为引用顾客表的顾客编号
关系：顾客签约订单
6．订单明细表（零件编号，订单编号，供应商编号，零件数量，订单明细价格，折扣，备注）
主码：订单编号
外码：订单编号，供应商编号，供应商编号
关系：订单填写订单明细表
7. 零件表（零件编号，零件名称，制作商，品牌，零售价格，备注）
主码：零件编号
外码：制作商引用供应商表的供应商名称
关系：供应商提供零件
8. 零件供应商联系表(零件编号、可用数量、供应价格、供应商编号、备注)
主码：零件编号，供应商编号
关系：零件与供应商的联系表
1)确定关系模式是否是第三范式的：
经分析：地区、国家、顾客、供应商、订单、订单明细、零件都是一个主码，没有部分依赖和传递依赖。属于第三范式。
2)确定信息的完整性：
确定好关系模式的结构之后，接下来要分析一下这些关系模式是否满足生成报表的信息需求。
该系统产生一个报表:零件供应商联系表
分析如下：
零件供应商联系表：内容包括：零件编号、可用数量、供应价格、供应商编号、备注。其中零件编号由零件表获得。供应商编号由供应商获得。
因此，所设计的关系模式满足所有报表的信息要求。
至此，关系模式设计完成
（4）数据库物理结构设计
根据上面设计物理结构
1. 地区表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
地区编号 |
Int |
主码 |
地区编号 |
|
地区名称 |
Varchar（30） |
否 |
地区名称 |
|
备注 |
Varchar（30） |
否 |
Remarks |
2. 国家表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
国家编号 |
Int |
主码 |
Country_id |
|
国家名称 |
Varchar（20） |
否 |
Country_Name |
|
地区编号 |
Int |
外码 |
Region_id |
|
备注 |
Varchar（30） |
否 |
Remarks |
3. 顾客表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
顾客编号 |
Int |
主码 |
Customer_id |
|
顾客姓名 |
Varchar（20） |
否 |
Customer_name |
|
顾客地址 |
Varchar（20） |
否 |
Customer_Address |
|
顾客电话 |
Char(20) |
否 |
Customer_Tel |
|
国家编号 |
Int |
外码 |
Country_id |
|
账户余额 |
Int |
否 |
Customer_remainder |
|
备注 |
Varchar（30） |
否 |
Remarks |
4. 供应商表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
|
供应商编号 |
Int |
主码 |
Supplier_id |
|
|
供应商名称 |
Varchar（20） |
否 |
Supplier_Name |
|
|
供应商地址 |
Varchar（20） |
否 |
Supplier_Address |
|
|
供应商电话 |
Varchar（20） |
否 |
Supplier_Tel |
|
|
国家编号 |
Int |
外码 |
Supplier_nationality |
|
|
供应商账户余额 |
Int |
否 |
Supplier_money |
|
|
备注 |
Varchar（30） |
否 |
Remarks |
|
5. 订单表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
订单编号 |
Int |
主码 |
Order_id |
|
订单状态 |
Varchar（20） |
否 |
Order_State |
|
订单总价 |
Int |
否 |
Totalprice |
|
订单日期 |
Date |
否 |
Order_data |
|
订单优先级 |
Varchar（20） |
否 |
Priority |
|
记账员 |
Varchar（20） |
否 |
Bookkeeper |
|
顾客编号 |
Int |
外码 |
Customer_id |
|
备注 |
Varchar（30） |
否 |
Remarks |
6. 订单明细表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
订单明细编号 |
Int |
主码 |
Order_drid |
|
订单编号 |
Int |
外码 |
Order_id |
|
零件编号 |
Int |
外码 |
Part_Id |
|
供应商编号 |
Int |
外码 |
Supplier_id |
|
零件数量 |
Int |
否 |
Part_number |
|
折扣 |
Float |
否 |
Discount |
|
税率 |
Float |
否 |
Cess |
|
订单明细价格 |
Float |
否 |
Order_ Price |
|
备注 |
Varchar（30） |
否 |
Remarks |
7. 零件表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
零件编号 |
Int |
主码 |
Part_id |
|
零件名称 |
Varchar（20） |
否 |
Part_name |
|
制作商 |
Varchar（20） |
否 |
Manufacture |
|
品牌 |
Varchar（20） |
否 |
Brand |
|
零售价格 |
Int |
否 |
Price |
|
备注 |
Varchar（30） |
否 |
Remarks |
8. 零件供应商联系表
|
列名 |
数据类型 |
主码、外码 |
说明 |
|
零件编号 |
Int |
主码 |
Part_id |
|
供应商编号 |
Int |
主码 |
Supplier_id |
|
可用数量 |
Int |
否 |
Number |
|
供应价格 |
Int |
否 |
Supplier_price |
|
备注 |
Varchar（30） |
否 |
Remarks |
（5）数据库模式SQL语句生成
create database 销售应用数据库 use 销售应用数据库 --1.地区表 CREATE TABLE 地区表 (地区编号 int PRIMARY KEY, 地区名称 varchar(30), 备注 varchar(30)default \'该地区无备注\'); --默认无备注 --2.国家表 CREATE TABLE 国家表 (国家编号 int PRIMARY KEY, 国家名称 varchar(20), 地区编号 int, 备注 VARCHAR(30) default\'该国家无备注\', FOREIGN KEY(地区编号) REFERENCES 地区表(地区编号)); --3.顾客表 CREATE TABLE 顾客表 (顾客编号 int PRIMARY KEY, 顾客姓名 varchar(20), 顾客地址 varchar(20), 顾客电话 char(20), 账户余额 int check(账户余额>0), 国家编号 int, 备注 varchar(20) default\'该顾客无备注\', FOREIGN KEY(国家编号) REFERENCES 国家表(国家编号)); --4.供应商表 CREATE TABLE 供应商表 (供应商编号 int PRIMARY KEY, 供应商名称 varchar(20), 供应商地址 varchar(20), 供应商电话 varchar(20), 供应商账户余额 int check(供应商账户余额>0), 国家编号 int, 备注 varchar(30) default\'该供应商无备注\', FOREIGN KEY(国家编号) REFERENCES 国家表(国家编号)); --5. 订单表 CREATE TABLE 订单表 (订单编号 int PRIMARY KEY, 顾客编号 int, 订单状态 varchar(20), 订单总价 int, 订单日期 DATE, 订单优先级别 varchar(20), 记账员 varchar(20), 备注 VARCHAR(100) default\'该订单无备注\', FOREIGN KEY(顾客编号) REFERENCES 顾客表(顾客编号)); --7. 零件表 CREATE TABLE 零件表 (零件编号 int PRIMARY KEY, 零件名称 varchar(20), 制作商 varchar(20), 品牌 varchar(20), 零售价格 int check(零售价格>0), 备注 VARCHAR(100)default\'该零件表无备注\'); --6. 订单明细表 CREATE TABLE 订单明细表 (订单明细编号 int PRIMARY KEY, 订单编号 int, 零件编号 int, 供应商编号 int, 零件数量 int not null, 折扣 float check(折扣>=0.00 and 折扣<=1.00), 税率 float check(税率>=0.00 and 税率<=0.08), 订单明细价格 float not null, 备注 VARCHAR(100)default\'该订单明细表无备注\', FOREIGN KEY(订单编号) REFERENCES 订单表(订单编号), FOREIGN KEY(零件编号) REFERENCES 零件表(零件编号), FOREIGN KEY(供应商编号) REFERENCES 供应商表(供应商编号) ); --8 零件供应商联系表 CREATE TABLE 零件供应商联系表 (零件编号 int, 供应商编号 int, 可用数量 int not null, 供应价格 int not null, 备注 VARCHAR(100), PRIMARY KEY(零件编号,供应商编号));
（6）数据库行为设计
对于数据库应用系统来说，最常用的功能就是安全控制功能、数据的增、删、改、查功能以及报表的功能。本系统也应包含这些基本的操作。
1) 安全控制
把系统的用户分为几类：
系统管理员：拥有对系统的全部权限
顾客:拥有自己信息的查改等，购买零件订单的权限
供应商：拥有对基本信息的维护权，提供零件
用户：对数据的查询权
在实现时，可将每一类用户定义为一个角色，这样在授权时只需对角色授权，而无须对每个具体的用户授权。
2）数据操作功能：
增删改查
数据录入：对数据的录入，比如对地区表操作
insert into 地区表(地区编号,地区名称)values(1,\'亚洲\'); insert into 地区表(地区编号,地区名称)values(2,\'美洲\'); insert into 地区表(地区编号,地区名称)values(3,\'澳洲\'); select* from 地区表;
数据修改：对数据修改，比如修改地区表
update地区表 set地区编号=5,地区名称=\'非洲\'where地区编号=3;
数据删除：对数据删除，比如删除地区表id=5
delete from 地区表 where地区编号=5;
查询操作：对数据查询，比如查询地区表
select地区名称 from地区表
7 实验小结
通过本次实验，我学习了数据库设计，如何从0开始设计一个要使用的符合规范的数据库。刚开始不知道怎样做，随着老师的教课和讲解，以及和同学的讨论，再结合自己所知道的知识和老师所发放下的课程内容，根据这些实际的情况，我对自己将要做的事也有了兴趣和信心。
7个实体:零件、供应商、顾客、订单、订单明细、国家、地区。