DB2实验报告Word格式.docx
- 文档编号:18774129
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:21
- 大小:389.79KB
DB2实验报告Word格式.docx
《DB2实验报告Word格式.docx》由会员分享,可在线阅读,更多相关《DB2实验报告Word格式.docx(21页珍藏版)》请在冰豆网上搜索。
实验地点
机器编号
指导教师
实验时间
年月日时分
一、实验综述
1.实验目的及要求
Thisexerciseisanonlinelabwhichcreatesobjectsusedinadatabase.
Attheendofthelab,studentsshouldbeableto:
•Createtables•Addreferentialintegrityconstraintstoatable
•Createindexes•Addcheckconstraintstoatable
•Createviews•Addtriggerstoatable
•Createanalias•AccessSystemCataloginformationaboutobjects
•RetrieveanXMLdocument
2.实验设备、软件
PC,
windows
XPProfessional,
DB2
9
Express-c
二、实验过程(实验步骤、记录、数据、分析)
Section1-CreateTables
1.CreateyourARTISTStable.Thetableshouldhavethefollowingcharacteristics.
•Tableschemaofstudent
•TableNameofartists
•Regulardatatogointablespacedms01
•Indexestogointablespacedms02
•Longdatatogointablespacedms03
•Thecolumnartnoshouldbedefinedasaprimarykey
•Columnsdefinedlikethefollowing:
(artnosmallintnotnull,
namevarchar(50),
classificationchar
(1)notnull,
bioclob(100K)loggedcompact,
pictureblob(500k)notloggedcompact)
Showyourworkbelowbyprintingscreen.
手动创建表空间dms01,dms02,dms03,如下:
执行SQL语句结果如下:
2.VerifythecreationofyourARTISTStableandthetabledescription.
Important:
Ensurethedefinitionofyourtableiscorrect.Itcouldcauseunexpectedfailuresinlaterlabsifyourdefinitionsarenotdonecorrectly.
3.Youwillcreatetheremainderofthetablesrequiredusingascriptfilecalledcrtables.ForWindows,itislocatedintheC:
\labfiles\cf23directory.
ExaminetheDB2statementsinthefileandanswerthefollowingquestions.
InwhichtablespacewillboththeindexesanddatafortableSTOCKbeplaced?
ForCONCERTS?
ForREORDER?
4.Executethecrtablesscripttocreatethetables.
Showyourworkbelowbyprintingscreen
5.Verifythatalltables(ALBUMS,STOCK,CONCERTS,andREORDER)werecreatedsuccessfully.
6.InformationabouteachcolumninatablecanbefoundbyaccessingtheSYSCAT.COLUMNSview.
EnteraninteractiveSQLstatementtoselectthecolumnsTABNAME,COLNAME,andTYPENAMEfromSYSCAT.COLUMNSforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAMEandCOLNO,andpipetheoutputtoafilenamedtabchk.file.
Showyoursqlstatementsandyourworkbelowbyprintingscreenforthelaststep.
7.Checkyouroutputagainstthetabchk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutputorthefollowinglabsmaynotworkcorrectly!
8.Informationaboutatable'
sdefinitiontoatablespacecanbefoundbyaccessingtheSYSCAT.TABLESview.EnteranSQLstatementtoselectthecolumnsTABNAME,TBSPACE,andINDEX_TBSPACEfromSYSCAT.TABLESforalltableswiththeTABSCHEMAof‘student’,orderthedatabyTABNAME,andpipetheoutputtoafilenamedtbschk.file.
9.Checkyouroutputagainstthetbschk.masterfile.
Ensurethattherearenodifferencesbetweenyouroutputandthemasteroutput,orthefollowinglabsmaynotworkcorrectly!
10.Grantselectprivilegeontablesthatareownedbystudenttopublicbyexecutingascriptfile,grants(anoperatingsystemcommandfile)forlocaladministration.
Section2-CreateIndexes
1.CreateanindexcalledITEMontheITEMNOcolumninSTOCKtable.
2.CreateauniqueindexcalledITEMNOontheITEMNOcolumninALBUMStable.
3.Selecttheinformationfromthecatalogtablesaboutyourindexes.Thecolumnsyoushouldselectarethefirst18charactersofTABNAME,UNIQUERULE,thefirst18charactersofINDNAME,andthefirst30charactersofCOLNAMESfromSYSCAT.INDEXES,whereINDSCHEMAisstudent,orderbyTABNAMEandINDNAME.
IfUNIQUERULE="
U"
thenonlyuniquevaluesareallowed.
D"
thenduplicatevaluesareallowed.
P"
thenitisaPrimarykey.
Showyourworkandyournewindexesbelowbyprintingscreen.
4.Usetheselectyouexecutedinthepreviousstepandroutetheoutputtoindchk.file.Checkyourresultsagainstindchk.master.
Section3-CreateViews
1.Createaviewcalledmusicthatwillselecttitle,classification,namefromalbums,artistswhereartists.artno=albums.artno.
2.Createanotherviewcalledinventorybyexecutingthescriptfile,crview.
3.InformationaboutviewscanbefoundintheSYSCAT.VIEWSandSYSCAT.TABLESviews.TablesandviewsforthecurrentusercanbeshownwithaLISTTABLESstatement.ATYPEofVisaview.
Issueoneofthefollowingstatementsandverifyyourviewsarelisted.
•LISTTABLESstatementOR
•SelectcolumnsTABSCHEMA,TABNAME,andTYPEfromtheSYSCAT.TABLESviewwithaTABSCHEMAofstudentOR
•SelectcolumnsVIEWSCHEMA,VIEWNAMEfromSYSCAT.VIEWSwithDEFINERofstudent
Section4-CreateAlias
1.CreateanaliascalledsingersfortheARTISTStable,andanaliascalledemptystockfortheREORDERtable.
2.InformationaboutaliasescanbefoundintheSYSCAT.TABLESview.Tables,views,andaliasesforthecurrentusercanalsobeshownwiththeLISTTABLESstatement.ATYPEofAisanalias.Issueoneofthefollowingstatementsandverifyyouraliasesarelisted.
•LISTTABLESstatementOR
•SelectcolumnsTABNAMEandTYPEfromtheSYSCAT.TABLESviewwithaTABSCHEMAofstudent.
Section5-AddReferentialIntegrity
1.AltertheALBUMStableanddefinereferentialintegrityrelationshipsithaswithothertables.
•Addaprimarykeyontheitemnocolumn
•AddaforeignkeycalledfkartnoontheartnocolumnwhichreferencesaprimarykeyintheARTISTStable
•ThedeleterulebetweenARTISTSandALBUMSshouldbedeletecascade
2.NowaltertheSTOCKtabletodefineitsreferentialintegrityrelationships,byexecutingthescriptfile,crri.
3.InformationaboutreferentialintegritycanbefoundbyaccessingtheSYSCAT.REFERENCESview.TabledependenciescanalsobeaccessedthroughtheSYSCAT.TABLESview.EnteraninteractiveSQLstatementtoselectCONSTNAME,TABNAME,REFTABSCHEMA,REFTABNAME,DELETERULEfromtheSYSCAT.REFERENCESviewwithaTABSCHEMAofstudent.
4.Runaselecttoretrievethefirst18charactersofTABNAME,andthePARENTSandCHILDRENcolumnsfromSYSCAT.TABLESwheretheTABSCHEMAisequaltoyouruserid.OrdertheresultsbyTABNAMEanddirectyouroutputtoafilenamedrichk.file.TheSQLcanbefoundinafilenamedrichk.sql.
Checkyouroutputagainsttherichk.masterfile.
Section6-AddCheckConstraints
1.AltertheSTOCKtableandaddacheckconstrainttoit.Thecheckconstraintshouldhavethefollowingcharacteristics.
•Itshouldbenamedcctype.
•Thebusinessruleitshouldenforceistoonlyallowthevaluesof'
D'
'
C'
or'
R'
inthetypecolumn.
Makesurethealphanumericliteralsaretypedinuppercaseletters.
2.InformationaboutcheckconstraintscanbefoundbyaccessingtheSYSCAT.CHECKS,SYSCAT.COLCHECKS,SYSCAT.TABCONST,andSYSCAT.TABLESviews.IssueSQLto:
SelectCONSTNAME,TABNAME,COLNAMEfromtheSYSCAT.COLCHECKSview.
SelectCONSTNAME,TABNAME,TYPEfromtheSYSCAT.TABCONSTview.
IftheTYPE=“K”,thenitisacheckconstraint.IftheType=“P”,thenitisaprimarykey.IftheType=“F”,thenitisaforeignkey.
3.Tocheckyourwork,runaselectstatementthatselectsthefirst100charactersoftheTEXTcolumnfromSYSCAT.CHECKSwhereCONSTNAME='
CCTYPE'
anddirectyouroutputtoafilenamedckchk.file.TheSQLcanbefoundinafilenamedckchk.sql.Checkyouroutputagainsttheckchk.masterfile.
Section7-CreateaTrigger
1.Createatriggerwhichhasthefollowingcharacteristics.
•Itshouldbenamedreorder
•ItshouldfireafteranupdateoftheqtycolumnontheSTOCKtable,ifthenewvalueofqtyis<
=5
•Newshouldbereferencedasn
•Thetriggeredactionshouldinsertthevaluesn.itemnoandcurrenttimestampintotheREORDERtable
•Foreachrowmodedb2sql
2.InformationabouttriggerscanbefoundbyaccessingtheSYSCAT.TRIGGERSandSYSCAT.TRIGDEPviews.
EnterinteractiveSQLtoresearchtheREORDERtrigger:
SelectTRIGNAME,TABNAME,andTRIGEVENTcolumnsfromtheSYSCAT.TRIGGERSview.
TRIGEVENTdescribestheeventthatfiresthetriggerasIforinsert,Dfordelete,orUforupdate.
SelectTRIGNAME,BTYPE,BSCHEMA,BNAMEcolumnsfromSYSCAT.TRIGDEPview.BTYPEandBSCHEMAgivethenameoftheobjecteddependedonbythetrigge
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 实验 报告
![提示](https://static.bdocx.com/images/bang_tan.gif)