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