学生选课系统SqlSever数据库课程设计

把数据库设计视频看完，写好数据库设计 多建点视图、触发器、索引、存储结构、安全设置。星期四之前完成，C# 学生表 create table Student( Sno char(10) primary key, Sname char(10) not null, Ssex tinyint not null, Spass char(32) not null, Sdept varchar(50) not null, Sprofess varchar(50) not null, ) 老师表 create table Teacher( Tno char(10) not null primary key, Tname char(10) not null, Tsex tinyint not null, Tpass char(32) not null, ) 课程表 create table Course( Cid int identity(1,1) primary key, Cname varchar(50) not null unique, ) 老师授课表 create table TCourse( TCid int identity(1,1) primary key, Tid int not null, Cid int not null, Cscore tinyint not null, Cperson tinyint not null, Cnow tinyint, Ctime int not null, foreign key (Tid) references Teacher(Tid), foreign key (Cid) references Course(Cid), ) 学生选课表 create table SCourse( SCid int identity(1,1) primary key, Sid int not null, TCid int not null, SCscore tinyint, SCstate tinyint, foreign key (Sid) references Student(Sid), foreign key (TCid) references TCourse(TCid), ) 数据库存储过程 create procedure addStudent(@Sno char(10),@Sname char(10),@Ssex int,@Sdept varchar(50),@Sprofess varchar(50),@Spass char(32)) as insert into Student values(@Sno,@Sname,@Ssex,HASHBYTES('MD5',@Spass),@Sdept,@Sprofess) 》》exec addStudent '2015010121','jiangang',1,'信工系','计科专业','121961' 时间戳 SELECT DATEADD(S,1160701488 + 8 * 3600,'1970-01-01 00:00:00') --时间戳转换成普通时间 SELECT DATEDIFF(S,'1970-01-01 00:00:00', '2006-10-13 09:04:48.000') - 8 * 3600 --普通时间转换成时间戳 插入时间 create procedure addTCourse(@Cid int,@Tid int,@Cscore int,@Cperson int,@Ctime char(50),@Cendtime char(50)) as insert into Student values(@Tid,@Cid,@Cscore,@Cperson, DATEDIFF(S,'1970-01-01 00:00:00', @Ctime) - 8 * 3600, DATEDIFF(S,'1970-01-01 00:00:00', @Cendtime) - 8 * 3600); 》》》exec addTCourse 1,1,5,50,'2017/12/19','2018/1/1'; 》》》alter table TCourse alter column Cnow int default 0;? 新建视图 1、当前可以选修的课程（当前时间<课程结束时间） create view chooseCourse as select TCid,c.Cname,Cscore,Cperson,Cnow,CONVERT(VARCHAR(24),DATEADD(S,Ctime + 8 * 3600,'1970-01-01'),102) as beginTime,CONVERT(VARCHAR(24),DATEADD(S,Cendtime + 8 * 3600,'1970-01-01'),102) as endTime from TCourse as tc left join Course as c on tc.Cid = c.Cid where (DATEDIFF(S,'1970-01-01 00:00:00', getdate()) - 8 * 3600)<Cendtime; 2、新建试图所有学生选课记录（效率问题） create view stCourse as select sc.TCid,c.Cname,Cscore,Cperson,Cnow,CONVERT(VARCHAR(24),DATEADD(S,Ctime + 8 * 3600,'1970-01-01'),102) as beginTime, CONVERT(VARCHAR(24),DATEADD(S,Cendtime + 8 * 3600,'1970-01-01'),102) as endTime from TCourse as tc left join SCourse as sc on tc.TCid=sc.TCid left join Course as c on tc.Cid = c.Cid 新建触发器 选课触发器 create trigger chCourse ON SCourse for insert as declare @SCid int,@TCid int,@res int select @SCid=SCid,@TCid=TCid from inserted set @res = (select count(TCid) from TCourse where TCid=@TCid and Cnow<Cperson) if(@res=1) update TCourse set Cnow+=1 where TCid=@TCid; else delete from SCourse where SCid=@SCid