RDM图

关系完整性约束

relational integrity constraints: we need to keep the relational database in a valid state, so three integrity constraints are important

• primary key constraint: candidate key values must be unique for every relation instance.
  主键约束: 要求每个关系实例的候选键的值必须是唯一的。如果有重复的候选键值，就会违反主键约束。
• entity constraint: an attribute that is part of a primary(candidate) key cannot be NULL
  实体完整性: 保证关系中每个元组都是可识别的和唯一的，关系数据库中所有的表都必须要有主键，在表中定义主键时，数据库管理系统会自动保证实体完整性，表中不允许存在主键为空或主键相同的记录，实体必须可去以区分。
• referential integrity
  引用完整性: 描述实体之间的联系，定义了外键与被引用的主键之间的引用规则，外键应当符合的要求是要么值为空，要么等于其所引用的关系中的某个元组的主键值。

何为valid state? 何为invalid state?

关系不违反任何完整性约束即为valid state; 一个关系至少违反了至少一个完整性约束即为invalid state。

About deletion

When delete something, we need to check referential integrity, check whether the primary key occurs in another relation.
从表中删除元组时，需要检查引用完整性约束，以确保不违反任何关系和约束条件。

For example, we want to delete the tuple with Person#=2 from Researcher.

Researcher:

Enrolment:

不难看出，Person#字段在Enrolment表中充当外键Supervisor，而Person#等于2的这个记录也在Enrolment表中有相应的记录，这违反了外键约束，破坏了引用完整性。

此时我既想要删除Person#等于2的研究员记录，同时保留Enrolment表中的相关信息，可以将与Supervisor等于2的记录的Supervisor字段置为NULL。

1
2
3

UPDATE Enrolment
SET Supervisor = NULL
WHERE Supervisor = 2;

也可以删除或者修改Enrolment表中与Person#等于2的记录相关的元组，确保Enrolment表中不再包含与Person#等于2的记录相关的引用。

1
2
3

DELETE FROM Enrolment
WHERE Supervisor = 2;
UPDATE Enrolment

1
2
3

UPDATE Enrolment
SET Supervisor = 1  -- 将Supervisor为2的记录更改为1或者其他合适的值
WHERE Supervisor = 2;

接下来就可以安全地删除Researcher表中Person#等于2的记录了。

1
2

DELETE FROM Researcher
WHERE Person# = 2;

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.

assignment_1_this_year

assignment_1.pdf