北郵數(shù)據(jù)庫實驗報告

1、數(shù)據(jù)庫實驗報告（二） 學(xué)號： 班級: 1. 按照給出的表用SQL Server Management Studio（企業(yè)管理器）建立表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)結(jié)果圖：department:student:student_course:teacher: teacher_course_class:3. 為每個表建立主鍵。在建表的時候已經(jīng)建立。結(jié)果見上面的截圖。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表創(chuàng)建外鍵，建立參照完整性約束，分別用SQL Server Management S

6、tudio（企業(yè)管理器）和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表，分別增加一列用于存儲學(xué)生和教師的E_mail地址。alter table student addemail char(20)alter table teacher addemail char(20)7. 創(chuàng)建Course2表，與course表結(jié)構(gòu)一樣，刪除course2表。分別用SQL Server Management Studio（企業(yè)管理器）和T