數(shù)據(jù)庫實驗報告-4

UyearUyearUnameUplaceUnoUplaceUnoBelongSdnameSdorSclnoSnoSnameSageLiveDeptMajorClassStudentSunion BelongSdnameSdorSclnoSnoSnameSageLiveDeptMajorClassStudentSunionJJoinyearSjoin mSjoin n pCnumCnum qClyear 1ClyearCClmjnameBelongOpenClnoBelongOpenClnoMnameMnameCldname 1 1DnoBelong nDnoBelongDnameDorplaceDnameDorplaceDormitoryDwkplace 1 1DormitoryDwkplaceDnumDnum消除沖突和冗余后的E-R圖UyearUyearUplaceUUnameUnoUnoSunionSunionJoinyearJoinyearSjoin mSjoinDorplaceDormitoryDnumDnameDwkplaceDnoBelongMjnamelyearClnumClnoOpenBelongSnoSnameSageLiveDeptMajortudentDorplaceDormitoryDnumDnameDwkplaceDnoBelongMjnamelyearClnumClnoOpenBelongSnoSnameSageLiveDeptMajortudentnp1ClassClass11n1 1 1三．邏輯結(jié)構(gòu)設(shè)計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)檢驗，以上7個關(guān)系模式均滿足第三范式要求四．應(yīng)用程序中遇到的問題及解決方法根據(jù)設(shè)計好的關(guān)系模式，即可在SQLSever2008環(huán)境下編程。前面創(chuàng)建表、視圖、觸發(fā)器的過程都比較簡單，創(chuàng)建存儲過程的時候遇到了一點問題，班號更改以后無法把相關(guān)表中的班號也修改了，最后發(fā)現(xiàn)原因是student和major兩個從表無法級聯(lián)更新，于是又給這兩個表增添了隨著class表級聯(lián)更新的屬性，問題迎刃而解。最后創(chuàng)建游標(biāo)的過程有點復(fù)雜，不過整體還是比較順利的。五．總結(jié)這次上機實驗收獲還是挺多的，上課的時候?qū)W到了理論，當(dāng)真正把理論運用到實踐中的時候發(fā)現(xiàn)其實沒那么容易。雖然只是設(shè)計了一個學(xué)生管理系統(tǒng)，這個系統(tǒng)規(guī)模不大，數(shù)據(jù)也不多，但是當(dāng)真正設(shè)計這個數(shù)據(jù)庫的時候過程是比較曲折的。我覺得重點就是根據(jù)需求設(shè)計E-R圖，設(shè)計出E-R圖后還要消除沖突和冗余，然后將E-R圖轉(zhuǎn)換為關(guān)系模式，還要對關(guān)系模式規(guī)范化，達到一個非常好的效果。此次上機實驗也鍛煉了同學(xué)之間的合作能力，我積極和同學(xué)探討問題，也向老師請教問題，學(xué)到了好多課本上沒有學(xué)到的知識。附錄：代碼:--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)建存儲過程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