1 / 22

Data Modeling—Topics

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

bcovington
Download Presentation

Data Modeling—Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related