求组，新手SQL求改进，有问题的地方都标了，求高手赐教
CREATE DATABASE 成绩管理系统
GO
USE 成绩管理系统
GO
CREATE TABLE DEPT--专业
(
D_NO CHAR(2)PRIMARY KEY NOT NULL CHECK(D_NO LIKE '[0-9][0-9]'),
D_NAME NCHAR(50)NOT NULL UNIQUE
)
CREATE TABLE CLASS--班级
(
CL_NO CHAR(4)PRIMARY KEY NOT NULL CHECK(CL_NO LIKE '[0-9][0-9][0-9][0-9]'),
CL_NAME NCHAR(50)NOT NULL UNIQUE,
CL_NUM INT DEFAULT(0),
D_NO CHAR(2),
CONSTRAINT FK_D_NO FOREIGN KEY(D_NO)REFERENCES DEPT(D_NO),
CONSTRAINT CK_CL_NO CHECK(CL_NO LIKE D_NO+'[0-9][0-9]')
)
CREATE TABLE DORM--宿舍
(
DO_NO CHAR(3)PRIMARY KEY NOT NULL CHECK(DO_NO LIKE '[0-9][0-9][0-9]'),
DO_NUM INT DEFAULT(0),
DO_LEVEL CHAR(7)CHECK(DO_LEVEL IN('STUDENT','TEACHER')),
DO_SEX CHAR(5)CHECK(DO_SEX IN('MEN','WOMEN')),
CONSTRAINT CK_DO_NUM_DO_LEVEL CHECK(DO_NUM<=1 AND DO_LEVEL='TEACHER' OR DO_LEVEL='STUDENT')
)
CREATE TABLE CLASSROOM--教室
(
CR_NO CHAR(3)PRIMARY KEY CHECK(CR_NO LIKE '[0-9][0-9][0-9]')
)
CREATE TABLE TEACHER--教师
(
T_NO CHAR(3)PRIMARY KEY CHECK(T_NO LIKE '[0-9][0-9][0-9]'),
T_NAME NCHAR(30)NOT NULL,
T_SEX CHAR(5)CHECK(T_SEX IN('MEN','WOMEN')),
T_AGE INT CHECK(T_AGE BETWEEN 20 AND 70),
DO_NO CHAR(3),
D_NO CHAR(2),
CONSTRAINT FK_D_NO1 FOREIGN KEY(D_NO)REFERENCES DEPT(D_NO),
CONSTRAINT FK_DO_NO FOREIGN KEY(DO_NO)REFERENCES DORM(DO_NO)
)
CREATE TABLE COURSE--课程--有错
(
C_NO CHAR(3)PRIMARY KEY CHECK(C_NO LIKE '[0-9][0-9][0-9]'),
C_NAME NCHAR(50)NOT NULL,
D_NO CHAR(2),
CR_NO CHAR(3)UNIQUE,
T_NO CHAR(3),
CREDIT INT CHECK(CREDIT BETWEEN 1 AND 9),
CONSTRAINT FK_D_NO2 FOREIGN KEY(D_NO)REFERENCES DEPT(D_NO),
CONSTRAINT FK_CR_NO FOREIGN KEY(CR_NO)REFERENCES CLASSROOM(CR_NO),
CONSTRAINT FK_T_NO FOREIGN KEY(T_NO)REFERENCES TEACHER(T_NO),
--CONSTRAINT CK_D_NO CHECK(D_NO=(SELECT TOP 1 D_NO FROM TEACHER T1 WHERE T_NO=T1.T_NO))--有错
)
CREATE TABLE STUDENT--学生
(
S_NO CHAR(6)PRIMARY KEY CHECK(S_NO LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]'),
S_NAME NCHAR(30)NOT NULL,
CL_NO CHAR(4),
S_SEX CHAR(5)CHECK(S_SEX IN('MEN','WOMEN')),
S_AGE INT CHECK(S_AGE BETWEEN 15 AND 30),
DO_NO CHAR(3),
CONSTRAINT FK_CL_NO FOREIGN KEY(CL_NO)REFERENCES CLASS(CL_NO),
CONSTRAINT FK_DO_NO1 FOREIGN KEY(DO_NO)REFERENCES DORM(DO_NO),
CONSTRAINT CK_S_NO CHECK(S_NO LIKE CL_NO+'[0-9][0-9]')
)
CREATE TABLE SC--选课--有错
(
S_NO CHAR(6),
C_NO CHAR(3),
GRADE INT CHECK(GRADE BETWEEN 0 AND 100),
CONSTRAINT PK_学号_课程代号 PRIMARY KEY(S_NO,C_NO),
CONSTRAINT FK_S_NO FOREIGN KEY(S_NO)REFERENCES STUDENT(S_NO),
CONSTRAINT FK_C_NO FOREIGN KEY(C_NO)REFERENCES COURSE(C_NO),
--CONSTRAINT CK_DEPT CHECK((SELECT D_NO FROM CLASS C2 WHERE C2.CL_NO=(SELECT CLNO FROM
--STUDENT S1 WHERE S_NO=S1.S_NO))=(SELECT D_NO FROM COURSE C1 WHERE C_NO=C1.C_NO))--有错
)
GO
CREATE TRIGGER TRIGGERCLASSNUMDELETESTUDENT
ON STUDENT
AFTER DELETE
AS
UPDATE CLASS SET CL_NUM=CL_NUM-(SELECT COUNT(S_NO)FROM DELETED DEL WHERE CL_NO=DEL.CL_NO)
WHERE CL_NO IN (SELECT CL_NO FROM DELETED)
go
CREATE TRIGGER TRIGGER_CLASSNUMINSERTSTUDENT
ON STUDENT
AFTER INSERT
AS
UPDATE CLASS SET CL_NUM=CL_NUM+(SELECT COUNT(S_NO)FROM INSERTED INS WHERE CL_NO=INS.CL_NO)
WHERE CL_NO IN (SELECT CL_NO FROM INSERTED)
go
CREATE TRIGGER TRIGGER_DORMNUMINSERTSTUDENT
ON STUDENT
AFTER INSERT
AS
IF(
'STUDENT'=(SELECT DO_LEVEL FROM DORM WHERE DO_NO=(SELECT DO_NO FROM INSERTED))
AND(SELECT S_SEX FROM INSERTED)=(SELECT DO_SEX FROM DORM WHERE DO_NO=(SELECT DO_NO FROM INSERTED))
)
UPDATE DORM SET DO_NUM=DO_NUM+(SELECT COUNT(S_NO)FROM INSERTED INS WHERE DO_NO=INS.DO_NO)
WHERE DO_NO IN (SELECT DO_NO FROM INSERTED)
ELSE UPDATE STUDENT SET DO_NO='null'WHERE S_NO=(SELECT S_NO FROM INSERTED)--ELSE里句子好像没执行，却也把插入操作失败了
go
CREATE TRIGGER TRIGGER_DORMNUMDELETESTUDENT
ON STUDENT
AFTER DELETE
AS
UPDATE DORM SET DO_NUM=DO_NUM-(SELECT COUNT(S_NO)FROM DELETED DEL WHERE DO_NO=DEL.DO_NO)
WHERE DO_NO IN (SELECT DO_NO FROM DELETED)
go
CREATE TRIGGER TRIGGER_DORMNUMINSERTTEACHER
ON TEACHER
AFTER INSERT
AS
IF(
'TEACHER'=(SELECT DO_LEVEL FROM DORM WHERE DO_NO=(SELECT DO_NO FROM INSERTED))
AND(SELECT T_SEX FROM INSERTED)=(SELECT DO_SEX FROM DORM WHERE DO_NO=(SELECT DO_NO FROM INSERTED))
)
UPDATE DORM SET DO_NUM=DO_NUM+(SELECT COUNT(T_NO)FROM INSERTED INS WHERE DO_NO=INS.DO_NO)
WHERE DO_NO IN (SELECT DO_NO FROM INSERTED)
ELSE UPDATE TEACHER SET DO_NO='null'WHERE T_NO=(SELECT T_NO FROM INSERTED)--ELSE里句子好像没执行，却也把插入操作失败了
go
CREATE TRIGGER TRIGGER_DORMNUMDELETETEACHER
ON TEACHER
AFTER DELETE
AS
UPDATE DORM SET DO_NUM=DO_NUM-(SELECT COUNT(T_NO)FROM DELETED DEL WHERE DO_NO=DEL.DO_NO)
WHERE DO_NO IN (SELECT DO_NO FROM DELETED)
gO
/*CREATE TRIGGER TRIGGER_DORMNUMUPDATE--这个触发器没用，问题在哪？？？
//ON STUDENT
//FOR UPDATE
//AS
//IF UPDATE(DO_NO)
//UPDATE DORM SET DO_NUM=DO_NUM-(SELECT COUNT(S_NO)FROM DELETED DEL WHERE DO_NO=DEL.DO_NO)+
//(SELECT COUNT(S_NO)FROM INSERTED INS WHERE DO_NO=INS.DO_NO)
//WHERE DO_NO IN (SELECT DO_NO FROM DELETED)
//GO
*/
/*缺陷：
//1、课程——教师专业不匹配，就是说A专业的老师可能教着B专业的课
//2、学生——选课专业不匹配，就是说A专业的学生可能选择B专业的课
//3、班级计算人数触发器只能对插入删除学生有效，修改班级时人数不发生相应的改变
//4、宿舍计算人数触发器只能对插入删除学生有效，修改宿舍时人数不发生相应的改变
//改进：
//1、添加存储过程：刷新班级人数
//2、添加存储过程：刷新宿舍人数
*/
