数据库实例.docx

1、数据库实例-易焱华，-03-10，增加注释-1、创建关系图时，只有在单独创建（ALTER）“Marriage”表的外健后才得以顺利创建。-2、创建关系图时，启服务器后才得以顺利创建。- SQL Server 2005 Bible - Hungry Minds - Paul Nielsen- Family sample database - CREATE- this script will drop an existing Family database - and create a fresh new installation- Drop and Create DatabaseUSE mast

2、erGOIF EXISTS (SELECT * FROM SysDatabases WHERE NAME=Family) DROP DATABASE Familygo- This creates the database data file and log file on the default directoriesCREATE DATABASE Familygouse Familygo- Create Tables, in order from primary to secondaryCREATE TABLE dbo.Person ( PersonID INT NOT NULL PRIMA

3、RY KEY NONCLUSTERED, -标识 LastName VARCHAR(15) NOT NULL, -名 FirstName VARCHAR(15) NOT NULL, -性 SrJr VARCHAR(3) NULL, -代 MaidenName VARCHAR(15) NULL, -少女名 Gender CHAR(1) NOT NULL, -性别 FatherID INT NULL, -父亲标识 MotherID INT NULL, -母亲标识 DateOfBirth DATETIME NULL, -生日 DateOfDeath DATETIME NULL -忌日 );goCRE

4、ATE CLUSTERED INDEX IxPersonName ON dbo.Person (LastName, FirstName);ALTER TABLE dbo.Person ADD CONSTRAINT FK_Person_Father FOREIGN KEY (FatherID) REFERENCES dbo.Person (PersonID);ALTER TABLE dbo.Person ADD CONSTRAINT FK_Person_Mother FOREIGN KEY (MotherID) REFERENCES dbo.Person (PersonID);go CREATE

5、 TABLE dbo.Marriage ( MarriageID INT NOT NULL PRIMARY KEY NONCLUSTERED, -婚姻标识 HusbandID INT NOT NULL, -丈夫标识 WifeID INT NOT NULL, -妻子标识 -HusbandID INT NOT NULL FOREIGN KEY REFERENCES dbo.Person, -丈夫标识 -WifeID INT NOT NULL FOREIGN KEY REFERENCES dbo.Person, -妻子标识 DateOfWedding DATETIME NULL, -结婚日期 Dat

6、eOfDivorce DATETIME NULL -离婚日期 )go -易焱华-增加-2010-03-10ALTER TABLE dbo.Marriage ADD CONSTRAINT FK_Marriage_Husband FOREIGN KEY (HusbandID) REFERENCES dbo.Person (PersonID);ALTER TABLE dbo.Marriage ADD CONSTRAINT FK_Marriage_Wife FOREIGN KEY (WifeID) REFERENCES dbo.Person (PersonID);go - Custom Constra

7、intsCREATE TRIGGER Person_ParentsON PersonAFTER INSERT, UPDATEAS - check that if the parent is listed that the gender is correctIF UPDATE(FatherID) BEGIN - Incorrect Father Gender IF EXISTS(SELECT * FROM Person JOIN Inserted ON Inserted.FatherID = Person.PersonID WHERE Person.Gender = F) BEGIN ROLLB

8、ACK RAISERROR(Incorrect Gender for Father,14,1) RETURN END END - Invalid Father Age - Father DeceasedIF UPDATE(MotherID) BEGIN - Incorrect Mother Gender IF EXISTS(SELECT * FROM Person JOIN Inserted ON Inserted.MotherID = Person.PersonID WHERE Person.Gender = M) BEGIN ROLLBACK RAISERROR(Incorrect Gen

9、der for Mother,14,1) RETURN END END - Invalid Mother age - Mother DeceasedRETURNgo- Sample DataINSERT dbo.Person (PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(1, Halloway, Kelly, Russell, F, NULL, NULL, 2/7/1904,5/13/1987)INSERT dbo.Person (

10、PersonID, LastName, FirstName, SrJr, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(2, Halloway, James, 1, M, NULL, NULL, 4/12/1899,5/1/2001)INSERT dbo.Person (PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(3,Miller, Karen, Conle

11、y, F, NULL, NULL, 9/11/1909,8/1/1974)INSERT dbo.Person (PersonID, LastName, FirstName, SrJr, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(4, Miller, Bryan, NULL, M, NULL, NULL, 4/12/1902,4/16/1948)goINSERT dbo.Person (PersonID, LastName, FirstName, SrJr, Gender, FatherID, MotherID, D

12、ateOfBirth, DateOfDeath) VALUES(5, Halloway, James, 2, M, 2, 1, 5/19/1922,2/2/1992)INSERT dbo.Person (PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(6, Halloway, Audry, Ross, F, 4, 3, 8/5/1928,3/12/2002)goINSERT dbo.Person (PersonID, LastName,

13、 FirstName, SrJr, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(7, Halloway, Corwin, NULL, M, 5, 6, 3/13/1961,NULL) INSERT dbo.Person (PersonID, LastName, FirstName, MaidenName, Gender, FatherID, MotherID, DateOfBirth, DateOfDeath) VALUES(8, Campbell, Melanie, Halloway, F, 5, 6, 8/19/1951,6/28/2009)INSERT dbo.Person (Pers