学生信息管理系统——建表

--创建密码表

create table pwd

(pno char(12) primary key,

ppwd char(16) not null,

pqx char(1) not null,

);

--创建学生表

create table student(

sno char(12) primary key,

sname char(20),

ssex char(2) check(ssex in('男','女')),

sbirth datetime,

class_id char(10),

sphone char (11),

saddress char (50),

FOREIGN KEY (class_id) REFERENCES class(class_id),

);

--创建教师表

create table teacher

(tno char(10) primary key,

tname char(20),

tsex char(2) check(tsex in('男','女')),

tbirth datetime,

truzhi datetime,   --入职年份

dept_id char(2),

tphone char(11),

tadddress char(50),

foreign key(dept_id) references dept(dno),

);

--院系

create table dept

(dno char(2) primary key,

dname char(30),

);

--专业

create table major

(mno char(10) primary key,

mname char(30) not null,

dept_id char(2),

foreign key (dept_id) references dept(dno),

);

--班级

create table class

(class_id char(10) primary key,

clname char(30) not null,

clruxue datetime, --入学年份

mno char(10),

foreign key (mno) references major(mno),

);

--课程表

create table course(

cno char(3) primary key,

cname char(30) not null,

credit char(1),

constraint ck_credit check(credit between 0 and 5),

);

--成绩表

create table cour_score

(sno char(12) not null,

cno char(3) not null,

score char(3),

constraint ck_score check(score between 0 and 100),

primary key(sno,cno),

foreign key(sno) references student(sno)

on delete cascade

on update cascade,

foreign key (cno) references course(cno)

on delete no action

on update cascade

);

--教师授课表

create table teach_course

(tno char(10) not null,

cno char(3) not null,

primary key (tno,cno),

foreign key (tno) references teacher(tno)

on delete cascade

on update cascade,

foreign key (cno) references course(cno)

on delete no action

on update cascade,

);