【HNU数据库实验】实验5：数据库设计
实验5：数据库设计实验环境：SQL Server（1）实验目的掌握数据库设计基本方法及数据库设计工具。（2）实验内容和要求掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，物理结构设计，数据库模式SQL语句生成。能够使用数据库设计工具进行数据库设计。（3）实验重点和难点实验重点：概念结构设计、逻辑结构设计。实验难点：逻辑结构设计。逻辑结构设计虽然可以按照一定的规则从概念结构转换而来，但
实验5：数据库设计
实验环境：SQL Server
（1）实验目的
掌握数据库设计基本方法及数据库设计工具。
（2）实验内容和要求
掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，物理结构设计，数据库模式SQL语句生成。能够使用数据库设计工具进行数据库设计。
（3）实验重点和难点
实验重点：概念结构设计、逻辑结构设计。
实验难点：逻辑结构设计。逻辑结构设计虽然可以按照一定的规则从概念结构转换而来，但是由于概念结构通常比较抽象，较少考虑更多细节，因此转换而成的逻辑结构还需要进一步调整和优化。逻辑结构承接概念结构和物理结构，处于核心地位，因而是数据库设计的重点，也是难点。
（4）实验过程
1. 需求分析
教务管理系统是面对学生、教师和管理员，并且为其提供服务的管理系统。下面依次说明这三者对系统的需求。
管理员：这是一个可以担任学生也可以担任教师职务的角色，并且还拥有自己独特的功能。因此，管理员不仅拥有学生的各种操作功能，也拥有教师的各种操作功能，还拥有修改个人密码以及维护整个系统的功能，其中维护整个系统包括发布公告和启动和关闭教务管理系统。
学生：当新生入学时，学生就被授予了一系列的个人信息，包括：学号、所在系所在班级，还有他自己设定的密码，再加上他本来就有的一些信息，包括:姓名、性别、出生日期和身份证号和电话号码，那么这些就构成了学生的个人信息。在教务管理系统中，学生还有选课这一功能。到了学期末，学生要对任课老师进行评教，只有当评教了之后才可以看到老师所录入的成绩。学期末的时候，学生要进行科目的考核，那么此时学生就需要通过系统获取自己的相关成绩的信息，因此教务管理系统还要提供给学生查询成绩的功能。
教师：教师为了知道自己所教授的课程所在的地方以及开课的时间，需要查询课表来获得这些信息。当学期末的时候，教师需要查看学生对自己的评教信息，并且根据试卷成绩和平时成绩来为每个学生打出成绩，为了让学生看到，教师需要将最终成绩录入到教务系统中，从而学生在查询成绩时，可以看到相关信息。另外，在某些时候，教师还需要修改个人密码来保证个人信息的安全。根据上述的用户需求，按照结构化的系统设计方法，教务管理系统分为三个子系统：学生管理系统、教师管理系统和管理员维护系统。
2. 概念结构设计
（1）学生管理子系统
在学生管理系统中学生能够查看自己的信息，进行选课，对教师进行评教，查看自己的课表和所修课程的成绩，涉及到的表有： 学生信息表 ， 学生课表 ， 学生所在院系表 ， 课程表 ， 学生对老师的评价分数 表 。
各实体之间的关系：
学生和课程：多对多
学生和院系：一对多
学生和课表：一对一
学生和老师：多对多
E-R 图如下：
（2）老师管理子系统
在老师管理子系统中，老师要能够查询自己的课表，录入学生的有关课程成绩，查看学生对自己的评教结果。涉及到的表有 老师信息表 ， 院系表 ， 课程表 ， 老师课表 和 学生成绩表 。
各实体之间的关系：
老师和课程：多对多
老师和院系：一对多
老师和学生成绩：多对多
老师和课表：一对一
老师和评教分数：多对多
E-R 图如下
（3）管理员维护系统
在该系统中管理员能够管理学生和老师的有关信息，并且安排老师和学生的课表。涉及到的表有 管理员信息表 ， 学生信息表 ， 老师信息表 ， 学生课表 ， 老师课表 。
各实体之间的关系为：
管理员和老师：一对多
管理员和学生：一对多
E-R 图如下
综合以上三个子系统，则最终的教务处系统的E-R图为：
① 在学生管理系统和教师管理系统中都存在课表这个实体，但是他们的含义是不相同的，所以在总 E-R中要将他们分别命名为学生课表和教师课表两个实体。
② 学生管理系统和教书管理系统中的成绩和评教分数可以分别用一个实体表示。
③ 学生管理子系统和教师管理子系统中都含有课程这个实体，他们的含义是相同的可以合并为一个实体。
根据上述的需求分析和E-R图可以确定出各个实体的属性及其关系模式
学生student：学号、姓名、性别、出生日期、所在系、密码、身份证号、电话号码
教师teacher：工号、姓名、性别、出生日期、所在系、密码
管理员administrator：密码
课程course：课程号、课程名、学分
学生课表sc：学号、课程号、上课时间、上课地点、教师号
教师课表tc：工号、课程号、上课时间、上课地点
成绩表：学号、课程号、学生姓名、课程名、学生成绩、课程学分、重修信息
院系：院系号、院系名
评教信息：课程号、教师号、分数
以下为使用powerdesiger设计的概念模型
值得注意以下几点：【设计中遇到的问题】
唯一性约束设置【E-R图中的唯一性】
打开这个实体的属性界面 ——> Identifiers-新建一个identifier ——> 双击左侧的小箭头 ——> 选择attributes选项卡 ——> 点击圈三指向的那个图标 ——> 选择你要设置为唯一的属性
遇到了一个比较严重的问题，一直没有百度到概念模型怎么处理外码问题，所以没有解决在概念模型中的外码问题，导致概念模型向物理模型转换的时候出现大量报错信息，所以我又重新做了物理模型。
唯一性问题：【物理模型中的唯一性】
key -> 属性 -> 添加相应字段即可
外码问题：在约束条件中定义，以学生和学院为例，当删除或更新时被参照表时选着置null
结合实际情况考虑，在我的设计中，学生和学院教师和学院之间的更新删除采用置空值的方法【如该学院撤销，学生和教师等待新学院分配】，选课信息和课表之间使用拒接删除的方法【若有学生选课，该课程就不能删除】，其余情况级联删除。
最终的物理模型如下：
转为概念模型：连线上面的代码为自己取的名字
看到用物理模型转换出来的概念模型后终于明白了自己在概念模型时出现的问题，外码是不包括在实体中的，应该是体现在对应关系上！！！
3. 逻辑结构设计
逻辑结构其实就是根据E-R图转为物理模型，但因为自己一开始对概念模型的外码理解不当，所以直接先做了逻辑结构的设计，此处不再重新截图
4. 物理结构设计
即根据逻辑结构自动生成sql语言
成功转换，但在运行时出现报错信息，那既然如此就设置为级联删除吧，课程信息删除选课信息也随之删除也是能解释的。
完整代码如下：
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2012 */
/* Created on: 2022/5/9 21:58:36 */
/*==============================================================*/
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('comment') and o.name = 'FK_COMMENT_COMCOU_COURSE')
alter table comment
drop constraint FK_COMMENT_COMCOU_COURSE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('comment') and o.name = 'FK_COMMENT_COMTEA_TEACHER')
alter table comment
drop constraint FK_COMMENT_COMTEA_TEACHER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('ggrade') and o.name = 'FK_GGRADE_GGCOU_COURSE')
alter table ggrade
drop constraint FK_GGRADE_GGCOU_COURSE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('ggrade') and o.name = 'FK_GGRADE_GGSTU_STUDENT')
alter table ggrade
drop constraint FK_GGRADE_GGSTU_STUDENT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('sc') and o.name = 'FK_SC_REFERENCE_STUDENT')
alter table sc
drop constraint FK_SC_REFERENCE_STUDENT
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('sc') and o.name = 'FK_SC_REFERENCE_COURSE')
alter table sc
drop constraint FK_SC_REFERENCE_COURSE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('student') and o.name = 'FK_STUDENT_actu_ACADEMY')
alter table student
drop constraint FK_STUDENT_actu_ACADEMY
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tc') and o.name = 'FK_TC_TCCOU_COURSE')
alter table tc
drop constraint FK_TC_TCCOU_COURSE
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('tc') and o.name = 'FK_TC_TCTEA_TEACHER')
alter table tc
drop constraint FK_TC_TCTEA_TEACHER
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('teacher') and o.name = 'FK_TEACHER_TEAC_ACADEMY')
alter table teacher
drop constraint FK_TEACHER_TEAC_ACADEMY
go
if exists (select 1
from sysobjects
where id = object_id('academy')
and type = 'U')
drop table academy
go
if exists (select 1
from sysobjects
where id = object_id('administrator')
and type = 'U')
drop table administrator
go
if exists (select 1
from sysobjects
where id = object_id('comment')
and type = 'U')
drop table comment
go
if exists (select 1
from sysobjects
where id = object_id('course')
and type = 'U')
drop table course
go
if exists (select 1
from sysobjects
where id = object_id('ggrade')
and type = 'U')
drop table ggrade
go
if exists (select 1
from sysobjects
where id = object_id('sc')
and type = 'U')
drop table sc
go
if exists (select 1
from sysobjects
where id = object_id('student')
and type = 'U')
drop table student
go
if exists (select 1
from sysobjects
where id = object_id('tc')
and type = 'U')
drop table tc
go
if exists (select 1
from sysobjects
where id = object_id('teacher')
and type = 'U')
drop table teacher
go
/*==============================================================*/
/* Table: academy */
/*==============================================================*/
create table academy (
Ano varchar(25) not null,
Aname varchar(25) null,
constraint PK_ACADEMY primary key (Ano)
)
go
/*==============================================================*/
/* Table: administrator */
/*==============================================================*/
create table administrator (
Code varchar(15) null
)
go
/*==============================================================*/
/* Table: comment */
/*==============================================================*/
create table comment (
Cno varchar(20) not null,
Tno varchar(20) not null,
Tgrade char(5) null,
constraint PK_COMMENT primary key (Cno, Tno)
)
go
/*==============================================================*/
/* Table: course */
/*==============================================================*/
create table course (
Cno varchar(20) not null,
Cname varchar(20) null,
Credit smallint null,
constraint PK_COURSE primary key (Cno)
)
go
/*==============================================================*/
/* Table: ggrade */
/*==============================================================*/
create table ggrade (
Sno varchar(20) not null,
Cno varchar(20) not null,
Grade char(5) null,
Gcredit smallint null,
is_restart char(5) null,
constraint PK_GGRADE primary key (Sno, Cno)
)
go
/*==============================================================*/
/* Table: sc */
/*==============================================================*/
create table sc (
Sno varchar(20) not null,
Cno varchar(20) not null,
STime varchar(30) null,
Place varchar(30) null,
Tno varchar(20) null,
constraint PK_SC primary key (Sno, Cno)
)
go
/*==============================================================*/
/* Table: student */
/*==============================================================*/
create table student (
Sno varchar(20) not null,
Ano varchar(25) null,
Sname varchar(20) null,
Ssex char(5) null,
Sbirth date null,
Scode varchar(20) null,
Sid varchar(30) null,
Stelnum varchar(20) null,
constraint PK_STUDENT primary key (Sno),
constraint AK_SID_STUDENT unique (Sid)
)
go
/*==============================================================*/
/* Table: tc */
/*==============================================================*/
create table tc (
Tno varchar(20) not null,
Cno varchar(20) not null,
Ttime varchar(20) null,
TPlace varchar(30) null,
constraint PK_TC primary key (Tno, Cno)
)
go
/*==============================================================*/
/* Table: teacher */
/*==============================================================*/
create table teacher (
Tno varchar(20) not null,
Tname varchar(20) null,
Tsex char(5) null,
Tbirth date null,
Ano varchar(25) null,
Tcode varchar(20) null,
constraint PK_TEACHER primary key (Tno)
)
go
alter table comment
add constraint FK_COMMENT_COMCOU_COURSE foreign key (Cno)
references course (Cno)
on update cascade on delete cascade
go
alter table comment
add constraint FK_COMMENT_COMTEA_TEACHER foreign key (Tno)
references teacher (Tno)
on update cascade on delete cascade
go
alter table ggrade
add constraint FK_GGRADE_GGCOU_COURSE foreign key (Cno)
references course (Cno)
on update cascade on delete cascade
go
alter table ggrade
add constraint FK_GGRADE_GGSTU_STUDENT foreign key (Sno)
references student (Sno)
on update cascade on delete cascade
go
alter table sc
add constraint FK_SC_REFERENCE_STUDENT foreign key (Sno)
references student (Sno)
on update cascade on delete cascade
go
alter table sc
add constraint FK_SC_REFERENCE_COURSE foreign key (Cno)
references course (Cno)
on update cascade on delete cascade
go
alter table student
add constraint FK_STUDENT_actu_ACADEMY foreign key (Ano)
references academy (Ano)
on update set null on delete set null
go
alter table tc
add constraint FK_TC_TCCOU_COURSE foreign key (Cno)
references course (Cno)
on update cascade on delete cascade
go
alter table tc
add constraint FK_TC_TCTEA_TEACHER foreign key (Tno)
references teacher (Tno)
on update cascade on delete cascade
go
alter table teacher
add constraint FK_TEACHER_TEAC_ACADEMY foreign key (Ano)
references academy (Ano)
on update set null on delete set null
go
完整建立9个关系！
实验总结
本次实验也不能说难，但因为我个人原因确实是这一章节的内容掌握的不好，也因为第一次接触powerDesigner这个软件，各方面操作也不够熟练，所以首先建立概念模型E-R图的时候就出现了问题。不过也正因为这个问题让我更加深刻的体会到了外码的概念，外码实际上就是两个表之间的联系，在做概念图的时候不需要展示在实体属性中而应该体现在两个实体的关系上。通过一对多、多对多等关系软件会自动分析给你转为逻辑模型进而转为SQL语言。总而言之E-R图部分还需加强，powerDesigner软件也确实很强大！
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
3
0- 0
已为社区贡献1条内容
所有评论(0)