《数据库系统概论》课程之实验五 数据库设计实验

《数据库系统概论》课程之实验五 数据库设计实验

1. 实验目的

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

2. 实验平台和工具

利用Power Designer数据库设计工具设计该数据库。

3. 实验内容和要求

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

设计一个采购、销售和客户管理应用数据库。其中，一个供应商可以供应多种零件，一种零件也可以有多个供应商。一个客户订单可以订购多种供应商供应的零件。客户和供应商都分属于不同的国家，而国家按世界五大洲八大洋划分地区。请利用Power Designer数据库设计工具设计该数据库。

（1）数据库概念结构设计

识别出零件Part、供应商Supplier、客户Customer、订单Order、订单项Lineitem、国家Nation、地区Region等7个实体。每个实体的属性、码如下。

  • 零件Part：零件编号partkey、零件名称name、零件制造商mfgr、品牌brand、类型type、大小size、零售价格retailprice、包装container、备注comment。主码：零件编号 partkey。

  • 供应商Supplier：供应商编号suppkey、供应商名称name、地址address、国籍nation、电话phone、备注comment等。主码：供应商编号suppkey。

  • 客户Customer：客户编号custkey、客户名称name、地址address、电话phone、国籍nation、备注comment。主码：客户编号custkey。

  • 订单Order：订单编号orderkey、订单状态status、订单总价totalprice、订单日期orderdate、订单优先级orderpriority、记账员clerk、运送优先级shippriority、备注comment。主码：订单编号orderkey。

  • 订单项Lineitem：订单项编号linenumber、所订零件号partkey、所订零件供应商号suppkey、零件数量quantity、零件总价extendedprice、折扣discount、税率tax、退货标记returnflag等。主码：订单项编号linenumber。

  • 国家Nation：国家编号nationkey、国家名称name、所属地区region、备注comment。主码：国家编号nationkey。

  • 地区Region：地区编号regionkey、国家名称name、备注comment。主码：地区编号regionkey。

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

E-R图：

![image-20220507135026495](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507135026495.png)

使用PowerDesigner设计概念模型

  • 新建概念模型

    image-20220507104734401

  • “工具”-“Model Options”修改“Notation”

    image-20220507105320883

  • 添加实体

    image-20220507105503374

  • 双击实体，填充实体信息和字段。以“零件”为例：

    image-20220507110628771 image-20220507110649799

    ![image-20220507111911066](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507111911066.png)

    • 字段勾选“P”后，<pi>代表主键

  • 完成所有实体的信息填充后，在“Palette”选取“Relationship”，为实体间创建联系。编辑“Relationship”的连线，以“零件”——“供应商”（m:n联系）为例：

    image-20220507124749370 image-20220507124822841
    • 供应商to零件：0,n，表示一个供应商对应0~n个零件
    • 零件to供应商：1,n，表示一个零件对应1~n个供应商

  • 其他联系如下：

    零件:供应商:订单项 —— 1:1:n

    订单:订单项 —— 1:n

    客户:订单 —— 1:n

    国家:客户 —— 1:n

    国家:供应商 —— 1:n

    地区:国家 —— 1:n

注意：受PowerDesigner的限制，无法在概念模型添加零件:供应商:订单项的1:1:n关系。这需要在逻辑数据模型中零件:供应商的m:n关系转化完成后，进行添加关系。

  • 设计的概念模型如下：

    ![image-20220507153822372](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507153822372.png)

    • 注意国籍、所属地区应该为int型，和国家编号、地区编号相对应。

（2）数据库逻辑结构设计

按照数据库设计原理中概念结构转化成逻辑结构的规则，每个实体转换成一个关系，多对多的联系也转换成一个关系。因此，根据上述画出的E-R图设计数据库逻辑结构。

使用PowerDesigner生成逻辑模型

  • 在概念模型的基础上，点击“工具”——“Generate Logical Data Model”生成逻辑数据模型：

    image-20220507135552001

  • 配置选项后，点击“确定”

    image-20220507140954144

  • 效果如下，可以看见自动生成了外键<fi>：

![image-20220507153954457](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507153954457.png)

  • 供应商、客户、国家都产生了额外的外键，我们需要把新生成的外键删除，然后进行更正：image-20220507151638928

    image-20220507151455232 image-20220507151526656

  • 可以看见零件:供应商的m:n关系单独转化成了一个关系模式“供应”。

  • 故添加概念模型中没有建立的关系（订单项和供应），最终效果如下：

逻辑数据模型

![image-20220507154132086](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507154132086.png)

（3）数据库物理结构设计

数据库物理结构首先根据逻辑结构自动转换生成，然后根据应用需求设计数据库的索引结构、存储结构。

使用PowerDesigner生成物理结构

  • 在逻辑模型的基础上，点击“工具”——“Generate Physical Data Model”生成物理数据模型：

    image-20220507140234223

  • 这里选取的数据库管理系统为Microsoft SQL Server 2012 （发现这里不支持MySQL8.0,而MySQL5.0的物理模型编辑不了索引的存储方法，虽然Microsoft SQL Server 2012也没好到哪去）

    image-20220507142327596

  • 效果如下

    ![image-20220507154221948](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507154221948.png)

设计数据库的索引结构

  • 双击一张表，选择“Indexes”，新建一个索引，点击左上角的“Index Properties”

    image-20220507141633500

  • “Index Properties”中选择“Column”，添加属性…在Sort中选择存储方法，发现只支持2个方法（升序和降序）

    （PowerDesigner不太行!）

    ![image-20220507143758512](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507143758512.png)

其他存储方法如下

B+树索引存取方法

  • 如果一个（或一组）属性经常在查询条件中出现，则考虑在这个（或这组）属性上建立索引（或组合索引）

    • 如本实验中，订单项的“退货标记”，订单的“客户编号”、“订单状态”、“订单日期”。

  • 如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑在这个属性上建立索引

    • 如本实验中，订单的“订单优先级”、“运送优先级”。

  • 如果一个（或一组）属性经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引

    • 如本实验中，作为其他关系模式外键的属性：零件的“零件编号”、供应商的“供应商编号”、客户的“客户编号”、国家的“国家编号”、地区的“地区编号”。
HASH存取方法
  • 如果一个关系的属性主要出现在等值连接条件中或主要出现在等值比较选择条件中，而且满足下列两个条件之一
    • 该关系的大小可预知，而且不变；
    • 该关系的大小动态改变，但所选用的数据库管理系统提供了动态Hash存取方法

这与上述B+树索引存取方法中第3点连接操作的连接条件基本一致。

聚簇存取方法

为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块中称为聚簇

本实验中，零件的“零件制造商”、“品牌”、“类型”，订单的“订单日期”、“记账员”，客户的“国籍”、供应商的“国籍”、国家的“所在地区”等可以使用聚簇存取。

（4）数据库模式SQL语句生成

这里生成MySQL5.0的SQL语句。

  • 点击“数据库”——“生成数据库”

    image-20220507152224409

  • 选择存储路径后，点击确定生成SQL语句的文件:

    image-20220507154425858

  • 内容如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    /*==============================================================*/
    /* DBMS name:      MySQL 5.0                                    */
    /* Created on:     2022/5/7 15:44:40                            */
    /*==============================================================*/


    drop table if exists Customer;

    drop table if exists Lineitem;

    drop table if exists Nation;

    drop table if exists "Order";

    drop table if exists Part;

    drop table if exists Region;

    drop table if exists Supplier;

    drop table if exists 供应;

    /*==============================================================*/
    /* Table: Customer                                              */
    /*==============================================================*/
    create table Customer
    (
       custkey              int not null,
       name                 varchar(128),
       address              varchar(128),
       phone                varchar(128),
       nation               int,
       comment              varchar(128),
       primary key (custkey)
    );

    /*==============================================================*/
    /* Table: Lineitem                                              */
    /*==============================================================*/
    create table Lineitem
    (
       linenumber           int not null,
       orderkey             int,
       partkey2             int,
       suppkey2             int,
       quantity             int,
       extendedprice        float,
       discount             float,
       tax                  float,
       returnflag           bool,
       primary key (linenumber)
    );

    /*==============================================================*/
    /* Table: Nation                                                */
    /*==============================================================*/
    create table Nation
    (
       nationkey            int not null,
       name                 varchar(128),
       region               int,
       comment              varchar(128),
       primary key (nationkey)
    );

    /*==============================================================*/
    /* Table: "Order"                                               */
    /*==============================================================*/
    create table "Order"
    (
       orderkey             int not null,
       custkey              int,
       status               bool,
       totalprice           float,
       orderdate            date,
       orderpriority        int,
       clerk                varchar(128),
       shippriority         int,
       comment              varchar(128),
       primary key (orderkey)
    );

    /*==============================================================*/
    /* Table: Part                                                  */
    /*==============================================================*/
    create table Part
    (
       partkey              int not null,
       name                 varchar(128),
       mfgr                 varchar(128),
       brand                varchar(128),
       type                 varchar(128),
       size                 float,
       retailprice          float,
       container            varchar(128),
       comment              varchar(128),
       primary key (partkey)
    );

    /*==============================================================*/
    /* Table: Region                                                */
    /*==============================================================*/
    create table Region
    (
       regionkey            int not null,
       name                 varchar(128),
       comment              varchar(128),
       primary key (regionkey)
    );

    /*==============================================================*/
    /* Table: Supplier                                              */
    /*==============================================================*/
    create table Supplier
    (
       suppkey              int not null,
       name                 varchar(128),
       address              varchar(128),
       nation               int,
       phone                varchar(128),
       comment              varchar(128),
       primary key (suppkey)
    );

    /*==============================================================*/
    /* Table: 供应                                                    */
    /*==============================================================*/
    create table 供应
    (
       partkey              int not null,
       suppkey              int not null,
       primary key (partkey, suppkey)
    );

    alter table Customer add constraint FK_国籍 foreign key (nation)
          references Nation (nationkey) on delete restrict on update restrict;

    alter table Lineitem add constraint FK_使用 foreign key (partkey2, suppkey2)
          references 供应 (partkey, suppkey) on delete restrict on update restrict;

    alter table Lineitem add constraint FK_组成 foreign key (orderkey)
          references "Order" (orderkey) on delete restrict on update restrict;

    alter table Nation add constraint FK_所在地区 foreign key (region)
          references Region (regionkey) on delete restrict on update restrict;

    alter table "Order" add constraint FK_签订 foreign key (custkey)
          references Customer (custkey) on delete restrict on update restrict;

    alter table Supplier add constraint FK_所在国家 foreign key (nation)
          references Nation (nationkey) on delete restrict on update restrict;

    alter table 供应 add constraint FK_供应 foreign key (partkey)
          references Part (partkey) on delete restrict on update restrict;

    alter table 供应 add constraint FK_供应2 foreign key (suppkey)
          references Supplier (suppkey) on delete restrict on update restrict;


  • 由于订单表的名称“Order”与Mysql的语句重复了，故把“Order”命名为“Order2”，在Navicat中运行：

    ![image-20220507154608819](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507154608819.png)

    ![image-20220507154625237](《数据库系统概论》课程之实验五 数据库设计实验/image-20220507154625237.png)

实验心得

通过此次实验，我掌握了数据库设计的基本过程，对数据库的概念结构、逻辑结构、物理结构都有了深刻的把握。同时，本次实验过程中，需要注意几个细节：

  • 画实体-关系图，设计概念模型时，就需要根据实际情况把握实体间的联系。此次我对实体间1:n、m:n甚至1:1:n的联系都有了更深的理解，其中m:n的联系会创建出一个新的关系模式，而1:1:n的联系在PowerDesigner工具中需要在下一步“逻辑结构”中实现。
  • 设计逻辑结构时，若是从概念模型转化得到逻辑模型，则需要把握好参照表和被参照表间通过外键建立的联系，若自动生成了多余的外键，则需要手动进行调整。此外，务必注意参照属性和被参照属性间的数据类型需要设置成一致的，否则生成数据库时会报错。
  • 对于物理结构，同样要根据实际需求确定存取方法，如B+树、Hash、聚簇，在课程中都有提及。遗憾的是PowerDesigner16.5并不支持设计较复杂的物理结构。
  • 将逻辑模型转化为物理模型时，要注意选择的数据库管理系统应该和需求相一致。
数据库系统课程实验
数据库系统实验

本博客所有文章除特别声明外，均为博客作者本人编写整理，转载请联系作者！