34,594
社区成员
发帖
与我相关
我的任务
分享
--老师表
create table Teacher
(
t_id char(5) primary key,
t_name varchar(10)
)
go
--班级表
create table Class
(
c_id varchar(10) primary key,
c_name varchar(20),
c_belong varchar(26),--所属学院
c_major varchar(10),
c_num tinyint,
c_grade smallint
)
go
--学生表
create table Student
(
stu_id char(10) ,
stu_name varchar(10),
入校时间 smalldatetime,
c_id varchar(10) ,
primary key(stu_id , c_id),
foreign key(c_id) references Class(c_id)
)
go
--课程表
create table Curriculum
(
k_id char(4) ,
t_id char(5) ,
c_id varchar(10) ,
k_name varchar(20),
k_发布时间 smalldatetime,
k_时长 tinyint,
k_optional char(4)
check(k_optional = '必修' or k_optional = '选修'),
k_address char(4),
k_starttime smalldatetime,
k_credit float, -- 学分
k_examtime smalldatetime,
k_examaddress char(4),
k_exam_makeuptime smalldatetime,
K_exam_makeupaddress char(4),
primary key(k_id , t_id , c_id),
foreign key(t_id) references Teacher(t_id),
foreign key(c_id) references Class(c_id)
)
go
--成绩表
create table Grade
(
k_id char(4) ,
stu_id char(10) ,
g_peacetime float default null,
g_Test float default null,
g_Total float default null,
g_makeup float default null,
primary key(k_id , stu_id),
foreign key(k_id) references Curriculum(k_id),
foreign key(stu_id) references Student(stu_id)
)
go
alter table Curriculum add constraint uq_Curriculum_k_id unique (k_id)
alter table Student add constraint uq_Student_stu_id unique (stu_id)
--成绩表
create table Grade
(
k_id char(4) ,
stu_id char(10) ,
g_peacetime float default null,
g_Test float default null,
g_Total float default null,
g_makeup float default null,
primary key(k_id , stu_id),
foreign key(k_id) references Curriculum(k_id),
foreign key(stu_id) references Student(stu_id)
)