學生宿舍管理數據庫程序設計

1、學生宿舍管理數據庫程序設計一、 系統(tǒng)需求分析1.1 需求分析階段的目標（ 1）了解目前宿舍的現(xiàn)狀以及SQL Server 2000 的功能和特點；（ 2）通過實地調查記錄的方式了解宿舍樓管理的工作業(yè)務流程，并記錄和處理相關的數據；1.2 需求分析階段的任務1、處理對象系統(tǒng)要處理的對象包括宿舍樓基本信息、學生基本信息、宿舍基本信息、樓道工作人員基本信息等 4 個方面，各個對象包括信息如下所示：（ 1） 宿舍樓基本信息（ Dormitory ）：包括宿舍樓編號、宿舍樓所在校區(qū)、宿舍樓在校區(qū)中區(qū)域、 每棟宿舍樓在樓管處的電話、 宿舍樓樓管員信息等， 方便管理者對宿舍樓的管理；（ 2） 學生基本信息（

2、 Student）：包括學生編號、學生所在學院信息、學生姓名、學生來自省份、學生出生日期、學生入學時間、學生所學專業(yè)、所在班級等信息，方便學生信息的查詢和更新；（ 3） 宿舍基本信息（ Room）：包括宿舍編號、宿舍學生所屬班級、宿舍學生所在學院、宿舍學生所學專業(yè)、學生所在班級編號等；（ 4） 樓道工作人員基本信息（ Worker ）：包括工作人員編號、工作人員姓名、工作類型、工資、性別、聯(lián)系方式、工作時間等，方便管理人員對宿舍工作人員的任用、信息查詢及更改；2、處理功能要求：（ 1）宿舍樓基本信息查詢與更新；（ 2）學生基本信息查詢與更新；3、安全性與完整性要求安全性先通過視圖機制，不同的用

3、戶只能訪問系統(tǒng)授權的視圖，這樣可滿足系統(tǒng)數據一定程度上的安全性，再通過用戶授權機制，用用戶登陸來識別用戶級別，根據這個級別來分配用戶權限，達到數據更高層次的安全保密功能。完整性要求描述宿舍樓基本信息、學生基本信息、宿舍工作人員基本信息中的數據項能否為 null ，以及一些用戶自定義完整性是否符合實際要求。1.3 數據字典1.數據項數據項編數據項名數據項定義與其他數據項的存儲結構別名號關系DI_1StuNo學生編號char(9)學號DI_2DepName學生所在學院char(20)學院DI_3StuName學生姓名Char(10)姓名DI_4StuSex學生性別Char(2)性別DI-5StuH

4、ome學生來自省份Char(10)祖籍DI_6StuBorth學生出生時間Date出生日期DI_7StuETime學生入學時間Date入學時間DI-8StuPerfect學生所在專業(yè)Char(20)專業(yè)DI_9StuClass學生所在班級編號Int編號DI_10WorNo工作人員編號Char(5)編號DI_11WorName工作人員姓名Char(10)姓名DI_12WorType工作類型Char(8)工作類型DI_13WorWage工作人員工資Int月工資DI_14WorSex工作人員性別Char(2)性別DI_15WorPhNo工作人員聯(lián)系方式Char(12)電話DI_16WorTime工作

5、人員工作時間Char(30)工作時間DI_17RNo宿舍編號Char(6)舍號DI_18RGrade宿舍學生所屬年級等于 StuETimeChar(4)年級DI_19RDepart宿舍學生所在學院等于 DepNameChar(20)學院DI_20RPerfect宿舍學生所學專業(yè)等于 StuPerfectChar(20)專業(yè)DI_21RClass學生所在班級編號等于 StuClassChar(2)班級DI_22DorNo宿舍樓編號Smallint宿舍樓號DI_23DorCampus宿舍樓所屬校區(qū)Char(4)校區(qū)DI_24DorLocation宿舍樓在校區(qū)位置Char(4)宿舍區(qū)位DI_25Do

6、rPhNo宿舍樓管處電話Char(12)電話DI_26DorAdminist宿舍樓樓管員信息等于 WorNoChar(10)樓管員2.數據結構數據結構編 數據結構名數據結構含組成號義DS_1Student宿舍學生信StuNo,DepName,StuName,StuSex,StuHome,息StuBorth,StuETime,StuPerfect,StuClassDS_2Worker宿舍樓工作WorTime,WorName,WorType,WorWage,人員信息WorSex,WorPhNo,WorNoDS_3Room宿舍信息RNo,RGrade,RDepart,RPerfect,RClassD

7、S_4Dormitory宿舍樓信息DorNo,DorCampus, DorLocation, DorPhNo,DorAdminist二、 概要設計2.1 概念模型設計（1）根據不同的對象，分別畫出E R圖：學生入住宿舍組成宿舍樓樓管工作人員工作（2）各分 E_R 圖中每個實體的屬性如下所示：學生： Student(StuNo, DepName,StuName,StuSex,StuHome,StuBorth,StuETime,StuPerfect,StuClass);宿舍： Room（ RNo,RGrade,RDepart,RPerfect,RClass）；宿舍樓： Dormitory （ Do

8、rNo,DorCampus, DorLocation, DorPhNo, DorAdminist ）；樓道工作人員： Worker （WorNo, WorTime,WorName,WorType,WorWage, WorSex,WorPhNo ）。2.2 系統(tǒng)流程用戶登錄是否成功？判斷用戶級別發(fā)出查詢提示信息發(fā)出修改信息判斷查詢內容判斷修改內容在宿宿樓在宿宿樓學舍舍道學舍舍道生樓基工生樓基工信信本人信信本人息息信基息息信基中中息本中中息本顯示查詢結果存儲修改數據三、邏輯設計階段3.1 將 E_R 圖轉化為關系模型E-R 圖向關系模型的轉化如下：樓道工人： Worker （ WorNo, Wor

9、Time,WorName,WorType,WorWage, WorPhNo ,DorNo,DorCampus, DorLocation ） ;宿舍樓： Dormitory （ DorNo,DorCampus, DorLocation, DorPhNo, DorAdminist）；宿舍： Room（ RNo,RGrade,RDepart,RPerfect,RClass ，DorNo,DorCampus, DorLocation ）；學生 :Student(StuNo, DepName,StuName,StuSex,StuHome,StuBorth,StuETime,StuPerfect,StuC

10、lass,RNo, DorNo,DorCampus, DorLocation);3.2 數據庫模式定義（ 1）樓道工人（ Worker ）基本信息表，用于記錄樓道工人基本信息表：屬性名數據類型取值范圍是否是主屬性或完整性外鍵WorNoChar(5)0000099999是Not nullWorNameChar(10)否Not nullWorTypeChar(8)否Not nullWorWageInt大于 500（元 /月） 否Not nullWorSexChar(2)男、女否Not nullWorPhNoChar(12)否NullWorTimeChar(30)否NullDorNoSmallint

11、0099是Not nullDorCampusChar(4)四面八方其中之是Not null一DorLocationChar(4)四面八方其中之是Not null一(2) 宿舍樓（ Dormitory ）基本信息表，用于記錄宿舍樓的基本信息表;屬性名數據類型取值范圍是否是主屬性或完整性外鍵DorNosmallint0099是Not nullDorCampusChar(4)四面八方其中之是Not null一DorLocationChar(4)四面八方其中之是Not null一DorPhNoChar(12)否NullDorAdimistChar(10)否Null(3) 宿舍（ Room）基本信息表，

12、用于記錄宿舍的基本信息：屬性名數據類型取值范圍是否是主屬性或完整性外鍵RNoChar(6)01-00199-999是Not nullRGradeChar(4)否Not nullRDepartChar(20)否Not nullRPerfectChar(20)否Not nullDorNoSmallint0099是Not nullDorCampusChar(4)四面八方其中之是Not null一DorLocationChar(4)四面八方其中之是Not null一(4) 宿舍學生（ Student）基本信息表，用于記錄宿舍學生的基本信息：屬性名數據類型取值范圍是否是主屬性或外鍵完整性StuNoCha

13、r(9)0000000099999999是Not nullDepNameChar(20)否Not nullStuNameChar(10)否Not nullStuSexChar(2)男、女否Not nullStuHomeChar(10)否Not nullStuBorthDatetime否Not nullStuETimeDatetime否Not nullStuPerfectChar(20)否Not nullStuClassInt大于 0小于 10否Not nullRNoChar(6)01-00199-999是Not nullDorNoSmallint0099是Not nullDorCampusCh

14、ar(4)四面八方其中之一是Not nullDorLocationChar(4)四面八方其中之一是Not null3.3視圖設計編號視圖（ view） 作用V-1WorView便于查詢和修改樓道工人的基本信息V-2DormView方便宿舍樓的基本信息的查詢、更新V-3RoomView以便于宿舍樓的基本信息的查詢和更新V-4StuView便于查詢和更改學生的基本信息3.4系統(tǒng)功能模塊學生宿舍管理系統(tǒng)學樓宿宿生道舍舍基工樓基本人基本信基本信息本信息管信息管理息管理管理理四、物理設計階段數據庫的物理設計就是為邏輯數據模型選取一個最合適應用要求的物理結構的過程，要完成兩個任務：（ 1） 確定數據庫的物

15、理結構，在關系數據庫中主要是存取方法和存儲結構；（ 2） 對物理結構進行評價。4.1 數據存儲為數據庫中各基本表建立的索引如下：（ 1） 由于基本表 Room、 Studentde 主碼 RoomID,SID 經常在查詢條件和連接操作的連接條件中出現(xiàn)，且他們的值是唯一，考慮在兩個屬性上建立唯一索引。（ 2） Dormitory 的主碼 DorNo,DorCampus 和 DorLocation 經常在查詢條件中出現(xiàn)， 且他們的組合值是唯一，考慮在他們之上建立組合索引。（ 3） 基本表 Student 的一屬性 SName，經常在查詢條件中出現(xiàn)，且經常出現(xiàn)在相等的比較的條件中，考慮建立聚簇索引。

16、4.2 系統(tǒng)處理模塊1、樓道工人基本信息的信息查詢和更新模塊用戶發(fā)出查詢或更新樓道工作人員的基本信息判斷是查詢還是更新？在樓道工人基本在樓道工人基本信息中查詢處理信息中進行更新是否修改修改后的數據存儲成功？查詢結果修改結果提示信息判斷是否還有終止命令信息2.宿舍樓基本信息的查詢和更新模塊用戶發(fā)出查詢或更新宿舍樓的基本信息判斷是查詢還是更新？在宿舍樓基本信息中查詢處理在宿舍樓基本信息中進行更新是否修改修改后的數據存儲成功？查詢結果修改結果提示信息判斷是否還有命令信息終止3.宿舍基本信息的查詢和更新模塊用戶發(fā)出查詢或更新宿舍的基本信息判斷是查詢還是更新？在宿舍基本信息中查詢處理在宿舍基本信息中進行

17、更新是否修改修改后的數據存儲成功？查詢結果修改結果提示信息判斷是否還有命令信息終止4.學生基本信息的查詢和更新模塊用戶發(fā)出查詢或更新學生的基本信息判斷是查詢還是更新？在學生基本信息中查詢處理在學生基本信息中進行更新是否修改修改后的數據存儲成功？查詢結果修改結果提示信息判斷是否還有命令信息終止五、數據庫實施階段5.1 建立數據庫和數據表create database Student_Dormitory_Management;(1) 樓道工人基本信息表的建立create table Worker (WorNo char(5)not null unique,WName char(10)not nul

18、l,WType char(8)not null,WWage intnot null,WSex char(2)not null,WPhNochar(12)null,WTime char(30)null,DorNosmallintnot null,DorCampuschar(4)DorLocation char(4)Primarykey (WorNo),not null,not null,Foreign key (DorNo ,DorCampus,DorLocation) referencesDormitory (Dorno ,DorCampus ,DorLocation ),Check (WWa

19、ge =0),Check(WSex = 男or WSex= 女 );(2) 宿舍樓基本信息表的建立Create table Dormitory(DorNosmallint not null,DorCampuschar(4)not null,DorLocation char(4)not null,DorPhNochar(12)null,DorAdministchar(10)null,Primary key (DorNo,DorCampus ,DorLocation);（3）宿舍基本信息表的建立Create table Room(RNochar(6)not null,RGrade char(4)n

20、ot null,RDepart char(20)not null,RPerfect char(20)not null,DorNosmallint not null,DorCampuschar(4)not null,DorLocation char(4)not null,Primarykey (RNo),Foreign key (DorNo ,DorCampus,DorLocation) referencesDormitory (Dorno ,DorCampus ,DorLocation );（4）宿舍學生基本信息表的建立Create table Student (StuNochar(9)not

21、 null,DepNamechar(20)not null,StuNamechar(10)not null,StuSexchar(2)not null,StuHomechar(10) not null,StuBorthdatetimenot null,StuETimedatetime not null,StuPerfectchar(20)not null,StuClassint not null,RNochar(6)not null,DorNosmallint not null,DorCampus char(4)not null,DorLocation char(4)not null,Prim

22、ary key (StuNo),Foreign key(DorNo ,DorCampus,DorLocation) referencesDormitory (Dorno ,DorCampus ,DorLocation ),Foreign key(RNo)referencesRoom(RNo),Check (StuSex=男 or StuSex=女 ),Check(StuClass0 and StuClass10);5.2建立視圖（1）用于查詢和更新樓道工人基本信息的視圖定義：Create view WorView(編號 ,姓名 ,工作類型 ,工資 ,性別 ,聯(lián)系方式 ,工作時間 ,宿舍樓編號

23、,校區(qū) ,區(qū)位 )AsSelect *From WorkerWith check option;（2）用于查詢和更新宿舍樓基本信息的視圖定義：Create view DormView( 宿舍樓編號 ,校區(qū) ,區(qū)位 ,電話 ,樓管員 )AsSelect *From DormitoryWith check option;（3）顯示宿舍基本信息的視圖定義：Create view RoomView( 舍號 ,年級 ,學院 ,專業(yè) ,宿舍樓編號 ,校區(qū) ,區(qū)位 )AsSelect *From RoomWith check option;(4) 用于查詢好更新學生基本信息的視圖定義：Create view

24、StuView( 學號 ,學院 ,姓名 ,性別 , 祖籍 ,出生日期 ,入學時間 ,專業(yè) ,編號 , 舍號 ,宿舍樓編號 ,校區(qū) ,區(qū)位 )AsSelect *From StudentWith check option;5.3 建立索引及數據入庫1、建立學號、房間號、宿舍號、學生名字的索引Create unique index StudentID on Student(StuNo);Create unique index RoomNo on Room(RNo);CreateuniqueindexDormitoryNo_Campus_LocationonDormitory(DorNodesc ,

25、DorCampus,DorLocation);Create unique index StudentName on Student(StuName);2、數據入庫先用 EXCEL 中錄入數據，然后使用 SQL SEVER 2000 數據導入 /導出向導功能，直接將數據導入到相應的基本表中。5.4 創(chuàng)建各個功能的存儲過程（1）在 Worker 中插入一元組。P1_Worker_Insert 的定義：Create procedure p1_Worker_Insertworker_no char(5),woker_name char(10),worker_type char(8),work_wage

26、 int,worker_sexchar(2),worker_phno char(12),work_time char(30),dormitory_no smallint,domitory_campus char(4),domitory_location char(4)AsInsert into WorkerValues(worker_no, woker_name, worker_type, work_wage, worker_sex, worker_phno,work_time, dormitory_no, domitory_campus, domitory_location)(2) 在 Ro

27、om 中插入一元組P2_Room_Insert 的定義 ;Create procedurep2_Room_Insertroom_RNo char(6),room_RGrade char(4),room_RDepart char(20),room_RPerfect char(20),dormitory_no smallint,domitory_campus char(4),domitory_location char(4)AsInsert into RoomValues(room_RNo, room_RGrade, room_RDepart, room_RPerfect, dormitory_n

28、o, domitory_campus, domitory_location)(3) 在 Dormitory 中插入一元組P3_Dormitory_Insert 的定義：Create procedurep3_Dormitory_Insertdormitory_DorNo smallint,domitory_DorCampus char(4),dormitory_DorLacation char(4),dormitory_DorPhNo char(12),dormitory_Dormitory char(10)AsInsert into DormitoryValues(dormitory_DorNo,domitory_DorCamp