數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告-4

UyearUyearUnameUplaceUnoUplaceUnoBelongSdnameSdorSclnoSnoSnameSageLiveDeptMajorClassStudentSunion BelongSdnameSdorSclnoSnoSnameSageLiveDeptMajorClassStudentSunionJJoinyearSjoin mSjoin n pCnumCnum qClyear 1ClyearCClmjnameBelongOpenClnoBelongOpenClnoMnameMnameCldname 1 1DnoBelong nDnoBelongDnameDorplaceDnameDorplaceDormitoryDwkplace 1 1DormitoryDwkplaceDnumDnum消除沖突和冗余后的E-R圖UyearUyearUplaceUUnameUnoUnoSunionSunionJoinyearJoinyearSjoin mSjoinDorplaceDormitoryDnumDnameDwkplaceDnoBelongMjnamelyearClnumClnoOpenBelongSnoSnameSageLiveDeptMajortudentDorplaceDormitoryDnumDnameDwkplaceDnoBelongMjnamelyearClnumClnoOpenBelongSnoSnameSageLiveDeptMajortudentnp1ClassClass11n1 1 1三．邏輯結(jié)構(gòu)設(shè)計(jì)stuunion(uno,uname,uyear,uplace)student(sno,sname,sage,clno)sjoin(uno,sno,joinyear)class(clno,clyear,cltotal)major(mjname,clno,dnl)dept(dno,dname,dwkplace,dtotal)dormitory(dno,dorplace)經(jīng)檢驗(yàn)，以上7個(gè)關(guān)系模式均滿足第三范式要求四．應(yīng)用程序中遇到的問(wèn)題及解決方法根據(jù)設(shè)計(jì)好的關(guān)系模式，即可在SQLSever2008環(huán)境下編程。前面創(chuàng)建表、視圖、觸發(fā)器的過(guò)程都比較簡(jiǎn)單，創(chuàng)建存儲(chǔ)過(guò)程的時(shí)候遇到了一點(diǎn)問(wèn)題，班號(hào)更改以后無(wú)法把相關(guān)表中的班號(hào)也修改了，最后發(fā)現(xiàn)原因是student和major兩個(gè)從表無(wú)法級(jí)聯(lián)更新，于是又給這兩個(gè)表增添了隨著class表級(jí)聯(lián)更新的屬性，問(wèn)題迎刃而解。最后創(chuàng)建游標(biāo)的過(guò)程有點(diǎn)復(fù)雜，不過(guò)整體還是比較順利的。五．總結(jié)這次上機(jī)實(shí)驗(yàn)收獲還是挺多的，上課的時(shí)候?qū)W到了理論，當(dāng)真正把理論運(yùn)用到實(shí)踐中的時(shí)候發(fā)現(xiàn)其實(shí)沒(méi)那么容易。雖然只是設(shè)計(jì)了一個(gè)學(xué)生管理系統(tǒng)，這個(gè)系統(tǒng)規(guī)模不大，數(shù)據(jù)也不多，但是當(dāng)真正設(shè)計(jì)這個(gè)數(shù)據(jù)庫(kù)的時(shí)候過(guò)程是比較曲折的。我覺(jué)得重點(diǎn)就是根據(jù)需求設(shè)計(jì)E-R圖，設(shè)計(jì)出E-R圖后還要消除沖突和冗余，然后將E-R圖轉(zhuǎn)換為關(guān)系模式，還要對(duì)關(guān)系模式規(guī)范化，達(dá)到一個(gè)非常好的效果。此次上機(jī)實(shí)驗(yàn)也鍛煉了同學(xué)之間的合作能力，我積極和同學(xué)探討問(wèn)題，也向老師請(qǐng)教問(wèn)題，學(xué)到了好多課本上沒(méi)有學(xué)到的知識(shí)。附錄：代碼:--student表createtablestudent(snovarchar(8)primarykey,snamevarchar(20)notnullunique,sageint,clnochar(6)referencesclass(clno));--class表createtableclass(clnochar(6)primarykey,clyearint,cltotalint)--major表createtablemajor(mjnamevarchar(20)primarykey,clnochar(6)referencesclass(clno),dnochar(2)referencesdept(dno))--dept表createtabledept(dnochar(2)primarykey,dnamevarchar(20)notnullunique,dworkplacevarchar(20),dtotalint)--dormitory表createtabledormitory(dorplacevarchar(20),dnochar(2)referencesdept(dno))--stuunion表createtablestuunion(unochar(6)primarykey,unamevarchar(20)notnullunique,uyearint,uplacevarchar(20))--sjoin表createtablesjoin(unochar(6)referencesstuunion(uno),snovarchar(8)referencesstudent(sno),joinyearint,primarykey(uno,sno))--創(chuàng)建視圖createviewuname_utotal(uname,utotal)asselectuname,count(*)fromstuunion,sjoinwherestuunion.uno=sjoin.unogroupbystuunion.unamewithcheckoption;--創(chuàng)建觸發(fā)器createtriggerinsert_cltotal_dtotalonstudentforinsertasdeclare@achar(6);declare@cchar(2);set@a=(selectclass.clnofromclass,insertedwhereclass.clno=inserted.clno);updateclasssetcltotal=cltotal+1whereclno=@a;set@c=(selectdept.dnofrommajor,dept,insertedwheremajor.dno=dept.dnoandmajor.clno=inserted.clno);updatedeptsetdtotal=dtotal+1wheredno=@c;createtriggerdelete_cltotal_dtotalonstudentfordeleteasdeclare@achar(6)declare@cchar(2)set@a=(selectclass.clno;fromclass,deleted;whereclass.clno=deleted.clno);updateclasssetcltotal=cltotal-1whereclno=@a;set@c=(selectdept.dnofrommajor,dept,deletedwheremajor.dno=dept.dnoandmajor.clno=deleted.clno);updatedeptsetdtotal=dtotal-1wheredno=@c;--創(chuàng)建存儲(chǔ)過(guò)程createprocedurepro_modifycno@oldcnochar(6),@newcnochar(6),@classtotalintoutputasupdateclasssetclno=@newcnowhereclno=@oldcno;updatestudentsetclno=@newcnowhereclno=@oldcno;updatemajorsetclno=@newcnowhereclno=@oldcno;set@classtotal=(selectcltotalfromclasswhereclno=@newcno)declare@xintexecpro_modifycno@oldcno='011221',@newcno='051221',@classtotal=@xoutputprint@xaltertablestudentaddconstraintc1foreignkey(clno)referencesclass(clno)onupdatecascadealtertablemajoraddconstraintc2foreignkey(clno)referencesclass(clno)onupdatecascade--建立游標(biāo)declare@Depvarchar(10);declare@Classvarchar(10);droptableChangecreatetableChange( dnochar(10)primarykey, dtotalint);insertintoChange(dno,dtotal)selectdept.dno,dept.dtotalfromdeptdeclareStuNumCheckcursorfor selectdno,clno frommajoropenStuNumCheckupdatedeptsetdtotal=0fetchnextfromStuNumCheckinto@Dep,@Classwhile@@FETCH_STATUS=0beginupdatedeptsetdtotal=dtotal+( selectCOUNT(*) fromstudent whereclno=@C