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

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

某学生宿舍管理系统，涉及的部分信息如下：
学生：学号，姓名，性别，专业，班级。
寝室：寝室号，房间电话。
管理员：员工号，姓名，联系电话。
其中：每个寝室可同时住宿多名学生，每名学生只分配一个寝室；每个寝室指定其中一名学生担当寝室长；每个管理员同时管理多个寝室，但每个寝室只有一名管理员。
每组同学从以上选题中任选一个进行相应的设计与实现。要求如下：
（1）从简单的选题语义出发，按照数据库设计步骤，分别完成如下内容：概念设计（ER模型），给出满足需求的最终全局ER图，要求模型尽量精简，消除不必要的冗余，并给出理由或说明。
（2）逻辑设计（关系模型），把ER模型转换成适当的关系模式，并进行适当地规范化（可选），设计相关完整性约束。
（3）物理设计，在某一主流DBMS中创建数据库，根据业务需求规划适当索引（包括聚簇索引和非聚簇索引），并设计必要的用户视图（即外模式），完成相关查询或更新要求。
四、实验要求
1、完成上述任务；
2、完成实验报告（包括上述任务中的E-R图、转换后的关系模型及优化过程、创建数据库的SQL语句）

---

E-R图

---

关系模式

学生（学号，姓名，性别，专业，班级，寝室号）

寝室（寝室号，房间电话，员工号）   管理员（员工号，姓名，联系电话）

---

物理实现

使用mysql8.0

第一步

创建数据库，并选择

CREATE DATABASE shiyan;
use shiyan;

第二步

创建学生表，宿舍表，管理员表

//学生表
CREATE TABLE student(
 sno int(5) PRIMARY KEY ,
 name varchar(6) NOT NULL,
 sex ENUM('男','女') DEFAULT '女',
 dept varchar(10),
 class varchar(10),
 dno int(5));
 
//宿舍表
CREATE TABLE dormitory(
 dno int(5) PRIMARY KEY ,
 phone varchar(11) NOT NULL,
 mno int(5)
);

//管理员表
CREATE TABLE manager(
 mno int(5) PRIMARY KEY ,
 name varchar(10) NOT NULL,
 mphone varchar(11) NOT NULL
);

第三步

添加约束和索引

给student表的dno添加外键约束

给dormitory表的mno添加外键约束

若这是一个实际应用的系统，那么学生查询无异于频繁的，所以为学生的姓名添加普通索引

//添加普通索引
alter table add index s_name('name');
//添加dno的外键约束
ALTER TABLE student ADD CONSTRAINT FK_ST_DO foreign key(dno) REFERENCES dormitory(dno);
//添加mno的外键约束
ALTER TABLE dormitory ADD CONSTRAINT FK_DO_MA foreign key(mno) REFERENCES manager(mno)

第四步

创建视图

create view v_student as select sno,name,sex,dept,class,dno from student;

第五步

插入数据

//manager表
INSERT INTO manager VALUES(12345,18989341833)
//dormitory表
INSERT INTO dormitory VALUES(12345,18989341832,12346)
//student表
INSERT INTO `student` (`sno`, `name`, `sex`, `dept`, `class`, `dno`) VALUES ('12345', '李铭炜', '男', '计算机科学与技术', '计科一班', '12345');

以下是外键生效的效果