宿舍管理数据库模型作业.doc

宿舍管理数据库模型一、 需求分析阶段进行数据库设计首先必须准确了解与分析用户需求（包括数据与处理），需求分析是整个设计过程的基础。1、信息要求学生宿舍管理系统对于一个学校来说是必不可少的组成部分。目前好多学校还停留在宿舍管理人员手工记录数据的最初阶段，手工记录对于规模小的学校来说还勉强可以接受，但对于学生量比较庞大，需要记录存档的数据比较多的高校来说，人工记录是相当麻烦的。我针对此情况，设计了合适的学生宿舍管理系统，可以方便学生宿舍的管理，提高宿舍管理工作效率及查询效率。2、功能要求能够进行数据库的数据定义、数据操纵、数据控制等处理功能,进行联机处理的相应时间要短。具体功能应包括：系统应该提供学生住宿数据的插入、删除、更新；添加、修改、删除学生及职工基本信息的功能以及提供查询功能；宿舍楼基本信息查询与修改；对学生基本信息的查询(要实现按多种条件的查询)及宿舍信息（人数等）的查询。3、安全性和完整性要求安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过用户授权机制，欲用户登陆来识别用户级别，根据这个级别来分配用户权限，达到数据更高层次的安全保密功能。完整性要求用于描述学生基本信息、宿舍基本信息、宿舍楼基本信息、职工基本信息中数据项能否为null，以及一些用户自定义完整性（符合实际要求），详细完整性要求见于系统的逻辑设计阶段。二、 概念结构设计阶段概念结构设计阶段是整个数据库设计的关键，它通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。设计学生宿舍管理数据库，包括学生、宿舍、宿舍楼、职工四个关系。其E-R图如下：学生专业学号hao号性别姓名宿舍电话床铺号 移动电话宿舍宿舍号宿舍楼号宿舍电话应住人数入住时间住宿 N 1包含 N宿舍数 1管理职工组职工号姓名性别职务职工组号宿舍楼宿舍数宿舍楼号楼层数性别职工组号 N 1 管理年限三、 逻辑结构设计阶段逻辑结构是独立于任何一种数据模型的信息结构。逻辑结构的任务是把概念结构设计阶段设计好的基本E-R图转化为宜选用的DBMS所支持的数据模型相符合的逻辑结构，并对其进行优化。E-R图向关系模型转化要解决的问题是如何将实体型和实体间的联系转化为关系模式，如何确定这些关系模式的属性和码。由于宿舍楼和职工的联系方式是1:n（一对多），可以将其之间的联系与n端实体职工合并，宿舍楼与宿舍之间的联系、宿舍与学生之间的联系方式也是1:n，同样也将其之间的联系与n端实体宿舍、学生合并。设计宿舍管理数据库，包括学生、宿舍、宿舍楼、职工组四个关系, 其关系模式中对每个实体定义的属性如下：学生表Student：（学号，姓名，性别，专业，床铺号，移动电话，宿舍电话，入住时间，宿舍号，宿舍楼号）宿舍表Dorm：（宿舍号，宿舍楼号，宿舍电话，应住人数，宿舍数） 宿舍楼表House：（宿舍楼号，性别，宿舍数，楼层数，职工组号）职工组表Employee：（职工号，职工组号，姓名，性别，职务，宿舍楼号，管理年限）1、学生基本情况表(Student)字段名描述数据类型数据长度字段限制Stu_Id学号char20PrimarykeyStu_Name姓名char10Not nullStu_Sex性别char2Not nullDor_Tel宿舍电话char10Not nullStu_Study专业char10Not nullStu_Tel移动电话varchar15Not nullStu_Bed床铺号varchar4Not null2、宿舍表(Dorm)字段名描述数据类型数据长度字段权限Dor_Id宿舍号char10Primarykey Hou_Id宿舍楼号char10Primarykey Dor_Tel宿舍电话char10Not nullDor_Num 应住人数int Not null 字段名描述数据类型数据长度字段权限Hou_Id宿舍楼号char5PrimarykeyHou_Num楼层数char2Not nullHou_sex性别char2Not nullEmp_Ids 职工组号char20ForeignkeyDorm_Num宿舍数varchar4Not null3、宿舍楼表(House)4、职工组表（Employee）字段名属 性数据类型数据长度字段权限Emp_Ids职工组号char5Not nullEmp_Id职工号char5PrimarykeyEmp_Name姓名char10Not nullEmp_Sex性别char2Not nullEmp_Job职务char10Not null四、 物理设计阶段1、物理设计阶段的目标与内容数据库的物理设计就是为逻辑数据模型选取一个最合适应用要求的物理结构的过程，在这个阶段中要完成两大任务：（1）确定数据库的物理结构，在关系数据库中主要是存取方法和存储结构；（2）对物理结构进行评价，评价的重点是时间和空间效率。2、数据存储结构设计为数据库中各基本表建立的索引如下：（1）由于基本表Student和House的主码Stu_Id,Hou_Id经常在查询条件和连接操作的连接条件中出现，且它们的值唯一，考虑在两个属性上建立唯一性索引；（2）Dorm的主码Dor_Id，Hou_Id经常在查询条件中出现，且它们的组合值唯一，考虑在它们之上建立组合索引；（3）基本表Student中属性Stu_Name，经常在查询条件中出现，且经常出现在相等的比较条件中，考虑在其之上建立聚簇索引；五、 数据库实施阶段在数据库实施阶段，设计人员运用DBMS提供的数据库语言（如SQL）及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。1、创建数据库DMS： Create database DMS2、创建数据表：宿舍学生基本信息表Student(Stu_Id, Stu_Name, Stu_Sex, Dor_Tel, Stu_Study, Stu_Tel, Dor_Bed)的建表语句：create table Student(Stu_Id char(20) not null unique,Stu_Name char(10) not null,Stu_Sex char(2) not null,Dor_Tel char(10) not null,Stu_Study char(10) not null,Stu_Tel varchar(15)not null,Dor_Bed varchar(4)not null,primary key(Stu_Id)宿舍基本信息表Dorm(Dor_Id, Hou_Id, Dor_Tel, Dor_Num)的建表语句：create table Dorm(Dor_Id char(10)not null,Hou_Id char(10) not null,Dor_Tel char(10)not null,Dor_Num intnot null,primary key(Dor_Id, Hou_Id),check(Dor_Num0 and Dor_Num0 and Hou_Id100)职工组基本信息表Employee(Emp_Id, Emp_Name, Emp_Sex, Emp_Job)create table Employee(Emp_Id char(5) not null unique,Emp_Ids char(5) not null,Emp_Name char(10) not null,Emp_Sex char(2) not null,Emp_Job char(10) not null,primary key(Emp_Id)3、对数据库DMS输入数据如下：对Student表设计如下：Stu_IdStu_NameStu_SexDor_TelStu_StudyStu_TelStu_Bed0811070101安康男55551信息管理13590846577030811070215聂巧女00007信息管理13833567805060821107012张雪女33332信息管理15843356788020811070201陈建强男55553信息管理13568900543030811070128姚娟女00007信息管理15965436899010811070124张宝钦女00007信息管理13689042257050811070207郭甲男55551信息管理13988767654020811070105刘磊男44448信息管理13790543222010811070108宋素珍女22226信息管理1357906555402对宿舍信息表设计如下：Dor_IdHou_IdDor_TelDor_Num1113 3333262015 5555382045 5555182014 4444863062 222266307广顺楼000076对宿舍楼信息设计如下：Hou_IdHou_NumHou_sexEmp_IdsDorm_Num2 5女0012003 5女00120046男00230055男002200广顺楼7女003400职工组表设计如下：Emp_IdsEmp_IdEmp_NameEmp_SexEmp_Job0010011王一女管理员0010012王二女卫生员0010013王三女卫生员0020021张一女管理员0020022张二男卫生员0020023张三男卫生员0030031李一男管理员0030032李二女卫生员0030033李三女卫生员六、 数据库运行和维护阶段1、 模式操作创建模式为用户ZHANG创建一个模式STUCreate schema STU authorization ZHANG删除模式Drop schema STU Cascade2、 数据定义：基本表的创建、修改及删除(1)基本表的创建：对于Student表建表语句为：Create table Student(Stu_Id char(20) primary key,Stu_Name char(10) not null,Stu_Sex char(2) not null,Dor_Tel char(10) not null,Stu_Study char(10) not null,Stu_Tel varchar(15) not null,Dor_Bed varchar(4) not null)（2）基本表的修改：为Student表添加家庭住址Stu_Home列，数据类型为verchar型Alter Table Student Add Stu_Home verchar 将House表中Hou_Num的类型由字符型改为整形 Alter Table House Alter Column Hou_Num int 为表Student增加Stu_Id必须取唯一值的约束条件 Alter Table Student Add Unique(Stu_Id)(3)基本表的删除：Drop table Student cascade3、数据库查询操作单表查询：（1）查询Stu_Name为聂巧的Stu_Id。select Stu_Idfrom Studentwhere Stu_Name =聂巧;结果： Stu_Id 0811070215（2）查询Stu_Bed为03，Stu_Tel为135*3的学生的Stu_Id。select Stu_Idfrom Studentwhere Dor_Bed=03and Stu_Tel LIKE 135%3结果： Stu_Id 0811070201（3）查询所有学生的宿舍电话和移动电话。 select Stu_Name,Stu_Tel, Stu_Tel from Student;结果：Stu_NameDor_TelStu_Tel安康5555113590846577聂巧0000713833567805张雪3333215843356788陈建强5555313568900543姚娟0000715965436899张宝钦0000713689042257郭甲5555113988767654刘磊4444813790543222宋素珍2222613579065554（4）查询宿舍应住人数在6人以上的宿舍的宿舍号。 select Dor_Id from Dorm where Dor_Num not between 0 and 6;结果： Dor_Id 201 204（5）选择学号为0811070101的学生的全部信息。Select *from Studentwhere Stu_Id=0811070101;结果：Stu_IdStu_NameStu_SexDor_TelStu_StudyStu_TelStu_Bed0811070101安康男55551信息管理1359084657703（6）查询所有已登记的5号楼的宿舍电话号码和宿舍号，并按电话号码降序排列。select Dor_Tel,Dor_Idfrom Dormwhere Hou_Id =5 order by Dor_Tel DESC;结果：Dor_TelDor_Id5555320155551204连接查询 （1）找出学生聂巧所在的宿舍号，宿舍楼号和床铺号。select Dor_Id,Hou_Id,Dor_Bedfrom Dorm,StudentWhere Student. Dor_Tel= Dorm.Dor_Tel and Stu_Name=聂巧;结果：Dor_IdHou_IdStu_Bed307广顺楼06（2）找出职工组003的全体女性成员的姓名和工作的宿舍楼。select Emp_Name,Emp_Job, Hou_Idfrom Employee,Housewhere Employee.Emp_Ids=House.Emp_Ids and Emp_Sex=女;结果：Emp_NameEmp_JobHou_Id李一管理员广顺楼李三卫生员广顺楼嵌套查询：(1)查询六人间宿舍中床铺号为01的学生的姓名。Select Stu_Namefrom Studentwhere Dor_Bed=01and Dor_Tel In( select Dor_Telfrom Housewhere Hou_Num =6);结果：Stu_Name刘磊 (2)查询所有5层宿舍楼的管理员的姓名。Select Emp_Namefrom Employeewhere Emp_Job=管理员and Emp_Ids In( select Emp_Idsfrom Housewhere Hou_Num=5);结果：Emp_Name王一（3）查询“宿舍应住人数”少于电话为“55553”的“宿舍应住人数”的宿舍的宿舍号和电话号码。 Select Dor_Id, Dor_Tel From Dorm Where Dor_Num0 and Dor_Num100);)七、 问题总结在这次课程设计中我通过自己的调查、构思，设计、实施了宿舍管理系统，在设计数据库过程中，好多地方考虑的不够周到，遇到了很多问题最终通过询问老师、同学、翻看相关资料问题一一得到解决，下面将设计总结汇报如下：1、 在进行E-R图设计时，宿舍实体属性中，只有宿舍号没有将宿舍楼号添加进去，导致无法选主码，加上宿舍楼号后，两个组合起来作主键，这样才达到唯一确定一个宿舍的目的。2、 在学生表中没有“宿舍电话号码”列，导致学生表和宿舍表没有办法连接，后经老师指