180 likes | 379 Views
Lecture 3. Mapping from ERD to Relational database schema. ER model used for mapping. 1. Mapping strong entity types.
E N D
Lecture 3 Mapping from ERD to Relational database schema
1. Mapping strong entity types • Create a relation with the name of the entity type and place each single entity attribute in its relation schema. For composite attributes, include constituent simple attributes. Example: Staff(staffno, fname, lname, position, sex, DOB, …) Owner(ownerno, address, telno, … ) Others attributes may be added later. Primary key is the same key identified in the ERD.
2. Mapping weak entity types • Create a relation that include all simple attributes • Primary key still has to be identified after the relationship types associated to the owner entities have been mapped. • Relation may not survive after relationship types are mapped. Example: Preference(preftype, maxrent, …)
3. Mapping binary 1-to-1 relationship types (a) Mandatory (total) participation on both sides • Combine entities into one relation and choose one of the primary keys of the original entities • Example: States relationship type Client(clientNo, fName, lName, telNo, staffNo, prefType, maxRent)remove Preference relation (b) Mandatory participation on one side • Place primary key of entity with ‘optional’ (partial) participation (parent entity) to act as foreign key in relation representing entity with ‘mandatory’ participation (child entity) • Place attributes of the relationship on the child entity
1-to-1 relationship type, cont. • Example of a type (b) 1-to-1 relationship • Suppose theStatesrelationship type had partial participation on the Client side • That is, not every client specifies preferences • A copy of clientNo would be placed in the Preference (child) relation, giving: Preference(clientNo, prefType, maxRent) Primary key clientNo Foreign key clientNo references Client(clientNo)
1-to-1 relationship type, cont. c) Optional participation on both sides of a 1:1 relationship • Designation of the optional participation (parent) and mandatory participation (child) entities is arbitrary unless can find out more about the relationship. • Consider 1:1 Client States Preference relationship with optional participation on both sides. Assume a majority of preferences, but not all, are used by clients and few clients state preferences. • Preferences entity, although optional, is closer to being mandatory than Client entity. Therefore designate Client as the parent entity and Preferences as the child entity.
1:1 recursive relationships • Follow the rules for participation as described above for a 1:1 relationship • Type (a): represent the recursive relationship as a single relation with two copies of the primary key. • Type(b): option to create a single relation with two copies of the primary key, or to create a new relation to represent the relationship (with only two attributes). • Type (c): create a new relation as described above.
4. Mapping binary 1-to-M relationship types • Entity on “1 side” is designated the parent entity and the entity on “M side” is the child entity • Place the primary key of the parent entity into the relation schema representing the child entity (acting as a foreign key) • Place the attributes of the relationship type in the relation schema representing the child entity • Example: Map Registers relationship type: • Client(clientNo, fName, lName, telNo, staffNo) • Primary key clientNo • Foreign key staffNo references Staff(staffNo)
5. Mapping binary *:* relationship types • Create a new relation with the same name as the relationship type or rename if it is necessary. • Place the primary keys of each participating entity types to act as foreign keys in the relation schema. • Place the simple attributes of the relationship type in the relation schema. • Identify primary key (usually a composite key made from the foreign keys). Example: Map Views and rename it to Viewing: Viewing (clientNo, propertyNo, viewDate, comment) Primary key: clientNo, propertyNo Foreign key: clientNo references Client(clientNo) Foreign key: propertyNo reference PropertyForRent(propertyNo)
6. Mapping Multi-valued attributes • For each multivalued attribute create a new relation to represent this attribute and place a copy of the primary key of the owner entity into the new relation (to act as a foreign key). • Primary key of the new relation is usually the primary key of the owner entity plus the multi-valued attribute, unless the multi-valued attribute is unique for all the rows. Example: Telephone(telfno, branchno)
7. Mapping complex relationship types • Create a relation to represent the relationship and include any attributes that are part of the relationship. • Post a copy of the primary key attribute(s) of the entities that participate in the complex relationship into the new relation, to act as foreign keys. • Identify primary key, which it is usually the combination of the foreign keys.
Example for Step 7. • Given the EERD in slide 4 of Chapter 4 for a Real Estate organization, the ternary relationship Registers is mapped into: Registers(clientNo, branchNo, staffNo, dateJoined) Registers could be renamed to Registration. Primary key: clientNo Foreign keys: clientNo, branchNo, staffNo
8. Mapping Generalization/Specialization to Relations • General approach:Create a relation for the superclass and each subclass. • Add primary key of superclass- into each subclass relation • Queries involving superclass records are easy, harder for accessing subclass attributes (queries require joins). • Works for any specialization • Alternative 1: Just relations for each subclass. • Superclass atributes are added to subclass relations. - Works for total participation • Alternative 2: Single relation with one type attribute. • Use a type attribute that indicate the type of a subclass. • Works for disjoint subclasses
Examples for Step 8 (slide 12, Ch. 4). • Relations for Staff and its subclasses. • General approach (adding attributes to slide): Staff(staffNo, fname, lname, position) Supervisor(staffNo, group) Manager(staffNo, bonus, mgrStartDate) • Alternative 1: Supervisor(staffNo, fname, lname, position, group) Manager(staffNo, fname, lname, position, bonus, mgrStartDate) • Alternative 2: Staff(staffNo, fname, lname, position, jobType, group, bonus, mgrStartDate)
Step 9. Mapping shared subclasses. • Use the general approach described before. • Add a surrogate key to the relations of the superclasses and subclass. • The surrogate key links the subclass to all the superclasses is related to. • For a UNION type: • Identical mapping of a shared subclass. • The surrogate key links one of the superclasses to the UNION shared subclass.
Example- mapping a Union type • Given the diagram: • Mapped entities to relations: Person(SSN, name, ownerID) Company(cname, address, ownerID) Owner(ownerID) PERSON SSN name COMPANY cname address OWNER
10. Mapping Aggregations to Relations The relation for the aggregation is represented by the relationship type in the aggregation That is, the relation created after the relationship type has been mapped represents the agregation.