sql.txt

学生选课数据库文件

create table student
(学号 char(10) primary key,
 姓名 char(10),
 性别 char(2),
 出生年月 char(8),
 班级号 char(10)
foreign key (班级号) references class(班级号)
);
create table teacher
(教师号 char(10) primary key,
 姓名 char(10),
 性别 char(2),
 职称 char(10)
);
create table course
(课程号 char(10) primary key,
 课程名 char(10),
 学分 smallint,
 教科书 char(20)
);
create table department
(系号 char(10) primary key,
 系名 char(10),
 系主任 char(10)
);
create table major
(专业号 char(10) primary key ,
 专业名 char(10),
 系号 char(10),
 foreign key (系号)references department(系号)
);
create table class
(班级号 char(10) primary key,
 专业号 char(10),
 foreign key (专业号) references major(专业号)
);
create table sc
(学号 char(10),
 课程号 char(10),
 成绩 smallint,
 primary key(学号,课程号),
 foreign key (学号) references student(学号),
 foreign key (课程号) references course(课程号)
);
create table tc
(教师号 char(10),
 课程号 char(10),
 教室 char(10),
 开课时间 char(8),
 foreign key (教师号) references teacher(教师号),
 foreign key (课程号) references course(课程号),
 primary key(教师号,课程号)
);
 
insert
into department
values('001','IS','陈庆强');
insert
into department
values('002','CS','金解放');
insert
into department
values('003','ES','林撒播');
insert 
into major
values('101','计算机','001');
insert 
into major
values('102','网络工程','001');
insert 
into major
values('103','信息管理','001');
insert 
into class
values('w0601','102');
insert 
into class
values('w0602','102');
insert 
into class
values('j0601','101');
 insert 
 into student 
 values('0306109201','陈与','女','19880202','w0602');
insert 
 into student 
 values('0306109202','林美美','女','19870826','w0602');
insert 
 into student 
 values('0306109203','苏拉拉','女','19880220','w0602');
insert 
 into student 
 values('0306109204','王来一','男','19870812','w0602');
insert 
 into student 
 values('0306109205','陈名明','男','19871102','w0602');
insert 
into teacher
values('10001','王小明','男','讲师');
insert 
into teacher
values('10002','林小红','女','教授');
insert 
into teacher
values('10003','蓝玉','男','副教授');
insert 
into course 
values('2001','数据库','4','数据库原理');
insert 
into course 
values('2002','操作系统','4','操作系统原理及其应用');
insert 
into course 
values('2003','微机原理','4','微机原理与接口技术');