Recap
| Model | Feature | Example |
|---|---|---|
| conceptual | abstract, high-level data model, no specific DBMS | ER, ODL(Object data language)-user friendly |
| logical | concrete, for implementation in specific DBMS | relational model |
| physical | actually implemented in real life | inside a specific DBMS |
Attribute
| Simple(atomic) attribute | Composite attribute | Single-valued attribute | Multi-valued attribute | Derived attribute |
|---|---|---|---|---|
| Attributes that are not divisible, don’t pay attention to substructure | Which can be divided into smaller subparts | 单值属性是指一个实体的属性只能包含一个值。这意味着每个实体的这个属性只有一个单一值。 | 多值属性指的是一个实体（或对象）的某个属性可以包含多个值，每个值通常是相同类型的。多值属性通常以集合、列表、数组等形式来表示。 | Some attributes like age are changed dynamically and age is said to be derivable from the attribute date of birth |
例如，一个人的出生日期通常是单值属性，因为每个人只有一个出生日期。
多值属性表示一个属性可以包含多个值，但这些值通常是相同类型的。
复合属性表示一个属性可以分解成多个子属性，这些子属性可以具有不同的数据类型。
如果存在一个派生属性（Derived Attribute），那么必须也存在一个可以从其他属性计算或推导出来的属性。派生属性是指在数据模型中的属性，其值不是直接存储在数据库中，而是通过计算或基于其他属性的值来获得的属性。例如，一个人的年龄就可以从其出生日期和当前日期计算得出，因此年龄是一个派生属性。
The derived attribute could have a business meaning, including it in the model helps to present a complete picture of the entity.
Entity
An entity type describes the schema or intension for a set of entities that share the same structure.
何为实体类型，何为实体集？
实体类型定义了一组具有相同属性的实体。
实体集是属于特定实体类型的实际实体的集合。也就是说，它包含了符合该实体类型定义的所有具体实体。实体集是实际存在的数据集合，而实体类型是对这些实体的通用描述。
The collection of entities of a particular entity type is grouped into an entity set, which is also called the extension of the entity type.
例如：考虑实体类型 “员工”。该实体类型的定义可能包括属性如姓名、员工号、工资等。实体集 “员工集” 是所有实际的员工实体的集合，每个员工都是 “员工” 实体类型的一个成员，而 “员工集” 就是 “员工” 实体类型的扩展。
Key
- composite key: 复合键是数据库表中的一个或多个属性的组合，用于唯一标识表中的每一行数据，与单一主键不同，复合键是由两个或多个属性共同组成的键，它们一起确保了数据的唯一性。复合键通常用于那些没有单一属性可以唯一标识每一行的情况。
举例：在表示学生选课记录的表中，可能需要使用学生ID和课程ID的组合作为复合键，因为单独的学生ID或课程ID并不能唯一标识一条记录，只有它们的组合才能做到。
- Super key: 超键是一个或多个属性（列）的组合，其组合的值能够唯一地标识表中的每一行。这意味着，超键可以包含一个或多个属性，可以唯一标识表中的行，但不一定是最小的唯一标识方式。超键可以包含主键，也可以包含其他属性，因此，主键
primary key本身也是一种超键。
举例：考虑一个员工表，超键可以是包含员工ID和员工姓名的组合，因为这个组合可以唯一标识每个员工。
- Primary Key: 主键是表中的一个特殊超键，它被选定为唯一标识表中的每一行的超键。也就是主键属性的值必须是唯一的，并且不能为空（即不允许空值）。表中只能有一个主键，因为主键的目的是提供唯一性标识。主键是确保数据完整性和一致性的重要工具，它确保每一行都有一个唯一的标识符。
举例：在员工表中，员工ID可能会被选为主键，因为每个员工都应该有唯一的员工ID，并且员工ID不能为空。
- Candidate key: 候选键属于超键，是最小的超键，也就是如果再去掉候选键中的任何一个属性，它将不再是超键了。
如何区分复合键与超键？
唯一性：
- 超键的目标是确保每一行都具有唯一的标识，但它可能包含多余的属性。超键并不一定是最小的唯一标识方式。
- 复合键确保表中的每一行都具有唯一的标识，但它只由特定属性的组合构成，不包含多余的属性。复合键通常是最小的唯一标识方式。
索引：
- 超键通常可以用于创建索引，以提高查询性能。这包括主键以及其他唯一标识属性组合。
- 复合键通常用于创建复合索引，以确保多个属性的组合的唯一性。
总而言之，every entity must have a final primary key: a minimal set of attributes that can uniquely indentify its entity instances.
Super key is as set of one or more attributes that can uniquely identify an entity instance of an entity type.
Candidate key is the minimal super key.
弱实体 强实体
强实体：其实例的存在不依赖于任何其他实体类型的实例（有自己独立的主键），唯一地标识它的每个实例。
弱实体：一个实体对于另一个实体（一般为强实体，也可以是依赖于其他强实体的弱实体）具有很强的依赖联系，而且该实体主键的一部分或全部从其强实体（或者对应的弱实体以来的强实体）中获得。
相关名词：
- A weak entity doesn’t have any primary key but does have a partial key(discriminator).
partial key/discriminator：a key that is partially unique, only a subset of attributes can be indentified using itIdentifying relationship: the relationship type between a weak entity type to the owner of the weak entity type
部分参与 全部参与
total: 每个实体必须参加关系
partial: 不是必须参加关系
exercise 1
A civil aviation organization hires you to design a small database with the following requirements:
-
An aircraft is uniquely identified by its RegistrationCode. For each aircraft, we record its model and capacity. Each aircraft is owned by only one airline.
-
An airline is uniquely identified by its AirlineCode. For each airline, its name, country will be recorded. Each airline could have multiple phone numbers. The number of aircrafts belonging to this airline is needed.
-
A pilot is uniquely identified by his/her PilotID. For each pilot, we also record his/her name, gender and DoB (Date of Birth).
-
A flight is uniquely identified by its FlightID. For each flight, its flight number, route and time are recorded. The route is composed by an origin and a destination.
-
A passenger is uniquely identified by his/her PassengerID. For each passenger, we also record his/her name and contact number.
-
A passenger must book at least one flight, and a flight could have zero or more passengers. Whenever a passenger books a flight, the booking reference is recorded.
-
A flight must be performed by at least one pilot, but a pilot may perform zero or more flights.
-
A flight must be operated by one aircraft, but an aircraft may operate zero or more flights.
-
An airline should own at least one aircraft and at least one pilot. Whenever a pilot starts to work in an airline, his employment date is recorded.
Draw an ER diagram to represent the scenario, clearly state the assumptions you make if any.
Assumption
Each aircraft must be owned by an airline.
Each pilot cannot work for multiple airlines at the same time.
Each pilot must work for an airline, and the airlines that the pilot has previously worked for are not recorded.