c o n s t r a i n t P K _ d e p a r t m e n t _ n o _ i n _ C o u r s e f o r e i g n k e y ( d e p a r t m e n t _ n o ) r e f e r e n c e s
Department(department_no).............................................................................................................................16
);.......................................................................................................................................................16
go......................................................................................................................................................16
create table StudentGrade.................................................................................................................16
(........................................................................................................................................................16
student_no nvarchar(10) not null.....................................................................................................16
course_no nvarchar(5) not null........................................................................................................16
score int null....................................................................................................................................16
constraint PK_StudentGrade primary key (student_no, course_no)................................................16
constraint CK_score check (score between 0 and 100)....................................................................16
c o n s t r a i n t F K _ s t u d e n t _ n o _ i n _ S G f o r e i g n k e y ( s t u d e n t _ n o ) r e f e r e n c e s
Student(student_no)..........................................................................................................................................16
c o n s t r a i n t F K _ c o u r s e _ n o _ i n _ S G f o r e i g n k e y ( c o u r s e _ n o ) r e f e r e n c e s
Course(course_no)............................................................................................................................................16
);.......................................................................................................................................................16
go......................................................................................................................................................16
--VI_student_information:学生信息的试图.................................................................................16
create view VI_student_information.................................................................................................16
as.......................................................................................................................................................16
select s.name as 姓名, s.sex as 性别, c.grade 年级, d.name as 所属系, c.levels as 学习性质........16
from Student as s..............................................................................................................................16
inner join Class as c on s.class_no =c.class_no................................................................................16
inner join Department as d on c.department_no = d.department_no;...............................................16
go......................................................................................................................................................16
--VI_studentGrade_information:学生成绩的试图........................................................................16
create view VI_studentGrade_information.......................................................................................16
as.......................................................................................................................................................16
select s.name as 姓名, s.no as 学号, c.name as 课程名称, sg.score as 成绩, c.credit as 学分, t.name
as 任课教师......................................................................................................................................................16
from Student as s,StudentGrade as sg,course as c,Teacher as t.........................................................16
where s.no=sg.no and sg.course_no=c.course_no=t.course_no;.......................................................17