Data Modeling—Topics

1. Data Modeling—Topics • Foreign Keys • Parent-Child Relationships • Strong-Weak Entities • Many to Many relationships • Unary Relationships • n-ary relationships • Binary 1:1 relationships

2. Foreign Keys • Relationships are established when the Primary Key attribute(s) of one entity is/are found in another entity • These attributes are called Foreign Keys in the other entity Foreign Keys

3. Parent & Child Relationships (Terminology) • The entity contributing the primary key is the parent • The entity receiving the foreign key is the child • Foreign Keys always, always, always go on the ‘many’ side of a 1:M relationship Shared Identifier Attribute(ForeignKey) Parent Child Identifier Attribute

4. Strong & Weak Entity Types • Traditional definitions: • Strong entity type exists independently of any other entity • Weak entity type depends on some other entity type StrongEntity Type WeakEntity Type

5. Strong & Weak Entity Types (cont.) • Identifying weak entities will often not happen until identifier attributes are specified • When the Identifier Attribute of one entity appears as an attribute in another entity a relationship is established • If the foreign key in the child table is not part of the child's PK the child is a strong entity Identifier Attribute Shared Identifier Attribute

6. Strong & Weak Entity Types (cont.) • When the identifying attribute of one entity appears as part of a composite identifying attribute set in another entity the child entity is a weak entity Dept Code partof Course PK Indicates WeakEntity in somedocumentationstyles (We will not use.) Parent(Strong) Child(Weak)

7. Strong vs. Weak Entity Types (Terminology) • The identifying attribute that appears in the composite identifying attribute in a weak entity is sometimes called a cascading primary key. • These primary key attributes can sometimes cascade through three or more entities • Do not confuse mandatoryrelationships with weakentities

8. Many-to-Many Relationships • What is the problem with implementing foreign keys for the following Many-to-Many relationships?

9. Many-to-Many Relationships (cont.) • Many-to-Many (M:N) relationships must be decomposed into a new entity and two relationships • Carefully examine the cardinality of the two new relationships • If one is still M:N then decompose again

10. Many-to-Many Relationships (cont.) • Always try the combination of theprimary keys from the two originalentities as the PK of the new entity • Sometimes not all attributes ofa composite parent PK are needed • Sometimes an alternate PK suggests itself • Sometimes this proposed PK is not unique • Add appropriate nonkey attributes to the new entity • Sometimes there won't be any—the new entity serves no role but to decompose the M:N relationship

11. Decomposing Many to Many Relationships • Create a new entity • Bring in PKs of original entities to the new entity • Ask if the combination of these FKs can be the PK of the new entity • Defines the entity • Is enough to guarantee uniqueness • Is more than enough to guarantee uniqueness • What is the new entity? • Does a different PK suggest itself

12. Decomposing Many to Many Relationships (cont.) • Determine the cardinality of the relationships from the original entities to the new entity • If one (or both) are still many-to-many decompose again

13. Many-to-Many Relationships (cont.) • Two kinds of entities created this way • A real ‘person, place, thing, event…’that was overlooked in the originaldesign • An ‘associative entity’ that has no purpose except to decompose the M:M relationship • The distinction isn’t terribly important • Both kinds can have non-key attributes

14. Many-to-Many Relationships (cont.) • Decompose this relationship • Sometimes the new entity has a natural meaning that should have been identified in the original data modeling step

15. Unary Relationships • Unary relationships are relationships between an entity and itself • One employee supervises many other employees; eachemployee is supervisedby, at most, one otheremployee • One part is a component ofmany other parts;One part (assembly)contains manyother parts

16. Unary Relationships and Foreign Keys • The foreign key in a unary relationship will be a different attribute in the entity

17. Unary Many-to-Many Relationships • Decomposing Unary M:N relationships

18. Unary M:M Relationships (cont.) • Fix this one

19. Ternary Relationships • Ternary (or n-ary) relationships are relationships between three (or more) entities • It will almost always be possible toidentify a natural associative entitythat reflects the relationship betweenthe entities • Create the weak associative entity and bring in foreign keys from the original entities

20. Ternary Relationships (cont.) • In n-ary relationshipsthere is a higherlikelihood that thenew entity will haveits own 'natural' PK • Examine the default PK carefullyto see if it is appropriate • Can you think of an alternate PKfor the HouseSale entity? • (What other entities are likely to be related to the HouseSale entity?)

21. Binary 1:1 Relationships • In a 1:M relationship the parent entity will always be on the '1' side of the relaionship • Foreign key will be in the 'M' side • Q: Where should the FK be in a 1:1 relationship?

22. Binary 1:1 Relationships (cont.) • A: It doesn't matter (much) • Some considerations • Use the simplest PK/FKavailable • If there is a 'natural' parent(e.g., employee 'owns' theoffice, not the other wayaround) make it the parent • If an occurrence of one member of the relationship may stand alone (not participate in a relationship) while the other will usually be in a relationship make it the parent