数据库理论与技术课程实验报告五(参考解答)(1)

宁波工程学院
数据库理论与技术课程实验报告
学院：电子与信息工程学院专业：计算机科学与技术班级：计科15- 实验时间： 2017年5月10日
学号姓名成绩__ ___ __
学号姓名成绩__ ___ __
学号姓名成绩__ ___ __
学号姓名成绩__ ___ __
备注：排名第一的学生为组长，负责此实验任务的组织和分配，负责实验报告的整合和提交。

指导教师签字：
实验五：SQL Server的综合练习
一、实验目的和要求
1、巩固并掌握数据定义语言的使用，正确认识创建数据库和数据表语句的作用，进一步理解数据类型和各类约束对实现数据完整性的重要性；
2、巩固并掌握数据操纵语言的使用，正确认识数据查询和数据更新语句的作用，熟练掌握数据查询语句的使用；
3、巩固并掌握创建视图语句的格式和功能，理解视图创建三个选项的含义，熟练掌握视图的一般应用。

4、要求学生在每次实验前，根据实验目的和内容设计出本次实验的具体步骤；在实验过程中，要求独立进行程序调试和排错，学会使用在线帮助和运用理论知识来分析和解决实验中遇到的问题，并记录实验的过程和结果；上机实验结束后，根据实验模板的要求写出实验报告，并对实验过程进行分析和总结。

二、实验内容与过程记录
某仓储超市采用POS（Point of Sale）收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库信息管理系统。

经过系统需求分析、概念结构设计和逻辑结构设计，可以简化得到如下一组关系模式（其中表示主键，表示外键）：
积分卡（用户编号，用户名，累积消费金额，积分点）
销售详单（销售流水号，商品编码，数量，金额，用户编号，收银员，时间）销售日汇总（日期，商品编码，数量）
存货表（商品编码，数量）
进货表（送货号码，商品编码，数量，日期）
商品（商品编码，商品名称，单价）
请在SQL Server的查询分析器中按要求完成如下各题：
1、创建名为Supermarket的数据库，数据文件名取为：Supermarket_data.mdf，
日志文件名取为：Supermarket_log.ldf。

create database Supermarket
on primary
(name=Supermarket_data,filename='e:\Supermarket_data.mdf',
size=10mb,maxsize=50mb,filegrowth=20%)
log on
(name=Supermarket_log,filename='e:\Supermarket_log.ldf',
size=5mb,maxsize=25mb,filegrowth=5mb)
collate chinese_prc_ci_as
go
2、按表1-6要求创建6张数据表，并为每张表设置主键码和外键码（若有的话）。

表1 Integralcard积分卡信息表
列名数据类型可否为空说明
User_id char（10）Not null 用户编号
User_name varchar（20）Not null 用户名Cumulative_consumption numeric（8，2）Not null 累计消费金额Integral_point numeric（5，0）Not null 积分点
表2 Salesdetails 销售详单信息表
列名数据类型可否为空说明
sales_id char（10）Not null 销售流水号commodity_code char（10）Not null 商品编码number numeric（4，0）null 数量
amount numeric（9，2）null 金额
User_id char（10）Not null 用户编号
cashier varchar（20）null 收银员
sd_time datetime null 时间
表3 Salesdatesummary 销售日汇总信息表
列名数据类型可否为空说明sds_date datetime Not null 日期
commodity_code char（10）Not null 商品编码number numeric（4，0）null 数量
表4 Inventorylist存货信息表
列名数据类型可否为空说明commodity_code char（10）Not null 商品编码number numeric（4，0）null 数量
表5 Purchasetable进货信息表
列名数据类型可否为空说明delivery_number char（10）Not null 送货号码commodity_code char（10）Not null 商品编码
number numeric（4，0）null 数量
pt_date datetime Not null 日期
表6 Commodity商品信息表
列名数据类型可否为空说明commodity_code char（10）Not null 商品编码commodity_name varchar（10）Not null 商品名称commodity_price numeric（7，2）Not null 商品单价
（1）创建Integralcard积分卡信息表
use Supermarket
go
create table Integralcard
(User_id char(10)not null,
User_name varchar(20)not null,
Cumulative_consumption numeric(8,2)not null,
Integral_point numeric(5,0)not null
)
go
alter table Integralcard
add constraint pk_Integralcard primary key(User_id)
（2）创建Salesdetails 销售详单信息表
use Supermarket
go
create table Salesdetails
(sales_id char(10)not null,
commodity_code char(10)not null,
number numeric(4,0)null,
amount numeric(9,2)null,
User_id char(10)not null,
cashier varchar(20)null,
sd_time datetime null
)
go
alter table Salesdetails
add constraint pk_Salesdetails primary key(sales_id,commodity_code) alter table Salesdetails
add constraint fk_Salesdetails foreign key(User_id)references Integralcard(User_id)
（3）创建Salesdatesummary 销售日汇总信息表
use Supermarket
go
create table Salesdatesummary
(sds_date datetime not null,
commodity_code char(10)not null,
number numeric(4,0)null
)
go
alter table Salesdatesummary
add constraint pk_Salesdatesummary primary key(sds_date,commodity_code)（4）创建Inventorylist存货信息表
use Supermarket
go
create table Inventorylist
(commodity_code char(10)not null,
number numeric(4,0)null
)
go
alter table Inventorylist
add constraint pk_Inventorylist primary key(commodity_code)
（5）创建Purchasetable进货信息表
use Supermarket
go
create table Purchasetable
(delivery_number char(10)not null,
commodity_code char(10)not null,
number numeric(4,0)null,
pt_date datetime not null
)
go
alter table Purchasetable
add constraint pk_Purchasetable primary key(delivery_number,commodity_code)
（6）创建Commodity商品信息表
use Supermarket
go
create table Commodity
(commodity_code char(10)not null,
commodity_name varchar(10)not null,
commodity_price numeric(7,2)not null
)
go
alter table Commodity
add constraint pk_Commodity primary key(commodity_code)
3、在建好的6张表中，利用对象资源管理器分别输入和更新若干条记录，要求主键码不能为空和重复，外键码只能取另一张表的主键码之一。

在对象资源管理器中展开“数据库”，选中Supermarket数据库，展开此数据库及下属的表，选择一个数据表（如dbo.Integralcard）,右击此表并在弹出的菜单中选择“编辑前200行”，即可输入或修改此表的内容，最后点击关闭按钮即可保存。

4、针对该数据库的6张表，完成如下6个查询请求：
（1）查询用户编号为’yh23001011’的用户的用户名、累积消费金额和积分点；
use Supermarket
go
select user_name,cumulative_consumption,integral_point
from Integralcard
where USER_ID='yh23001011'
（2）查询’张三’用户所购的全部商品的商品编码、商品名称、单价、数量和金额；
use Supermarket
go
select
modity_code,commodity_name,commodity_price,number,amo unt
from Integralcard,Salesdetails,Commodity
where er_id=er_id and
modity_code=modity_code
（3）查询2016年4月各类商品销售数量的排行榜，要求显示商品编号、商品名称和数量（按降序排列）；
use Supermarket
go
select modity_code,commodity_name,sum(number)as sum_number
from Salesdatesummary,Commodity
where modity_code=modity_code and
sds_date between'2016-04-01'and'2016-04-30'
group by modity_code,commodity_name
order by sum_number desc
（4）根据销售详单中的销售流水号’xs80020001’和商品编码’sp03004561’，对存货表中的数量进行更新；
use Supermarket
go
update Inventorylist
set number=number-(select number from Salesdetails
where Salesdetails.sales_id='xs80020001'and
commodity_code='sp03004561')
where commodity_code='sp03004561'
（5）根据进货表中的送货号码’sh00012288’和商品编码’sp03006677’，对存货表中的数量进行更新；
use Supermarket
go
update Inventorylist
set number=number+(select number from Purchasetable
where Purchasetable.delivery_number='sh00012288'
and commodity_code='sp03006677')
where commodity_code='sp03006677'
（6）统计2016年4月中每一天的销售金额，要求显示日期、销售金额（按降序排列）。

use Supermarket
go
select sds_date,sum(number*commodity_price)as day_comsumption
from Salesdatesummary,Commodity
where modity_code=modity_code and
sds_date between'2016-04-01'and'2016-04-30'
group by sds_date
order by day_comsumption desc
5、针对该数据库的6张表，定义如下2个视图：
（1）定义一个商品存货的视图Commodity_Inventorylist，属性包括商品编码、商品名称、单价和数量；
use Supermarket
go
create view Commodity_Inventorylist
as select modity_code,commodity_name,
commodity_price,number
from Commodity,Inventorylist
where modity_code=modity_code
（2）定义一个用户购买商品的详细清单User_Purchase_Details, 属性包括用户编号、用户名、商品编码、商品名称、单价和数量。

use Supermarket
go
create view User_Purchase_Details
as select er_id,User_name,modity_code, commodity_name,commodity_price,number
from Integralcard,Salesdetails,Commodity
where er_id=er_id and
modity_code=modity_code
三、实验分析和总结
自述
四、实验任务分配和贡献情况
（注意：每个组员必须分开书写，组长分配每个组员的贡献百分比）
自述。