1 / 17

Entity-Relationship Model Design Principles, Keys, Subclasses, Exercises

Entity-Relationship Model Design Principles, Keys, Subclasses, Exercises. Design Principles: Faithfulness. Whatever relationships are asserted should make sense given what we know about the part of the real world being modeled.

jmilan
Download Presentation

Entity-Relationship Model Design Principles, Keys, Subclasses, Exercises

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. Entity-Relationship ModelDesign Principles, Keys, Subclasses, Exercises

  2. Design Principles: Faithfulness • Whatever relationships are asserted should make sense given what we know about the part of the real world being modeled. • If we define a relationship Stars-in between Stars and Movies, it should be a many-many relationship. • Not always obvious. • E.g. Courses and Instructors: What’s the multiplicity of a relationship “Teaches”? • In such cases, please state your assumptions.

  3. Redundancy or Right Relationships • Should we have a relationship “Works-for” between “Stars” and “Studios”? It depends. We need it if a star might work for a studio in a manner unrelated to a movie.

  4. Movies Stars-In Stars Owns Contracts Movies Stars Studios Studios Redundancy or Right Relationships • Say everything once only! …well, not always obvious. • Do we still need the two-ways relationships “Owns” and “Stars-In”?

  5. Redundancy or Right Relationships • Can it be possible to deduce the relationship “Owns” from “Contracts”? • If for every movie, there is at least one contract involving: • that movie, • its owning studio, and • some star for that movie, then we can dispense with Owns. • However, if there is the possibility that a studio owns a movie, yet has no stars under contract for that movie, or no such contract is known to our database, then we must retain Owns.

  6. Contracts Movies Stars Studios Multiway relationships vs. Connecting Entity Sets • Let’s suppose that contracts involve one star, one movie, but any set of studios. With this we can represent the fact that a contract can involve not more than one star and one movie, but many studios.

  7. Beers name manf Keys • A key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key. • It’s allowed for two entities to agree on some, but not all, of the key attributes. • We must designate a key for every entity set. • In E/R, we underline the key attribute(s).

  8. Key for Movies • Let’s consider the entity set Movie. • We might assume that the attribute title is a key. However, there can be different movies with the same name: • “Godzilla” has several different versions (Japanese, American etc.). • If we enforce in the database a key constraint on attribute title of Movie class, then the DBMS will not allow us to insert information about different “Godzilla’s”. • A better choice is to take the set {title, year} of attributes as a key. • We still run the risk that there are two movies made in the same year, with the same title, but that’s very unlikely.

  9. Keys for Studios and Stars • For Studios: • Reasonable to assume that there are no two studios having the same name. • So, we will enforce name to serve as a key. • For Stars: • We may think that the name can’t serve to distinguish two people, but… • Yes! For stars the name distinguishes them since traditionally they choose “stage names”. • So, again here, we will enforce name to serve as a key.

  10. Surrogate Keys • Often, people introduce attributes whose role is to serve as a key for classes. • Companies assign employee ID’s to all employees, and these ID’s are carefully chosen to be unique numbers. • In Canada everyone has a SIN. • Students ID’s in universities • Driver license numbers • Automobile registration numbers

  11. name manf manfAddr Beers Entity Sets Versus Attributes I • Example: Bad design 1. This design repeats the manufacturer’s address once for each beer; 2. Loses the address if there are temporarily no beers for a manufacturer.

  12. name name addr ManfBy Beers Manfs Entity Sets Versus Attributes II • An entity set should satisfy at least one of the following conditions: • It is more than the name of something; it has at least one nonkey attribute. or • It is the “many” in a many-one or many-many relationship. • Example: Good Manfs deserves to be an entity set because of the nonkey attribute addr. Beers deserves to be an entity set because it is the “many” of the many-one relationship ManfBy.

  13. Relate parent with child by a special (1-1) relationship called isa. length title year filmType to Stars Movies Voices isa isa weapon Cartoons Murder- Mysteries Subclasses • Often, a class contains certain objects that have special properties not associated with all members of the class. • If so, organize the class into subclasses, each subclass having its own special attributes and/or relationships.

  14. length title year filmType to Stars Movies Voices isa isa weapon Cartoons Murder- Mysteries Inheritance in the E/R Model • In the object-oriented world, objects are in one class only. • Subclasses inherit properties from superclasses. • In contrast, E/R entities have components in all subclasses to which they belong. • The entity has whatever attributes any of its components has, and it participates in whatever relationships its components participate in.

  15. length title year filmType to Stars Movies Voices isa isa weapon Cartoons Murder- Mysteries Components Example • Take the movie Roger Rabit, which is both a cartoon and murder-mystery. • It will have components in all three entity sets: Movies, Cartoons, and Murder-Mysteries. • The three components are connected together into one entity by the isa relationships. • Roger Rabit will have all four attributes of Movies, the attribute weapon, and finally will participate in the relationship voices.

  16. length title year filmType to Stars Movies Voices isa isa weapon Cartoons Murder- Mysteries Keys for entity set hierarchies In entity set hierarchies the key at root is key for all. {title,year} is the key for Movies, Cartoons and Murder-Mysteries.

  17. Exercises III • ! Exercise 2.1.6 : Suppose we wish to keep a genealogy. We shall have one entity set, Person. The information we wish to record about persons includes their name (an attribute) and the following relationships: mother, father, and children. Give an E/R diagram involving the Person entity set and all the relationships in which it is involved. Include relationships for mother, father, and children. Do not forget to indicate roles when an entity set is used more than once in a relationship. • ! Exercise 2.1.7 : Modify your “people" database design of Exercise 2.1.6 to include the following special types of people: • Females. • Males. • People who are parents. • You may wish to distinguish certain other kinds of people as well, so relationships connect appropriate subclasses of people.

More Related