有以下几个constraint 要写 用check 或者trigger 但是第九题 我觉得不需要trigger 好像也用不上trigger
请教各位应该怎么写
9. Only staffs in the same department as a professor can be assigned to be his secretary.(这题用到Staff table)
10. A student can re-take a course only if he never passed the course before.
11. A student cannot take two course offerings that have overlapping lecturing time.
12. No two offerings have room conflicts, i.e., they use the same lecture room but have overlapping
lecture time.下面是几个表
Staff:
ID Name Sex Salary Dept Is Head Title
s001 Sta1 M 60000 CSE No Lecturera
s002 Sta2 F 150000 CSE YES Professor
s003 Sta3 M 150000 EEE YES Professor
s004 Sta4 M 90000 ACC NO Associate professor
s005 Sta5 F 150000 ACC YES Professor
s006 Sta6 F 65000 MAR NO Lecturer
s007 Sta7 F 20000 MAR YES Professor
s008 Sta8 F 20000 ACC NO Non-professor
s009 Sta9 M 20000 EEE No Senior Lecturer
In addition, and “Sta8” is the secretary of “Sta5”.
Course Oering:
ROWNO Course Semester Taught by Schedule Room
1 COMP1011 2004 s1 STAFF1 Tue, 3pm-5pm KBT (240)
2 COMP2011 2004 s1 STAFF2 Thu, 9am-10am EE G24 (200)
3 COMP3311 2004 s2 STAFF2 Thu, 10am-12am EE G24 (200)
4 COMP2011 2004 s2 STAFF1 Wed, 4pm-5pm EE 218 (50)
5 ACC101 2004 s2 STAFF5 Mon, 9am-11am SCI (240)
6 MAR245 2004 s2 STAFF6 Fri, 8pm-10pm RMT (100)
The number in the parenthesis is the capacity of the corresponding room. For example, KBT (240)
means that the capacity of KBT is 240.
NOTE: ROWNO is just for the ease of referencing between tables.Student taking oerings:
Student ID ROWNO Grade
1 1 80
1 2 35
1 4 55
2 1 90
2 2 70
2 3 98
3 1 50
3 2 45
3 4 60
5 5 70
5 6 89
6 5 30
6 6 90
7 5 84
7 6 90
8 5 90
8 6 98
NOTE: ROWNO is just for the ease of referencing between tables
请教各位应该怎么写
9. Only staffs in the same department as a professor can be assigned to be his secretary.(这题用到Staff table)
10. A student can re-take a course only if he never passed the course before.
11. A student cannot take two course offerings that have overlapping lecturing time.
12. No two offerings have room conflicts, i.e., they use the same lecture room but have overlapping
lecture time.下面是几个表
Staff:
ID Name Sex Salary Dept Is Head Title
s001 Sta1 M 60000 CSE No Lecturera
s002 Sta2 F 150000 CSE YES Professor
s003 Sta3 M 150000 EEE YES Professor
s004 Sta4 M 90000 ACC NO Associate professor
s005 Sta5 F 150000 ACC YES Professor
s006 Sta6 F 65000 MAR NO Lecturer
s007 Sta7 F 20000 MAR YES Professor
s008 Sta8 F 20000 ACC NO Non-professor
s009 Sta9 M 20000 EEE No Senior Lecturer
In addition, and “Sta8” is the secretary of “Sta5”.
Course Oering:
ROWNO Course Semester Taught by Schedule Room
1 COMP1011 2004 s1 STAFF1 Tue, 3pm-5pm KBT (240)
2 COMP2011 2004 s1 STAFF2 Thu, 9am-10am EE G24 (200)
3 COMP3311 2004 s2 STAFF2 Thu, 10am-12am EE G24 (200)
4 COMP2011 2004 s2 STAFF1 Wed, 4pm-5pm EE 218 (50)
5 ACC101 2004 s2 STAFF5 Mon, 9am-11am SCI (240)
6 MAR245 2004 s2 STAFF6 Fri, 8pm-10pm RMT (100)
The number in the parenthesis is the capacity of the corresponding room. For example, KBT (240)
means that the capacity of KBT is 240.
NOTE: ROWNO is just for the ease of referencing between tables.Student taking oerings:
Student ID ROWNO Grade
1 1 80
1 2 35
1 4 55
2 1 90
2 2 70
2 3 98
3 1 50
3 2 45
3 4 60
5 5 70
5 6 89
6 5 30
6 6 90
7 5 84
7 6 90
8 5 90
8 6 98
NOTE: ROWNO is just for the ease of referencing between tables
ID Name Sex Salary Dept Is Head Title
s001 Sta 1 M 60000 CSE No Lecturera
....
------------------------------
select * from Staff where title="professor" group by Dept;
er。。
这个是select 语句么? 那要怎么写成constraint
还有个问题 为什么我Google的时候 说EMSSQLManager 这个软件有 constraint 的table tool, 但是我弄了很久也弄不出 是不是table type 那里有问题?