实验五 数据库设计实验
1.实验目的
掌握数据库设计基本方法及数据库设计工具。
2.实验内容和要求
掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，物理结构设计，数据库模式SQL语句生成。能够使用数据库设计工具进行数据库设计。
3.实验重点和难点
实验重点：概念结构设计、逻辑结构设计。
实验难点：逻辑结构设计。逻辑结构设计虽然可以按照一定的规则从概念结构转换来，但是由于概念结构通常比较抽象，较少考虑更多细节，因此转换而成的逻辑结构还需进一步调整和优化。逻辑结构承接概念结构和物理结构，处于核心地位，因而是数据库设计的重点。
4.实验过程
设计一个教务信息系统的数据库。数据库包含院系信息，学生信息，教师信息，课程信息，学生选课信息，考试信息。其中，一个学生属于一个院系，可以选修多门课程。一个教师属于一个院系，可以任教多门课程，一门课程也可以有多个老师任教。一门课程有多个课程安排，可以有多场考试安排。
(1)数据库概念结构设计
识别出学生，教师，院系，课程，考试，课程安排六个实体。每个实体的属性、码如下：
- 学生Student：学号sno，姓名sname，性别ssex，年龄sage，班级class，院系编号dno。主码：学号sno。
- 教师teacher：编号tno，姓名tname，性别tsex，年龄tage，院系dno，职称title。主码：编号tno。
- 院系department：院系名depart_name，院系编号dno，院长编号dean_tno。主码：院系编号dno。
- 课程course：课程号cno，课程名course_name，学时数hours，学分credit，类型type，开设院系dno。主码：课程号。
- 课程安排course_arrangement：课程号cno，周次week，节次course_time，教室classroom。主码：课程号cno，周次week，节次course_time
- 考试安排exam_arrangement：课程号cno，考试时间exam_time，考试时长hours，考试地点classroom。主码：课程号，考试地点。
根据语义，分析实体之间的联系，确定实体之间一对一，一对多和多对多联系。
E-R图如下：
(2)数据库逻辑结构设计
根据上述E-R图使用PowerDesigner设计数据库逻辑结构如下：
共有8个关系，其中6个是实体，tc和sc分别是任教和选修这两个多对多的联系。关系的外键这里没有画出。
(3)数据库物理结构设计
自动转换得到数据库物理结构：
选择索引存取方法，数据库会自动为每个关系的主码建立索引。对于该系统，不需要建立其他索引。
(4)SQL语句生成
由PowerDesigner生成的SQL语句如下：
student表：
if exists(select 1 from sys.sysforeignkey where role='fk_sc_s') then
alter table sc
delete foreign key fk_sc_s
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_sd') then
alter table student
delete foreign key fk_sd
end if;
if exists(
select 1 from sys.systable
where table_name='student'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table student
end if;
/*==============================================================*/
/* Table: student */
/*==============================================================*/
create table student
(
sno char(12) not null,
sname char(25) null,
ssex char(2) null,
sage smallint null,
class char(8) null,
dno char(2) null,
constraint PK_STUDENT primary key clustered (sno)
);
alter table student
add constraint fk_sd foreign key (dno)
references department (dno)
on update restrict
on delete restrict;
course表：
if exists(select 1 from sys.sysforeignkey where role='fk_cd') then
alter table course
delete foreign key fk_cd
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_ca_c') then
alter table course_arrangement
delete foreign key fk_ca_c
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_exam_c') then
alter table exam_arrangement
delete foreign key fk_exam_c
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_sc_c') then
alter table sc
delete foreign key fk_sc_c
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_tc_c') then
alter table tc
delete foreign key fk_tc_c
end if;
if exists(
select 1 from sys.systable
where table_name='course'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table course
end if;
/*==============================================================*/
/* Table: course */
/*==============================================================*/
create table course
(
cno char(8) not null,
cname char(25) null,
hours smallint null,
credit smallint null,
type char(8) null,
dno char(2) null,
constraint PK_COURSE primary key clustered (cno)
);
alter table course
add constraint fk_cd foreign key ()
references department (dno)
on update restrict
on delete restrict;
teacher表：
if exists(select 1 from sys.sysforeignkey where role='fk_tc_t') then
alter table tc
delete foreign key fk_tc_t
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_td') then
alter table teacher
delete foreign key fk_td
end if;
if exists(
select 1 from sys.systable
where table_name='teacher'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table teacher
end if;
/*==============================================================*/
/* Table: teacher */
/*==============================================================*/
create table teacher
(
tno char(12) not null,
tname char(25) null,
tsex char(2) null,
tage smallint null,
dno char(2) null,
title char(10) null,
constraint PK_TEACHER primary key clustered (tno)
);
alter table teacher
add constraint fk_td foreign key (dno)
references department (dno)
on update restrict
on delete restrict;
department表:
if exists(select 1 from sys.sysforeignkey where role='fk_cd') then
alter table course
delete foreign key fk_cd
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_sd') then
alter table student
delete foreign key fk_sd
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_td') then
alter table teacher
delete foreign key fk_td
end if;
if exists(
select 1 from sys.systable
where table_name='department'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table department
end if;
/*==============================================================*/
/* Table: department */
/*==============================================================*/
create table department
(
dno char(2) not null,
dame char(25) null,
tno char(12) null,
constraint PK_DEPARTMENT primary key clustered (dno)
);
course_arrangement:
if exists(select 1 from sys.sysforeignkey where role='fk_ca_c') then
alter table course_arrangement
delete foreign key fk_ca_c
end if;
if exists(
select 1 from sys.systable
where table_name='course_arrangement'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table course_arrangement
end if;
/*==============================================================*/
/* Table: course_arrangement */
/*==============================================================*/
create table course_arrangement
(
cno char(8) not null,
week smallint not null,
course_time char(12) not null,
classroom char(10) null,
constraint PK_COURSE_ARRANGEMENT primary key clustered (cno, week, course_time)
);
alter table course_arrangement
add constraint fk_ca_c foreign key (cno)
references course (cno)
on update restrict
on delete restrict;
exam_arrangement
if exists(select 1 from sys.sysforeignkey where role='fk_exam_c') then
alter table exam_arrangement
delete foreign key fk_exam_c
end if;
if exists(
select 1 from sys.systable
where table_name='exam_arrangement'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table exam_arrangement
end if;
/*==============================================================*/
/* Table: exam_arrangement */
/*==============================================================*/
create table exam_arrangement
(
cno char(8) not null,
exam_time char(10) null,
classroom char(10) not null,
hour smallint null,
constraint PK_EXAM_ARRANGEMENT primary key clustered (cno, classroom)
);
alter table exam_arrangement
add constraint fk_exam_c foreign key (cno)
references course (cno)
on update restrict
on delete restrict;
sc:
if exists(select 1 from sys.sysforeignkey where role='fk_sc_c') then
alter table sc
delete foreign key fk_sc_c
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_sc_s') then
alter table sc
delete foreign key fk_sc_s
end if;
if exists(
select 1 from sys.systable
where table_name='sc'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table sc
end if;
/*==============================================================*/
/* Table: sc */
/*==============================================================*/
create table sc
(
sno char(12) not null,
cno char(8) not null,
grade smallint null,
constraint PK_SC primary key clustered (sno, cno)
);
alter table sc
add constraint fk_sc_c foreign key (cno)
references course (cno)
on update restrict
on delete restrict;
alter table sc
add constraint fk_sc_s foreign key (sno)
references student (sno)
on update restrict
on delete restrict;
tc:
if exists(select 1 from sys.sysforeignkey where role='fk_tc_c') then
alter table tc
delete foreign key fk_tc_c
end if;
if exists(select 1 from sys.sysforeignkey where role='fk_tc_t') then
alter table tc
delete foreign key fk_tc_t
end if;
if exists(
select 1 from sys.systable
where table_name='tc'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table tc
end if;
/*==============================================================*/
/* Table: tc */
/*==============================================================*/
create table tc
(
tno char(12) not null,
cno char(8) not null,
term char(8) not null,
class char(8) null,
constraint PK_TC primary key clustered (tno, cno, term)
);
alter table tc
add constraint fk_tc_c foreign key (cno)
references course (cno)
on update restrict
on delete restrict;
alter table tc
add constraint fk_tc_t foreign key (tno)
references teacher (tno)
on update restrict
on delete restrict;
5.实验总结
数据库的设计的基本步骤包括数据库概念结构设计、逻辑结构设计，物理结构设计。其中概念结构设计通常用E-R图表示。逻辑结构设计比较重要，需要从E-R图转换得到，并需要完成一些细节，进行调整优化。物理结构设计则需要完成存储路径的设计和存储结构的设计，需要根据实际情况进行设计。例如每个关系的存储结构，具体的数据类型和长度都要符合实际，同时尽量减小开销。使用类似PowerDesigner的工具可以辅助设计，方便的进行模型间的转化，并可以直接生成SQL语句，有必要掌握使用的基本方法。
5.实验总结
数据库的设计的基本步骤包括数据库概念结构设计、逻辑结构设计，物理结构设计。其中概念结构设计通常用E-R图表示。逻辑结构设计比较重要，需要从E-R图转换得到，并需要完成一些细节，进行调整优化。物理结构设计则需要完成存储路径的设计和存储结构的设计，需要根据实际情况进行设计。例如每个关系的存储结构，具体的数据类型和长度都要符合实际，同时尽量减小开销。使用类似PowerDesigner的工具可以辅助设计，方便的进行模型间的转化，并可以直接生成SQL语句，有必要掌握使用的基本方法。
2007
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
