数据库的综合实验

一、实验内容与要求

某公司因业务扩展需要开发一套电子商务系统（E-commerce system），用于在线销售各类商品。作为数据库设计人员，通过走访与跟班作业的方式，从商品销售部和商品管理部获得了如下业务信息。

商品管理部的业务信息

商品管理部负责管理销售的各类商品。目前公司所有可供销售的商品（Product）都记录在Excel表格中。

Excel表格中每条记录的主要内容包括：商品编号（P_id）、商品名称（P_Name）、商品类别（P_ Category）、商品价格（P_ Price）、生产厂家（P_Facturer）、入库时间（P_time）、商品的详细信息（P_ Descn）、商品的缩略图（P_ Image）。其中，商品类别包括图书、手机、数码影像和电脑等。商品的缩略图为jpg或png类型的图片。生产厂家根据商品类型表达的含义略有差异。如果是图书类型的商品，则生产厂家表示出版社。如果是其他类型商品，生产厂家即为实际生产机构。Excel中商品记录的示例信息如图1所示。

图1 商品记录的示意信息

商品销售部的业务信息

商品销售部负责销售各类商品并对每次销售的结果进行记录。目前公司所有销售结果都记录Excel表格中。

销售过程为：购买人下单（Generate）生成订单，然后再从全部商品中挑选（Pick）部分商品加入到订单中。

业务规则：

一个购买人可以下多个订单，不同的购买人不能生成同一个订单，一个订单可以包含（挑选）多个商品，同一个商品可以被挑选到多个订单中。

挑选过程由销售记录体现出来，由三部分内容构成，分别是订单的基本信息（Order）、订单的购买人信息（Customer）和挑选商品信息，一个销售记录的示例信息如图2所示。

订单的基本信息包括：订单编号（O_id）、订单的提交时间（O_Time）和订单的当前状态（O_Status）。其中，订单编号为17位数字，前8位为当前日期，后9位为按订单提交顺序生成的编码，该编号能够唯一标识每一条销售记录；订单提交时间精确到秒；订单状态包括：已提交、已发货、已完成等。

订单的购买人信息包括：购买人的姓名（C_Name）、购买人的性别（C_Sex）、购买人的联系方式（C_Contact）、购买人的电子邮箱（C_Email）。其中，电子邮箱确定唯一购买人，购买人的联系方式统一存储了购买者的送货地址（C_Addr）、邮政编码（C_Zip）和购买人的手机号码（C_phone）。

挑选商品信息包括：（1）商品基本信息：编号（P_id）、名称（P_Name）、类别（P_Category）、商品的缩略图（Image），上述信息来自于商品管理部所记录的商品信息；（2）挑选信息：挑选数量（Qty）、实际销售单价（P_ Price1）。

图2 销售记录的示例信息

1.根据以上描述，利用数据库系统基础知识分析如下问题。

（1）分析商品信息中编号为“PC1”和购物人“吴*”的数据分别是什么？

（2）目前以上数据表的应用属于哪个发展阶段？

（3）该数据库系统组成具体有哪些？

（4）该数据库系统的内部体系结构分别包括哪些？

（5）该数据库系统的外部体系结构是什么？

（6）该系统属于哪一种数据模型？其要素具体包括什么？ 

（7）根据销售过程，请在信息世界中分析有哪些实体（实体顺序按照销售过程标出）？根据业务规则，分析这些实体之间的联系，并指明联系的类型，注：用中文表示。

（8）根据题（7）的分析结果，说明该系统有哪些关系，并结合图2用形如R（A1，A2，…，An）写出关系模式，并指明每个关系的主键（主码），注：用中文表示。

（9）你认为开发该系统的前沿或热点技术是什么？需要解决哪些技术难题？

2.根据第1题（8）分析的关系模式，用关系代数表达式表示以下各种查询要求。

（1）查询王丽丽所选商品的商品编号和商品名称。

（2）查询已完成的订单编号。

（3）查询购买商品名称为“计算机图形学”的相关信息，包括订单编号、购买人姓名、购买数量、实际销售单价。

（4）查询所有女性购买人全部购买信息，包括购买人姓名、订单编号、商品名称、购买数量。

（5）查询“张艳”本年度的订单信息，包括订单编号、当前状态。

（6）查询“张艳”购买商品情况，包括订单编号、商品名称、购买数量。

（7）查询商品价格小于1000的商品名称、价格和详细信息。

（8）查询姓名为“张军”购买人的联系方式和电子邮箱。

3.根据以上数据表，用SQL完成以下任务，注意：以下所有名称不能用中文，

（1）使用SQL命令创建数据库，数据库的名称设为自己的学号。

（2）创建数据库之后，将主数据文件的增长上限改为100MB。

（3）根据第1（8）题得到的关系模式，分别以下表的形式给出每个关系模式的各字段的属性定义和说明。

XXX表

 

（4）根据第3（3）题的结果，使用SQL命令在该数据库中创建各个表。

（5）在商品表中插入图1的数据。

（6）查询各商品的商品编号、分类和名称。

（7）查询自己的订单的全部信息。

（8）查询数据库中有哪些商品分类。

（9）查询价格大于100的商品信息。

（10）查询在本年度的商品的编号、名称和价格。

（11）查询有三个订单以上并且每个订单都在50元分以上客户姓名、订单号。

（12）查询姓张的客户的姓名和联系方式。

（13）对数据表Product添加新列：商品库存（P_Qty），其数据类型为整型，约束为大于0。

（14）查询没有订单的客户姓名和电子邮箱。

（15）查询电子邮箱为EE0538@163.COM的客户的订单编号。

（16）查询价格不超过50元的图书信息。

（17）统计每个购买人每年（订单年份）购买商品的总数量，查询结果模型如下。

购买人邮箱	年份	商品总数量
Lxy@qq.com	2021	254
zjj@126.net	2020	68
WJZ@163.com	2020	367
...	...	...

（18）查询2021年入库的手机信息。

（19）统计各类商品每年（订单年份）的总利润，查询结果模型如下。（使用连接查询和子查询方式）

商品类别	年份	总利润
手机	2021	18524
手机	2020	15245
图书	2020	2354
...	...	...

（20）删除客户张三已完成的订单。

（20）删除客户张三已完成的订单。

（21）为表Customer在C_Name上建立非聚集索引。

（22）查询选购商品号为K002的客户姓名和联系方式（使用连接查询、普通子查询、使用exists关键字的相关子查询）。

（23）在客户表中添加一条自己的记录。

（24）将所有两年前的商品价格改成8.5折。

（25）创建每个手机类商品销售情况视图V1，显示信息包括编号、名称、总销售数量。