如果你对其他数据库原理的实验感兴趣，请考虑阅读我的专栏

SQL Server实验系列

实验六 简单数据库应用系统设计与实现

实验目的：
（1）理解并能运用数据库设计的常见步骤来设计满足给定需求的概念模型（ER模型）和关系数据模型；
（2）能结合有关的指南对模型进行优化处理。
（3） 能够在某主流DBMS上创建数据库模式、所建数据库模式满足用户的基本业务需求;

实验内容:
给出如下选题，每组同学任选一个选题进行相应的设计与实现。具体选题如下：
（1）某汽车维修部门的维修工单如下表所示：

（2）某科技管理部门欲开发一个科研项目申报与评审系统，涉及的部分信息如下：
项目：项目编号，项目名称，申请者，期限，金额，项目描述。
项目类别：类别编号，类别名称。
专家：专家编号，姓名，职称，单位。
其中：根据项目类别，每个申报项目需由对应领域的多位专家进行评审；每位专家只参与一类项目的评审，评审时要记录评审日期和评审意见。
（3）某学生宿舍管理系统，涉及的部分信息如下：
学生：学号，姓名，性别，专业，班级。
寝室：寝室号，房间电话。
管理员：员工号，姓名，联系电话。
其中：每个寝室可同时住宿多名学生，每名学生只分配一个寝室；每个寝室指定其中一名学生担当寝室长；每个管理员同时管理多个寝室，但每个寝室只有一名管理员。

实验过程：（选择：学生宿舍管理系统）
每组同学从以上选题中任选一个进行相应的设计与实现。要求如下：
（1）从简单的选题语义出发，按照数据库设计步骤，分别完成如下内容：概念设计（ER模型），给出满足需求的最终全局ER图，要求模型尽量精简，消除不必要的冗余，并给出理由或说明。

• 实体：

  • 学生：属性有学号，姓名，性别，专业，班级
  • 宿舍：属性有房间号，房间电话
  • 管理员：属性有员工号，姓名，联系电话

• 实体间关系：

  • 一个宿舍有多名同学住宿，一名同学只分配一个宿舍
  • 一个管理员管理多个宿舍，一个宿舍只能有一个管理员
  • 每间宿舍的同学中，要挑选一个寝室长

（2）逻辑设计（关系模型），把ER模型转换成适当的关系模式，并进行适当地规范化（可选），设计相关完整性约束。

规范化	定义
第一范式	确保字段原子性（确保每列不可再分）
第二范式	非键字段必须依赖键字段（一个表描述一件事）
第三范式	消除传递依赖 （所有非键字段不能有传递依赖）

（方式1）

• 学生：（学号、姓名、性别、专业、班级、寝室号）
  • 主键：学号
  • 外键：寝室号
• 寝室：（寝室号、房间电话、寝室长学号、员工号）
  • 主键：寝室号或寝室长学号
  • 外键：员工号、寝室长学号
• 管理员：（员工号、姓名、联系电话）
  • 主键：员工号

（方式2）

• 学生：（学号、姓名、性别、专业、班级）

  • 主键：学号

• 寝室：（寝室号、房间电话）

  • 主键：寝室号

• 管理员：（员工号、姓名、联系电话）

  • 主键：员工号

• 住宿：（寝室号，学号）

  • 主键：学号
  • 外键：寝室号、学号

• 学生管理寝室：（寝室号，寝室长学号）

  • 主键：寝室号、寝室长学号
  • 外键：寝室号，寝室长学号

• 管理：（寝室号，员工号）

  • 主键：寝室号
  • 外键：寝室号，员工号

（3）物理设计，在某一主流DBMS中创建数据库，根据业务需求规划适当索引（包括聚簇索引和非聚簇索引），并设计必要的用户视图（即外模式），完成相关查询或更新要求。

方式1
创建表

--创建员工表
CREATE TABLE employee(eno varchar(8) PRIMARY KEY,ename VARCHAR(8),etel varchar(12)
);--创建学生表
create table student(sno varchar(9) PRIMARY KEY,sname varchar(8),ssex varchar(4)  CHECK (ssex='男' or ssex='女'),sspecialty varchar(9),sclass varchar(9),dno varchar(8),
);
--创建宿舍表
CREATE TABLE dormitory(dno varchar(8) PRIMARY KEY,dtel varchar(12),sno varchar(9),eno varchar(8),CONSTRAINT fk_Student FOREIGN KEY (sno) REFERENCES student(sno),CONSTRAINT fk_Emplyee FOREIGN KEY (eno) REFERENCES employee(eno));--将学生表中dno修改为外键
ALTER TABLE student 
ADD CONSTRAINT fk_Dormitory FOREIGN KEY (dno) REFERENCES dormitory(dno)

设计视图，完成查询要求

方法2
建表

--学生表
CREATE TABLE student
(sno varchar(10) PRIMARY KEY,sname varchar(8),ssex varchar(4) CHECK (ssex='男' or ssex='女'),sspecialty varchar(9),sclass varchar(8)
);
--寝室表
CREATE TABLE dormitory(dno varchar(6) PRIMARY KEY,dtel varchar(12)
);
--管理员表
CREATE TABLE manager
(mno varchar(8) primary key,mname varchar(8),mtel varchar(12)
);--住宿表（寝室号，学号）
CREATE TABLE stu_putup(dno varchar(6),sno varchar(10) primary key,CONSTRAINT fk_stu_putup_sno FOREIGN KEY (sno) REFERENCES Student(Sno) on delete cascade on update cascade,CONSTRAINT fk_stu_putup_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade
);--学生管理寝室表（寝室号，寝室长学号）
CREATE TABLE stu_manage_dor(dno varchar(6) primary key,head_sno varchar(10),CONSTRAINT fk_stu_mange_dor_Sno FOREIGN KEY (head_sno) REFERENCES Student(Sno) on delete cascade on update cascade,CONSTRAINT fk_stu_mange_dor_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade
);--管理表（寝室号，员工号）
CREATE TABLE manage_dor(dno varchar(6) primary key,mno varchar(8),CONSTRAINT fk_mange_dor_dno FOREIGN KEY (dno) REFERENCES dormitory(dno) on delete cascade on update cascade,CONSTRAINT fk_mange_dor_mno FOREIGN KEY (mno) REFERENCES manager(mno) on delete cascade on update cascade
);

设计视图