北邮数据库实验报告

1、数据库实验报告（二） 学号： 班级: 1. 按照给出的表用SQL Server Management Studio（企业管理器）建立表B-1, 表B-2, 表B-3book：class:course:2. 用Transact-SQL语句建立表B-5, 表B-6, 表B-7, 表B-8, 表B-9create table department (department_id nchar(10) not null primary key,department_name nchar(10),department_header nchar(10),teacher_num nchar(10)create

2、table student (student_id nchar(10) not null primary key,student_name nchar(10),sex nchar(10),birth nchar(10),entrance_date nchar(10),home_addr nchar(10),email nchar(10)create table student_course (course_id nchar(10) not null,student_id nchar(10) not null,grade nchar(10),credit nchar(10),semester n

3、char(10),school_year nchar(10),constraint C2 primary key(course_id,student_id)create table teacher (teacher_id nchar(10) not null primary key,teacher_name nchar(10),sex nchar(10),birth nchar(10),department_id nchar(10),profession nchar(10),telephone nchar(10),home_addr nchar(10),postalcode nchar(10)

4、,email nchar(10)create table teacher_course_class (teacher_id nchar(10) not null,course_id nchar(10) not null,class_id nchar(10) not null,semester nchar(10),school_year nchar(10),course_schedule nchar(10),course_classroom nchar(10),book_id nchar(10),constraint C3 primary key(teacher_id,course_id,cla

5、ss_id)结果图：department:student:student_course:teacher: teacher_course_class:3. 为每个表建立主键。在建表的时候已经建立。结果见上面的截图。4. 对Course表建立如下约束：course_name非空，credit取值在1-6之间alter table course add constraint C1 check(credit between 1 and 6)5. 为student_course表、teacher_course_class表创建外键，建立参照完整性约束，分别用SQL Server Management S

6、tudio（企业管理器）和Transact-SQL语句完成。alter table student_course add constraint FK1 foreign key(course_id)  references course(course_id),constraint FK2 foreign key(student_id)  references student(student_id)on delete cascadealter table teacher_course_class add constraint FK3 foreign key(book_id)&#

7、160; references book(book_id), constraint FK4 foreign key(teacher_id)  references teacher(teacher_id), constraint FK5 foreign key(course_id)  references course(course_id), constraint FK6 foreign key(class_id)  references class(class_id) on delete cascade6. 修改Student和teacher表，分别增加一列用于存储学生和教师的E_mail地址。alter table student addemail char(20)alter table teacher addemail char(20)7. 创建Course2表，与course表结构一样，删除course2表。分别用SQL Server Management Studio（企业管理器）和T