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

一、实验内容与要求

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

1.商品管理部的业务信息

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

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

2.商品销售部的业务信息

商品销售部负责销售各类商品并对每次销售的结果进行记录。目前公司所有销售结果都记录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”和购物人“吴*”的数据分别是什么？

(商品编号，PC1)、（购买人姓名,吴*）

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

文件系统阶段

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

数据库、数据库用户、计算机硬件系统和计算机软件系统

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

三级模式结构，即外模式（用户模式）、模式（概念模式）和内模式（物理模式）。

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

客户机/服务器结构

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

关系模型。其要素包括关系结构（即二维表）、关系操纵（主要包括查询、插入、删除和修改数据）、完整性约束（实体完整性、参照完整性和用户定义的完整性）

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

实体：购买人、下单、订单、商品、挑选。其中商品和购买人是多对多联系，购买人和订单是一对多联系。

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

购买人（姓名，性别，联系方式，电子邮箱（主码））

订单（订单编号（主键），订单的提交时间，订单的当前状态）

商品（商品编号（主码），商品名称，商品类型，商品价格，生产产家，入库时间，商品的详细信息，商品的缩略图）

挑选（订单编号，订单的提交时间，订单的当前状态，购买人的姓名，购买人的性别，购买人的联系方式，购买人的电子邮箱，商品的编号，商品的名称，商品的类别，商品的缩略图，商品的挑选数量，商品的实际销售单价）【其中主码为（订单编号，商品编号，购买人的电子邮箱）】

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

大数据技术。一是数据量大。日积月累下的购物信息数据量十分巨大，导致其中进行数据分析的难度较高。

二是数据处理速度要快。当今时代日息万变，大数据时代的很多应用，效率是核心，只有及时处理购物数据，才能更有效地进行商业指导。三是数据价值密度低，要实现“大数据淘金”。大数据中具有很多有价值的潜在信息，但其价值的密度远远低于传统关系型数据库的数据价值。

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

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

ΠP_id,P_Name(σ c_Name=’王丽丽’（Product⋈Pick⋈Customer）)

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

ΠO_id(σ O_Status=’已完成’（Order1）)

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

ΠO_id,C_Name,Qty,P_Price1(σ P_Name=’计算机图形学’（Product⋈Pick⋈Customer）)

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

ΠC_Name,O_id,P_Name,Qty(σ c_Sex=’女’（Product⋈Pick⋈Customer）)

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

ΠO_id,O_Status(σ c_Name=’张艳’^O_Time>=’2023/1/1 00:00:00’^O_Time<=’2023/12/31 23:59:59’（Order1⋈Pick⋈Customer）)

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

ΠO_id,P_Name,Qty(σ c_Name=’张艳’（Product⋈Pick⋈Customer）)

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

ΠP_Name,P_Price,P_Descn(σ c_Price<1000（Product）)

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

ΠC_Addr+’,’+C_Zip+’,’+C_Phone,C_Email(σ c_Name=’张军’（Customer）)

3.根据以上数据表，用SQL完成以下任务，注意：以下所有名称不能用中文
（1）使用SQL命令创建数据库，数据库的名称设为自己的学号。

create database wzy2104423305on primary(name=wzy2104423305_data,filename='D:\wzy_zhxsy1\wzy2104423305.mdf',size=100MB,maxsize=unlimited,filegrowth=10%)log on(NAME=wzy2104423305_log,filename='D:\wzy_zhxsy1\wzy2104423305.ldf',size=5MB,maxsize=25MB,filegrowth=1MB)

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

alterdatabasewzy2104423305modifyfile(name=wzy2104423305_data,maxsize=100MB)

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

XXX表

购买人（Customer）表

订单（Order1）表

商品（Product）表

挑选（Pick）表

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

usewzy2104423305

createtableProduct

(P_id varchar(4)primarykey,

P_Name varchar(100),

P_Category varchar(100)check(P_Category=‘图书’orP_Category=‘手机’orP_Category=‘数码影像’orP_Category=‘电脑’),

P_Price moneydefault0,

P_Facturer varchar(100),

P_time datenotnull,

P_Descn varchar(500),

P_image varbinary(max))

usewzy2104423305

createtableOrder1

(

O_id char(17)primarykey,

O_Time datetimenotnull,

O_Status char(20)notnulluniquecheck(O_Status=‘已提交’orO_Status=‘已发货’orO_Status=‘已完成’),

)

usewzy2104423305

createtableCustomer

(C_Name varchar(4)notnull,

C_Sex char(2)check(C_Sex=‘男’orC_Sex=‘女’)default‘男’,

C_Addr varchar(200)notnull,

C_Zip char(6),

C_phone varchar(100)notnull,

C_Email varchar(100)primarykey

)

usewzy2104423305

createtablePick

(O_id char(17)foreignkey(O_id)referencesOrder1 ,

C_Email varchar(100)foreignkey(C_Email)referencesCustomer,

P_id varchar(4)foreignkey(P_id)referencesProduct ,

Qty intnotnullcheck(Qty>=1)default1,

P_Price1 moneynotnullcheck(P_Price1>=0)default0

)

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

usewzy2104423305

insertintoProduct(P_id,P_Name,P_Category,P_Price,P_Facturer,P_time,P_Descn)values(‘PC1’,‘数据库原理及应用’,‘图书’,’36’,‘人民邮电出版社’,‘2015/2/1’,‘全面系统的介绍了数据库的基本原理和应用，全书共七章’)

updateProduct

setP_image=(select*fromopenrowset(bulk‘G:\DataBase\DataBase Experiment\picture.png’,single_blob)asx)

whereP_id=‘PC1’

select*fromProduct

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

usewzy2104423305

selectP_id,P_Category,P_Name fromProduct

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

usewzy2104423305

select*fromProduct,Order1,Customer,Pick

whereProduct.P_id=Pick.P_id andCustomer.C_Email=Pick.C_Email andPick.O_id=Order1.O_id

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

usewzy2104423305

selectdistinctP_Category fromProduct

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

usewzy2104423305

select*fromProduct whereP_Price>100

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

usewzy2104423305

selectP_id,P_Name,P_Price fromProduct whereyear(P_time)=year(getdate())

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

usewzy2104423305

selectC_Name,O_id fromCustomer,Pick

whereexists(selectC_Email,O_id fromPick whereCustomer.C_Email=Pick.C_Email groupbyC_Email,O_id havingcount(O_id)>=3 andCount(Qty*P_Price1)>=50)

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

usewzy2104423305

selectC_Name,C_Addr+‘，’+C_Zip+‘，’+C_phone as联系方式 fromCustomer whereC_Name like‘张%’

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

usewzy2104423305

altertableProduct

addP_Qty intcheck(P_Qty>0)

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

usewzy2104423305

selectC_Name,C_Email fromCustomer whereC_Email notin(selectC_Email fromPick)

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

usewzy2104423305

selectO_id fromPick whereC_Email=‘EE0538@163.COM’

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

usewzy2104423305

select*fromProduct whereP_Price<=50

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

usewzy2104423305

selectC_Email,year(O_Time)as年份,sum(Qty)as商品总数量 fromOrder1,Pick whereOrder1.O_id=Pick.O_id groupbyC_Email,year(O_Time)

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

usewzy2104423305

select*fromProduct whereyear(P_time)=2021 andP_Category=‘手机

usewzy2104423305

selectP_Category,year(O_Time)as年份,sum(Qty*P_Price1)as利润 fromProduct,Order1,Pick whereProduct.P_id=Pick.P_id andOrder1.O_id=Pick.O_id groupbyP_Category,year(O_Time)

三已完成的订单。

usewzy2104423305

deletefromOrder1 whereOrder1.O_id=any(selectOrder1.O_id fromOrder1,Customer,Pick whereCustomer.C_Email=Pick.C_Email andPick.O_id=Order1.O_id andO_Status=‘已完成’andC_Name=‘张三’)

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

usewzy2104423305

createnonclusteredindexindex1 onCustomer(C_Name)

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

连接查询：

usewzy2104423305

selectC_Name,C_Addr+‘，’+C_Zip+‘，’+C_phone as联系方式 fromCustomer,Pick whereCustomer.C_Email=Pick.C_Email andP_id=‘K002’

普通子查询：

usewzy2104423305

selectC_Name,C_Addr+‘，’+C_Zip+‘，’+C_phone as联系方式 fromCustomer whereC_Email=any(selectC_Email fromPick whereP_id=‘K002’)

exists的相关子查询：

usewzy2104423305

selectC_Name,C_Addr+‘，’+C_Zip+‘，’+C_phone as联系方式 fromCustomer whereexists(select*fromPick whereC_Email=Customer.C_Email andO_id=‘K002’)

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

usewzy2104423305

insertintoCustomer values(‘IKUN’,‘男’,‘J你太美’,‘7500000’,‘182000000’,‘000000003@qq.com’)

usewzy2104423305

select*fromCustomer

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

usewzy2104423305

updatePick

setP_Price1=0.85*P_Price1 whereP_id=any(selectP_id fromProduct whereyear(P_time)=year(getdate())-2)

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

createviewV1

as

selectProduct.P_id,P_Name,sum(Pick.Qty)as总销售数量 fromProduct,Pick

wherePick.P_id=Product.P_id andP_Category=‘手机’groupbyProduct.P_id,P_Name