数据库课程设计光盘在线销售系统

西南交通大学本科毕业设计（论文） 第 1 页光盘在线销售系统设计步骤一、需求分析阶段光盘在线销售数据库系统是面向所有顾客的数据管理系统，其目的是使顾客订购光盘更方便，更快捷，更愉快。本系统主要包括以下几个模块：光盘参数、光盘价格、库存数量以及买家评价信息这四个功能模块基本实现设计本系统的目的，从而可以进一步满足机场对乘客登机管理的要求。二、概念设计阶段在光盘在线销售系统应包括光盘的基本信息、光盘价格信息、以及仓库的库存信息和顾客的基本资料等，为了促进与顾客的交流，应该在顾客购物完成后，让顾客给本系统提出宝贵的建议，还需要设置一个评论环节。因此需要设计五个模块。可设 3 个实体：光盘、仓库、顾客。联系类型有：仓库与光盘之间的联系，价格与光盘型号之间的联系，顾客与光盘之间的联系以及顾客对光盘的评价等。他们之间的关系为：一个仓库可以存放多种光盘，一种光盘也可以存放在多个仓库（M:N） ；一张光盘只有一个价格，一个价格可以对应多张光盘（1：N） ；一个顾客可以购买多张光盘，而一张光盘只能对应一个顾客（1：N ） ；一个评论只能是一个顾客对一张光盘的发出的，一个顾客对一张光盘发出的只能有一个评论（1：N） 。实体及其联系 见图 1.实体与联系应具有的属性 见图 2。N1光盘N1N1111M顾客评论 购买仓库库存价格具有图 1 实体及其联系图西南交通大学本科毕业设计（论文） 第 2 页三、逻辑设计阶段根据 ER 模型转换成关系模型的规则，可把上述 ER 图转换成 6 个关系模式，从而实现对光盘在销售 ER 模型向关系数据模型的转换，具体如下：顾客（顾客账号，昵称，姓名，电话，地址，注册日期，积分）光盘（光盘编号，所属公司， 类型，读写性，容量，厚度，直径，材质）仓库（仓库编号，容量，可用容量）销售（收据编号，顾客账号，光盘编号，日期，售价，数量，顾客评价）价格（光盘编号，标价，最低售价）库存（光盘编号，仓库编号）冗余问题检查：在各关系模式中，可得出每个关系的属性值都是不可分的，因此各关系模式都是规范化关图 2 光盘在线销售系统的 ER 图顾客账号昵称注册日期电话 地址积分光盘编号 所属公司类型读写性容量厚度直径材质仓库编号可用容量 最低售价 光盘编号标价容量N1光盘N1N1111M顾客评论 销售仓库库存价格具有西南交通大学本科毕业设计（论文） 第 3 页系；又可从以上各关系模式中可得每个属性都不传递依赖于各关系模式的候选键，且转换后各个属性值的数据并没有丢失，数据共享率高，所以在 ER 模型向关系模式转化时不存在冗余问题。四、物理设计阶段4.1 创建数据库【CourseDesign 】创建一个名为 CourseDesign 的数据库：CREATE DATABASE CourseDesign4.2 创建各个表格用 Microsoft SQL 2000 Server 数据库系统的语句在数据库CourseDesign 中定义创建每个表：4.2.1 创建表 顾客【customer】顾客（顾客账号，昵称，姓名，邮箱，年龄，电话，职业，地址，注册日期，积分）其中顾客账号为主码， 昵称 、 邮箱 、 注册日期和积分不允许为空，其他的可以为空，没有外码。代码如下：CREATE TABLE coursedesign.dbo.customer (ID varchar (20) PRIMARY KEY,pet_name varchar (20) NOT NULL,name varchar (20),e_mail varchar (50) NOT NULL,age smallint,telephone varchar (13),profession varchar (20),address varchar (50),when_register numeric (8) NOT NULL,credit int NOT NULL);在企业管理器里面查看该表的属性，结果如右图：4.2.2 创建表 光盘【light_disk】假设光盘是按照光盘编号进行区分的，每张光盘都有自己的光盘编号。光盘（光盘编号，所属公司，类型，读写性，容量，厚度，直径，材质）西南交通大学本科毕业设计（论文） 第 4 页这里的类型只有这几种：CD-Audio, CD-Video, CD-ROM, DVD-Audio,DVD-Video, DVD-ROM,CD-R, CD-RW, DVD-R, DVD+R,DVD+RW, DVD-RAM, Double layer DVD+R，所以要用 CHECK 语句将 type 限制在这几种类型当中；对于容量 ，我们目前只销售这几种容量的光盘，700M，3G，4.7G，8.5G，15G，25G，30G ，50G ，所以得将其用 CHECK 语句加以限制；同理，光盘直径只有 60 毫米和 120 毫米的两种，没有第三种了，所以也必须用 CHECK 语句限制。其中 NO 为主码，其他的属性都不允许空，没有外码。代码如下：CREATE TABLE CourseDesign.dbo.light_disk (NO varchar (20) PRIMARY KEY,company varchar (20) NOT NULL,type varchar (20) NOT NULL,canRW varchar (20) NOT NULL,volume varchar (5) NOT NULL,diameter smallint NOT NULL,making varchar (50) NOT NULL,CHECK (type IN (CD-Audio,CD-Video,CD-ROM,DVD-Audio,DVD-Video,DVD-ROM,CD-R,CD-RW,DVD-R,DVD+R,DVD+RW,DVD-RAM,Double layer DVD+R),CHECK (volume IN (700M,3G,4.7G,8.5G,15G,25G,30G,50G),CHECK (diameter IN (60,120),); 在企业管理器里面查看该表的属性，结果如下：4.2.3 创建表 仓库【storage】仓库（仓库编号，容量，可用容量）其中仓库编号为主码，其他的属性都不允许为空，没有外码。代码如下：CREATE TABLE CourseDesign.dbo.storage(identifier smallint PRIMARY KEY,西南交通大学本科毕业设计（论文） 第 5 页volume smallint NOT NULL,surplus smallint NOT NULL,CHECK (surpluslow_price),FOREIGN KEY (NO) REFERENCES CourseDesign.dbo.light_disk(NO); 在企业管理器里面查看该表的属性，结果如下：4.2.5 创建表 库存【repertory 】库存（光盘编号，仓库编号）其中， 光盘编号和仓库编号合起来构成了主码，同时他也都是外码， 光盘编号引用了表【光盘】的主属性光盘编号 ， 仓库编号引用了表【仓库】的主属性仓库编号 。没有其它的属性了。代码如下：CREATE TABLE CourseDesign.dbo.repertory(NO varchar (20) NOT NULL,identifier smallint NOT NULL,PRIMARY KEY (NO,identifier),西南交通大学本科毕业设计（论文） 第 6 页FOREIGN KEY (NO) REFERENCES CourseDesign.dbo.light_disk(NO),FOREIGN KEY (identifier) REFERENCES CourseDesign.dbo.storage(identifier);在企业管理器里面查看该表的属性，结果如下：4.2.6 创建表 销售【sell】 销售（收据编号，顾客账号，光盘编号，日期，售价，数量，顾客评价）其中， 收据编号是主码， 顾客账号和光盘编号是外码， 顾客账号引用了表【顾客】的主属性顾客账号 ， 光盘编号引用了表【光盘】的主属性光盘编号 ， 顾客评价可以为空，其他属性都不允许为空。代码如下：CREATE TABLE CourseDesign.dbo.sell(sell_NO varchar (20) PRIMARY KEY,ID varchar (20) NOT NULL,NO varchar (20) NOT NULL,sells_date numeric (8) NOT NULL,sells_price smallint NOT NULL,appraise varchar (500),FOREIGN KEY (ID) REFERENCES CourseDesign.dbo.customer(ID),FOREIGN KEY (NO) REFERENCES CourseDesign.dbo.light_disk(NO);在企业管理器里面查看该表的属性，结果如下：4.3 向表格插入数据用插入语句 INSERT INTO VALUES( )向各个表中插入数据。西南交通大学本科毕业设计（论文） 第 7 页4.3.1 向表【 顾客】插入数据也就是用户注册。假设已经有 10 个用户注册了。注：注册日期的类型为固定 8 位的数据，所以格式为 年月日，中间没有任何分隔符；新注册的用户积分为 0 。代码略，插入结果如下：4.3.2 向表【 光盘】插入数据代码略，插入结果如下（假设只有这几张光盘）：4.3.3 向表【 仓库】插入数据代码略，插入结果如下（假设只有 3 个仓库）：4.3.4 向表【 价格】插入数据进货后，要给每一张光盘定价格。西南交通大学本科毕业设计（论文） 第 8 页代码略，插入结果如右图：4.3.5 向表【 库存】插入数据将刚刚进货的光盘存放在库存里面。代码略，插入结果如右图：4.3.6 向表【 销售】插入数据收据编号由销售事件发生时的年月日时分秒和一个 4 位的随机数构成。假设某一段时间，销售了 10 张光盘。代码略，插入结果如下：五、数据库实施阶段5.1 查询数据5.1.1 多表、嵌套查询第一个在本系统注册的用户的账号、昵称和邮箱。代码如下：SELECT ID as 账号,pet_name AS 昵称,e_mail AS 邮箱 FROM coursedesign.dbo.customer AS F1WHERE F1.when_register =(SELECT MIN(F2.when_register)FROM coursedesign.dbo.customer AS F2);查询结果如右图：西南交通大学本科毕业设计（论文） 第 9 页5.1.2 LIKE 语句查询地址在重庆的顾客的账号、昵称和地址。代码如下：SELECT ID as 账号,pet_name AS 昵称,address AS 地址FROM coursedesign.dbo.customerWHERE address LIKE %重庆 %;查询结果如右图：5.1.3 视图、 COUNT GROUP、连接查询购买光盘次数最多的顾客的账号、昵称、年龄和职业先创建一个视图，统计每个顾客购买光盘的数量，再根据统计信息，找出其中购买数量最大的值对应的顾客账号，再根据顾客账号对应到其个人资料。代码如下：CREATE VIEW COU_SELL ASSELECT ID,COUNT(ID) AS D0FROM coursedesign.dbo.sellGROUP BY IDSELECT F1.ID AS 账号,pet_name AS 昵称,age AS 年龄,profession AS 职业FROM coursedesign.dbo.customer AS F1,COU_SELL AS F2WHERE F1.ID=F2.ID AND F2.D0 IN (SELECT MAX(D0) FROM COU_SELL);DROP VIEW COU_SELL查询结果如右图：5.1.4 ORDER BY 语句查询所有顾客的出生年份。代码如下：SELECT name AS 姓名,2011-age AS 出生年份FROM coursedesign.dbo.customerORDER BY 出生年份;查询结果如右图：5.1.5 等值连接查询购买过本系统的光盘的顾客的账号和昵称。代码如下：西南交通大学本科毕业设计（论文） 第 10 页SELECT DISTINCT F2.ID AS 账号,pet_name AS 昵称FROM coursedesign.dbo.customer AS F1,coursedesign.dbo.sell AS F2WHERE F1.ID=F2.ID;查询结果如右图：5.1.6 嵌套、等值连接查询购买光盘价格最高的顾客的昵称。代码如下：SELECT F1.pet_name AS 昵称FROM coursedesign.dbo.customer AS F1,coursedesign.dbo.sell AS F2WHERE F1.ID=F2.ID AND F2.sells_price=(SELECT MAX(sells_price) FROM coursedesign.dbo.sell);查询结果如右图：5.1.7 单表比较查询在今年五月份才注册的顾客的账号、昵称、邮箱和职业。代码如下：SELECT ID AS 账号,pet_name AS 昵称,e_mail AS 邮