1 / 27

Entity-Relationship Modeling

Entity-Relationship Modeling. Week 5, Day 2 based on Ch 12 of Connolly & Begg. E-R Modeling - Overview. This chapter is full of E-R concepts that need to be learned, then applied. Entities & Objects Relationships Attributes Today we will focus on how these concepts relate to

amanda-conway
Download Presentation

Entity-Relationship Modeling

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 Modeling Week 5, Day 2 based on Ch 12 of Connolly & Begg CMPT 355 Sept-Dec 2010 - w5d2

  2. E-R Modeling - Overview • This chapter is full of E-R concepts • that need to be learned, then applied. • Entities & Objects • Relationships • Attributes • Today we will focus on • how these concepts relate to • Object-Oriented concepts • SQL concepts CMPT 355 Sept-Dec 2010 - w5d2

  3. Entity-Relationship Modeling • E-R modeling is a high-level technique intended to: • Model data components of an application • Lead to the design of databases that meet these application needs • NOTES: • Entity-Relationship modeling • preceded the popularity of Object-Oriented development. • Thus, it is similar but often uses different jargon! • However, this text is using UML style of diagrams • NOTE: • UML has only been around since 1999 • ER has been around since 1976 and commonly in use since the early 1980’s • Today we will focus on the basics of E-R modeling. (ch 12.1-12.6) • We will look at more advanced concepts next class. (ch 21.7-13.2) CMPT 355 Sept-Dec 2010 - w5d2

  4. An Example to Consider • Let’s focus on our bookstore example that involved tables for: • Textbook requisitions • Requisition lines • Books • And let’s remember that it also involves: • Students • Instructors • Bookstore employees CMPT 355 Sept-Dec 2010 - w5d2

  5. Entities & Objects • Entity Type • “A group of objects with the same properties, • which are identified by the enterprise • as having an independent existence.” (p 322) • i.e. an Object Class • which will be implemented as a table for the entity type • Like objects: entities have attributes • Unlike objects: E-R does not focus on functions • Some of the entity types we should consider include: • Instructors • Students • Books • Bookstore employees • Requisitions • NOTE: requisition lines do not have an independent existence CMPT 355 Sept-Dec 2010 - w5d2

  6. Entities & Objects • Entity Occurrence • “A uniquely identifiable object of an entity type.” (p 323) • i.e. an Instance of an Object • which will be implemented as a record in a table • Some of the entity occurrences that we should consider include: • Instructor: Carter • Students: each of you • Book: Database Systems by Connolly & Begg • Requisition: for CMPT 355 CMPT 355 Sept-Dec 2010 - w5d2

  7. Relationships • Relationship type • “A set of meaningful associations among entity types.” (p 324) • NOTE: the “IS-A” and “Part-of” of O-O modeling are less important than other more unique types of relationships in an ER model. • Relationships may suggest: • JOINS • CONSTRAINTS • Some of the relationships we should consider include: • Instructors teach students, use books, teach classes • Students are taught by instructors, use books, take classes • Books are used by instructors, students, and classes • Classes are taught by instructors, are taken by students, use books • NOTE: not all these relationships are important to our application CMPT 355 Sept-Dec 2010 - w5d2

  8. Relationships • Relationship occurrence • “A uniquely identifiable association, • which includes one occurrence • from each participating entity type.” (p 325) • i.e. a unique set of object instances that are related to one another • Some of the relationship occurrences that we should consider include: • Carter teaches you CMPT 355, • Carter uses Database Systems • Students you are taught by Carter, • you use Database Systems, • you take CMPT 355 • Database Systems is used by • Carter, you, CMPT 355 CMPT 355 Sept-Dec 2010 - w5d2

  9. Relationships • Degree of a Relationship Type • “The number of participating entity types in a relationship.” (p 326) • As the degree gets bigger, the model gets more difficult! • NOTE: in the diagrams of ternary and quaternary relationships in the book, the direction of the relationship is not clear! • Examples of the degrees of relationship types include: • Binary relationships: • you are taught by Carter • you take CMPT 355 • A ternary relationship: • Carter teaches you: CMPT 355 • A quaternary relationship: • Database Systems is used by Carter, you, CMPT 355 CMPT 355 Sept-Dec 2010 - w5d2

  10. Relationships • It is preferable to use lower degree relationships • whenever the same information • can be expressed in different degrees of relationship. • Consider the ternary relationship: • Carter teaches you CMPT 355 • It is preferable to consider that: • Carter teaches CMPT 355 • You take CMPT 355 • And allow us to derive the fact that • Carter teaches you • The quaternary relationship: • Database Systems is used by Carter, you, CMPT 355 • Can be simplified if we consider that: • CMPT 355 uses the book Database Systems • and that the other binary relationships also exist NOTE: ALL relationships in CMPT 355 MUST be expressed as (1 or more) binary relationships CMPT 355 Sept-Dec 2010 - w5d2

  11. Relationships • Recursive Relationship • “A relationship type where the same entity type • participates more than once in different roles.” (p 328) • Important to recognize different roles that entities may play, • since each role may lead to different data views. • NOTE: specifying roles may also be useful • to identify subsets of the instances of a given entity type • (e.g. only some staff are managers). CMPT 355 Sept-Dec 2010 - w5d2

  12. Modeling Entities & Relationships • E-R models start off simple, using: • Boxes to represent each entity type • Lines to represent each relationship • NOTE: it is best to indicate the direction of the name of relationships by using an arrowhead on all relationship lines • Words to name both entity types and relationships CMPT 355 Sept-Dec 2010 - w5d2

  13. Modeling Entities & Relationships • E-R models can model various situations • Complex relationships (of degree greater than 2) • can be illustrated with a diamond as their center • with the name of the relationship within the diamond • Remember: • only relationships of degree 2 should be used in CMPT 355 • You can always break down complex relationships into a series of degree 2 relationsips • Multiple relationships between two entity types • can be illustrated • by multiple different named relationship lines • between the two entities • NOTE: Only relationships involving different sets of entity occurrences are really different relationships • Recursive relationships • can be illustrated by the relationship • starting and ending at the same entity type box and • by indicating the two roles that it involves CMPT 355 Sept-Dec 2010 - w5d2

  14. Attributes • Attribute • “A property of an entity or a relationship type.” (p 329) • NOTES: • This goes along with our SQL concept of domains. • Based on this we see that relationships • may also be entities with their own data that belonging in their own table! • Attribute domain • “The set of allowable values • for one or more attributes.” (p 329) • NOTE: • This goes along with our SQL concept of domains. CMPT 355 Sept-Dec 2010 - w5d2

  15. Attributes • Simple attribute • “An attribute composed of a single component • with an independent existence.” (p 329) • NOTE: • This goes along with our SQL concept of attributes. • Composite attributes • “An attribute composed of multiple components, • each with an independent existence.” (p 330) • NOTE: • In SQL this would be implemented as a number of individual attributes. CMPT 355 Sept-Dec 2010 - w5d2

  16. Attributes • Single-valued attribute • “An attribute that holds a single value • for each occurrence of an entity type.” (p 330) • NOTE: • This goes along with our SQL concept of attributes. • Multi-valued attributes • “An attribute that holds multiple values • for each occurrence of an entity type.” (p 330) • NOTE: • In SQL this would be implemented as • a number of individual records in a separate table • that could be joined to the main table representing the entity type. CMPT 355 Sept-Dec 2010 - w5d2

  17. Attributes • Derived attributes • “An attribute that represents a value • that is derivable from the value • of a related attribute or set of attributes, • not necessarily in the same entity type.” (p 330) • NOTE: • Derived attributes • should not normally be stored in a database, • since they are considered redundant. • However, • if a derived attribute needs to remain constant, and • if the values that it is derived from may change, • then storing it is not redundant. CMPT 355 Sept-Dec 2010 - w5d2

  18. Keys - quickly • Keys are pretty much the same as for SQL: • Candidate key • “the minimal set of attributes that uniquely identifies each occurrence of an entity type.” (p 331) • Primary key • “the candidate key that is selected to uniquely identify each occurrence of an entity type.” (p 331) • Composite key • “a candidate key that consists of two or more attributes.” (p 33) CMPT 355 Sept-Dec 2010 - w5d2

  19. Modeling Attributes • Attributes can be modeled by • Dividing an entity type box horizontally in two • Placing the entity type name in the top • Listing the attributes in the bottom • An even better way of modeling an entity is • Dividing an entity type box horizontally in three • Placing the entity type name in the top • Listing attributes that compose the Primary Key in the middle • Listing the other attributes in the bottom CMPT 355 Sept-Dec 2010 - w5d2

  20. Modeling Attributes • Further attribute information can be modeled by: • Indicating composite attributes • by indenting their component attributes • beneath a descriptive name for the composite attribute • Designating constraints (such as being the primary key) • by annotations after their name • it is good to list primary key attributes first in your list of attributes • some people even separate primary key attributes from other attributes with a horizontal line (making the box have 3 rather than 2 horizontal sections) • Be careful not to make a single diagram too cluttered • for a user to be able to visualize • e.g. you do not need to identify a data type, since the attribute name should include the name of the domain to which it belongs. • It may be better to have separate high level and detailed diagrams CMPT 355 Sept-Dec 2010 - w5d2

  21. Entity Types • Strong entity type • “an entity type that • is non existence-dependent • on some other entity type.” (p 333) • In other words a real object. • E.g. a requisition • Weak entity type • “an entity type that • is existence-dependent • on some other entity type.” (p 333) • In other words an entity that serves to elaborate on a real object. • E.g. a requisition line CMPT 355 Sept-Dec 2010 - w5d2

  22. Structural Constraints • Multiplicity • “The number (or range) of possible occurrences • of an entity type that may relate to • a single occurrence of an associated entity type • through a particular relationship.” (p 335) • Each end of a relationship may involve • {0, 1, many} related entities. • Multiplicity is expressed • on each side of a relationship • by considering the {minimum and maximum} number • of that entity type that can be involved in the relationship. CMPT 355 Sept-Dec 2010 - w5d2

  23. Structural Constraints • Cardinality • “describes the maximum number • of possible relationship occurrences • for an entity participating in a given relationship type.” (p 340) • How many can an entity be related to? • Participation • “determines whether • all or only some occurrences participate in a relationship.” (p 341) • Does an entity have to be related to any? • Multiplicity is stated in terms of • {minimum .. maximum} • {participation .. cardinality} CMPT 355 Sept-Dec 2010 - w5d2

  24. Structural Constraints • Dealing with Multiplicity • Many to one or one to many relationships • Foreign keys can define a relationship • directly to 0 or 1 related entities • from any number of records • Attribute values in a single record can identify a partial relationship • to many entities • where the single value in one record can only be used as part of the keys of a number of records in a related table • Many to many relationships are ugly, • since they generally need an intermediary (table) to sort them out. CMPT 355 Sept-Dec 2010 - w5d2

  25. Modeling Multiplicity • {participation .. cardinality} • should be specified on each side of a relationship line • referring to the number of entities on that side of the relationship • that can enter into an instance of relationship • Reading a relationship • (if the arrow & relationship name of the relationship goes left to right) • Multiplicity from left side, Entity name from left side • Relationship name • Multiplicity from right side, Entity name from right side • Example (from p 341) • (1,,1) staff • manage (s) • (0,,1) branch CMPT 355 Sept-Dec 2010 - w5d2

  26. Simplifying Multiplicity • E-R diagrams should be examined • to identify any many to many relationships that occur • If these relationships cannot be readily accommodated by a suitable database query • they should be replaced by • a pair of {one to many / many to one} relationships • connecting the existing entity types • to a new (intermediary) entity type CMPT 355 Sept-Dec 2010 - w5d2

  27. Class Standard for Multiplicity • Always make multiplicity completely explicit • Text defaults are confusing • Sometimes assuming min and sometimes max • Do not use “assumed” defaults from text • (“just 1” or “just *” as stated in Table 12.1) CMPT 355 Sept-Dec 2010 - w5d2

More Related