1. E-R 建模及从E-R图导出关系

主题：

某医院病房管理系统中有四个实体，如下：

① 部门（Department）：Dno（部门编号）、Dname（部门名称）、Location（位置）、Phone（电话）

② 病房（Ward）：Wno（病房编号）、Location（位置）

③ 医生（员工）（Doctor(Employee)）：Eno（员工编号）、Ename（员工姓名）、Title（职称）、Gender（性别）、Birthday（生日）

④ 患者（Patient）：Pno（患者编号）、Pname（患者姓名）、Gender（性别）、Birthday（生日）

在上述医院病房管理系统中，需考虑以下业务规则：

① 一个部门可以有多个病房和多个医生，但一个医生必须始终属于一个部门，且一个病房在某个特定时间必须属于一个部门。

② 一个医生可以负责多个患者的诊断和治疗，但一个患者只有一个主治医生。

③ 一个病房可以有多个患者，但一个患者在某个特定时间只能住在一个病房。

要求：

（1）请绘制上述医院病房管理系统的E-R图。

{
  "diagramId": "2100ba3b-6e64-4553-b28c-8d929396fb37",
  "database": "mysql",
  "name": "数据库作业1",
  "gistId": "",
  "lastModified": "2026-05-24T05:18:32.874Z",
  "tables": [
    {
      "id": "qkvNu2BcocriOc-xosAJh",
      "name": "科室",
      "x": -244.26657104492188,
      "y": 96.26647949218727,
      "locked": false,
      "fields": [
        {
          "name": "Dno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": true,
          "unique": false,
          "unsigned": true,
          "notNull": true,
          "increment": true,
          "comment": "FK\n",
          "id": "QcetQjuo88BiYGe6GxrVr"
        },
        {
          "id": "PKqD3WS-0WjyXnDgjnvfn",
          "name": "Dname",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "MQBzh0mVg6CQosNXkTeWH",
          "name": "Location",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "4HeAZjxz1aKjOWA0oN6vv",
          "name": "Phone",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        }
      ],
      "comment": "",
      "indices": [
        {
          "id": 0,
          "name": "table_qkvNu2BcocriOc-xosAJh_index_0",
          "unique": false,
          "fields": [
            "Dno"
          ]
        }
      ],
      "color": "#175e7a",
      "collapsed": false
    },
    {
      "id": "cNwUNC3W6d9sHeGvcyGQF",
      "name": "病房",
      "x": 218.13348388671878,
      "y": -264.8000183105469,
      "locked": false,
      "fields": [
        {
          "name": "Wno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": true,
          "unique": false,
          "unsigned": true,
          "notNull": true,
          "increment": true,
          "comment": "",
          "id": "7Bc_TOcihjl4sS2kFbY2M"
        },
        {
          "id": "vQAyx_KomrkMv4RY1zn7G",
          "name": "Location",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "Osx7LpkK2KXHhvKtJZqMj",
          "name": "Dno",
          "type": "INTEGER",
          "default": "",
          "check": "FK",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "外键",
          "size": "",
          "values": []
        }
      ],
      "comment": "",
      "indices": [],
      "color": "#175e7a",
      "collapsed": false
    },
    {
      "id": "sH3UmADoYGKErzyweIjHM",
      "name": "医生",
      "x": -645.8667907714845,
      "y": -310.3999786376953,
      "locked": false,
      "fields": [
        {
          "name": "Eno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": true,
          "unique": false,
          "unsigned": true,
          "notNull": true,
          "increment": true,
          "comment": "",
          "id": "WX7Ym04TbpexTRo2jRp4l"
        },
        {
          "id": "pWM64XzjzbqYiAmDS7Nan",
          "name": "Ename",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "Ef9ngG4HxlbqZlEaeWRaG",
          "name": "Tittle",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "ukBLhKRBb5Ex7plsSH7V5",
          "name": "Gender",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "CUG5U8JKJXzqvgsw0zu28",
          "name": "Birthday",
          "type": "CHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 1
        },
        {
          "id": "9_z5rmP8uOIVeBdLePW0f",
          "name": "Dno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": "",
          "values": []
        }
      ],
      "comment": "",
      "indices": [],
      "color": "#175e7a",
      "collapsed": false
    },
    {
      "id": "_7uUiArDZOJRRymbsUwf4",
      "name": "病人",
      "x": -157.06677246093744,
      "y": -565.0667114257812,
      "locked": false,
      "fields": [
        {
          "name": "Pno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": true,
          "unique": false,
          "unsigned": true,
          "notNull": true,
          "increment": true,
          "comment": "",
          "id": "wcoRIYzcbHDyvnsMprfcl"
        },
        {
          "id": "9IR7l85z3RXX5qzhajdV5",
          "name": "Pname",
          "type": "VARCHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 255
        },
        {
          "id": "dVqO2QcHDZPi__zRN7a_R",
          "name": "Gender",
          "type": "CHAR",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": 1
        },
        {
          "id": "ox3Tb86VHuUKDcYB7GNvm",
          "name": "Birthday",
          "type": "DATE",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": "",
          "values": []
        },
        {
          "id": "FYjbL3m2oC1PWTcaqEDFa",
          "name": "Eno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": "",
          "values": []
        },
        {
          "id": "QlsUM9QCMZ1jkzTYiA9nD",
          "name": "Wno",
          "type": "INTEGER",
          "default": "",
          "check": "",
          "primary": false,
          "unique": false,
          "notNull": true,
          "increment": false,
          "comment": "",
          "size": "",
          "values": []
        }
      ],
      "comment": "",
      "indices": [],
      "color": "#175e7a",
      "collapsed": false
    }
  ],
  "notes": [],
  "pan": {
    "x": 0,
    "y": -66.66666666666666
  },
  "zoom": 1,
  "loadedFromGistId": "",
  "id": 1,
  "relationships": [
    {
      "startTableId": "qkvNu2BcocriOc-xosAJh",
      "startFieldId": "QcetQjuo88BiYGe6GxrVr",
      "endTableId": "cNwUNC3W6d9sHeGvcyGQF",
      "endFieldId": "Osx7LpkK2KXHhvKtJZqMj",
      "cardinality": "one_to_many",
      "updateConstraint": "No action",
      "deleteConstraint": "No action",
      "name": "fk_科室_Dno_病房",
      "id": "UAqR6exUEHWmWuRzD7Skr"
    },
    {
      "startTableId": "qkvNu2BcocriOc-xosAJh",
      "startFieldId": "QcetQjuo88BiYGe6GxrVr",
      "endTableId": "sH3UmADoYGKErzyweIjHM",
      "endFieldId": "9_z5rmP8uOIVeBdLePW0f",
      "cardinality": "one_to_many",
      "updateConstraint": "No action",
      "deleteConstraint": "No action",
      "name": "fk_科室_Dno_医生",
      "id": "f3F5tnS6ozgbwkMNHyasm"
    },
    {
      "startTableId": "sH3UmADoYGKErzyweIjHM",
      "startFieldId": "WX7Ym04TbpexTRo2jRp4l",
      "endTableId": "_7uUiArDZOJRRymbsUwf4",
      "endFieldId": "FYjbL3m2oC1PWTcaqEDFa",
      "cardinality": "one_to_many",
      "updateConstraint": "No action",
      "deleteConstraint": "No action",
      "name": "fk_医生_Eno_病人",
      "id": "QzuEIZ4TEFHVBQ6TASOCm"
    },
    {
      "startTableId": "cNwUNC3W6d9sHeGvcyGQF",
      "startFieldId": "7Bc_TOcihjl4sS2kFbY2M",
      "endTableId": "_7uUiArDZOJRRymbsUwf4",
      "endFieldId": "QlsUM9QCMZ1jkzTYiA9nD",
      "cardinality": "one_to_many",
      "updateConstraint": "No action",
      "deleteConstraint": "No action",
      "name": "fk_病房_Wno_病人",
      "id": "Xqd57tFtNOXVg6XZbaG7o"
    }
  ],
  "subjectAreas": []
}

（2）请将E-R模型（概念模型）转换为关系模型（逻辑模型），并标注每个关系的主键、候选键和外键。

创建Department表

CREATE TABLE Department (
    Dno INTEGER PRIMARY KEY,
    Dname VARCHAR(255) NOT NULL,
    Location VARCHAR(255),
    Phone VARCHAR(255)
);

创建Ward表

CREATE TABLE Ward (
    Wno INTEGER PRIMARY KEY,
    Location VARCHAR(255),
    Dno INTEGER,
    FOREIGN KEY (Dno) REFERENCES Department(Dno)
);

创建Doctor表

CREATE TABLE Doctor (
    Eno INTEGER PRIMARY KEY,
    Ename VARCHAR(255) NOT NULL,
    Tittle VARCHAR(255),
    Gender VARCHAR(255),     
    Birthday CHAR(1),        
    Dno INTEGER,
    FOREIGN KEY (Dno) REFERENCES Department(Dno)
);

创建Patience表

CREATE TABLE Patience (
    Pno INTEGER PRIMARY KEY,
    Pname VARCHAR(255) NOT NULL,
    Gender CHAR(1),
    Birthday DATE,
    Eno INTEGER,
    Wno INTEGER,
    FOREIGN KEY (Eno) REFERENCES Doctor(Eno),
    FOREIGN KEY (Wno) REFERENCES Ward(Wno)
);

（注：术语说明：E-R图=实体 - 联系图；relational model=关系模型；primary key=主键；alternate keys=候选键；foreign keys=外键）

2. 规范化 (Normalization)

Invoice_data (发票编号 I_number, 发票日期 I_date, 客户编号 C_number, 客户姓名 C_name, 客户城市 C_city, 商品编号 Item_number, 商品名称 Item_name, 商品数量 Item_qty, 商品单价 Item_price, 总价 Total price)。

背景说明如下：

  • 每张发票都有一个编号 (I_number)、日期 (I_date)、唯一的客户编号 (C_number) 以及所有商品的合计金额 (Total price)。

  • 每张发票可能列出一种或多种客户购买的商品；发票中的每件商品都有唯一的商品编号 (Item_number)、商品名称 (Item_name)、购买数量 (Item_qty) 和单价 (Item_price)。

  • 同一种商品可能出现在多张发票中（即可能被多个客户购买）。

  • 每个客户都有客户编号 (C_number)、客户姓名 (C_name) 和客户所在城市 (C_city)。

要求：

(1) 确定上述表的主键；

(I_number, Item_number)

(2) 确定上述表中存在的函数依赖关系；

函数依赖 (FD)

解释说明

I_number → I_date​

发票决定日期：给定一个发票号 (I_number)，其开票日期 (I_date) 是唯一确定的。

I_number → Total_price​

发票决定总价：给定一个发票号 (I_number)，该发票的总金额 (Total_price) 是唯一确定的。

I_number → C_number​

发票决定客户：给定一个发票号 (I_number)，它只属于一个特定的客户 (C_number)。

C_number → C_name​

客户决定姓名：给定一个客户编号 (C_number)，客户姓名 (C_name) 是确定的。

C_number → C_city​

客户决定城市：给定一个客户编号 (C_number)，客户所在城市 (C_city) 是确定的。

Item_number → Item_name​

商品决定名称：给定一个商品编号 (Item_number)，商品名称 (Item_name) 是确定的。

Item_number → Item_price​

商品决定单价：给定一个商品编号 (Item_number)，商品的单价 (Item_price) 是确定的。

{I_number, Item_number} → Item_qty​

主键决定数量：只有在确定了哪张发票 (I_number) 和哪个商品 (Item_number) 后，购买的数量 (Item_qty) 才被确定。

总结该函数依赖可以总结为以下内容

F={
I_number→I_date, Total_price, C_number
C_number→C_name, C_city
Item_number→Item_name, Item_price
(I_number, Item_number)→Item_qty
}

(3) 描述并演示将上述表规范化为 BCNF（巴斯-科德范式）​ 的过程。

表名 (Relation)

属性 (Attributes)

主键 (Primary Key)

外键 (Foreign Key)

Invoice​

I_number, I_date, Total_price, C_number

I_number

C_number →Customer

Customer​

C_number, C_name, C_city

C_number

(无)

Item​

Item_number, Item_name, Item_price

Item_number

(无)

Invoice_Item​

I_number, Item_number, Item_qty

(I_number, Item_number)

I_number →Invoice
Item_number →Item

一、关系模型、码键、实体完整性约束知识梳理

1. 关系模型基础 (Relational Model Basics)

这是最底层的概念，定义了数据的组织方式。

  • 关系 (Relation)：​ 对应题目中的 Invoice_data，也就是我们常说的“表”。

  • 元组 (Tuple)：​ 表中的一行（Row），代表一个具体的实体或联系。

  • 属性 (Attribute)：​ 表中的一列（Column），如 I_number、C_name等。

2. 码/键 (Keys)

这是本题考察的重点，用于唯一标识数据。

  • 超码 (Superkey)：​ 能够唯一标识一个元组的属性集。

    • 例子：(I_number, Item_number)是超码，(I_number, Item_number, I_date)也是超码（加了多余属性也能唯一标识，但不精简）。

  • 候选码 (Candidate Key)：​ 是最小的超码（Minimal Superkey）。去掉其中任何一个属性，它就不再能唯一标识元组了。

    • 分析：在本题中，(I_number, Item_number)去掉任何一个都无法唯一标识一行，所以它是候选码。

  • 主键 (Primary Key)：​ 从候选码中选出一个，作为表的主要标识符。

    • 答案：(I_number, Item_number)。

  • 主属性 (Prime Attribute)：​ 包含在任何一个候选码中的属性。

    • 对应：I_number和 Item_number是主属性。

  • 非主属性 (Non-prime Attribute)：​ 不包含在任何候选码中的属性。

    • 对应：I_date, C_name, Item_price等其余所有属性。

3. 实体完整性约束 (Entity Integrity Constraint)

这是选择主键时的规则依据。

  • 规则：​ 主键的值不能为空（NULL），也不能重复。

  • 应用：​ 题目要求我们找出能确保“每一行都不一样”的属性，正是基于这一约束。

4. 复合主键 (Composite Key)

  • 定义：​ 当一个属性无法唯一标识记录时，需要多个属性组合起来形成主键。

  • 判断逻辑（解题技巧）：

    1. 看语义：I_number（发票号）不能唯一确定一行，因为一票多物。

    2. 看语义：Item_number（物品号）不能唯一确定一行，因为一物多票。

    3. 结论：必须使用 复合主键​ (I_number, Item_number)。

二、 核心概念总览

概念

英文

解释

关系​

Relation

二维表，如 Invoice_data。

属性​

Attribute

表中的列（字段）。

元组​

Tuple

表中的行（记录）。

主键​

Primary Key (PK)

能唯一标识一条记录的属性或属性组合。

外键​

Foreign Key (FK)

一个表中的属性，引用另一个表的主键，用于建立联系。

函数依赖​

Functional Dependency

若知道属性 X 的值，就能确定属性 Y 的值，记作 X→Y。

三、 第（2）题：函数依赖 (Functional Dependencies)

1. 什么是函数依赖 (FD)？

定义：​ 在关系 R 中，如果属性集 X 的值能唯一决定属性集 Y 的值，则称 Y 函数依赖于 X。

  • 类比：​ 就像数学中的函数 y=f(x)。给定一个 x（如学号），只有一个 y（如姓名）与之对应。

2. 本题中的依赖类型

针对 Invoice_data (I_number, I_date, C_number, C_name, C_city, Item_number, Item_name, Item_qty, Item_price, Total_price)：

依赖类型

示例

解释

完全函数依赖​

(I_number,Item_number)→Item_qty

必须知道完整的发票号和商品号，才能知道数量。缺一不可。

部分函数依赖​

I_number→I_date

只需要主键的一部分（发票号），就能知道日期。这是 2NF 要解决的问题。​

传递函数依赖​

I_number→C_number→C_name

通过中间属性（C_number）间接决定的依赖。这是 3NF 要解决的问题。​

3. 候选键与主键

  • 候选键：​ 能唯一标识元组的最小属性集。

  • 本题分析：​ 单独 I_number不能区分同一张发票里的不同商品；单独 Item_number不能区分商品被卖到了哪张发票。因此，必须组合两者。

  • 结论：​ 主键是 (I_number, Item_number)。

四、 第（3）题：范式 (Normal Forms)

规范化是将低一级范式的关系模式转换为高一级范式的过程。

1. 第一范式 (1NF) —— 原子性

  • 规则：​ 表中的每一列都是不可再分的原子项。

  • 本题情况：​ 题目给出的表已经是 1NF（没有重复的列或数组）。

2. 第二范式 (2NF) —— 消除部分依赖

  • 规则：​ 在满足 1NF 的基础上，非主属性必须完全依赖于主键，而不能只依赖主键的一部分。

  • 操作（拆表）：

    • 原表问题：​ I_date只依赖于 I_number（主键的一部分）。

    • 解决方案：​ 把只依赖于 I_number的数据拿出来，建立新表 Invoice。把只依赖于 Item_number的数据拿出来，建立新表 Item。剩下的建立 Invoice_Item。

3. 第三范式 (3NF) —— 消除传递依赖

  • 规则：​ 在满足 2NF 的基础上，非主属性不能依赖于其他非主属性（即消除传递依赖）。

  • 操作（拆表）：

    • 原表问题：​ I_number -> C_number -> C_name。客户姓名依赖于客户编号，而不是直接依赖于发票。

    • 解决方案：​ 建立独立的 Customer表。

4. BCNF (巴斯-科德范式) —— 主属性优化

  • 规则：​ 在满足 3NF 的基础上，每一个决定因素（左边的X）都必须是候选键。

  • 检查：​ 在我们的结果中：

    • Invoice表：I_number→...（I_number是主键 ✅）

    • Customer表：C_number→...（C_number是主键 ✅）

    • Item表：Item_number→...（Item_number是主键 ✅）

    • Invoice_Item表：(I_number,Item_number)→...（组合是主键 ✅）

  • 结论：​ 已满足 BCNF。

五、 总结流程图（思维导图结构）

原始表: Invoice_data
(PK: I_number, Item_number)
│
├── 存在部分依赖 (Partial Dependency)
│   └── 拆出: Invoice表 (I_number...)
│   └── 拆出: Customer表 (C_number...)
│   └── 拆出: Item表 (Item_number...)
│   └── 剩余: Invoice_Item表 (I_number, Item_number, qty)
│
├── 存在传递依赖 (Transitive Dependency)
│   └── 已在拆表过程中解决
│
└── 达到 BCNF
    ├── Invoice (I_number...)
    ├── Customer (C_number...)
    ├── Item (Item_number...)
    └── Invoice_Item (I_number, Item_number...)

六、 为什么要这样做？（考试常考点）

  1. 减少数据冗余：​ 客户“张三”的名字不需要在每张发票里都存一遍，只需要在 Customer 表里存一次。

  2. 避免更新异常：​ 如果要改商品价格，只需要改 Item 表的一行，而不是改所有发票记录。

  3. 避免插入异常：​ 即使还没有人买某个商品，也可以先把商品信息存入 Item 表。

  4. 避免删除异常：​ 删除最后一张发票时，不会误删掉客户的基本信息。

# 数据库
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

AtomGit 首发开源｜光轮智能发布全球首个十万小时级全模态人类行为开源数据集 EgoSuite-Open100K

avatar AtomGit开源社区
cover

构筑赋能金融行业应用 Agent｜20 强决战北京，一场金融 Agent 的硬核答卷

avatar AtomGit开源社区
cover

最高赢取价值万元大奖，仓颉生态创新开发挑战赛开启！

avatar AtomGit开源社区