数据库实例.docx
- 文档编号:2150330
- 上传时间:2022-10-27
- 格式:DOCX
- 页数:37
- 大小:30.80KB
数据库实例.docx
《数据库实例.docx》由会员分享,可在线阅读,更多相关《数据库实例.docx(37页珍藏版)》请在冰豆网上搜索。
数据库实例
-----------------------------------------------------------
--易焱华,-03-10,增加注释
-----------------------------------------------------------
--1、创建关系图时,只有在单独创建(ALTER)“Marriage”表的外健后才得以顺利创建。
--2、创建关系图时,启服务器后才得以顺利创建。
-----------------------------------------------------------
--SQLServer2005Bible
--HungryMinds
--PaulNielsen
--Familysampledatabase-CREATE
--thisscriptwilldropanexistingFamilydatabase
--andcreateafreshnewinstallation
-----------------------------------------------------------
-----------------------------------------------------------
--DropandCreateDatabase
USEmaster
GO
IFEXISTS(SELECT*FROMSysDatabasesWHERENAME='Family')
DROPDATABASEFamily
go
--Thiscreatesthedatabasedatafileandlogfileonthedefaultdirectories
CREATEDATABASEFamily
go
useFamily
go
-----------------------------------------------------------
-----------------------------------------------------------
--CreateTables,inorderfromprimarytosecondary
CREATETABLEdbo.Person(
PersonIDINTNOTNULLPRIMARYKEYNONCLUSTERED,--标识
LastNameVARCHAR(15)NOTNULL,--名
FirstNameVARCHAR(15)NOTNULL,--性
SrJrVARCHAR(3)NULL,--代
MaidenNameVARCHAR(15)NULL,--少女名
GenderCHAR
(1)NOTNULL,--性别
FatherIDINTNULL,--父亲标识
MotherIDINTNULL,--母亲标识
DateOfBirthDATETIMENULL,--生日
DateOfDeathDATETIMENULL--忌日
);
go
CREATECLUSTEREDINDEXIxPersonName
ONdbo.Person(LastName,FirstName);
ALTERTABLEdbo.PersonADDCONSTRAINT
FK_Person_FatherFOREIGNKEY(FatherID)REFERENCESdbo.Person(PersonID);
ALTERTABLEdbo.PersonADDCONSTRAINT
FK_Person_MotherFOREIGNKEY(MotherID)REFERENCESdbo.Person(PersonID);
go
CREATETABLEdbo.Marriage(
MarriageIDINTNOTNULLPRIMARYKEYNONCLUSTERED,--婚姻标识
HusbandIDINTNOTNULL,--丈夫标识
WifeIDINTNOTNULL,--妻子标识
--HusbandIDINTNOTNULLFOREIGNKEYREFERENCESdbo.Person,--丈夫标识
--WifeIDINTNOTNULLFOREIGNKEYREFERENCESdbo.Person,--妻子标识
DateOfWeddingDATETIMENULL,--结婚日期
DateOfDivorceDATETIMENULL--离婚日期
)
go
--易焱华--增加--2010-03-10
ALTERTABLEdbo.MarriageADDCONSTRAINT
FK_Marriage_HusbandFOREIGNKEY(HusbandID)REFERENCESdbo.Person(PersonID);
ALTERTABLEdbo.MarriageADDCONSTRAINT
FK_Marriage_WifeFOREIGNKEY(WifeID)REFERENCESdbo.Person(PersonID);
go
----------------------------------------------------------------------------
--CustomConstraints
CREATETRIGGERPerson_Parents
ONPerson
AFTERINSERT,UPDATE
AS
--checkthatiftheparentislistedthatthegenderiscorrect
IFUPDATE(FatherID)
BEGIN
--IncorrectFatherGender
IFEXISTS(SELECT*FROMPersonJOINInsertedONInserted.FatherID=Person.PersonIDWHEREPerson.Gender='F')
BEGIN
ROLLBACK
RAISERROR('IncorrectGenderforFather',14,1)
RETURN
END
END
--InvalidFatherAge
--FatherDeceased
IFUPDATE(MotherID)
BEGIN
--IncorrectMotherGender
IFEXISTS(SELECT*FROMPersonJOINInsertedONInserted.MotherID=Person.PersonIDWHEREPerson.Gender='M')
BEGIN
ROLLBACK
RAISERROR('IncorrectGenderforMother',14,1)
RETURN
END
END
--InvalidMotherage
--MotherDeceased
RETURN
go
----------------------------------------------------------------------------
--SampleData
INSERTdbo.Person(PersonID,LastName,FirstName,MaidenName,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(1,'Halloway','Kelly','Russell','F',NULL,NULL,'2/7/1904','5/13/1987')
INSERTdbo.Person(PersonID,LastName,FirstName,SrJr,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(2,'Halloway','James','1','M',NULL,NULL,'4/12/1899','5/1/2001')
INSERTdbo.Person(PersonID,LastName,FirstName,MaidenName,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(3,'Miller','Karen','Conley','F',NULL,NULL,'9/11/1909','8/1/1974')
INSERTdbo.Person(PersonID,LastName,FirstName,SrJr,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(4,'Miller','Bryan',NULL,'M',NULL,NULL,'4/12/1902','4/16/1948')
go
INSERTdbo.Person(PersonID,LastName,FirstName,SrJr,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(5,'Halloway','James','2','M',2,1,'5/19/1922','2/2/1992')
INSERTdbo.Person(PersonID,LastName,FirstName,MaidenName,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(6,'Halloway','Audry','Ross','F',4,3,'8/5/1928','3/12/2002')
go
INSERTdbo.Person(PersonID,LastName,FirstName,SrJr,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(7,'Halloway','Corwin',NULL,'M',5,6,'3/13/1961',NULL)
INSERTdbo.Person(PersonID,LastName,FirstName,MaidenName,Gender,FatherID,MotherID,DateOfBirth,DateOfDeath)
VALUES(8,'Campbell','Melanie','Halloway','F',5,6,'8/19/1951','6/28/2009')
INSERTdbo.Person(Pers
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实例