1.4 Logical Database Design (Mapping ER model to Relational Model) 数据库第一周

Mapping ER model concepts to relations• Entity• Binary 1:1, 1:m, m:m relationships• Complex relationships• Multi-valued attributesEntity• For each entity:• create a relation that includes all the attributes of that entity.• For composite attributes, include only the constituent simple attributes (broken into several columns). 对于复合属性仅包含构成它的简单属性拆分为多个列。One-to-many (1:*) binary relationships• Post a copy of the primary key attribute(s) of one-side entity into the relation representing the many-side, to act as a foreign key. 将一侧实体的主键属性副本发布到表示多侧的关系中以充当外键。在数据库设计里这叫**“子随父姓”**父表1端国家是总部。子表*端城市是员工。每个员工城市身上带一个总部的 ID外键这样不管员工在哪我们都能知道他属于哪个总部。Exercise1CLASS(classNo, name, year)STUDENT(studentID, sName, gender, DoB,classNo)One-to-one (1:1) binary relationships• Put the foreign key in Dept. 将外键置于部门表(Dept)Every instance of Dept will record empno of the employee who is the boss. All depts have boss so the foreign key will not be null. 每个部门实例将记录作为该部门主管的雇员编号(empno)。所有部门都有主管因此该外键不会为空值• Put the foreign key in Emp. 将外键置于雇员表(Emp)Every instance of Emp should record deptname of the department this employee manages. Most employees are not bosses, so this is usually null. 每个雇员实例应记录该雇员所管理部门名称(deptname)。大多数雇员并非主管因此该字段通常为空。• Put a foreign key in both Dept and Emp. 在部门表和雇员表中同时设置外键• Sound approach: select entity that results in fewest nulls (less confusing to clients). 合理方案选择产生最少空值的实体对客户端更清晰• This example: simplest approach is to put the foreign key in Dept. 本案例最简方案是在部门表中设置外键DEPT(deptname, deptfloor, deptphone, empno)empno in Dept is the boss empno for a department, it is the foreign key indicating the 1:1 relationship. 部门表中的雇员编号字段表示部门主管编号是体现1:1关系的外键。EMP(empno, empfname, empsalary, deptname)Deptname in Emp is the department an employee works at, it is the foreign key indicating the 1:* relationship. 雇员表中的部门名称字段表示雇员所属部门是体现1:*关系的外键。One-to-one (1:1) relationships mapping rules• mandatory participation on both sides, • 双方均为强制参与时• Combine the entities involved into one relation 将相关实体合并为一个关系• Choose one primary key of the original entities as the primary key of the new relation, the other primary key is candidate key. 选择原实体中的一个主键作为新关系的主键另一主键作为候选键• mandatory participation on only one side, 仅单方强制参与时• Two relations: each entity is a relation 保留两个独立关系每个实体对应一个关系• Copy of the primary key from optional participation relation is placed in the mandatory participation relation, as foreign key 将可选参与方关系的主键副本作为外键放入强制参与方关系中• optional participation on both sides, 双方均为可选参与时• new relation to represent the relationship. 新建关系表示该关联• Two attributes, copies of primary key from both relations. Copies of the primarykeys act as foreign keys and have to be renamed to indicate the purpose of each 包含两个属性分别复制双方主键作为外键需重命名以明确各外键用途Exercise 2One-to-many (1:*) recursive relationships• For a 1:* recursive relationship, follow same rules for a 1:* relationship. Add column(s) for foreign key, for the entity at the “many ” side of the relationship. • 对于1:*递归关系遵循与1:*关系相同的规则。在关系的“多”方实体添加外键列。Exercise 3One-to-one (1:1) recursive relationships• mandatory participation on both sides,• Represent recursive relationship as one relation with two copies of primary key, one primary key serves as a foreign key and should be renamed to indicate the recursive relationship.• optional participation on both sides,• new relation to represent the relationship. Two attributes, both copies of primary key. Copies of the primary keys act as foreign keys and have to be renamed to indicate the purpose of each• mandatory participation on only one side,• a) A single relation with two copies of the primary key (as when both mandatory).• Or b) create a new relation to represent the relationship (as when both optional).Exercise 4Many-to-many (*:*) binary relationships• Create a new relation to represent the relationship• Foreign Key columns pointing to participating entities (these foreign keys will also form the primary key of the new relation)• Further columns for attributes of the relationship.• We covered this in the ER model discussion:create an associative entity for *:* binary relationships (see next slide)• 创建新关系以表示关联• 外键列指向参与实体这些外键将共同构成新关系的主键• 为关系属性添加额外列• 我们在ER模型讨论中已涵盖为*:*二元关系创建关联实体参见下面幻灯片• Relational schema for the above ER model:Client (clientNo, fName, lName, telNo)PropertyForRent(propertyNo, street, city, postcode, type, rooms, rent)Viewing(clientNo, propertyNo, dateView, comment)Complex relationships• Create a new relation to represent the relationship .• Include any attributes that are part of the relationship.• Post a copy of the primary key attribute(s) of the participating entities as foreign keys.• Any foreign keys that represent a ‘many’ relationship (for example, 1..*, 0..*) usually form primary key of this new relation• The primary key of the new relation possibly require some of the new attributes• 创建新关系以表示该关联关系• 包含属于该关系的所有属性• 将参与实体的主键属性副本作为外键添加• 代表多方关系的外键如1..、0..通常构成该新关系的主键• 新关系的主键可能需要包含部分新增属性Multi-Valued Attributes• Create a new relation to represent multi-valued attribute. Include primary key of entity in new relation, to act as a foreign key. 创建一个新关系来表示多值属性。在新关系中包含实体的主键作为外键Example:Telephone number in Branch relation:Telephone(telNo, BranchNo)This relation tells us that telNo is a telephone number that belongs in the Branch BranchNo. Allows Branch to have many telephone numbers (which could not fit in one attribute) 该关系告诉我们电话号码属于某个分支机构编号对应的分支机构。这样分支机构可以拥有多个电话号码无法用一个属性容纳。Exercise 5