Database Learning Notes (iv)

Generate Database Teaching Script

Use master
GO


IF EXISTS (
SELECT Name
From sys.databases
WHERE name = N ' teaching '
)
DROP DATABASE Teaching
CREATE DATABASE Teaching
On PRIMARY
(NAME = n ' teaching ', FILENAME = N ' E:\DATA\teaching.mdf ', SIZE = 5120KB, MAXSIZE = UNLIMITED, filegrowth = 1024KB)
LOG on
(NAME = n ' teaching_log ', FILENAME = N ' E:\DATA\teaching_log.ldf ', SIZE = 1024KB, MAXSIZE = 2048GB, filegrowth = 10%)
GO


Use teaching
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' Student '
)
DROP TABLE Student
CREATE TABLE Student (
Studentno nchar (Ten) not NULL,
Sname NCHAR (8) Not NULL,
Sex nchar (1) Not NULL,
Birthday datetime not NULL,
Classno NCHAR (6) Not NULL,
Point smallint is not NULL,
Phone nchar () not NULL,
Email nchar () not NULL,
CONSTRAINT ck_sex CHECK (sex= ' male ' OR sex= ' female '),
CONSTRAINT pk_student PRIMARY KEY CLUSTERED (Studentno ASC)
)
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' Course '
)
DROP TABLE Dbo.course
CREATE TABLE Dbo.course (
Courseno NCHAR (6) Not NULL,
CNAME nchar () NULL,
Type nchar (8) NULL,
Period tinyint NULL,
Credit numeric (4, 1) NULL,
CONSTRAINT pk_course PRIMARY KEY CLUSTERED (Courseno ASC)
)
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' Teacher '
)
DROP TABLE Dbo.teacher
CREATE TABLE Dbo.teacher (
Teacherno NCHAR (6) Not NULL,
Tname nchar (8) NULL,
Major nchar (TEN) NULL,
Prof NChar (Ten) not NULL,
Department nchar () NULL,
CONSTRAINT pk_teacher PRIMARY KEY CLUSTERED (Teacherno ASC)
)
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' class '
)
DROP TABLE Dbo.class
CREATE TABLE Dbo.class (
Classno NCHAR (6) Not NULL,
ClassName nchar () NULL,
Department nchar () NULL,
Monitor nchar (8) NULL,
CONSTRAINT pk_class PRIMARY KEY CLUSTERED (Classno ASC)
)
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' score '
)
DROP TABLE Dbo.score
CREATE TABLE Dbo.score (
Studentno nchar (Ten) not NULL,
Courseno NCHAR (6) Not NULL,
Usually numeric (6, 2) DEFAULT 0,
Final numeric (6, 2) DEFAULT 0,
Score as usually*30/100+final*70/100,
CONSTRAINT pk_score PRIMARY KEY CLUSTERED (studentno asc,courseno ASC)
)
GO


IF EXISTS (
SELECT * FROM sys.objects
WHERE name = N ' Teach_class '
)
DROP TABLE Dbo.teach_class
CREATE TABLE Dbo.teach_class (
Teacherno NCHAR (6) Not NULL,
Classno NCHAR (6) Not NULL,
Courseno NCHAR (6) Not NULL,
CONSTRAINT pk_teach_class PRIMARY KEY CLUSTERED (teacherno asc,classno Asc,courseno ASC)
)
GO


ALTER TABLE Score
ADD CONSTRAINT fk_score_student FOREIGN KEY (studentno) REFERENCES student (Studentno) on UPDATE CASCADE on DELETE CASCADE ,
CONSTRAINT fk_score_course FOREIGN KEY (Courseno) REFERENCES Course (Courseno) on UPDATE CASCADE on DELETE CASCADE
GO


ALTER TABLE Teach_class
ADD CONSTRAINT fk_teach_class_teacher FOREIGN KEY (teacherno) REFERENCES Teacher (Teacherno) on UPDATE CASCADE on DELETE C Ascade,
CONSTRAINT fk_teach_class_class FOREIGN KEY (CLASSNO) REFERENCES class (Classno) on UPDATE CASCADE on DELETE CASCADE,
CONSTRAINT fk_teach_class_course FOREIGN KEY (Courseno) REFERENCES Course (Courseno) on UPDATE CASCADE on DELETE CASCADE
GO


CREATE RULE Score_rule as
@score between 0 and 100
GO
EXEC sp_bindrule ' score_rule ', ' score.usually '
EXEC sp_bindrule ' score_rule ', ' score.final '


INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0822111208 ', ' Han Yanqiu ', ' Male ', ' 1989-12-18 ', ' 080601 ', 666, ' 15878945612 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0823210007 ', ' Stay far ', ' Male ', ' 1988-12-7 ', ' 080501 ', 658, ' 12545678998 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0824113307 ', ' Cui Yan ', ' Male ', ' 1988-4-8 ', ' 080601 ', 787, ' 15556845645 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0922210009 ', ' Xu Hai Bing ', ' Male ', ' 1989-9-8 ', ' 080501 ', 789, ' 13623456778 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0922221324 ', ' He Ying ', ' female ', ' 1990-10-7 ', ' 080501 ', 879, ' 13178978999 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0925111109 ', ' Jing Bingchen ', ' Male ', ' 1991-1-2 ', ' 080801 ', 789, ' 15678945623 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0925121107 ', ' liangxin ', ' female ', ' 1989-7-7 ', ' 080502 ', 777, ' 13145678921 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0935222201 ', ' Herman Gustav ', ' Female ', ' 1990-8-9 ', ' 080502 ', 867, ' 15978945645 ', ' [email protected] '
INSERT into student (Studentno,sname,sex,birthday,classno,point,phone,email) VALUES (' 0937221508 ', ' placid ', ' female ', ' 1989-12-17 ', ' 080802 ', 789, ' 12367823453 ', ' [email protected] '


Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t05001 ', ' Korean promotion ', ' software engineering ', ' Professor ', ' Computer Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t05003 ', ' Liu Yuan ', ' network technology ', ' Professor ', ' Computer Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t05011 ', ' Sea seal ', ' computer design ', ' Associate Professor ', ' Computer Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t05017 ', ' Lumingshin ', ' Software test ', ' Lecturer ', ' Computer Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t06011 ', ' Hu Haiyuai ', ' mechanical manufacturing ', ' Professor ', ' Mechanical Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t06023 ', ' Yiu Siyuan ', ' Foundry process ', ' associate Professor ', ' Mechanical Academy ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t07019 ', ' Ma Efen ', ' economic management ', ' lecturers ', ' School of Management ')
Insert into teacher (teacherno,tname,major,prof,department) VALUES (' t08017 ', ' Tian Yu ', ' Financial management ', ' associate Professor ', ' Management Academy ')


Insert into course (Courseno,cname,type,period,credit) VALUES (' c05103 ', ' electronic technology ', ' required ', 64,4.0)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c05109 ', ' C ', ' compulsory ', 72,4.5)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c05127 ', ' data structure ', ' required ', 64,4.0)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c05138 ', ' Software engineering ', ' elective ', 48,3.0)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c06108 ', ' mechanical drawing ', ' required ', 60,3.5)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c06127 ', ' Mechanical design ', ' compulsory ', 64,4.0)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c06172 ', ' casting process ', ' elective ', 42,2.5)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c08106 ', ' economic law ', ' compulsory ', 48,3.0)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c08123 ', ' finance ', ' compulsory ', 40,2.5)
Insert into course (Courseno,cname,type,period,credit) VALUES (' c08171 ', ' Accounting software ', ' elective ', 32,2.0)


INSERT into Class (Classno,classname,department,monitor) VALUES (' 080501 ', ' Count 08 of Ben 1 ', ' Computer Academy ', ' Han Yanqiu ')
INSERT into Class (Classno,classname,department,monitor) VALUES (' 080502 ', ' Count 08 of Ben 2 ', ' Computer Academy ', ' Stay far ')
INSERT into Class (Classno,classname,department,monitor) VALUES (' 080601 ', ' Machine 08 Ben 1 class ', ' Mechanical Academy ', ' Cui Yan ')
INSERT into Class (Classno,classname,department,monitor) VALUES (' 080602 ', ' Machine 08 Ben 2 class ', ' Mechanical Academy ', ' Han Yanqiu ')
INSERT into Class (Classno,classname,department,monitor) VALUES (' 080801 ', ' tube 08 Ben 1 ', ' Management Academy ', ' Xu Hai Bing ')
INSERT into Class (Classno,classname,department,monitor) VALUES (' 080802 ', ' tube 08 Ben 2 ', ' Management Academy ', ' He Ying ')


Insert INTO score (studentno,courseno,usually,final) VALUES (' 0822111208 ', ' c05109 ', 85.00,91.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0822111208 ', ' c06108 ', 89.00,95.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0822111208 ', ' c06127 ', 78.00,67.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0823210007 ', ' c05103 ', 82.00,69.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0823210007 ', ' c05109 ', 87.00,86.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0824113307 ', ' c05103 ', 85.00,77.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0824113307 ', ' c05109 ', 88.00,79.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0824113307 ', ' c06108 ', 66.00,82.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0922210009 ', ' c05103 ', 87.00,82.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0922210009 ', ' c05109 ', 77.00,91.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0922221324 ', ' c05103 ', 88.00,62.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0922221324 ', ' c05109 ', 91.00,77.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0925111109 ', ' c08106 ', 79.00,99.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0925111109 ', ' c08123 ', 85.00,92.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0925111109 ', ' c08171 ', 77.00,92.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0925121107 ', ' c05103 ', 74.00,91.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0925121107 ', ' c05109 ', 89.00,62.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0935222201 ', ' c05109 ', 99.00,92.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0935222201 ', ' c08171 ', 95.00,82.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0937221508 ', ' c08106 ', 78.00,95.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0937221508 ', ' c08123 ', 78.00,89.00)
Insert INTO score (studentno,courseno,usually,final) VALUES (' 0937221508 ', ' c08171 ', 88.00,98.00)


Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05001 ', ' 080501 ', ' c05103 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05001 ', ' 080501 ', ' c05109 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05001 ', ' 080501 ', ' c05127 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05017 ', ' 080501 ', ' c05138 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05017 ', ' 080501 ', ' c06108 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t05017 ', ' 080501 ', ' c06127 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t08017 ', ' 080501 ', ' c08171 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t08017 ', ' 080501 ', ' c06172 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t08017 ', ' 080501 ', ' c08106 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t07019 ', ' 080501 ', ' c08123 ')
Insert into Teach_class (Teacherno,classno,courseno) VALUES (' t07019 ', ' 080501 ', ' c08171 ')
GO

Database Learning Notes (iv)