【正文】 ent,Tel) (6)維修記錄 (RoomNo,RepairerNo,RepaireRecordNo,RepaireResult,RepairTime, Supplyname) (7)課程安排 (CNo,TNo,RoomNo,WeekNo,Weekday,SectionNo,Pname,ClassNo ,NewWeekday, (8)NewWeekNo,NewSectionNo,NewRoomNo, AdjLessonID) (9)學(xué)生出勤記錄 (SNo,CNo,AttendSum) (10)學(xué)生使用教室 (SNo,RoomNo,DamageTime,DamageInfo,Devicename, LeaveTime,EnterTime) (11)教室信息 (RoomNo,Roomname,Campusname,Buildname,Capacity, IsUusable,Type,RemainCapacity) (12)單位借用記錄 (RoomNo, Deptname, RentReason, WeekNo,Weekday,SectionNo) 對(duì)模式進(jìn)行檢驗(yàn)和優(yōu)化。操作上存在大量冗余?？战淌也樵冎邪臻e教室和教室空余座位的查詢以及學(xué)生上課出勤的查詢統(tǒng)計(jì)；調(diào)課管理子系統(tǒng)主要包含教室調(diào)課管理與課 表查詢；教室借用子系統(tǒng)主要完成教室借用管理和記錄的查詢功能；設(shè)備管理子系統(tǒng)主要有設(shè)備損壞報(bào)告記錄查詢和設(shè)備維護(hù)記錄查詢。 索引的建立 1. 建立唯 一性索引 在課程安排表上建立課程號(hào)的唯一索引，以加快查詢速度。數(shù)據(jù)入庫(kù)通過(guò)兩種途徑：一是 EXCEL 表格導(dǎo)入信息，二是存儲(chǔ)過(guò)程錄入信息。 測(cè)試內(nèi)容 針對(duì)系統(tǒng)的幾大功能模塊，涉及的待測(cè)試的內(nèi)容主要有： 。加深了對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)每一步驟的理解，例如對(duì)每一個(gè)步驟的目的、任務(wù)和所要得到的技術(shù)文檔。在接下來(lái)的概念設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)和物理設(shè)計(jì)階段，我更是加深了對(duì)每一階段特點(diǎn)的理解，并且按照每一階段的任務(wù)，設(shè)計(jì)了 ER圖，數(shù)據(jù)庫(kù)模式和用戶子模式，以及存儲(chǔ)過(guò)程和觸發(fā)器，來(lái)完成事務(wù)邏輯。在實(shí)習(xí)中，我發(fā)現(xiàn)自己理論知識(shí)還并不是很扎實(shí)，這就要求在以后要加強(qiáng)數(shù)據(jù)庫(kù)理論的學(xué)習(xí)，并且爭(zhēng)取機(jī)會(huì)，多做類似的實(shí)戰(zhàn)訓(xùn)練。在邏輯設(shè)計(jì)階段，對(duì)課程表信息在數(shù)據(jù)庫(kù)中的存儲(chǔ)方式不太清楚。 /* 創(chuàng)建學(xué)生信息基本表 */ CREATE TABLE STUDENT ( SNo Char(8) PRIMARY KEY, Sname Char(14) NOT NULL, Sex Char(3) NOT NULL, BirthYear int, Pname Char(24) NOT NULL, ClassNo Char(5) NOT NULL, AttendSum int, photo Image, DeptChar(20), CHECK (Sex =39。), ) /* 創(chuàng)建課程信息表 */ CREATE TABLE COURSE ( Cno Char(6) PRIMARY KEY, Cname Char(20)Not null, Time smallint, WeekNo Char(10) Not null, Weekday Char(10) Not null, SectionNo Char(10) Not null, Pname Char(20), ClassNo Char(5), StuNum int, CHECK (time BETWEEN 1 AND 200), CHECK(StuNum BETWEEN 1 AND 300), ) 07 信管專業(yè) 072 班數(shù)據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)論文 23 /* 創(chuàng)建教室信息基本表 */ CREATE TABLE CLASSROOM ( RoomNo Char(4) PRIMARY KEY, Roomname Char(6) , Campusname Char(10), Buildname Char(10), Capacity int, IsUusable Char(8), Type Char(10), RemainCapacity int, CHECK (Capacity BETWEEN 20 AND 500), CHECK (RemainCapacity =Capacity ), ) /* 創(chuàng)建教師信息基本表 */ CREATE TABLE TEACHER ( Tno Char(6) PRIMARY KEY, Tname Char(14) NOT NULL, DeptChar(20), Title Char(8), Sex char(30), Tel Char(12), CHECK (Sex IN(39。)), ) /* 創(chuàng)建借用單位信息基本表 */ CREATE TABLE Department ( RentDeptname Char(20) PRIMARY KEY, Leadername Char(14) NOT NULL, IDtype Char(6) NOT NULL, ID Char(20) NOT NULL, Tel Char(12) NOT NULL, ) /* 創(chuàng)建維修工信息基本表 */ CREATE TABLE Repairer ( RepairerNo Char(6) PRIMARY KEY, Repairername Char(14)NOT NULL, Statement Char(6), 07 信管專業(yè) 072 班數(shù)據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)論文 24 Tel Char(12), ) /* 創(chuàng)建學(xué)生出勤信息表 */ CREATE TABLE ATTENDENCE ( SNo Char(8), CNo Char(6), AttendSum smallint, PRIMARY KEY (SNo,CNo), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), CHECK (AttendSum=0), ) /* 創(chuàng)建設(shè)備損壞報(bào)告基本表 */ CREATE TABLE DAMAGEREPORT ( SNo Char(8), RoomNo Char(4), DamageTime smalldatetime, DamageInfo Char(50), Devicename Char(20), PRIMARY KEY (SNo,RoomNo), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), ) /* 創(chuàng)建維修記錄基本表 */ CREATE TABLE MENDRECORD ( RepairerNo Char(6), RoomNo Char(4) NOT NULL, RepaireRecordNo Char(6) UNIQUE, Devicename char(10), RepaireResult char(6), Supplyname char(10), RepaireTime smalldatetime, FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (RepairerNo) REFERENCES Repairer (RepairerNo), ) /* 創(chuàng)建課程安排基本表 */ CREATE TABLE CURRICULUM ( 07 信管專業(yè) 072 班數(shù)據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)論文 25 CNo Char(6), RoomNo Char(4), Tno Char(6), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), Pname Char(20), ClassNo Char(6), PRIMARY KEY(CNo,RoomNo,TNo,WeekNo,Weekday,SectionNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), FOREIGN KEY (TNo) REFERENCES TEACHER(TNo), ) /* 創(chuàng)建調(diào)課記錄基本表 */ CREATE TABLE ADJUSTRECORD ( CNo Char(6), RoomNo Char(4), Tno Char(6), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), NewWeekNo Char(10), NewWeekday Char(10), NewSectionNo Char(10), NewRoomNo Char(4), PRIMARY KEY(RoomNo,WeekNo,Weekday,SectionNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY (CNo) REFERENCES COURSE (CNo), FOREIGN KEY (TNo) REFERENCES TEACHER(TNo), ) /* 創(chuàng)建借用記錄表 */ CREATE TABLE RENTRECORD ( RoomNo Char(4), Deptname Char(20), RentReason Char(50), WeekNo Char(10), Weekday Char(10), SectionNo Char(10), PRIMARY KEY(RoomNo,Deptname), FOREIGN KEY(RoomNo) REFERENCES CLASSROOM (RoomNo), FOREIGN KEY(Deptname) REFERENCES Department(RentDeptname), 07 信管專業(yè) 072 班數(shù)據(jù)庫(kù)系統(tǒng)概論課程設(shè)計(jì)論文 26 ) /* 創(chuàng)建學(xué)生進(jìn)出教室信息表 */ CREATE TABLE ENTER_LEAVE ( SNo Char(8), RoomNo Char(4), EnterTime smalldatetime, LeaveTime smalldatetime, PRIMARY KEY(SNo,RoomNo,EnterTime), FOREIGN KEY (SNo) REFERENCES STUDENT (SNo), FOREIGN KEY (RoomNo) REFERENCES CLASSROOM (RoomNo), CHECK(lEAVETIMEENTERTIME), ) 視圖的建立 /* 創(chuàng)建學(xué)生出勤視圖 */ CREATE VIEW V_Attendence AS SELECT ,Sname,Cname, FROM STUDENT,ATTENDENCE,COURSE WHERE = AND = /* 建立查看空教室的視圖 */ CREATE VIEW EMPTYCLASSROOM AS SELECT Roomname,Campusname,Buildname,IsUsable,Type,RemainCapacity FROM CLASSROOM WHERE Isusable=39。0920703539。2021123039。 CREATE TRIGGER DAMAGEEVENT ON DAMAGEREPORT AFTER INSERT AS BEGIN UPDATE CLASSROOM SET IsUsable=39。 WHERE RoomNo=RoomNo。 WHERE RoomNo=RoomNo。 ELSE UPDATE ATTENDENCE SET AttendSum=AttendSum+1 WHERE SNo=SNo AND CNo=CNo EXEC CHANGEATTENDENCE 39。 處理查詢事務(wù)的部分存儲(chǔ)過(guò)程的建立與驗(yàn)