1 实验目的
1、学习分析系统需求，了解数据库设计方法
2、学习并掌握E-R 图绘图方法
3、了解数据库设计及实现过程
2 实验内容
分析学生基本信息管理系统需求，画出E-R图，设计及实现数据库，并存入自己的信息。
3 实验软件
Oracle 数据库、SQL Developer、Visio
4 实验内容
4.1 系统需求
以本校为例，目前要新建一个学生基本信息管理系统，具体情况如下：
• 学生入学时会记录身份信息，包括姓名、性别、出生日期、民族、身份证号、学号、政治面貌、手机号码等相关信息。
• 学校有多个学院和多个校区。
• 学生可因转专业而更换所属学院，但其入学时分配的学号不变。
• 学生分布在所属学院所在的校区，并且可能会根据情况进行调整。例如某学年计算机学院在校区 1，第二年可能调整到校区 3。
• 每个校区有多栋宿舍楼，宿舍楼的类别有博士生公寓、硕士生公寓和本科生公寓。
系统需要完整记录学生的基本信息、当前所在学院、所在校区、所住宿舍楼寝室，以及其他可能需要的信息。
4.2 设计E-R 图
根据系统需求，用Visio 画出所有的E-R 图，如下所示：
设计共分为学生，学院，校区，宿舍楼和宿舍五个实体，其中的关系和主键已经标注，设计宿舍楼和宿舍是为了更改宿舍楼的类型的时候不发生冲突。、
同时，我们去掉了学院的学员人数属性，防止在学生转院的时候，学院表中的人数未经修改而违反范式，当然实际应用中可以是使用触发器解决。
我们设计了课程这一实体，课程和学生实体之间存在m对n的关系，故而这一属性需要单独的表来作为体现。
4.3 设计并创建数据库表
根据ER图的实体，关系和属性，设计数据库表，使用SQL Developer 将表创建在数据库中，为这些表建立关系模型。
设计关系模型如下所示：
表格创建语句如下所示：
CREATE TABLE STU(
STUID CHAR(13),
STUSEX CHAR(5),
STUNAME VARCHAR(10),
NAT VARCHAR(5),
TEL CHAR(11),
POL VARCHAR(10),
BIR DATE,
IDCARD CHAR(18),
MAJID CHAR(4),
DORMID CHAR(10),
CONSTRAINT STU_PK PRIMARY KEY(STUID)
ENABLE
);
CREATE TABLE MAJOR(
MAJID CHAR(4),
MAJNAME VARCHAR(20),
COLNAME VARCHAR(20),
CONSTRAINT MAJOR_PK PRIMARY KEY(MAJID)
ENABLE
);
CREATE TABLE COLLEGE(
COLNAME VARCHAR(20),
AREANAME VARCHAR(10),
CONSTRAINT COLLEGE_PK PRIMARY KEY (COLNAME)
ENABLE
);
CREATE TABLE COURSE(
COUID CHAR(11),
COUNAME VARCHAR(20),
TEACHER VARCHAR(20),
OFFERED VARCHAR(20),
CONSTRAINT COURSE_PK PRIMARY KEY (COUID)
ENABLE
);
CREATE TABLE AREA(
AREANAME VARCHAR(10),
LOC VARCHAR(20),
CONSTRAINT AREA_PK PRIMARY KEY (AREANAME)
ENABLE
);
CREATE TABLE BUILDING(
BID CHAR(10),
AREANAME VARCHAR(10),
TYPE VARCHAR(10),
CONSTRAINT BUILDING_PK PRIMARY KEY (BID)
ENABLE
);
CREATE TABLE DORM(
DID CHAR(10),
NUM NUMBER(2,0),
BUID CHAR(10),
CONSTRAINT DORM_PK PRIMARY KEY (DID)
ENABLE
);
ALTER TABLE STU ADD CONSTRAINT STU_MAJ_FK FOREIGN KEY ("MAJID") REFERENCES MAJOR (MAJID);
ALTER TABLE STU ADD CONSTRAINT STU_DORM_FK FOREIGN KEY ("DORMID") REFERENCES DORM (DID);
ALTER TABLE MAJOR ADD CONSTRAINT MAJ_COL_FK FOREIGN KEY ("COLNAME") REFERENCES COLLEGE (COLNAME);
ALTER TABLE COLLEGE ADD CONSTRAINT COL_AREA_FK FOREIGN KEY ("AREANAME") REFERENCES AREA (AREANAME);
ALTER TABLE BUILDING ADD CONSTRAINT BUI_AREA_FK FOREIGN KEY ("AREANAME") REFERENCES AREA (AREANAME);
ALTER TABLE DORM ADD CONSTRAINT DOR_BUI_FK FOREIGN KEY ("BUID") REFERENCES BUILDING (BID);
ALTER TABLE COURSE ADD CONSTRAINT COU_COL_FK FOREIGN KEY ("OFFERED") REFERENCES COLLEGE (COLNAME);
-- 处理MN的关系
CREATE TABLE LEARN(
STUID CHAR(13),
COUID CHAR(11),
GRADE NUMBER(3,0),
CONSTRAINT LEA_PK PRIMARY KEY (STUID,COUID)
ENABLE
);
ALTER TABLE LEARN ADD CONSTRAINT LEA_STU_FK FOREIGN KEY ("STUID") REFERENCES STU (STUID);
ALTER TABLE LEARN ADD CONSTRAINT LEA_COU_FK FOREIGN KEY ("COUID") REFERENCES COURSE (COUID);
4.4 存入个人信息
将自己的信息存储在相应的数据库表中，并记录存储的表数据。
由于存在外键的约束，在插入的时候，我们需要首先插入基本的，如校区和宿舍信息，再插入个人信息：
有语句如下：
INSERT INTO AREA VALUES('清水河校区','西源大道2006号');
INSERT INTO AREA VALUES('沙河校区','建设路二段4号');
INSERT INTO COLLEGE VALUES('信息与通信工程学院','清水河校区');
INSERT INTO MAJOR VALUES('0108','互联网+','信息与通信工程学院');
INSERT INTO BUILDING VALUES('010410','清水河校区','本科生公寓');
INSERT INTO DORM VALUES('010410448','4','010410');
INSERT INTO STU VALUES('2019010801003','男','嘎嘎嘎','汉族','15882191111','预备党员',TO_DATE('2001-05-07','yyyy-MM-dd'),'41132620010507XXXX','0108','010410448');
INSERT INTO COURSE VALUES('R0112830.01','数据库与软件工程','懂得懂得','信息与通信工程学院');
INSERT INTO LEARN VALUES('2019010801003','R0112830.01','100');
首先插入校区信息：
有了校区才能有学院信息：
宿舍和宿舍楼信息，其命名按照校区编号(01,02)-组团号-楼栋号[-寝室号]，例如清水河校区，四组团十栋448，记为010410448
最后插入个人信息，如下所示：
随后插入课程信息
创建学生和课程的关系：
5 思考题
1、 请举例说明本次设计中的数据库表如何来满足数据库三大范式。
本次设计中共有五个实体，分别是学生，专业，学院，课程，校区，宿舍楼和宿舍，其中明显的除了学生实体之外，其他的关系中性都仅有一个或者两个，容易判断他们满足三大范式要求。
我们只对学生关系进行分析描述其中包括的属性有：学号，姓名，性别，民族，手机号，身份证，政治面貌和出生日期。其中，显然的，学号，手机号和身份证号是主属性，然而这样的问题是显而易见的，他们违反了第二范式(学号，身份证号)→姓名，学号→姓名，则姓名和(学号，身份证号)这个码之间存在了部分函数依赖，自然违反了第二范式。
但是经过和老师的交流，实际的应用中，就本系统而言，这样违反的例子并不影响系统的使用，我们的设计并不能满足第二范式以上。实际上只要任何存在A，B属性任意一个都能作为关系的码的，因而(A，B)也能作为关系的码，那么对于非主属性而言，自然会存在和码(A,B)之间的部分函数依赖，从而违反第二范式。
2、 用一张视图来展示学生的基本信息、所在学院、所住寝室、所在校区的情况。请写出创建视图的代码，以及查询该视图信息的SELECT命令和结果截图。
CREATE VIEW STU_VIEW AS
SELECT * FROM STU NATURAL JOIN
(SELECT * FROM MAJOR NATURAL JOIN COLLEGE)
WITH CHECK OPTION;
SELECT * FROM STU_VIEW;
6 总结及心得体会
在本次实验中，给定需求设定ER图，并且考虑范式约束，最终设计数据库表，运用SQL语句实现我们的设计。通过这次实验，我们了解实践了数据库模型的设计方法，掌握了ER图的绘制方法，掌握了关系模型中1:1，1:n，n:m关系的存储方法，收获良多。
相关文章
您阅读本篇文章共花了：
发表评论