新建表格

use hngy
CREATE  TABLE  DepartInfo /*院系信息表（DepartInfo）*/        
(
    DepNO  char(4)   PRIMARY KEY,   
    DepName  varchar(30)  not null, 
)
GO
CREATE  TABLE  SpecialtyInfo/*专业信息表（SpecialtyInfo）*/         
(
    SpeNO  char(5)   PRIMARY KEY,   
    SpeName  varchar(20)  not null,
    DepNO char(4)
)
GO
CREATE  TABLE  StudentInfo /*学生信息表（StudentInfo）*/       
(
    StuNO  char(12)   PRIMARY KEY,   
    StuName  varchar(20)  not null,
    Sex char(2) check(sex='男'or sex='女'),
    Birth datetime,
    EnterDate int,
    SpeNO char(5),
    Address varchar(50)
)
GO
CREATE  TABLE  TeacherInfo /* 教师信息表（ TeacherInfo）*/       
(
    TNO  char(5)   PRIMARY KEY,
    TName varchar(20),
    Sex char(2)default'男',
    Birth datetime,
    Position char(8)default'讲师',
    DepNO char(4),
    Phone char(13),
    Email varchar(50),
)
GO
CREATE  TABLE  CourseInfo /*课程信息表（CourseInfo）*/        
(
    CNO  char(8)   PRIMARY KEY,   
    CName  varchar(50)  not null,
    Credit int not null,
    ClassHour int not null
)
GO
CREATE  TABLE  CourseStatus  /*课程状态表（CourseStatus）*/       
(
    CNO  char(8)   ,   
    SpeNO  varchar(5),
    constraint pk_CourseStatus PRIMARY KEY(CNO,SpeNO),/*组合主键*/
    Semester int,
    Ctype varchar(10),
    OrdinarySc bit,
    TNO char(5)
)
GO
CREATE  TABLE  SC  /*学生成绩表(SC)*/       
(
    StuNo  char(12),
    CNO char(8),
    constraint pk_SC PRIMARY KEY(StuNo,CNO),/*组合主键*/
    OrdinarySc float(8),
    EndSc float(8),
    TotalSc float(8)
)
GO
CREATE  TABLE  UserInfo /*用户信息表(UserInfo)*/         
(
    UserID  varchar(20)   PRIMARY KEY,   
    UserPassword varchar(20)  not null,
    UserIdentity varchar(20)  not null 
)
GO

alter table SpecialtyInfo
add constraint fk_DepNO foreign key(DepNO)
references DepartInfo(DepNO)

alter table StudentInfo
add constraint fk_SpeNO foreign key(SpeNO)
references SpecialtyInfo(SpeNO)

alter table TeacherInfo
add constraint fk_DepNO foreign key(DepNO)
references DepartInfo(DepNO)

alter table CourseStatus
add constraint fk_CNO foreign key(CNO)
references CourseInfo(CNO)

alter table CourseStatus
add constraint fk_SpeNO foreign key(SpeNO)
references SpecialtyInfo(SpeNO)

alter table CourseStatus
add constraint fk_TNO foreign key(TNO)
references TeacherInfo(TNO)

alter table SC
add constraint fk_StuNo foreign key(StuNo)
references StudentInfo(StuNo)

alter table SC
add constraint fk_CNO foreign key(CNO)
references CourseInfo(CNO)