S2 优化数据库设计 第二章上级

USE masterGOIF EXISTS(SELECT * FROM sysdatabases WHERE name='MySchool1')  --判断DROP DATABASE MySchool1   --删除语句CREATE DATABASE MySchool1  --执行ON  (NAME='MySchool10.mdf',    --逻辑名称FILENAME='F:\vis\MySchool10.mdf',   --物理名称SIZE=10,   --初始大小FILEGROWTH=20%   --增长率),(NAME='MySchool10.ndf',FILENAME='F:\vis\MySchool10.ndf',SIZE=10,FILEGROWTH=20%)LOG ON(NAME='MySchool1_log',FILENAME='F:\vis\MySchool1log_log.ldf',SIZE=3,MAXSIZE=20,  --增长的最大值FILEGROWTH=1),(NAME='MySchool2_log',FILENAME='F:\vis\MySchool2log_log.ldf',SIZE=3,MAXSIZE=20,FILEGROWTH=1)USE  MySchool1 IF EXISTS(SELECT * FROM sysobjects WHERE NAME='kecheng')  --判断DROP TABLE kecheng         --删除CREATE TABLE kecheng(       --创建SubjectName nvarchar(50),ClassHour int,GradeID int)ALTER TABLE kechengADD SubjectNo int IDENTITY(1,1)USE MySchool1IF EXISTS(SELECT * FROM sysobjects WHERE NAME='chengji' )DROP TABLE chengjiCREATE TABLE chengji(StudentNo int not null,SubjectNo int not null,ExamDate datetime not null,StudentResult int not null)USE MySchool1IF EXISTS (SELECT * FROM sysobjects WHERE NAME='xinxi')DROP TABLE xinxiCREATE TABLE xinxi(StudentNo int not null,LoginPwd  nvarchar not null,StudentName  nvarchar not null,Sex bit not null,GradeID int NUll,Phone varchar(50),Address nvarchar(255),BornDate nvarchar not null,Email varchar(50),IdentityCard varchar(18)not null)USE MySchool1IF EXISTS (SELECT * FROM sysobjects WHERE NAME='nianji')DROP TABLE nianjiCREATE TABLE nianji(GradeName nvarchar(50) not null)ALTER TABLE nianjiADD GradeID int IDENTITY(1,1)ALTER TABLE nianjiADD CONSTRAINT PK_GradeID PRIMARY KEY(GradeID)  --主键约束ALTER TABLE xinxiADD CONSTRAINT PK_StudentNo PRIMARY KEY(StudentNo) --主键约束ALTER TABLE xinxiADD CONSTRAINT UQ_IdentityCard UNIQUE (IdentityCard) --唯一约束ALTER TABLE xinxiADD CONSTRAINT DF_Address DEFAULT('地址不详') FOR AddressALTER TABLE xinxiADD CONSTRAINT CK_BornDate CHECK (BornDate>='1998-01-01')ALTER TABLE chengjiADD CONSTRAINT FK_StudentNoFOREIGN KEY(StudentNo) REFERENCES xinxi(StudentNo)