数据库系统分析与数据管理----某公司因业务扩展需要开发一套电子商务系统（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 wzy2104423305
on 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。

alter  database wzy2104423305
 
modify file
 
(name=wzy2104423305_data,
 
maxsize=100MB
 
)

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

XXX表

 购买人（Customer）表

订单（Order1）表

商品（Product）表

挑选（Pick）表

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

use wzy2104423305

create table Product

(P_id varchar(4) primary key,

P_Name varchar(100) ,

P_Category varchar(100) check(P_Category='图书' or P_Category='手机'or P_Category='数码影像'or P_Category='电脑') ,

P_Price money default 0,

P_Facturer varchar(100) ,

P_time date not null,

P_Descn varchar(500) ,

P_image varbinary(max))

use wzy2104423305

create table Order1

(

O_id char(17) primary key,

O_Time datetime not null,

O_Status char(20) not null unique check(O_Status='已提交'or O_Status='已发货' or O_Status='已完成'),

)

 

use wzy2104423305

create table Customer

(C_Name varchar(4) not null,

C_Sex char(2) check(C_Sex='男'or C_Sex='女')default '男',

C_Addr varchar(200) not null,

C_Zip char(6),

C_phone varchar(100) not null,

C_Email varchar(100) primary key

)

 

use wzy2104423305

create table Pick

(O_id char(17) foreign key(O_id) references Order1 ,

C_Email varchar(100) foreign key(C_Email) references Customer,

P_id varchar(4)foreign key(P_id) references Product ,

Qty int not null check(Qty>=1) default 1,

P_Price1 money not null check(P_Price1>=0) default 0

)

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

use wzy2104423305

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

 update Product

set P_image=(select * from openrowset(bulk 'G:\DataBase\DataBase Experiment\picture.png',single_blob) as x)

where P_id='PC1'

 select * from Product

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

use wzy2104423305

select P_id,P_Category,P_Name from Product

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

use wzy2104423305

select * from Product,Order1,Customer,Pick

where Product.P_id=Pick.P_id and Customer.C_Email=Pick.C_Email and Pick.O_id=Order1.O_id

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

use wzy2104423305

select distinct P_Category from Product

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

use wzy2104423305

select * from Product where P_Price>100

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

use wzy2104423305

select P_id,P_Name,P_Price from Product where year(P_time)=year(getdate())

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

use wzy2104423305

select C_Name,O_id from Customer,Pick

 where exists (select C_Email,O_id from Pick where Customer.C_Email=Pick.C_Email group by C_Email,O_id having count(O_id)>=3 and Count(Qty*P_Price1)>=50)  

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

use wzy2104423305

select C_Name,C_Addr+'，'+C_Zip+'，'+C_phone as 联系方式 from Customer where C_Name like '张%' 

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

use wzy2104423305

alter table Product

add P_Qty int check(P_Qty>0) 

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

use wzy2104423305

select C_Name,C_Email from Customer where C_Email not in (select C_Email from Pick)

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

use wzy2104423305

select O_id from Pick where C_Email='EE0538@163.COM'

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

use wzy2104423305

select * from Product where P_Price<=50

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

use wzy2104423305

select C_Email,year(O_Time) as 年份,sum(Qty) as 商品总数量  from Order1,Pick where Order1.O_id=Pick.O_id group by C_Email,year(O_Time)

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

use wzy2104423305

select * from Product where year(P_time)=2021 and P_Category='手机

use wzy2104423305

select P_Category,year(O_Time) as 年份,sum(Qty*P_Price1) as 利润  from Product,Order1,Pick where Product.P_id=Pick.P_id and Order1.O_id=Pick.O_id group by P_Category,year(O_Time)

三已完成的订单。

use wzy2104423305

delete from Order1 where Order1.O_id=any(select Order1.O_id from Order1,Customer,Pick where Customer.C_Email=Pick.C_Email and Pick.O_id=Order1.O_id and O_Status='已完成' and C_Name='张三')

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

use wzy2104423305

create nonclustered index index1 on Customer(C_Name)

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

连接查询：

use wzy2104423305

select C_Name, C_Addr+'，'+C_Zip+'，'+C_phone as 联系方式 from Customer,Pick where Customer.C_Email=Pick.C_Email and P_id='K002'

普通子查询：

use wzy2104423305

select C_Name, C_Addr+'，'+C_Zip+'，'+C_phone as 联系方式 from Customer where C_Email=any(select C_Email from Pick where P_id='K002')

exists的相关子查询：

use wzy2104423305

select C_Name, C_Addr+'，'+C_Zip+'，'+C_phone as 联系方式 from Customer where exists (select * from Pick where C_Email=Customer.C_Email and O_id='K002')

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

use wzy2104423305

insert into Customer values('IKUN','男','J你太美','7500000','182000000','000000003@qq.com')

use wzy2104423305

select * from Customer

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

use wzy2104423305

update Pick

set P_Price1=0.85*P_Price1 where P_id=any(select P_id from Product where year(P_time)=year(getdate())-2)

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

create view V1

as 

select Product.P_id,P_Name,sum(Pick.Qty) as 总销售数量 from Product,Pick

where Pick.P_id=Product.P_id and P_Category='手机'  group  by Product.P_id,P_Name