创建表teacher,score,course，user,student.sql

学生信息管理系统

use studentManageSystem
go
create table department
(
	department_id varchar(10)primary key,
	departmentName varchar(10)not null
) 
create table speciality
(
	speciality_id varchar(10)primary key,
	specialityName varchar(10)not null,
	department_id varchar(10)not null foreign key references department(department_id)
)

create table teacherInformation
(
	teacher_id varchar(10) primary key,
	name varchar(10)not null,
	department_id varchar(10) not null foreign key references department(department_id),
	sex char(2) not null check (sex in('男','女'))
)
create table teacherphone
(
	id int identity(1,1)primary key,
	teacher_id varchar(10)foreign key references teacherinformation(teacher_id),
	telephone varchar(15)
)
create table teacherEmail
(
	id int identity (1,1)primary key,
	teacher_id varchar(10)foreign key references teacherinformation(teacher_id),
	email varchar(40)
)
create table class
(
	class_id varchar(10)primary key,
	className varchar(20)not null,
	speciality_id varchar(10)not null foreign key references speciality(speciality_id),
	teacher_id varchar(10)not null foreign key references teacherinformation(teacher_id),
	matriculation_year int not null check(matriculation_year<year(getDate()))

)
create table student
(
	student_id varchar(10) primary key , 
	name varchar(10) not null,
	sex char(2)not null  check (sex in('男','女')),
	class_id varchar(10)not null foreign key references class(class_id),
	birth_date varchar(20) ,
	address varchar(50)not null)

create table studentphone
(
	id int identity (1,1) primary key,
	student_id varchar(10)  foreign key references student(student_id),
	telephone varchar(15)
)
create table studentemail
(
	id int identity (1,1)primary key,
	student_id varchar(10)  foreign key references student(student_id),
	email varchar(30)
)


create table course
(
	course_id  varchar(10) ,
	coursename varchar(20)not null,
	term char(1) not null check (term in('1','2','3','4','5','6','7','8')),
	examtype char(6)not null check (examtype in('必修课','选修课')),
	total int not null check(total>0),
	score int not null ,
	primary key(course_id,term)
)
create table exam
(
	course_id varchar(10)not null,
	student_id varchar(10)not null,
	teacher_id varchar(10)not null,
	term char(1) not null check (term in('1','2','3','4','5','6','7','8')),
	examtype char(6) check (examtype in('考试课','考察课')),
	examtime varchar(10) ,
	address varchar(20),
	primary key(student_id,course_id,term),
	foreign key (course_id,term)references course(course_id,term)
)
create table compulsory_score
(
	student_id varchar(10) not null references student(student_id),
	course_id varchar(10) not null,
	term char(1) not null check(term in('1','2','3','4','5','6','7','8')),
	score float  check (score>0 and score<=100),
	datetimes varchar(20),
	class_id varchar(10)not null references class(class_id),
	note varchar(60),
	primary key(student_id,course_id,term,class_id),
	foreign key (course_id,term)references course(course_id,term)
)

create table optional_score
(
	student_id varchar(10)not null references student(student_id),
	course_id varchar(10)not null,
	term char(1) not null check(term in('1','2','3','4','5','6','7','8')),
	grade char(8) check(grade in('优秀','良好','合格','不合格')),
	note varchar(60) ,
	datetimes varchar(20),
	primary key(student_id,course_id,term),
	foreign key (course_id,term)references course(course_id,term)
)

create table teacher_class_course
(
	teacher_id varchar(10)not null foreign key references teacherinformation(teacher_id),
	class_id varchar(10)not null foreign key references class(class_id),
	course_id varchar(10)not null,
	term char(1)not null check(term in('1','2','3','4','5','6','7','8')),
	primary key (teacher_id,class_id,course_id,term),
	foreign key (course_id,term)references course(course_id,term)
)

create table teacher_student_course
(
	  student_id varchar(10)not null foreign key references student(student_id),
	  teacher_id varchar(10)not null foreign key references teacherinformation(teacher_id),
	  course_id varchar(10)not null,
	  term char(1) not null check(term in('1','2','3','4','5','6','7','8')),
	  primary key (teacher_id,student_id,course_id,term),
	  foreign key (course_id,term)references course(course_id,term)
)

create table student_compulsorycourse
(
	student_id varchar(10) not null references student(student_id),
	course_id varchar(10)not null ,
	class_id varchar(10)not null  references class(class_id),
	term char(1) not null check(term in('1','2','3','4','5','6','7','8')),
	course_name varchar(20) not null,
	primary key (student_id,course_id,class_id,term),
	foreign key (course_id,term)references course(course_id,term)
)
create table student_optionalcourse
(
	
	student_id varchar(10)not null  references student(student_id),
	course_id varchar(10)not null  ,
	term char(1)not null check(term in('1','2','3','4','5','6','7','8')),
	course_name varchar(20) not null,
	primary key (student_id,course_id,term),
	foreign key (course_id,term)references course(course_id,term)
)
create table users
(
	userName varchar(10)primary key, 
	password varchar(10) default '111111' not null,
	type char(1)not null
)