设计《Teaching 教学数据库》
--一、创建数据库Teaching
SET NOCOUNT ON
SET DATEFORMAT YMD
GO
USE MASTER
GO
IF EXISTS(SELECT * FROM SYSDATABASES WHERE NAME='Teaching')--如果存在则删除
DROP DATABASE Teaching
GO
CREATE DATABASE Teaching
ON
(NAME=Teaching_data1,
FILENAME='D:\Teaching_data1.mdf',
SIZE=5MB,
MAXSIZE=50MB,
FILEGROWTH=1MB
)
LOG ON
(NAME=Teaching_log1,
FILENAME='D:\Teaching_log1.ldf',
SIZE=2MB,
MAXSIZE=100MB,
FILEGROWTH=10% )
GO
--二、创建S（学生表）
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='S')--如果S表存在则删除
DROP TABLE dbo.S
GO
USE Teaching
GO
CREATE TABLE S
(SNO char(6) NOT NULL,
SNAME varchar(8) NOT NULL,
SSEX char(2) NOT NULL,
SDEPT varchar(10) NOT NULL,
SBIRTHDAY date NOT NULL)
GO
--三、创建C（课程表）
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='C')
DROP TABLE dbo.C
GO
CREATE TABLE C
(CNO CHAR (6) NOT NULL,
CNAME VARCHAR(20) NOT NULL,
CT SMALLINT NULL,
TNO CHAR(6) NULL)
GO
--四、创建SC（选课表）
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SC')
DROP TABLE dbo.SC
GO
CREATE TABLE SC
(SNO CHAR(6) NOT NULL,
CNO CHAR(6) NOT NULL,
SCORE SMALLINT NULL
)
GO
--五、创建T（教师表）
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='T')
DROP TABLE dbo.T
GO
CREATE TABLE T
(TNO CHAR(6) NOT NULL,
TNAME VARCHAR(8) NOT NULL,
TSEX CHAR(2) NOT NULL,
TITLE VARCHAR(10) NULL
)
GO
--六、给表添加主键约束
USE Teaching
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='S_PK')
ALTER TABLE S DROP CONSTRAINT S_PK
GO
ALTER TABLE S ADD CONSTRAINT S_PK PRIMARY KEY(SNO)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='C_PK')
ALTER TABLE S DROP CONSTRAINT C_PK
GO
ALTER TABLE C ADD CONSTRAINT C_PK PRIMARY KEY(CNO)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SC_PK')
ALTER TABLE S DROP CONSTRAINT SC_PK
GO
ALTER TABLE SC ADD CONSTRAINT SC_PK PRIMARY KEY(SNO,CNO)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='T_PK')
ALTER TABLE S DROP CONSTRAINT T_PK
GO
ALTER TABLE T ADD CONSTRAINT T_PK PRIMARY KEY(TNO)
GO
--七给表添加外键约束
USE Teaching
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='C_FK')
ALTER TABLE S DROP CONSTRAINT C_FK
GO
ALTER TABLE C ADD CONSTRAINT C_FK FOREIGN KEY(TNO) REFERENCES T(TNO)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SC_FK_SNO')
ALTER TABLE S DROP CONSTRAINT SC_FK_SNO
GO
ALTER TABLE SC ADD CONSTRAINT SC_FK_SNO FOREIGN KEY(SNO) REFERENCES S(SNO)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SC_FK_CNO')
ALTER TABLE S DROP CONSTRAINT SC_FK_CNO
GO
ALTER TABLE SC ADD CONSTRAINT SC_FK_CNO FOREIGN KEY(CNO) REFERENCES C(CNO)
GO
--八、给表添加检查约束
USE Teaching
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='S_CK')
ALTER TABLE S DROP CONSTRAINT S_CK
ALTER TABLE S ADD CONSTRAINT S_CK CHECK(SSEX='男' OR SSEX='女')
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='SC_CK')
ALTER TABLE S DROP CONSTRAINT SC_CK
ALTER TABLE SC ADD CONSTRAINT SC_CK CHECK(SCORE>=0 AND SCORE<=100)
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='T_SEX')
ALTER TABLE S DROP CONSTRAINT T_SEX
ALTER TABLE T ADD CONSTRAINT T_SEX CHECK(TSEX='男' OR TSEX='女')
GO
--九、给表添加默认约束
USE Teaching
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='T_DK')
ALTER TABLE S DROP CONSTRAINT T_DK
ALTER TABLE T ADD CONSTRAINT T_DK DEFAULT '助教' FOR TITLE
GO
--十、输入测试数据
INSERT T VALUES('T1','李力','男','教授')
INSERT T VALUES('T2','王平','女','讲师')
INSERT T VALUES('T3','刘伟','男','讲师')
INSERT T VALUES('T4','张雪','女','教授')
INSERT T VALUES('T5','张兰','女','副教授')
INSERT T VALUES('T6','王一','男','助教')
INSERT T VALUES('T7','李明','男','助教')
GO
INSERT S VALUES('95001','李勇','男','数学','1978-03-05')
INSERT S VALUES('95002','刘晨','女','信息','1977-10-03')
INSERT S VALUES('95003','王敏','女','数学','1978-05-10')
INSERT S VALUES('95004','张立','男','信息','1979-04-07')
INSERT S VALUES('95005','欧阳锋','男','信息','1980-07-30')
INSERT S VALUES('95006','欧阳正华','男','数学','1981-06-09')
INSERT S VALUES('95007','刘秋明','女','计算机','1980-08-10')
INSERT S VALUES('95008','李岩','女','计算机','1983-05-23')
GO
INSERT C VALUES('C1','数据库',50,'T1')
INSERT C VALUES('C2','数学',80,'T5')
INSERT C VALUES('C3','信息系统',30,'T3')
INSERT C VALUES('C4','操作系统',60,'T2')
INSERT C VALUES('C5','数据结构',80,'T1')
INSERT C VALUES('C6','计算机网络',50,'T3')
INSERT C VALUES('C7','C语言',60,'T4')
INSERT C VALUES('C8','DB_Design',50,'T4')
GO
INSERT SC VALUES('95001','C1',92)
INSERT SC VALUES('95001','C2',85)
INSERT SC VALUES('95001','C3',NULL)
INSERT SC VALUES('95001','C4',NULL)
INSERT SC VALUES('95001','C5',NULL)
INSERT SC VALUES('95002','C2',90)
INSERT SC VALUES('95002','C3',80)
INSERT SC VALUES('95003','C5',50)
INSERT SC VALUES('95004','C2',66)
INSERT SC VALUES('95004','C3',NULL)
INSERT SC VALUES('95007','C1',80)
GO
--十一、创建学生选课视图
CREATE VIEW S_SCORE(学号,姓名,系部,课程号,课程名,学时,授课教师号,授课教师名,成绩)
AS
SELECT S.SNO,SNAME,SDEPT,C.CNO,CNAME,CT,T.TNO,TNAME,SCORE
FROM S LEFT JOIN SC
ON S.SNO=SC.SNO
LEFT JOIN C
ON SC.CNO=C.CNO
LEFT JOIN T
ON C.TNO=T.TNO
GO
--十二、创建C(课程表)索引
CREATE INDEX IX_CNO ON C(CNAME)
GO
--十三、创建触发器,限定学生选课门数不超过5门
CREATE TRIGGER TR_SC_COU
ON SC
FOR INSERT
AS
DECLARE @COUNT INT
SELECT @COUNT=COUNT(CNO) FROM SC WHERE SNO=(SELECT SNO FROM INSERTED)
IF @COUNT>=5
BEGIN
ROLLBACK
RAISERROR('最多选修5门课程！',16,1)
END
--十四、]创建存储过程，根据给定的课程号，打印该课程的成绩清单
CREATE PROC PrintScore
@COUNO VARCHAR(6)
AS
DECLARE @CNO VARCHAR(5),@COUNAME VARCHAR(20),@SNO VARCHAR(6),@SNAME VARCHAR(8),@SCORE SMALLINT
DECLARE Cr1 CURSOR FOR
SELECT C.CNO,CNAME,S.SNO,SNAME,SCORE
FROM S,C,SC
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND SC.CNO=@COUNO
OPEN Cr1
PRINT '--------------------成绩清单-----------------'
PRINT '课程号 课程名 学号 姓名 成绩'
FETCH NEXT FROM Cr1 INTO @CNO,@COUNAME,@SNO,@SNAME,@SCORE
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @CNO++SPACE(5)+CONVERT(CHAR(6),@COUNAME)+SPACE(5)+@SNO+SPACE(3)+CONVERT(CHAR(8),@SNAME)+SPACE(3)+CONVERT(CHAR(3),@SCORE)
FETCH NEXT FROM Cr1 INTO @CNO,@COUNAME,@SNO,@SNAME,@SCORE
END
CLOSE Cr1
DEALLOCATE Cr1
