1 / 40

The Entity-Relationship Data Model

The Entity-Relationship Data Model. Chapter 2 (Database Design). Steps in designing a database. Analysis: What information needs to be stored? What are the relationships between different components of the stored information? What is the suitable database structure (or schema)?

leoma
Download Presentation

The Entity-Relationship Data Model

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. The Entity-Relationship Data Model Chapter 2 (Database Design)

  2. Steps in designing a database • Analysis: • What information needs to be stored? • What are the relationships between different components of the stored information? • What is the suitable database structure (or schema)? • Design the database structure (using a database design language or notation suitable for expressing design) • Implementation in DBMS once committed to the design

  3. Steps in picture • E/R diagram • Relational model • ODL, ORDL Ideas/ Problems Database Structure Database Design DBMS Analysis Design Implementation

  4. Entity-Relationship Model • Traditional & popular • Graphical representation • Three types of elements • Entity set (rectangle) • Attributes (oval) • Relationship (diamond)

  5. Entity Set • Collection of similar objects • Similar to a class in the sense of OOP • Entity vs. entity set • Example: Database about movies, their stars, the studio that producing them, and other aspects of movies. • A movie is an entity • Collection of movies is an entity set • Other entities in this db: ?

  6. Attributes • Entity set has associated attributes • Each attribute represents a property of entities belonging to the entity set • Example: • The entity set Movies has the associated attribute Title each movie has a title • Assumption: attributes are atomic values  address with two components (e.g. number, street) cannot be used as an attribute of an entity set

  7. Relationship • Connections among entity sets • Represent a relationship between entity sets • If E and F are two entity sets and R connect the two, then R is a binary relation between E and F, mathematically we write R  E x F. • Example: Movies and Stars are two entity sets and a connection Stars-In between the two  the intention: m is related to s means that star s starts in movie m.

  8. E/R Diagram • A graph representing entity sets, attributes, and relationships. • Entity set (rectangle) • Attributes (oval) • Relationship (diamond)

  9. name address year title length film type Stars Stars_in Movies name Owns Studios address

  10. A reading of the previous diagram • Three entity sets: • Movies [title,year,length,film type] • Stars [name,address] • Studio [name,address] • Two relationships • (m:n) – Stars_in(Movies,Stars) • (m:1) – Owns(Movies,Studios)

  11. Instance of an E/R diagram • Concrete data of a database, whose structure is represented by a E/R diagram, creates a database instance • In an instance • For each entity set: a particular finite set of entities, each has values for each attribute • For each relationship connecting the entities E1, E2,…,En a set of tuples (e1,e2,…,en) each ei is an entity in the entity set of Ei • Where are the attributes?

  12. Example • A possible database instance of the previous E/R diagram (Note: The info is inserted for illustrative purpose only – it needs not be true in real life) Movies Stars Studio Stars_in Owns

  13. A little of math before continuing • E, F are two sets • R  E x F: R is a binary relation from E to F • R is a set whose member is a pair (e,f) where e is a member of E and f is a member of F • R could be empty, could be equal the Cartesian product of E and F • R is many to one relation if for each e in E there is at most one element (e,f) in R • R is one to one: many to one from E to F and many to one from F to E • R is many to many: not many to one from E to F and not many to one from F to E

  14. many-one one-one one-many many-many

  15. Multiplicity of Binary E/R Relationship • R is a binary relation from E to F • One to many • One to one • Many to many • Representing using arrow in the connection between entity set and relationship • Arrow entering an entity set represents the ‘one’ in the above description

  16. name address year title many-many many-one length film type Stars Stars_in Movies name Owns Studios address

  17. Multiway Relationships • Easy to handle in E/R diagram: connect all related entity sets with the relationship (Note: weakening the relationship (movie,studio)) Stars Movies Contracts Studios Contracts relationship: (studio,star,movie)

  18. Roles in Relationship • An entity set can appear more than one times in a relationship • E/R diagram: labeling the arc connecting the entity set and the relationship Sequel Sequel_of Movies Original

  19. Another example Stars Movies Contracts Producing studio Studio of star Studios

  20. Attributes on Relationships • How to record the salary of a star in a contract? Stars Movies Contracts Studios ? ? ? salary

  21. Attributes on Relationships • How to record the salary of a star in a contract? Ans: attribute of Contracts! Stars Movies Contracts Studios salary

  22. name address year title length film type Stars Movies Contracts name * Studios * salary address The complete diagram of the Contracts relationship. Can we live without *?

  23. name address year title length film type Stars Movies Contracts name Studios Salaries address salary

  24. Eliminating Attributes on Relationship • Introducing a new entity set whose attributes are the attributes attached to the relationship • Connecting the new entity set to the relationship, with the arrow pointed to the new entity set

  25. Converting Multiway Relationship to Binary Relationship • Useful when the language for defining the database structure does not allow multiway relationship. • Simple: • Introducing a new entity set representing the relationship • Introducing binary relation between the new entity set and the old ones which are connecting to the relationship

  26. Stars Movies Contracts Stars Movies Star_of Producing studio Studio of star Movie_of Producing studio Studios Contracts Studios Studio of star Before After

  27. Subclasses in the E/R Model • Subclass: common in real-life • Represented by the isa relationship • isa is one-one relationship • In E/R diagram: • draw as a triangle • no arrows into the entity sets

  28. year title length film type Stars Movies weapon Voices isa isa Cartoons Murder Mysteries At home: read example 2.10 and 2.11.

  29. Design Principles • Faithfulness • Avoiding redundancy • Simple • Select the right relationships • Select the right kind of element

  30. Faithfulness • Entity sets and attributes should reflect reality. • Relationships are created only if they make sense given what we know about the domain/application. • Example: • Stars_in relationship should be many-many • Teaches relationship between Courses and Instructors? What? How?

  31. No Redundancy • Motto: Stored everything only one! • Space reason (less serious) • Consistency (serious) • Example: Add an attribute studioName to the entity set Movies while having the relationship Owns between Movies and Studios – consequences: • More space (obvious: studio name stored twice) • Change in ownership of a movie  change in the Owns relationship and the Movies entity set

  32. Simplicity • Do only whatever is necessary! • Example: Movies Owns Studios is better than Movies Represents Holdings Owns Studios

  33. Right Relationships • Should we represent every possible relationships? NO: due to space & redundancy requirements  get only the necessarily one • How? Consider the assumptions, identify those that cannot be deduced from or represented by others

  34. name address year Stars Stars_in title Movies Contracts name Owns Studios length film type address Assumption: a star can plays in a movie if there is a contract involving the star and the movie  Stars_in is redundant

  35. name address year title length film type Stars Stars_in Movies Works_for name Owns Studios address Question: Would it make sense to have a relationship Works_for?Depending …

  36. Right Kind of Element • Attribute vs. Entity set/Relationship • Example: address • in the examples so far: attribute • better as an entity with attributes such as street, number, zip code, state

  37. name address year Stars title Stars_in Movies name Owns Studios length film type address Example: What happens if we replace Studios by its two attributes?  redundancy, losing information (if a studio does not own a movie its address is lost)

  38. When to use attribute for entity set E? • All relationships connecting to E must have an arrow entering E. • Attributes for E must collectively identify an entity. If there are more than one attributes then they must not depend on each other. • No relationship involves E more than one.

  39. Converting from Entity sets to Attributes R E F A B F A B

  40. Converting from Entity sets to Attributes R E F A B F A R F B F

More Related