实验三 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语句，有必要掌握使用的基本方法。