图书管理子系统数据库课程设计.doc

数据库原理实验指导

实验一 1
实验二 2
实验三 3
实验四 4
实验五 5
实验六 6
实验七 6
实验八 7
实验九 8

实验前准备（2课时）：
请设计一个企业销售管理据库，其中需要保存的信息如下：
员工信息，包括：员工编号、员工姓名、性别、所属部门、职称、到职日、生日、薪水、填表日期；
客户信息，包括：客户号，客户名称，客户住址，客户电话、邮政编码；
产品信息，包括：产品编号，产品名称；
员工和客户可以签订订单，每签订一个订单，就要保存订单信息，包括：订单编号、客户号、业务员编号、订单金额、订货日期、出货日期、发票号码。
此外，每个订单可能涉及到多种产品，每种产品可能被多个订单订购。因此需要每个订单中每类产品的销售明细，包括每种产品的销售数量、单价、订单日期；
要求：
（1） 给出系统的ER图（可以用word或其它画图工具，如Visio画），要求画出所有的实体，联系，属性以及联系的类型；
（2） 将ER图转换为关系模型；

实验一
实验名称：建立数据库和基本表结构（2课时）
一、实验目的
理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。
另外，帮助学生理解上述基本表之间的关系，从而正确、合理地建立表结构，并输入相应记录信息。
二、实验环境
MS SQL SERVER或者My Sql。
三、实验示例
1、 使用SQL建立数据库。
例如：create database studentdb
2、 使用SQL脚本建立基本表结构。
例如：
create table employee1(
emp_no char(5) not null,
emp_name char(10) not null,
emp_sex char(1) not null,
emp_phone char (13) not null,
emp_add varchar(60) not null,
emp_salary char(5) not null,
)
四、实验内容与步骤
建立一个数据库和五张表的表结构。
（1）/*员工人事表employee */
emp_no
char(5)
Not null
primary key
员工编号
emp_name
char(10)
Not null

员工姓名
sex
char(1)
Not null

性别
dept
char(4)
Not null

所属部门
title
char(6)
Not null

职称
date_hired
datetime
Not null

到职日
birthday
datetime
Null

生日
salary
int
Not null

薪水
addr
char(50)
null

住址
Mod_date
datetime
Default(getdate())
操作日期

（2）/*客户表customer */
cust_id
char(5)
Not null
primary key
客户号
cust_name
char(20)
Not null,

客户名称
addr
char(40)
Not null,

客户住址
tel_no
char(10)
Not null,

客户电话
zip
char(6)
null

邮政编码

（3）/*销售主表sales */
order_no
int
Not null
primary key
订单编号
cust_id
char(5)
Not null,

客户号
sale_id
char(5)
Not null,

业务员编号
tot_amt
numeric(9,2)
Not null,

订单金额
order_date
datetime
Not null,

订货日期
ship_date
datetime
Not null,

出货日期
invoice_no
char(10)
Not null

发票号码

（4）/*销货明细表sale_item */
order_no
int
Not null,
primary key
订单编号
prod_id
char(5)
Not null,
产品编号
qty
int
Not null

销售数量
unit_price
numeric(9,2)
Not null

单价
order_date
datetime
Null

订单日期

（5）/*产品名称表product */
prod_id
char(5)
Not null
primary key
产品编号
prod_name
char(20)
Not null

产品名称
五、实验报告

实验二
实验名称：添加表记录（2课时）
一、实验目的
掌握修改表结构的基本方法，熟练使用INSERT语句为每张表追加表记录。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
例如：
insert into employee
(emp_no,emp_name,sex,dept,title,date_hired,salary)

values('E0025','彭江','M','业务','职员',1900/01/21,43200)
四、实验内容与步骤
初步理解表之间的关系，用insert命令输入有效记录（每张表至少需要5条记录）。
五、实验报告

实验三
实验名称： 数据库的更新实验（2课时）
一． 实验目的
本实验的目的是要求学生熟练掌握修改数据和删除数据的操作。

二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1. 修改操作语句的一般格式为
UPDATE 表名
SET <列名>=<表达式>[,<列名>=<表达式>]……
[where <条件>];
其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

2. 删除语句的一般格式为
DELETE
FROM <表名>
[WHERE <条件>];
其功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，则表示要删除表中的所有元组，但表的定义仍在字典中。

四、实验内容与步骤

1、 将员工崔庆华的薪水改为10000。
update employee
set salary=10000
where emp_name='崔庆华'

2、 将所有员工的薪水增加100。
update employee
set salary=salary+100

3、 删除所有女性员工的记录。
delete
from employee
where sex='f'

4、 删除订单金额小于100000的订单。
delete
from sale_item
where unit_price<100000

五、实验报告

实验四
实验名称：表的完整性约束（2课时）
一、实验目的
理解并掌握关系的完整性约束，熟练使用T-SQL语句为每张表添加表约束。

二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、
create table orders
(
order_id char(8),
p_id char(8),
p_name char(10) ,
constraint pk_order_id primary key (order_id) ,
foreign key (p_id, p_name)
References products(p_id, p_name)
)
2、
create table employee1(
emp_no char(5) not null,
emp_name char(10) not null,
emp_sex char(1) not null,
constraint emp_sexchk check(emp_sex in ('m','f')),
emp_phone char (13) not null,
emp_add varchar(60) not null,
emp_salary char(5) not null,
constraint emp_salarychk check (emp_salary between 60000 and 80000)
)
3、
create table employees
(
emp_id char(8),
emp_name char(10) ,
emp_cardid char(18),
constraint pk_emp_id primary key (emp_id),
constraint uk_emp_cardid unique (emp_cardid)
)

四、实验内容与步骤
通过SQL语句修改表约束。
①分别为每张表建立主键约束。
②为相关表建立外键约束。
③ 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。
④为销售主表sales中的发票编号字段建立UNIQUE约束。

五、实验报告

实验五
实验名称：简单查询（2课时）
一、实验目的
理解SELECT语句的操作和基本使用方法，掌握简单查询。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、查找表中所有姓刘的职工的工号，部门，薪水
select emp_no, emp_name, dept,salary
from employee
where emp_name like '刘%'

2、查找所有定单金额高于20000的所有客户编号
select cust_id
from sales
where tot_amt>20000

3、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。
select emp_name,title,sex
from employee
where title='经理'or title='职员'and sex='F'
4、选取销售数量最多的前5条订单订单号、数量。
select top 5 with ties order_no,sup_id,qty
from sale_item
order by qty desc
5、按部门进行汇总，统计每个部门的总工资
select dept,sum(salary)
from employee
group by dept
四、实验内容与步骤
1、 查找所有经理的姓名、职称、薪水。
select emp_name,title,salary
from employee
where title='经理'

2、 查找出姓“王”并且姓名的最后一个字为“功”的员工。
select *
from employee
where emp_name='王%功'

3、 查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。
select emp_name,title,dept,addr,sex
from employee
where sex='m' and addr='上海'or addr='北京'

4、 在表sales中挑出销售金额大于等于10000的订单
select *
from sale_item
where unit_price>=10000

5、 选取订单金额最高的前10%的订单数据。
select top 10 percent *
from sales
order by tot_amt desc

6、 查找出职称为经理或职称为职员的女员工的信息。
select *
from employee
where sex='f' and (title='经理'or title='职员')

7 删除sales表中作废的订单（其发票号码为4）。（注意：约束）
delete
from sale_item
where order_no in(select order_no
from sales
where invoice_no='4')

8、计算出一共销售了几种产品。
select count(distinct prod_id)
from sale_item

9、显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。
select prod_id,month(unit_price*qty)
from sale_item
group by prod_id
order by sum(unit_price*qty)desc
10、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。
select prod_id,month(order_date),sum(unit_price*qty)
from sale_item
group by prod_id,month(order_date)
order by prod_id,month(order_date)

五、实验报告

实验六
实验名称：表连接查询JOIN（2课时）
一、实验目的
理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自连接的概念和使用。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/
on (a.prod_id=b.pro_id) and a.qty>2
order by a.prod_id
2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。
select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr
from employee as a inner join employee as b
on (a.emp_no!=b.emp_no) and (a.emp_name>b.emp_name) and (a.addr=b.addr)
四、实验内容与步骤
1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。
select a.emp_name,a.sex,a.salary,a.addr
from employee a,employee b
where(a.dept=b.dept)and(a.addr=b.addr)
and a.sex='f'and b.sex='f'and a.emp_no=b.emp_no

2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。
select a.prod_id,a.qty,a.unitprice,b.prod_name
from sale_item as a inner join product as b
on(a.prod_id=b.prod_id)

3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a inner join product as b
on(a.prod_id=b.prod_id)and unit_price>2400

4、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。
左向外连接：
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a left join product as b
on(a.prod_id=b.prod_id)and unit_price>2400

右向外连接：
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a right join product as b
on(a.prod_id=b.prod_id)and unit_price>2400

完整外部链接：
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a full join product as b
on(a.prod_id=b.prod_id)and unit_price>2400

五、实验报告
实验七
实验名称：嵌套查询（2课时）
一、实验目的
掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、由employee表中查找出薪水最高的员工信息。
select *
from employee
where salary=
(select max(salary )
from employee )
2、由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接每一张订单的金额”的所有订单。
select *
from sales
where tot_amt>all
(select tot_amt
from sales
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt

四、实验内容与步骤
1、 由sales表中查找出销售金额最高的订单。
select *
from sales
where tot_amt=(
select max(tot_amt)
from sales)

2、 由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。
select sale_id,tot_amt
from sales
where tot_amt>all(select tot_amt
from sales
where sale_id='3'and order_date='1900-1-24 ')

3、 找出公司女业务员所接的订单。
select sale_id,tot_amt
from sales
where sale_id in (select sale_id
from employee
where sex='f')

4、 找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。
select *
from employee a
where exists(select emp_name
from employee b
where a.emp_name=b.emp_name and a.emp_no<>b.emp_no)

5、 找出目前业绩未超过20000元的员工。
select *
from employee
where emp_no in (select sale_id
from sales
group by sale_id
having sum(tot_amt)<=20000)

6、 计算公司内各个部门的工资支出总和。
select dept,sum(salary)
from employee
group by dept

7、 计算每一产品销售数量总和与平均销售单价。
select prod_id,sum(qty),avg(unit_price)
from sale_item

五、实验报告
实验八
实验名称：视图的建立与使用（2课时）
一、实验目的
掌握视图的定义与工作原理。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验示例
1、
create view emp_view as
select emp_no,emp_name,salary
from employee
where sex='f'
2、
create view sale_item_view as
select sales.order_no,sales.order_date,prod_id,qty
from sales,sale_item
where sales.order_no=sale_item.order_no

/*注意：插入数据时要考虑数据列是否有默认值或允许为空，否则INSERT失败。*/
insert into sale_item_view(order_no,order_date)
values(10010,'1996/12/20')

insert into sale_item_view(prod_id,qty)
values('p0010',8)
四、实验内容与步骤
1、 创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址。
create view sh_kehu
as select cust_id,cust_name,addr
from customer
where addr like '上海'

2、 对视图添加一条记录数据。（注意：分别查看customer表和该视图的结果。）
insert into sh_kehu(cust_id,cust_name,addr)
values('08','陈晨','南京')
3、 删除视图中所有姓“王”的客户数据。
delete
from sh_kehu
where cust_name like '王%'

4、 通过视图修改表内某一客户的姓名。
update sh_kehu
set cust_name='陈晨'
where cust_id=01

5、 有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。
create view em_sa
as select sale_id,emp_name,order_no,tot_amt
from employee,sales
where employee.emp_no=sales.sale_id

6、 将上述视图中订单号为10001的记录的销售总金额改为60000。
update em_sa
set tot_amt='60000'
where order_no='10001'

7、 给上述视图添加一条记录数据。
insert into em_sa(sale_id,emp_name,order_no,tot_amt)
values('05','小名明','06','55000')
8、 删除上述视图。
drop view em_sa
drop view sh_kehu
五、实验报告
实验九
教材上的最后两个实验。

16