📚 Database Design Principles and ER Model Analysis | 数据库设计原理与ER模型解析
Database design is the process of producing a detailed data model of a database. It is a critical skill in computer science, as a well-designed database ensures data consistency, eliminates redundancy, and supports efficient querying. The Entity-Relationship (ER) model is the most widely used conceptual design tool, and mastering it is essential for exam success.
数据库设计是建立详细数据库数据模型的过程。它是计算机科学中的关键技能——设计良好的数据库能保证数据一致性、消除冗余并支持高效查询。实体-关系（ER）模型是最广泛使用的概念设计工具，掌握它是考试成功的关键。
1. Database Design Lifecycle | 数据库设计生命周期
A complete database design process typically follows a structured lifecycle. The first phase is requirements analysis, where designers interview users and study existing systems to understand what data must be stored and how it will be accessed. The second phase is conceptual design, where an abstract, implementation-independent model — usually an ER diagram — is constructed. The third phase is logical design, where the ER model is converted into relational tables. Finally, physical design determines how those tables are stored on disk, including indexing and file organisation.
完整的数据库设计过程通常遵循结构化生命周期。第一阶段是需求分析，设计者通过访谈用户、研究现有系统来理解需要存储哪些数据以及如何访问它们。第二阶段是概念设计，构建一个抽象的、与实现无关的模型——通常是ER图。第三阶段是逻辑设计，将ER模型转换为关系表。最后，物理设计决定这些表如何在磁盘上存储，包括索引和文件组织方式。
2. Core Concepts of the ER Model | ER模型的核心概念
The ER model is built upon three fundamental building blocks. An entity is a real-world object or concept that has independent existence, such as a student, a course, or an employee. An attribute is a property that describes an entity, such as a student’s name or age. A relationship is an association between two or more entities, such as “a student enrols in a course”. These three components form the vocabulary of the ER model, and every ER diagram is a combination of them.
ER模型建立在三个基本构件之上。实体是独立存在的现实世界对象或概念，例如学生、课程或员工。属性是描述实体的特征，例如学生的姓名或年龄。联系是两个或多个实体之间的关联，例如”学生选修课程”。这三个组件构成ER模型的词汇表，每张ER图都是它们的组合。
In an ER diagram, a rectangle represents an entity, a diamond represents a relationship, and an ellipse represents an attribute. The primary key attribute is underlined. These conventional notations — known as the Chen notation — are universally recognised in examinations.
在ER图中，矩形表示实体，菱形表示联系，椭圆表示属性。主键属性加下划线。这些传统记号——称为Chen表示法——在考试中被普遍认知。
3. Attribute Types and Classification | 属性类型与分类
Attributes can be classified in several ways, and examiners frequently test this taxonomy. A simple (atomic) attribute cannot be divided further, such as “age”. A composite attribute can be broken into smaller parts, such as “address” which contains “street”, “city”, and “postcode”. A single-valued attribute holds only one value for each entity instance, while a multi-valued attribute can hold multiple values — represented by a double-line ellipse. A derived attribute is computed from other attributes, such as “age” derived from “date of birth”.
属性可以从几个角度分类，考官经常测试这一分类体系。简单（原子）属性不可再分，如”年龄”。复合属性可分解为更小的部分，如”地址”包含”街道””城市”和”邮政编码”。单值属性对每个实体实例只保存一个值，而多值属性可保存多个值——用双线椭圆表示。派生属性是由其他属性计算得出的，如”年龄”由”出生日期”推导而来。
| Attribute Type | Example | ER Notation |
| Simple | 简单 | Age 年龄 | Single ellipse 单椭圆 |
| Composite | 复合 | Address 地址 | Ellipse with branches 带分支的椭圆 |
| Multi-valued | 多值 | Phone numbers 电话号码 | Double ellipse 双椭圆 |
| Derived | 派生 | Age from DOB 由出生日期得年龄 | Dashed ellipse 虚线椭圆 |
4. Relationship Cardinality | 联系基数
Cardinality defines the number of entity instances that can participate in a relationship. The three fundamental types are one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N). In a 1:1 relationship, exactly one entity instance of each type is associated — for example, one citizen has one passport. In a 1:N relationship, one entity instance can be associated with many instances of the other — one department has many employees. In an M:N relationship, many instances on both sides are associated — many students enrol in many courses.
基数定义了可以参与联系的实体实例数量。三种基本类型是一对一（1:1）、一对多（1:N）和多对多（M:N）。在1:1联系中，每种类型恰好一个实体实例相关联——例如，一个公民持有一本护照。在1:N联系中，一个实体实例可以与另一方的多个实例相关联——一个部门有多名员工。在M:N联系中，双方都有多个实例关联——多名学生选修多门课程。
1:1 → one instance on each side | 每侧一个实例
1:N → one instance on left, many on right | 左侧一个实例，右侧多个
M:N → many instances on both sides | 两侧多个实例
Cardinality is typically written on the edge of the relationship diamond in the form of “1”, “N”, or “M”. Understanding which cardinality applies is the first step in converting an ER diagram into a relational schema.
基数通常以”1″”N”或”M”的形式标注在联系菱形的边上。理解适用哪个基数是把ER图转换为关系模式的第一个步骤。
5. Participation Constraints and Weak Entities | 参与约束与弱实体
An important extension of cardinality is participation constraint, which specifies whether every entity instance must participate in a relationship. If every instance must participate, the participation is total (drawn as a double line). If only some instances participate, it is partial (a single line). A weak entity is an entity that cannot be uniquely identified by its own attributes alone; it depends on a owner (strong) entity and is drawn as a double rectangle. For example, a “Room” entity may depend on “Building” — rooms are only meaningful within a building, and the room number alone is not globally unique.
基数的一个重要扩展是参与约束，它规定是否每个实体实例都必须参与联系。如果每个实例都必须参与，则参与是全参与（用双线表示）。如果只有部分实例参与，则为部分参与（单线）。弱实体是不能仅凭自身属性唯一标识的实体；它依赖于属主（强）实体，用双矩形表示。例如，”房间”实体可能依赖”大楼”——房间只有在大楼内才有意义，且单靠房间号并不是全局唯一的。
Weak entities are connected to their owner entity by an identifying relationship (double diamond). The discriminator of a weak entity, called a partial key, is underlined with a dashed line. This concept is frequently examined because it demonstrates a deeper understanding of entity identity.
弱实体通过标识联系（双菱形）与其属主实体相连。弱实体的区分符称为部分键，用虚下划线标注。这个概念经常被考查，因为它能体现对实体标识的更深层次理解。
6. Mapping the ER Model to Relational Tables | 将ER模型映射为关系表
Once the conceptual ER model is complete, the logical design phase transforms it into relational tables using a set of well-defined mapping rules. For an entity, each attribute becomes a column in a table, and the primary key of the entity becomes the primary key of the table. For a 1:N relationship, the primary key of the “one” side is added as a foreign key to the table on the “many” side. For a 1:1 relationship, the foreign key may be placed in either table, with preference given to full participation. For an M:N relationship, a new junction table is created containing the primary keys of both participating entities, together forming a composite primary key.
概念ER模型完成后，逻辑设计阶段通过一套明确定义的映射规则将其转换为关系表。对于实体，每个属性成为表中的一列，实体的主键成为表的主键。对于1:N联系，”一”方的主键作为外键添加到”多”方的表中。对于1:1联系，外键可以放在任一方表中，优先放在全参与的一侧。对于M:N联系，则创建新连接表，包含双方实体的主键，共同组成复合主键。
| Cardinality | Mapping Strategy |
| 1:1 | Foreign key in either table 外键放在任一方 |
| 1:N | Foreign key on the “many” side 外键放在”多”方 |
| M:N | Junction table with composite key 带复合键的连接表 |
7. Normalisation: 1NF, 2NF, and 3NF | 规范化：第一、第二、第三范式
Normalisation is a systematic process of organising data to reduce redundancy and eliminate anomalies. A table is in First Normal Form (1NF) if all attributes are atomic, meaning no repeating groups or multi-valued attributes exist. In Second Normal Form (2NF), the table must be in 1NF, and every non-key attribute must be fully functionally dependent on the entire primary key — this matters specifically for tables with composite keys. In Third Normal Form (3NF), the table must be in 2NF, and there must be no transitive dependencies, meaning non-key attributes must not depend on other non-key attributes.
规范化是一个系统化地组织数据以减少冗余并消除异常的过程。如果所有属性都是原子的，即不存在重复组或多值属性，则表满足第一范式（1NF）。在第二范式（2NF）中，表必须满足1NF，且每个非键属性必须完全函数依赖于整个主键——这尤其适用于复合键的表。第三范式（3NF）要求表满足2NF且没有传递依赖，即非键属性不能依赖于其他非键属性。
1NF: Atomic values only | 仅原子值
2NF: No partial dependency | 无部分依赖
3NF: No transitive dependency | 无传递依赖
For example, in a table Enrolment(StudentID, CourseID, StudentName, CourseTitle, Grade), StudentName depends only on StudentID, creating a partial dependency. Normalising to 2NF splits it into two tables. If we then add CourseInstructorOffice, which depends on CourseTitle, a transitive dependency exists, requiring decomposition into a third table to achieve 3NF.
例如，在表选课(学生号, 课程号, 学生姓名, 课程名称, 成绩)中，学生姓名只依赖于学生号，形成部分依赖。规范化为2NF将其拆分为两个表。如果再添加课程教师办公室，而它依赖于课程名称，就存在传递依赖，需要分解为第三个表以达到3NF。
8. Integrity Constraints | 完整性约束
Integrity constraints are rules that ensure the accuracy and consistency of data in a database. Entity integrity ensures that the primary key cannot be NULL — every row must be uniquely identifiable. Referential integrity ensures that a foreign key value in one table must match an existing primary key value in the referenced table, or be NULL — this prevents “orphan” records. Domain integrity ensures that attribute values fall within a specified domain, such as data type, range, or format constraints. These constraints are implemented using CREATE TABLE statements in SQL with NOT NULL, PRIMARY KEY, FOREIGN KEY, and CHECK clauses.
完整性约束是确保数据库中数据准确性和一致性的规则。实体完整性确保主键不能为NULL——每一行必须能唯一标识。参照完整性确保一个表中的外键值必须匹配被引用表中已存在的主键值，或为NULL——这可以防止”孤儿”记录。域完整性确保属性值在指定域内，如数据类型、范围或格式约束。这些约束在SQL中通过CREATE TABLE语句的NOT NULL、PRIMARY KEY、FOREIGN KEY和CHECK子句来实现。
9. Common Examination Mistakes | 常见考试错误
Examiners report several recurring mistakes in database design questions. The first is confusing relationship cardinality with participation constraints — they are independent concepts, and both must be read from the diagram. The second is drawing an M:N relationship without creating a junction table in the logical design — this is a structural error. The third is naming issues: students often use non-descriptive names like “T1” for tables, or fail to underline the primary key. The fourth is ignoring weak entities entirely, especially in decomposition questions — if an entity lacks a full key, it must be modelled as weak rather than forcing a placeholder key.
考官报告了数据库设计题中几个反复出现的错误。第一是混淆联系基数和参与约束——它们是独立概念，两者都必须从图中读取。第二是在逻辑设计中绘制了M:N联系却没有创建连接表——这是结构性错误。第三是命名问题：学生常使用”T1″这类无描述性的表名，或忘记给主键加下划线。第四是完全忽略弱实体，尤其是在分解题中——如果实体缺少完整键，必须建模为弱实体，而不是强行添加占位键。
10. Worked Example: University ER Model | 实例演练：大学ER模型
Consider a university system with requirements: an instructor teaches courses; each course is taught by exactly one instructor, but an instructor can teach many courses; a course has multiple sections, and each section meets in a specific room; students enrol in sections, receiving a grade. The ER model has entities Instructor, Course, Section, Room, and Student. The relationships are Teaches (1:N from instructor to course), Has (1:N from course to section), Scheduled in (1:1 or 1:N between section and room), and Enrols (M:N between student and section).
考虑一个大学系统，需求如下：教师教授课程；每门课程由一位教师教授，但一位教师可教授多门课程；一门课程有多个教学班，每个教学班在一个特定教室上课；学生选课并获得成绩。ER模型包含实体教师、课程、教学班、教室和学生。联系包括教授（教师到课程1:N）、拥有（课程到教学班1:N）、安排在（教学班与教室之间1:1或1:N）和选修（学生与教学班M:N）。
Instructor (1) → Teaches → (N) Course → Has (N) Section → Scheduled in → Room
Student (N) → Enrols → (M) Section [with attribute Grade]
Mapping this to relations yields: Instructor(InstructorID, Name), Course(CourseID, Title, InstructorID), Section(SectionID, CourseID, RoomID, Semester), Room(RoomID, Capacity), and Student(StudentID, Name), plus Enrolment(StudentID, SectionID, Grade). Notice how the 1:N relationships place foreign keys, and the M:N relationship produces a junction table with an extra attribute, Grade.
将其映射为关系模式得到：教师(教师号, 姓名)、课程(课程号, 课程名, 教师号)、教学班(教学班号, 课程号, 教室号, 学期)、教室(教室号, 容量)和学生(学生号, 姓名)，加上选课(学生号, 教学班号, 成绩)。注意1:N联系如何放置外键，M:N联系如何产生带有额外属性”成绩”的连接表。
11. ER Model Extensions and Modern Practice | ER模型的扩展与现代实践
Beyond basic entities, several extensions enhance the expressive power of ER models. Specialisation and generalisation allow hierarchical classification: for example, “Person” can be specialised into “Student” and “Instructor”. Aggregation treats a relationship as an entity in a higher-level relationship, which is useful when a relationship has additional relationships. In modern practice, UML class diagrams and object-relational databases have built upon ER concepts, but the underlying design principles — identifying entities, attributes, relationships, and constraints — remain unchanged.
在基本实体之外，一些扩展增强了ER模型的表达能力。特化和泛化允许层次化分类：例如，”人”可特化为”学生”和”教师”。聚合将联系视为更高层联系中的实体，这在联系本身具有额外联系时非常有用。在现代实践中，UML类图和对象关系数据库都建立在ER概念之上，但底层设计原理——识别实体、属性、联系和约束——保持不变。
12. Exam Strategy and Checklist | 考试策略与检查清单
When solving ER modelling questions, follow a consistent method. First, read the requirement statement carefully and underline every noun that represents an entity, and every verb that represents a relationship. Second, identify attributes for each entity and mark the primary key. Third, determine cardinality and participation for every relationship. Fourth, draw the ER diagram using correct notation. Fifth, for logical design questions, apply mapping rules systematically. Finally, check for normalisation — ensure the resulting schema is at least in 3NF, and specify foreign keys clearly.
在解答ER建模题时，遵循一致的方法。第一，仔细阅读需求描述，给每个表示实体的名词和每个表示联系的动词加下划线。第二，识别每个实体的属性并标出主键。第三，确定每个联系的基数和参与约束。第四，用正确记号绘制ER图。第五，对于逻辑设计题，系统化地应用映射规则。最后，检查规范化——确保生成的关系模式至少满足3NF，并明确指定外键。
Before submitting, verify your solution against this checklist: every entity has a primary key, every relationship is labelled with cardinality, M:N relationships have junction tables, weak entities are connected via identifying relationships, and no non-atomic attributes remain. These checks catch the majority of marks-losing errors.
提交前，对照以下检查清单验证你的解答：每个实体都有主键，每个联系都标注了基数，M:N联系有连接表，弱实体通过标识联系连接，且没有非原子属性残留。这些检查能捕捉到大部分丢分错误。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导Cancel reply