数据库原理实验指导

实验一

实验名称：数据定义（4课时）
一、实验目的
1、理解数据库模式的概念，通过使用SQL SERVER企业管理器或者My Sql建立数据库和基本表。模式为人事表、客户表、销售表、销售明细表、产品表。熟悉SQL SERVER企业管理器的使用，并将得到的表生成脚本，然后保存。
2、理解上述基本表之间的关系，建立关系表。
3、掌握修改表结构的基本方法
4、掌握索引和视图的创建方法
二、实验环境
MS SQL SERVER或者My Sql。
三、实验内容与步骤
1、建立一个数据库和五张表的表结构。

--员工人事表
create table employee(
	emp_no char(5) primary key,
	emp_name char(10) not null,
	sex char(1) not null,
	dept char(4),
	title char(6),
	date_hired datetime,
	birthday datetime,
	salary int,
	addr char(50),
	Mod_date datetime default(getdate())
)
--客户表
create table customer(
	cust_id char(5) primary key,
	cust_name char(20) not null,
	sex char(1) not null,
	addr char(40) not null,
	tel_no char(10) not null,
	zip char(6)
)
--销售主表
create table sales(
	order_no char(5) primary key,
	cust_id char(5) not null,
	sale_id char(5) not null,
	tot_amt numeric(9,2) default 0,
	order_date datetime,
	ship_date datetime,
	invoice_no char(10)
)
--销售明细表
create table sales_item(
	order_no char(5),
	prod_id char(5),
	qty int default 0,
	unit_price numeric(9,2) default 0,
	order_date datetime
	primary key(order_no,prod_id)
)
--产品名称表
create table product(
	prod_id char(5) primary key,
	prod_name char(20) not null
)

2、建立5张表的关系图

--2.建立5张表的关系图
alter table sales add foreign key(cust_id) references customer
alter table sales add foreign key(sale_id) references employee(emp_no)
alter table sales_item add foreign key(order_no) references sales
alter table sales_item add foreign key(prod_id) references product

3、修改表结构，通过SQL语句修改表约束。
① 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。

alter table employee add check(emp_no like('E____'))
alter table employee add check(sex in('M','F'))

②为销售主表sales中的发票编号字段建立UNIQUE约束。

alter table sales add unique(invoice_no)

③所有性别属性，限制取值为f或m
说明：用drop constraint 删除①中关于sex的约束，重新建立约束，并把表中已存有的数据改为符合要求的数据格式。

alter table employee drop constraint CK__employee__sex__30F848ED
alter table employee add check(sex in('m','f'))
update employee set sex='m' where sex='M' 
update employee set sex='f' where sex='F'

4、员工表建立唯一索引：emp_no属性、升序；员工表建立聚集索引：emp_name属性、升序；

create unique index emp_no_unique on employee(emp_no asc)
create clustered index emp_name_clustered on employee(emp_name asc)
drop index PK__employee__129850FAAF8804E9
go

说明：无法为员工表建立聚集索引，因为聚集索引只能有一个，表中已有PK索引表示聚集索引。
5、创建视图：
① 视图只含上海客户信息，即客户号、客户姓名、住址。

create view 上海客户信息(客户号,客户姓名,住址) as
select cust_id,cust_name,addr from customer
go

②有两个基本表employee和sales，创建一个视图，该视图包含相同业务员的编号、姓名、订单号、销售总金额。

create view 业务员销售(编号,姓名,订单号,销售总金额) as
select customer.cust_id,customer.cust_name,sales.order_no,qty*unit_price
from customer,sales,sales_item 
where sales.cust_id=customer.cust_id and sales.order_no=sales_item.order_no
go

四、实验报告

实验二

实验名称： 数据操纵（4课时）
一． 实验目的
1、要求学生熟练掌握添加、修改、删除数据的操作。
2、要求学生熟练掌握数据查询操作。
二、实验环境
MS SQL SERVER 2000或者My SQL
三、实验内容与步骤
1.数据更新
①在每个表中插入若干条记录；

--1.数据更新
--(1)在每个表中插入若干条记录
--员工表employee 添加记录
insert into employee values('E0001','张三','m','业务','经理','1999-10-13','1970-08-01',8000,'上海市',getDate())
insert into employee values('E0002','李四','m','业务','职员','1999-10-01','1981-08-01',4000,'上海市',getDate())
insert into employee values('E0003','王五','m','会计','科长','1999-09-15','1982-06-09',4800,