求组，新手SQL求改进，有问题的地方都标了，求高手赐教

[daijianlu]

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、添加存储过程：刷新宿舍人数
*/

[Shawn]

--设计得有问题
#1.专业,班级,宿舍,教室,教师,课程,学生 每个一张表,各自定义主键,之间没有任何联系
#2.他们之间的关系,再建立另外的关系表来描述。包括学生的选课表
#3.不需要触发器。当班级增加修改删除学生时，直接操作#2中关系表的记录即可
#4.至于要统计班级，宿舍的人数等问题。可以直接从关系表查询出数量；或者再建立两个统计表，当关系表增加删除记录时，实时个性统计表（和你的触发器道理一样，也可建立触发器来实现）
但一般你的班级和人数都不是太大，所以直接统计更好维护。唯一的缺点是要写统计的SQL。但也应该不会太复杂。

[daijianlu]

引用 1 楼 wwwwgou 的回复:

--设计得有问题
#1.专业,班级,宿舍,教室,教师,课程,学生 每个一张表,各自定义主键,之间没有任何联系
#2.他们之间的关系,再建立另外的关系表来描述。包括学生的选课表
#3.不需要触发器。当班级增加修改删除学生时，直接操作#2中关系表的记录即可
#4.至于要统计班级，宿舍的人数等问题。可以直接从关系表查询出数量；或者再建立两个统计表，当关系表增加删除记录时，实时个性统计表（和你的触发器道理一样，也可建立触发器来实现）
但一般你的班级和人数都不是太大，所以直接统计更好维护。唯一的缺点是要写统计的SQL。但也应该不会太复杂。

那么多表那以后查询不是要连接很多表，不是n:1关系最好和实体合并吗？？ 我可不可以把那两个没弄好的约束弄到前台程序上？？ 我知道我这触发器是很坑的，很慢，但想不出别的办法出来啊。。

[Shawn]

永远没有最好，只有合适不合适。 表设计是基础，设计时要考虑到它的可扩展和可维护性。 请楼主，试一下，用我思路建立表，然后看看，除了表多些，查询数据时要多JOIN几张表，还有其它问题吗？

[daijianlu]

引用 3 楼 wwwwgou 的回复:

永远没有最好，只有合适不合适。 表设计是基础，设计时要考虑到它的可扩展和可维护性。 请楼主，试一下，用我思路建立表，然后看看，除了表多些，查询数据时要多JOIN几张表，还有其它问题吗？

诶，好的