数据库设计课程设计-高校选课管理系统.zip

/*建立班级表*/ create table 班级( 班级编号 nvarchar(4) primary key, 班级名称 nvarchar(20) not null, ) /*建立学生表*/ create table 学生( 学号 nvarchar(12) primary key, 姓名 nvarchar(5) not null, 性别 nvarchar(1) not null, 年龄 int not null, 生源所在地 nvarchar(20) not null, 已修学分总数 float , 登录密码 nvarchar(20) not null , 班级编号 nvarchar(4) , constraint FK_班级编号 foreign key(班级编号) references 班级 ) /*建立课程表*/ create table 课程( 课程编号 nvarchar(5) primary key, 课程名称 nvarchar(20) not null, 学时 int not null, 考试或考查 bit not null , 学分 float not null , ) /*建立教师表*/ create table 教师( 教师编号 nvarchar(5) primary key, 姓名 nvarchar(20) not null, 性别 nvarchar(1) not null, 年龄 int not null, 职称 nvarchar(10) not null, 电话 nvarchar(20) not null , 登录密码 nvarchar(20) not null , ) /*建立开课情况表*/ create table 开课情况( 开课编号 nvarchar(15) primary key, 课程编号 nvarchar(5) not null, 任课教师编号 nvarchar(5) not null, 开课学年 nvarchar(4) not null, 开课学期 bit not null, constraint FK_课程编号_开课 foreign key(课程编号) references 课程编号, constraint FK_任课教师编号_开课 foreign key(任课教师编号) references 教师, ) /*建立选课情况表*/ create table 选课情况( 开课编号 nvarchar(15), 学号 nvarchar(12), 成绩 float, primary key(开课编号,学号), constraint FK_开课编号_选课 foreign key(开课编号) references 开课情况, constraint FK_学号_选课 foreign key(学号) references 学生, ) /*建立管理员表*/ create table 管理员( 管理员编号 nvarchar(4) primary key , 登录密码 nvarchar(20) not null, ) /*建立未选课临时表*/ create table 未选课( 开课编号 nvarchar(15) primary key, 课程编号 nvarchar(5) not null, 任课教师编号 nvarchar(5) not null, 开课学年 nvarchar(4) not null, 开课学期 bit not null, ) /*建立未开课临时表*/ CREATE TABLE 未开课( 课程编号 nvarchar(5) primary key, 课程名称 nvarchar(20) not null, 学时 int not null, 考试或考查 bit not null , 学分 float not null , ) /*向班级表插入数据*/ insert into 班级 values ('1001','001','软工1701') ,('1002','001','软工1702') ,('1003','001','软工1703') ,('1004','001','软工1801') ,('1005','001','软工1802') ,('1006','001','软工1803') ,('2001','002','计科1701') ,('2002','002','计科1702') ,('2003','002','计科1703') ,('2004','002','计科1801') ,('2005','002','计科1802') ,('2006','002','计科1803') ,('3001','003','网工1701') ,('3002','003','网工1801') select * from 班级 /*向学生表插入数据*/ insert into 学生 values ('201706060101','李一','男',19,'浙江杭州',40,'123456','1001') ,('201706060102','李天欣','女',19,'浙江宁波',40,'123456','1001') ,('201706060103','郭文心','女',19,'浙江台州',40,'123456','1001') ,('201706060104','金涛','男',19,'浙江嘉兴',40,'123456','1001') ,('201706060105','王天音','女',19,'浙江金华',40,'123456','1001') ,('201706060106','林月人','男',19,'上海静安',40,'123456','1001') ,('201706060107','肖金鹏','男',19,'上海徐汇',40,'123456','1001') ,('201706060108','魏征','男',19,'浙江杭州',35,'123456','1001') ,('201706060110','李浩然','男',19,'浙江温州',40,'123456','1001') ,('201706060111','尤宏博','男',19,'浙江宁波',40,'123456','1001') ,('201706060112','郑莉莉','女',19,'浙江台州',40,'123456','1001') ,('201706070101','汤飞扬','男',19,'浙江杭州',20,'123456','2001') ,('201706070102','侯蒙蒙','男',19,'浙江宁波',20,'123456','2001') ,('201706070103','罗婷婷','女',19,'浙江台州',20,'123456','2001') ,('201706070104','金铭','男',19,'浙江嘉兴',20,'123456','2001') ,('201706070105','王萱萱','女',19,'浙江金华',20,'123456','2001') ,('201706070106','林国庆','男',19,'上海静安',20,'123456','2001') ,('201706070107','肖元宵','男',19,'上海徐汇',20,'123456','2001') ,('201706070108','庄晓曼','女',19,'浙江杭州',15,'123456','2001') ,('201706070110','吕齐铭','男',19,'浙江温州',20,'123456','2001') ,('201706070111','陈欣怡','女',19,'浙江宁波',20,'123456','2001') ,('201706070112','张世豪','男',19,'浙江台州',20,'123456','2001') ，('201706060109','张佳琪','男',19,'浙江丽水',38,'123456','1001') ,('201706070109','蒋博文','男',19,'浙江丽水',18,'123456','2001') select * from 学生 /*向课程表插入数据*/ insert into 课程 values ('J0001','数据结构',64,0,4) ,('J0002','C++程序设计',64,0,4) ,('J0003','数据库原理及应用',48,0,3) ,('J0004','机器学习',36,1,2) ,('J0005','Java程序设计',48,0,3) ,('J0006','操作系统原理',48,0,3) ,('L0001','高等数学',48,0,3) ,('M0001','马克思主义基本原理',48,0,3) ,('M0002','毛泽东思想概论',64,0,4) ,('J0007','编译原理',48,0,3) ,('L0002','信息论基础',36,0,3) ,('J0008','软件工程',48,1,3) select * from 课程 /*向教师表插入数据*/ insert into 教师 values ('T0001','李强','男',34,'讲师','13989895985','123456','001') , ('T0002','陆伟','男',44,'副教授','13789455985','123456','001') , ('T0003','何霜','女',51,'教授','13289458985','123456','001') , ('T0004','刘值','男',29,'讲师','13459845985','123456','001') , ('T0005','黄国庆','男',42,'副教授','13449894945','123456','001') , ('T0006','杨永利','男',49,'副教授','13945895455','123456','001') , ('T0007','洪邓媛','女',51,'教授','13983295325','123456','008') , ('T0008','余海潮','男',58,'教授','13915895155','123456','001') , ('T0009','马美玉','女',40,'讲师','13915891985','123456','008') , ('T0010','张超','男',39,'讲师','13129895155','123456','001') select * from 教师 /*向开课情况表插入数据*/ insert into 开课情况 values ,('J0001T000120180','J0001','T0001','2018',0) ,('J0001T000520180','J0001','T0005','2018',0) ,('J0003T000220181','J0003','T0002','2018',1) ,('J0003T000320181','J0003','T0003','2018',1) ,('J0004T000320181','J0004','T0003','2018',1) ,('J0004T000420181','J0004','T0004','2018',1) ,('M0002T000720181','M0002','T0007','2018',1) ,('J0005T000420180','J0005','T0004','2018',0) ,('J0006T000820190','J0006','T0008','2019',0) ,('J0006T000120190','J0006','T0001','2019',0) ,('J0006T001020190','J0006','T0010','2019',0) ,('J0007T000120190','J0007','T0001','2019',0) ,('J0008T000720190','J0008','T0007','2019',0) ,('L0002T000820190','L0002','T0008','2019',0) select * from 开课情况 /*向选课情况插入数据*/ insert into 选课情况 values ('J0001T000120180','201706060101',98) ,('J0001T000120180','201706060102',88) ,('J0001T000120180','201706060103',45) ,('J0001T000120180','201706060104',59) ,('J0001T000120180','201706060105',68) ,('J0001T000120180','201706060106',78) ,('J0001T000120180','201706060107',82) ,('J0001T000120180','201706060108',92) ,('J0001T000120180','201706060109',94) ,('J0001T000120180','201706060110',91) ,('J0001T000120180','201706060111',76) ,('J0001T000120180','201706060112',78) ,('J0001T000520180','201706070101',95) ,('J0001T000520180','201706070102',85) ,('J0001T000520180','201706070103',47) ,('J0001T000520180','201706070104',57) ,('J0001T000520180','201706070105',67) ,('J0001T000520180','201706070106',74) ,('J0001T000520180','201706070107',95) ,('J0001T000520180','201706070108',97) ,('J0001T000520180','201706070109',92) ,('J0001T000520180','201706070110',97) ,('J0001T000520180','201706070111',71) ,('J0001T000520180','201706070112',74) ,('J0003T000220181','201706060101',78) ,('J0003T000220181','201706060102',99) ,('J0003T000220181','201706060103',100) ,('J0003T000220181','201706060104',98.5) ,('J0003T000220181','201706060105',59.5) ,('J0003T000220181','2017060601