基于软件工程的数据库设计与实现

参考学生基本信息管理系统数据库设计参考资料

一、实验目的

1. 学习分析系统需求，了解数据库设计方法
2. 学习并掌握E-R 图绘图方法
3. 了解数据库设计及实现过程

二、实验内容

分析学生基本信息管理系统需求，画出 E-R 图，设计及实现数据库，并存入自己的信息。

三、实验软件

Oracle 数据库、SQL Developer、draw.io（开源的绘图软件，画E-R图和网络拓扑图都很不错）

四、实验步骤及数据记录

1. 系统需求

  以本校为例，目前要新建一个学生基本信息管理系统，具体情况如下：

• （1）学生入学时会记录身份信息，包括姓名、性别、出生日期、民族、身份证号、学号、政治面貌、手机号码等相关信息。
• （2）学校有多个学院和多个校区。
  学生可因转专业而更换所属学院，但其入学时分配的学号不变。
  学生分布在所属学院所在的校区，并且可能会根据情况进行调整。例如某学年计算机学院在校区 1，第二年可能调整到校区 3。
• （3）每个校区有多栋宿舍楼，宿舍楼的类别有博士生公寓、硕士生公寓和本科生公寓。

系统需要完整记录学生的基本信息、当前所在学院、所在校区、所住宿舍楼寝室，以及其他可能需要的信息。

2. 设计 E-R 图

根据系统需求，用draw.io画出所有的 E-R 图。

3. 设计并创建数据库表

完整设计出学生基本信息管理系统的所有数据库表及相应的字段。

  使用 SQL Developer 将表创建在数据库中，为这些表建立关系模型并截图记录。

--校区信息表
CREATE TABLE CAMPUS_INFORMATION
(
    CAMPUSID NUMBER(1,0) NOT NULL,      --校区ID
    CAMPUSNAME VARCHAR2(50) NOT NULL,   --校区名称
    CAMPUSADDR VARCHAR2(300),           --校区地址
    CONSTRAINT CAMPUS_INFORMATION_PK PRIMARY KEY(CAMPUSID)
);

--学院信息表
CREATE TABLE COLLEGE_INFORMATION
(
    COLLEGENAME VARCHAR2(50) NOT NULL,  --学院名称
    CAMPUSID NUMBER(1,0) NOT NULL,      --校区ID
    CONSTRAINT COLLEGE_INFORMATION_PK PRIMARY KEY(COLLEGENAME),
    CONSTRAINT COLLEGE_INFORMATION_FK FOREIGN KEY(CAMPUSID)
    REFERENCES CAMPUS_INFORMATION(CAMPUSID)
);

--专业信息表
CREATE TABLE MAJOR_INFORMATION
(
    MAJORID NUMBER(2,0) NOT NULL,       --专业ID
    COLLEGENAME VARCHAR2(50) NOT NULL,  --学院名称
    MAJORNAME VARCHAR2(50) NOT NULL,    --专业名称
    CONSTRAINT MAJOR_INFORMATION_PK PRIMARY KEY(MAJORID),
    CONSTRAINT MAJOR_INFORMATION_FK FOREIGN KEY(COLLEGENAME)
    REFERENCES COLLEGE_INFORMATION(COLLEGENAME)
);

--宿舍楼信息表
CREATE TABLE BUILD_INFORMATION
(
    BUILDID NUMBER(4,0) NOT NULL,     --宿舍楼ID
    CAMPUSID NUMBER(1,0) NOT NULL,    --校区ID
    BUILDTYPE NUMBER(1,0) NOT NULL,   --宿舍楼类别（1：本科生，2：硕士生，3：博士生）
    BUILDNUM NUMBER(2,0) NOT NULL,    --宿舍楼序号
    CONSTRAINT BUILD_INFORMATION_PK PRIMARY KEY(BUILDID),
    CONSTRAINT BUILD_INFORMATION_FK FOREIGN KEY(CAMPUSID)
    REFERENCES CAMPUS_INFORMATION(CAMPUSID),
    CONSTRAINT BUILD_INFORMATION_CK CHECK
    (
        BUILDTYPE = '1' OR BUILDTYPE = '2' OR BUILDTYPE = '3'
    )
);

--寝室信息表
CREATE TABLE ROOM_INFORMATION
(
    ROOMID NUMBER(7,0) NOT NULL,        --寝室ID
    BUILDID NUMBER(4,0) NOT NULL,       --宿舍楼ID
    ROOMNUM NUMBER(3,0) NOT NULL,       --寝室序号
    CONSTRAINT ROOM_INFORMATION_PK PRIMARY KEY(ROOMID),
    CONSTRAINT ROOM_INFORMATION_FK FOREIGN KEY(BUILDID)
    REFERENCES BUILD_INFORMATION(BUILDID)
);

--学生信息表
CREATE TABLE STUDENT_INFORMATION
(
    SNO NUMBER(13,0) NOT NULL,          --学号
    MAJORID NUMBER(2,0) NOT NULL,       --专业ID
    ROOMID NUMBER(7,0) NOT NULL,        --寝室ID
    SNAME VARCHAR2(50) NOT NULL,        --学生姓名
    SGENDER VARCHAR2(20) NOT NULL,      --学生性别
    SBIRTH DATE NOT NULL,               --学生出生日期
    SNATION VARCHAR2(20) NOT NULL,      --学生民族
    SIDCARD VARCHAR2(30) UNIQUE,        --学生身份证号
    SPOLIT VARCHAR2(20) NOT NULL,       --学生政治面貌
    SPHONE VARCHAR2(20) NOT NULL,       --学生手机电话 
    SGRADE NUMBER(4,0) NOT NULL,        --学生年级
    CONSTRAINT STUDENT_INFORMATION_PK PRIMARY KEY(SNO),
    CONSTRAINT STUDENT_INFORMATION_FK1 FOREIGN KEY(MAJORID)
    REFERENCES MAJOR_INFORMATION(MAJORID),
    CONSTRAINT STUDENT_INFORMATION_FK2 FOREIGN KEY(ROOMID)
    REFERENCES ROOM_INFORMATION(ROOMID)
);

4. 存入个人信息

将自己的信息存储在相应的数据库表中，并截图记录存储的表数据。

--存入个人信息
INSERT INTO CAMPUS_INFORMATION VALUES('1','清水河校区','西源大道2006号');
INSERT INTO COLLEGE_INFORMATION VALUES('信息与通信工程学院','1');
INSERT INTO MAJOR_INFORMATION VALUES('02','信息与通信工程学院','网络工程');

五、实验结论及思考题

1、请举例说明本次设计中的数据库表如何来满足数据库三大范式。

1. 为什么学院这里不是创建学院ID，而是学院名称作为主键？
   因为学院名称在一个学校中已经可以唯一了。
2. 为什么专业有专业ID？
   因为一个学校的同一个专业可能在不同学院，例如参考电子科技大学 2021 年本科招生专业目录电子信息工程，在信息与通信工程学院、经济与管理学院、格拉斯哥学院都有通信工程是信息与通信工程学院和格拉斯哥学院都有。微电子科学与工程是电子科学与工程学院和格拉斯哥学院都有。学校专业本科招生不超过100个，所以设计是NUMBER(2,0)。
3. 此次实验并没有”第5章关系型数据设计“的（学号，课程号）→成绩，而是将年级最后作为学生的一个属性。

第一范式：首先不同校区以及不同类型的宿舍，宿舍楼序号存在相同的情况，同时每个宿舍楼都有101寝室，所以加入宿舍楼ID，和寝室ID，能够保证不重复，从而达到属性不可分的目的。

第二范式：如下图所示，一开始的宿舍设计是这样的，导致存在非主属性对码传递依赖，所以，为什么寝室需要寝室ID，因为宿舍名称412不能够唯一区分不同元组的属性或属性组合。不同的宿舍楼都有412，但是将寝室和宿舍楼分开是为了消除非主属性对码的部分函数依赖。在分解后的关系模式中，非主属性都完全函数依赖于码，由此才能变成2NF。

第三范式：在学院和专业是否拆分的过程中，考虑到学生属于专业，专业属于学院，这中间是存在非主属性对码传递依赖，所以分开之后才能变成3NF。

2、用一张视图来展示学生的基本信息、所在学院、所住寝室、所在校区的情况。请写出创建视图的代码，以及查询该视图信息的 SELECT 命令和结果截图。

CREATE VIEW STU_INFO AS
SELECT STUDENT_INFORMATION.SNO,SNAME,SGENDER,SBIRTH,SNATION,SIDCARD,SPOLIT,SPHONE,SGRADE,COLLEGE_INFORMATION.COLLEGENAME,(CASE WHEN BUILD_INFORMATION.BUILDTYPE = '1' THEN '本科生公寓' WHEN BUILDTYPE = '2' THEN '硕士生公寓' ELSE '博士生公寓' END) AS BUILDTYPE,BUILD_INFORMATION.BUILDNUM,ROOM_INFORMATION.ROOMNUM,CAMPUS_INFORMATION.CAMPUSNAME
FROM STUDENT_INFORMATION,COLLEGE_INFORMATION,BUILD_INFORMATION,ROOM_INFORMATION,CAMPUS_INFORMATION;

SELECT * FROM STU_INFO;

六、总结及心得体会

1. 设计的流程依据需求分析→局部ER模型→总体ER模型→优化总体ER模型，通过不断的迭代才能得到最后的设计结果。
2. 系统需求并未符合学校实际。因为一个学院有多个专业，可能这里假设的是一个学院只有一个专业吧，总得来说系统需求不够具体，不太符合设计得过程，所以以后自己在工作中应当尽可能地去挖掘客户需求。
3. 在学院的本科生招生中看到，如因教学安排等原因导致教学地点发生变化，以学校调整后的教学地点为准，所以调整专业所在校区以及学院校区太复杂了，这个之间存在依赖关系。一开始没有考虑设计专业这个实体的，因为这里的系统需求不明确，但是看到计算机专业第一年，第二年怎么怎么的，所以应该设计为一个实体。
4. 在设计的过程中，参考了数据库作业2，巧妙使用工具EXCEL能减轻工作压力，同时可以直观地看到所有数据库表及相应的字段，方便代码地书写。
5. 通过查阅网络资料，对SQLDEVELOPER的使用有了更新的理解，同时还掌握了CASE WHEN语句的使用，方便之后的SQL语句学习。
6. 参考以下老师讲解的PPT，在设计过程中，应当设计为实体还是说是一个属性，以及设计的流程，对设计有帮助。