实验三 SJK数据库设计

实验题目

实验三 SJK数据库设计

实验时间

2023.3.22

实验地点

软件工程基础实验室

实验课时

2

实验目的

​掌握数据库设计基本方法及数据库设计工具。

实验要求

​ 掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，物理结构设计，数据库模式SQL语句生成。能够使用数据库设计工具进行数据库设计。

实验步骤

及内容

 设计一个教务信息系统的数据库。数据库包含院系信息，学生信息，教师信息，课程信息，学生选课信息，考试信息。其中，一个学生属于一个院系，可以选修多门课程。一个教师属于一个院系，可以任教多门课程，一门课程也可以有多个老师任教。一门课程有多个课程安排，可以有多场考试安排。

(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。主码：课程号，考试地点。

​ 根据语义，分析实体之间的联系，确定实体之间一对一，一对多和多对多联系。

SQL语句生成

​ 由PowerDesigner生成的SQL语句如下：

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                 small int                       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;

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                small int                       null,

   credit               small int                       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;

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                 small int                       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;

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)

);

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                 small int                       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;

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                 small int                       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;

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                small int                       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;

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;

实验结果及分析

经检验，实验基本达到预期效果。数据库的设计的基本步骤包括数据库概念结构设计、逻辑结构设计，物理结构设计。其中概念结构设计通常用E-R图表示。逻辑结构设计比较重要，需要从E-R图转换得到，并需要完成一些细节，进行调整优化。物理结构设计则需要完成存储路径的设计和存储结构的设计，需要根据实际情况进行设计。例如每个关系的存储结构，具体的数据类型和长度都要符合实际，同时尽量减小开销。使用类似PowerDesigner的工具可以辅助设计，方便的进行模型间的转化，并可以直接生成SQL语句，有必要掌握使用的基本方法。