学生宿舍管理系统数据库设计60462

1、学生宿舍管理系统数据库设计1需求简介1.1功能1. 宿舍楼基本信息查询与修改；2. 学牛基木信息查询与更新；3. 每一噫宿舍楼中宿舍信息的查询与信息更新；4. 宿舍保卫处基木信息的查询和修改；5. 宿舍事故基本信息及事故处理信息的查询和修改;6. 宿舍楼物品出入审批及记录；1.2数据1 宿舍楼基本信息(dormitory):包括 宿舍楼编号、彳首舍楼所在校区、宿 舍楼在校区中区域、每一幢宿舍楼楼管处的电话、宿舍楼楼管员信息等方面， 这样可以方便管理者对宿舍楼的管理，提高查询效率；2.学生基本信息(student)：包括 学生编号、学生所在学院信息、学生姓 名、学生性别、学生来自省份、学生出生日

2、期、学生入学时间、学生所学专业、 所在班级等方面的信息，可以方便学生信息的查询和更新；3 宿舍基 本信息(room , fi tment , fitmentdestruction , fitmentcompensate):宿舍基本信息包括四个数据结构(宿舍信息(room),宿 舍物品信息(fitment),宿舍物品损坏信息(fitmentdestruetion)，宿舍损坏物 品赔偿信息)，每个数据结构中的数据项见数据字典；4. 楼道工作人员基本信息(worker):包括 工作人员编号、工作人员姓名、 工作类型、工资、性别、联系方式、工作时间等数据项，可以方便管理人员对 宿舍楼道工人的任用、信息

3、查询及更改；5. 宿舍保卫处基本信息(safeguard):包括保卫处名称、人员数目、负责人 信息、联系电话等四方面的信息；6. 宿舍事故基本信息(accident, accident re search, accident compensate): 事故信息包括三个数据结构(事故信息、事故处理信息、事故赔偿信息)，具体 的数据项见数据字典；7. 物品出入基本信息(articaltnout)：包括出入物品的学生信息、出入的物 品信息、岀入物品时的负责人信息、出入物品时间，尽量减少宿舍事故的发生, 保障学生宿舍财产的安全。1.3数据字典数据项列表数据项编号数据项名数据项含义与其它数据项的关系存储

4、结构别名数据项编号数据项名数据项含义与其它数据项的关系存储结构别名di-1stuno学生编号char(9)学号di-2depname学生所在学院char(20)学院di-3stuname学生姓名char(10)姓名di-4stusex学生性别char(2)性别di-5stuhome学生来白省份char(10)祖籍di-6stuborth学生出生时间date出生日期di-7stuetime学生入学时间date入学时间di-8stuperfect学生所在专业char(20)专业di-9stuclass学生所在班级编号int编号di-10worno工作人员编号char(5)编号di-11wornam

5、e工作人员姓名char(10)姓名di-12wortype工作类型char(8)工作类型di-13worwage工作人员工资int月工资di-14worsex工作人员性别char(2)性别di-15worphno工作人员联系方式char(12)电话di-16wortime工作人员工作时i'可char(30)工作时间di-17rno宿舍编号char(6)舍号di-18rheader舍长信息等丁 stunamechar(10)舍长di-19rone宿舍学生信息同上char(10)舍员1di-20rtwo宿舍学生信息同上char(10)舍员2di-21rthree宿舍学生信息同上char(1

6、0)舍员3di-22rfour宿舍学生信息同上char(10)舍员4di-23rfive宿舍学生信息同上char(10)舍员5di-24rsix宿舍学生信息同上char(10)舍员6di-25rgrade宿舍学生所属年级等于 stuetimechar(4)年级di-26rdepart宿舍学生所在学院等于 depnamechar(20)学院di-27rperfect宿舍学生所学专业等于 stuperfectchar(20)专业di-28rciass学生所在班级编号等于 stuclasschar(2)班级di-29dorno宿舍楼编号smallint宿舍楼号di-30dorcampus宿舍楼所属校

7、区char(4)校区di-31dorlocatio n宿舍楼在校区位置char(4)宿舍区位di-32dorphno宿舍楼管处电话char(12)电话di-33doradminist宿舍楼楼管员信息等于wornochar(10)楼管员di-34sgname保卫处名称char(15)名字di-35sgwornum保卫处人员总数int人员数目di-36sgheader保卫处负责人信息char(10)负责人di-37sgphone保卫处电话char(12)电话di-38fitname宿舍物品名称char(16)宿舍物品数据项编号数据项名数据项含义与其它数据项的关系存储结构别名di-39fitpric

8、e宿舍物品价格float价格di-40fitnum每一种宿舍的数量int数量di-41fdfitment损坏物品信息等于 fitnamechar(16)物品名di-42fdstudent损坏的学生信息等于stunochar(9)学生di-43fdroom损坏物品宿舍信息等于rnochar(6)舍号di-44fdfitnum损坏物品的数量int数蜀di-45fcompfit赔偿物品信息等于 fitnamechar(16)物品名di-46fcompstu需赔偿学生信息等于stunochar(9)学生di-47fcompmon赔偿价格float赔偿价格di-48fcompprin赔偿负责人信息等于w

9、ornochar(10)负责人di-49fcompdate赔偿日期date日期di-50fcompnum赔偿物品数量int数蜀di-51acno事故编号int编号di-52actype事故类型char(10)类型di-53acartical事故损失物品char(30)物品名di-54acarnum事故损失物品数量int数蜀di-55acstu事故受害学生等于stunochar(9)学生di-56acdate事故发生日期date日期di-57acprin事故负责人信息等于 sgheaderchar(15)负责人di-58acstuph受害人联系方式char(12)学生电话di-59acverif

10、y事故是否属实bool核查di-60arno事故调查编号char(4)编号di-61arname事故调查名称char(15)调查di-62arprin事故调查负责人等于 sgheaderchar(10)负责人di-63arresult事故调查结果bool结果di-64acstu事故赔偿学生信息等于stunochar(10)学生di-65acartical事故赔偿物品信息char(30)物品名di-66acdate事故赔偿日期date日期di-67acprin事故赔偿负责单位等于 sgheaderchar(15)负贵单位di-68alostu要求物品出入学生等于stunochar(10)学生di

11、-69aioartical岀入物品信息char(20)物品名di-70aloprin岀入物品审查人等于wornochar(10)负责人di-71aiodate岀入物品日期date日期di-72aiono物品出入序号int序号数据结构列表数据结构编号数据结构名数据结构 含义组成ds-1student宿舍学生信息stuno,depname,stuname,stusex,stuhome,stuborth,stuetime,stuperfect,stuclassds-2worker宿舍楼工作人员信息wortime,worname,wortype,worwage?worsex,worphno,worno

12、rno,rheader,rone, rciass,ds-3room宿舍信息rthree,rfour,rfive,rsix,rgrade,rdepart,rperfect,rtwo,ds-4dormitory宿舍楼信息dorno,dorcampus,dorphnodorlocation,doradministds-5safeguard宿舍保卫处信息sgname,sgwornum,sg header,sgph oneds-6fitment宿舍物品配备信息fitname,fitprice,fitnumds-7fitme ntdestructi on宿舍物品损坏信息fdfitme nt,fdstude

13、nt,fdroom,fdfitnumds-8fitmentcompe nsate宿舍损坏物品赔偿信 息fcompfit,fcompstu,fcompprin,fcompdate,fcompnumacno,actype, acstu,acdate,ds-9accide nt宿舍事故注册信息acartical,acverify,acprin,acarnum,acstuphds-10accide ntresearch宿舍半故调查信息arno,arname,arprin, ar resultds-11accide ntcompe nsate事故损失物品赔偿信息acstu,acartical,acdat

14、e,acprinds-12articallnout宿舍楼物品出入信息aiostu,aioartical,aioprin,aiodate,aiono2概念结构设计2.1绘制局部er图学生宿舍入住分er图:学生宿舍物品管理分er图1：宿舍物品管理分er图2：岀入物品管理分er图:事故管理分er图:各分e-r图中每个实体的属性如下所示: 学生：student (stuno, depname, stuname, stusex, stuhome, stuborth, stuetime, stuperfect, stuclass)；宿舍：room(rno, rheader, rone, rclass, r

15、three, rfour, rfive, rsix, rgrade, rdepart, rperfect, rtwo)；宿舍楼：dormitory (dorno, dorcampus, dorlocation, dorphno, doradminist)； 宿舍物品：fitment (fitname, fitprice, fitnum)；楼道工作人员：worker (worno, worname, wortype, worwage, worsex,worphno, wortime)；保卫处：safeguard (sgname, sgwornum, sgheader, sgphone)；各分er

16、图中联系的展性如下所示：物品出入：art i cal inou t （aiono, alostu, aioartical, aloprin, aiodate）；宿舍物品处理：包含物品损坏和物品赔偿两个数据结构（将在逻辑设计阶段给 出）；事故：包含宿舍事故注册、宿舍事故调查、事故损失物品赔偿三个数据结构（具 体的结构将在系统逻辑设计阶段给出）02.2 er图合并3逻辑结构设计31将er图转换为关系模型基本e-r图向关系模型的转化如下：楼道工人：worker (worno, worname, wortype, worwage, worsex, worphno, wort i me,dorno ,

17、dorcampus , dor locat i on )；宿舍楼：dorm i tory (dorjno, dorcampus, dorlocat i on, dorphno, doradmi n i st)；宿舍：room (rno, rheader, rone, rc lass, rthree, rfour, rf i ve, rs i x, rgrade, rdepart,rperfect, rtwo, dorno, dorcampus, dorlocation)；宿舍物品：f i tment ( f i tname, f i tpr i ce, f i tnum, dorno, dor

18、campus, dorlocat ion)； 学生：student (stuno, depname, stuname, stusex, stuhome, stuborth, stuet ime,stuperfect, stuci ass, rno, dorno, dorcampus, dorlocat i on)；保卫处：safeguard (sgname, sgwornum, sgheader, sgphone)；物品出入：art i ca i i nout (a i ono, stuno, ai oart i ca i , ai op r in, ai odate, dor no,dorc

19、ampus, dorlocation)；宿舍物品处理包含两个数据结构(宿舍物品损坏信息，宿舍物品损坏赔偿信息), 基于表的各个属性都是原子项的考虑，现将宿舍物品处理分解为：宿舍物品损 坏、宿舍损坏物品赔偿，具体如下：宿舍物品损坏：f i tmentdestruct i o n (fitname, stuno, rno, fdf i tnum, dorno,dorcampus, dorlocat i on)；(消除命名冲突)宿舍物品损坏赔偿：f i tmentcompensate (f i tname, stuno, fcpr i n, fcompdate,fcompnum)；(消除命名冲突)宿

20、舍事故包含三个数据结构(宿舍事故注册信息、宿舍事故调查信息、宿舍事 故损失物品赔偿信息)，同样基于表的原子性的考虑也将事故分解为：事故注册、事故调查、事故赔偿，具体如下：事故注册：acc i dent (acno, actype, stuno, acdate, acart i ca i , acver i fy, sgname,acarnum, acstuph)；事故调查：acc ide nt re search ( acno, arname, sgname, arresu it)；事故赔偿：acc i dentcompensate ( acno, acstu, acart i ca i ,

21、acdate, sgname)；(注：标有直线下划线的为主属性，标有波浪线下划线的是外键属性，主属性 与外键屈性一起构成主码)3.2关系模式优化关 系模式 worker、 dormitory , fitment, safeguard , articaltnout,fitmentdestruct ion , fitmentcompensate , accident , accidentresearch , accidentcompensate不存在非主属性对主属性的部分函数依赖，也不存在传递 函数依赖，己经达到了 3nf,但是宿舍关系模式(room)中存在着一些不应该有 的数据冗余，现将模型优化

22、为：room ( rno , rheader , rgrade , rdepart , rperfect , dorno , dorcampus , dorlocation)；虽然room中还存在一些数据冗余，但可以提高查询效率。3.3数据库模式设计dbms选择的sql server 2005,根据该系统特点设计数据库的模式如下。1 .楼道工人(worker)基本信息表，用于记录楼道工人的基本信息:属性名数据类型取值范围wornochar(5)00000 99999wornamechar(10)wortypechar(8)是否是主属性或外键完整性是not nullnot nullnot nul

23、lworwageint大于500 (元/月)否not nullworsexchar(2)男、女否not nullworphnochar(12)否nullwortimechar(30)否nulldornosmallint00 99是not nulldorcampuschar(4)四而八方其中之一是not nulldorlocati onchar(4)四面八方共中z是not null2.宿舍楼(dormitory)基本信息表，用于记录宿舍楼的基本信息:属性名数据类型取值范围是否是主属性或外键完整性dornosmallint00 99是not nulldorcampuschar(4)四面八方其中之一

24、是not nulldorlocationchar(4)四面八方其屮之一是not nulldorphnochar(12)否nulldoradministchar(10)否null宿舍(room)基本信息表,用于记录宿舍的基本信息：属性名数据类型取值范围是否是主属性或外键完整性rnochar(6)01-001-99-999是not nullrheaderchar(10)否nullrgradechar(4)否not nullrdepartchar(20)否not nullrperfectchar(20)否not nulldornosmalli nt00 99是not nulldorcampuscha

25、r(4)四面八方其中之一是not nulldorlocationchar(4)四面八方其中之一是not null宿舍楼配备物品(fitment)基本信息表，用于记录宿舍楼配备物品的基本信属性名数据类型取值范围是否是主属性或外键完整性fitnamechar(16)是not null属性名数据类型取值范围是否是主属性或外键完整性fitpricefloat大于0否not nullfitnumint大于0否not nulldornosmallint00 99是not nulldorcampuschar(4)四面八方其中z是not nulldorlocationchar(4)四面八方其中z是not nu

26、ll5.宿舍学生(student)基本信息表，用于记录宿舍学生的基本信息:属性名数据类型取值范围是否是主属性或外键完整性stunochar(9)00000000999999999是not nulldepnamechar(20)否not nullstunamechar(10)否not nullstusexchar(2)男、女否not nullstuhomechar(10)否not nullstuborthdatetime否not nullstuetimedatetime否not nullstuperfectchar(20)否not nullstuclassint大于0小于10否not nullr

27、nochar(6)01-001 99999是not nulldornosmallint00 99是not nulldorcampuschar(4)四面八方瓦中之一是not nulldorlocationchar(4)四面八方其中之一是not null6猪舍保卫处(safeguard)基本信息表，用于记录保卫处的基本信息:属性名数据类型取值范围是否是主属性或外键完整性sgnamechar(15)是not nullsgwornumint大于0否not nullsgheaderchar(10)否not nullsgphonechar(12)否null7. 宿舍楼物品出入(articallnout)基

28、本信息表，用于记录物品出入的基本信息:属性名数据类型取值范围是否是主属性或外键完整性属性名数据类型取值范围是否是主属性或外键完整性stunochar(9)00000000999999999是not nullaioarticalchar(16)否nullaloprinchar(9)否not nullaiodatedatetime是not nullaionoint大于0是not nulldornosmalli nt00 99是not nulldorcampuschar(4)四面八方其中z是not nulldorlocationchar(4)四面八方其中z是not null8. 宿舍配备物品损坏(f

29、itment destruct ion)基本信息表，用于记录物品损坏的基本信息:属性名数据类型取值范围是否是主属性或外键完整性fitnamechar(16)是not nullstunochar(9)00000000999999999是not nullrnochar(6)01-001 99999是not nullfdfitnumint大于等于0否not nulldornosmallint00 99是not nulldorcampuschar(4)四面八方其中之一是not nulldorlocationchar(4)四面八方其中之一是not null9. 宿舍配备物品损坏赔偿(fi tmentco

30、mpenscite)基本信息表，记录损坏物品赔偿的基本信息:属性名数据类型取值范围是否是主属性或外键完整性fitnamechar(16)是not nullstunochar(9)00000000999999999是not nullfcprinchar(10)是not nullfcompdatedatetime否not nullfcompnumint大于等于0否not null10宿舍事故注册(accident)基本信息表，用于记录宿舍事故注册的基本信息:属性名数据类型取值范围是否是主属性或外键完整性acnoint0000-9999是not nullactypechar(1o)否not null

31、stunochar(30)00000000-999999999是not nullacdatedatetime是not nullacarticalchar(30)否not nullacverifybool否nullsgnamechar(15)是nullacarnumint大于等于0否not nullacstuphchar(12)否null11宿舍事故调查(accidentresearch)基本信息表，用于记录宿舍事故调查的基本信息:属性名数据类型取值范围是否是主属性或外键完整性acnoint0000-9999是not nullarnamechar(15)是not nullsgnamechar(1

32、5)是not nullarresultbool否not null12.宿舍事故赔偿(accidentcompensate)基本信息表，用于记录宿舍事故赔偿的 基本信息：属性名数据类型取值范围是否是主属性或外键完整性acnoint0000-9999是not nullacstuchar(10)00000000999999999是not nullacarticalchar(30)否not nullacdatedatetime否not nullsgnamechar(15)是not null3.4用户子模式设计编号用户子模式 作用(共性：提供数据保密和安全保护机制)(view)v-1worview便丁查

33、询和修改楼道工人的基木信息编号用户子模式(view)作用(共性：提供数据保密和安全保护机制)v-2dormview方便宿舍楼的基本信息的查询、更新v-3roomview以便于宿舍的基本信息的查询和更新v-4fitview用于宿舍楼配备物品的基本信息的查询v-5stuview便于查询和更改学生的基本信息v-6sgview方便学生査询宿舍保卫处的基木信息v-7arloview以便于物品出入的管理和信息的查询、更改v-8fdview便于宿舍物品损坏的的登记及处理和信息的查询v-9fcview查询损坏物品赔偿的基本信息，便于宿舍物品的管理v-10accview方便学生事故的注册及保卫人员对事故注册的查

34、询v-11arview便于学生查询宿舍事故调查的基本信息v-12acview方便宿舍事故赔偿的信息查询和更新view的具体结构略。4物理结构设计4.1存储结构由于系统数据不多，所以只在两个盘驱分别建立一个主数据文件和一个日志 文件就可以了。4.2索引为数据库中各基木表建立的索引如下：由于基本表room, student的主码rno, stuno经常在查询条件和连接操作 的连接条件中出现，r它们的值唯一，考虑在两个属性上建立唯一性索引；dormitory的主码dorno, dorcampus, dorlocation经常在查询条件中出现, 且它们的组合值唯一，考虑在它们之上建立组合索引;基本表s

35、tudent的属性stunamc,经常在查询条件中出现，且经常岀现在相等的比较条件屮，考虑在其z上建立聚簇索引；基本表fitment、safeguard的属性值儿乎不会有什么变化，更新率很低, 可考虑适当建立索引；基本表 worker, articalln out, fit men tdestruc tion, fit mentcompensa te, accident, accident re scar ch, accidcntcompcnsatc 的属性值经常发生变化， 权衡系统为维护索引付出的代价，可考虑不建立索引，也可以适当建立索引。5实施5.1建立数据库create database

36、 student_dormitory_management;5.2建立数据表（1）楼道工人基本信息表的建立:create table worker (not null unique,not null,not null,not null,not null,null,null,not null,not null,not null,worno char(5)worname char(10)wortype char(8)worwage intworsex char(2)worphno char(12)wortime char(30)dorno smallintdorcampus char(4)dorl

37、ocation char(4) primary key(worno), foreign key(dorno, dorcampus, dorlocation) referencesdormitory(dornot dorcampus, dorlocation)，check(worwage >= 0), check(worsex ='男or worsex ='女 j);(2) 宿舍楼基本信息表的建立：create table dormitory(dorno smallintnot null,dorcampus char(4)not null,dorlocation char(

38、4)not null,dorphno char(12)null,doradminist char(10)null,primary key(dorno,dorcampus,dorlocation), check(dorno>0 and dorno<100);(3) 宿舍基本信息表的建立：create table room(rno char(6)not null unique,rheader char(10)null,rgrade char(4)not null,rdepart char(20)not null,rperfect char(20)not null,dorno small

39、intnot null,dorcampus char(4)not null,dorlocati on char(4)not null,primary key(rno),foreign key(dorno,dorcampus, dorlocation) referencesdormitory(dorno, dorcampus, dorlocation);(4)宿舍楼配备物品基本信息表的建立:create table fitment(fitname char(16)not null unique,fitprice floatnot null,fitnum intnot null,dorno sma

40、llintnot null,dorcampus char(4)not null,dorlocation char(4)not null,primary key(fitname),foreign key(dorno, dorcampus, dorlocation) referencesdormitory(dorno, dorcampus, dorlocation);(5)宿舍学生基本信息表的建立create table student(stuno char(9)not null unique,depname char(20)not null,stuname char(10)not null,st

41、usex char(2)not null,stuhome char(10)not null,stuborth datetimenot null,stuetime datetimenot null,not null, not null, not null, not null, not null, not null,stuperfect char(20)stuclass intrno char(6)dorno smallintdorcampus char(4)dorlocati on char(4) primary key(stuno), foreign key(rno) references r

42、oom(rno), foreign key(dorno, dorcampus, dorlocation) referencesdormitory(dorno, dorcampus, dorlocation), check(stuclass>0 and stuclass<=10);(6) 宿舍保卫处基本信息表的建立：not null unique, not null, not null, null,create table safeguard(sgname char(15)sgwornum intsgheader char(10)sgphone char(12) primary ke

43、y(sgname), check(sgwornum > 0);(7) 宿舍楼物品出入基本信息表的建立：not null, null,not null,not null,not null unique,not null,not null,not null,create table articallnout(stuno char(9)aioartical char(16)aloprin char(9)aiodate datetimeaiono intdorno smallintdorcampus char(4)dorlocation char(4)primary key(aiono,aiod

44、ate),foreign key(stuno) references student(stuno),foreign key(aioprin) references worker(worno),foreign key(dorno, dorcampus, dorlocation) referencesdormitory(dorno, dorcampus, dorlocation),check(aiono > 0);(8) 宿舍配备物品损坏基本信息表的建立:not null unique,not null,not null,create table fitmentdestruction( fi

45、tname char(16) stuno char(9) rno char(6)fdfitnum intnotnull,dorno smallintnotnull,dorcampus char(4)not null,dorlocation char(4)not null,foreign key(dorno, dorcampus, dorlocation) referencesdormitory(dorno, dorcampus, dorlocation),foreign key(fitname) references fitment(fitname),foreign key(stuno) re

46、ferences student(stuno), foreign key(rno) references room(rno), check(fdfitnum >= 0);(9) 宿舍损坏配备物品赔偿基本信息表的建立：create table fitmentcompensate(fitname char(16)not null,stuno char(9)notnull,fcprin char(15)notnull,fcompdate datetime not null,fcompnum intnotnull,foreign key(fitname) references fitment(f

47、itname), foreign key(stuno) references student(stuno), foreign key(fcprin) references safeguard(sgname), check(fcompnum >= 0);(10) 宿舍事故注册基本信息表的建立：create table accident(acno intnot null unique,actype char(10)not null,stuno char(9)not null,acdate datetimenot null,acartical char(30)not null,acverify

48、 boolnull,sgname char(15)not null,acarnum intnot null,acstuph char(12)not null,primary key(acno,acdate),foreign key(stuno) references student(stuno), foreign key(sgname) references safeguard(sgname), check(acarnum > 0),);(11) 宿舍事故调查基本信息表的建立：create table accidentresearch(acno intnot null,arname ch

49、ar(15) not null,sgname char(15)not null,arresult boolnot null,primary key(sgname), foreign key(acno) references accident(acno), foreign key(sgname) references safeguard(sgname),);(12) 宿舍事故赔偿基本信息表的建立：create table accidentcopensate(not null,acno intacartical char(30)acdate datetimesgname char(15)not n

50、ull,not null,not null,acstu char(9)not null,foreign key(acno) references accident(acno),foreign key(acstu) references student(stuno), foreign key(sgname) references safeguard(sgname);5.3建立视图（1）用于查询和更新楼道工人基本信息的视图定义如下：create view worview（编号，姓名，工作类型，工资，性别，联系方式， 工作时间，宿舍楼编号，校区，区位）asselect *from workerwith