数据库原理测试（二）

数据库原理测试（二）

 

任务一、数据库完整性

1、使用Ms SQL Server2005 创建数据库School，包含表学生表Students和班级表Classes，其中学生与班级之间有如下联系，一个学生属于一个班级，一个班级有多个学生。学生与学生之间有一个班长的联系，每个学生都有自己的班长，一个班长与多个学生有联系。

2、创建班级表Classes

      属性有班级号（整数，从1开始自动编号），班级名（变长字符串50位，不能为空，不能重复），班级人数（整数）。

3、创建学生表Students

     属性有 学号（整数，从1开始自动编号），姓名（变长字符串，20位），性别（只能是男或女），年龄（16-25的整数），班级（外码，参照班级表的主码。要求删除时拒绝，更新时，级联更新）和班长（外码，参照本表（students）的主码。要求删除时拒绝，更新时，拒绝更新）。

 数据表关系与设计后的属性、约束如下图所示。

任务二、触发器设置

1、先向班级表内插入一行元组，如：1,2011软件工程1班，0

2、编写触发器，当向学生表内插入一行元组，如：1，张同学，男，18，1，1

使得对应的班级人数（班级表内）加1。

 

 

参考代码：

create database school;
go

use school
go

create table classes
(
   cid int identity(1,1) primary key,
   cname varchar(50) not null unique,
   cTotal int
);
go

create table students
(
	sid int identity(1,1) primary key,
    sname varchar(20),
	ssex varchar(2) check(ssex in ('男','女')),
	sage int check(sage>=16 and sage<=25),
	sclassid int,
	smonitor int,
	foreign key (sclassid) references classes(cid) on delete no action on update cascade,
	foreign key (smonitor) references students(sid) on delete no action on update no action
);
go

触发器参考代码：

create trigger StudentNumbersAutoIncrease
on  students 
for Insert as  
declare   
 @sClassId int

Begin 
	select @sClassId = sClassId 
		from inserted
	update classes   
	set cTotal = cTotal + 1  
	where cid = @sClassId  
End 

 

 

 测试代码：

insert into classes(cname,cTotal) 
values('2011软件工程1班',0)

insert into students(sname,ssex,sage,sclassid,smonitor) 
values('张同学','男',18,1,1)

select * from students;
select * from classes;

结果图：

 

任务三、思考题

1、假设删除一个学生，如何使得班级表内对应班级人数减1？

2、假设修改一个学生的所在班级（就是“转班级”），如何使得班级表的对应班级人数合理变化（转出的班级人数减1，转入的班级人数加1）？