1 / 45

Mastering Entity-Relationship Modeling for Efficient Database Design

Learn how to use ER modeling for effective database design, including entity types, relationship types, attributes, and structural constraints. Explore the diagrammatic techniques and build ER models from requirements specifications.

rtonia
Download Presentation

Mastering Entity-Relationship Modeling for Efficient Database Design

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. Chapter 12 Entity-Relationship Modeling

  2. Chapter 12 - Objectives • How to use Entity–Relationship (ER) modeling in database design. • Basic concepts associated with ER model. • Diagrammatic technique for displaying ER model using Unified Modeling Language (UML). • How to build an ER model from a requirements specification.

  3. ER diagram of Branch user views of DreamHome

  4. Concepts of the ER Model • Entity types (Rectangles) • Relationship types (Links) • Attributes (Names inside rectangles)

  5. Entity Type • Entity type (Class) • Group of objects with same properties, identified by enterprise as having an independent existence. • Entity occurrence (Instance of a Class; Object) • Uniquely identifiable object of an entity type.

  6. Examples of Entity Types (Nouns; appear in middle of box if alone, at top of box if attributes are listed)

  7. ER diagram of Staff and Branch entity types

  8. Relationship Types • Relationship type • Meaningful associations among entity types. • i.e., Link between classes • Name occurs as label on the link • Arrow indicates direction of relationship type • Relationship occurrence • Uniquely identifiable association, which includes one occurrence from each participating entity type. • i.e., Link between instances

  9. ER diagram of Branch Has Staff relationship type

  10. Semantic net of Has relationship occurrence

  11. Relationship Types • Degree of a Relationship • Number of participating entities in relationship. • Relationship of degree : • two is binary • three is ternary • four is quaternary.

  12. Binary relationship called POwns

  13. Ternary relationship called Registers (add diamond instead of label)

  14. Quaternary relationship called Arranges (add diamond instead of label)

  15. Relationship Types • Recursive Relationship • Relationship type where same entity type participates more than once in different roles. • Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship. • End up with 2 labels on arrow (one per role)

  16. Recursive relationship called Supervises with role names

  17. Entities associated through two distinct relationships with role names

  18. Attributes • Attribute • Property of an entity or a relationship type. • Line below relationship type name, separating name from attributes • Attribute Domain • Set of allowable values for one or more attributes.

  19. Attributes • Simple Attribute • Attribute composed of a single component with an independent existence. • Composite Attribute • Attribute composed of multiple components, each with an independent existence. • Indent components under attribute name

  20. Attributes • Single-valued Attribute • Attribute that holds a single value for each occurrence of an entity type. • Add name under relationship type name • Multi-valued Attribute • Attribute that holds multiple values for each occurrence of an entity type. • Add number n or range [n..m] after attribute name

  21. Attributes • Derived Attribute • Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type. • Add ‘/’ before attribute name • E.g., birthdate • /age • Composite Attribute • Indent the components

  22. Keys • Candidate Key (add {CK} or {AK} for alternate key after attribute name) • Minimal set of attributes that uniquely identifies each occurrence of an entity type. • Primary Key (add {PK} after attribute) • Candidate key selected to uniquely identify each occurrence of an entity type. • Composite Key (unknowable; put {PK} after both attributes) • A candidate key that consists of two or more attributes.

  23. ER diagram of Staff and Branch entities and their attributes

  24. Relationship called Advertises with attributes (Note: No name on the box for relationship attributes)

  25. Structural Constraints • Main type of constraint on relationships is called multiplicity. • Multiplicity - 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. • Represents policies (called business rules) established by user or company.

  26. Structural Constraints • The most common degree for relationships is binary. • Binary relationships are generally referred to as being: • one-to-one (1:1) • one-to-many (1:*) • many-to-many (*:*)

  27. Semantic net of Staff Manages Branch relationship type

  28. Multiplicity of Staff Manages Branch (1:1) relationship

  29. Semantic net of Staff Oversees PropertyForRent relationship type

  30. Multiplicity of Staff OverseesPropertyForRent(0:*) relationship type

  31. Semantic net of Newspaper Advertises PropertyForRent relationship type

  32. Multiplicity of Newspaper Advertises PropertyForRent (*:*) relationship

  33. Structural Constraints • Multiplicity for Complex Relationships • Number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed.

  34. Summary of multiplicity constraints

  35. Structural Constraints • Multiplicity is made up of two types of restrictions on relationships: cardinality and participation.

  36. Structural Constraints • Cardinality • Describes maximum number of possible relationship occurrences for an entity participating in a given relationship type. • Participation • Determines whether all or only some entity occurrences participate in a relationship.

  37. Problems with ER Models • Problems may arise when designing a conceptual data model called connection traps. • Often due to a misinterpretation of the meaning of certain relationships. • Two main types of connection traps are called fan traps and chasmtraps.

  38. ER Modeling Process • Get requirements from user • Create ER diagram (Conceptual modeling) • Nouns -> entities or attributes • Verbs -> relationships • Map from ER diagram to SQL tables (Logical modeling) • Entities -> tables • Attributes -> tables or attributes • Relationship attributes -> tables or attributes

  39. Example • Draw the ER Diagram for the following online registration system: • The university offers courses uniquely identified by department and course number. • There may be multiple sections of a given course in a particular semester, including no offerings at all. • Sections are taught by professors in rooms (identified by building number and room number). • Rooms have specific capacities, but sections may have lower capacity (unused seats). • Students enroll in sections.

  40. Example – Identify nouns • Draw the ER Diagram for the following online registration system: • The university offers courses uniquely identified by department and course number. • There may be multiple sections of a given course in a particular semester, including no offerings at all. • Sections are taught by professors in rooms (identified by building and room number). • Rooms have specific capacities, but sections may have lower capacity (unused seats). • Students enroll in sections.

  41. Example1 – Select entities (in red) (Rest are attributes or discarded) • Draw the ER Diagram for the following online registration system: • The university offers coursesuniquely identified by department and course number. • There may be multiple sectionsof a given course in a particular semester, including no offerings at all. • Sections are taught by professors in rooms (identified by building and room number). • Rooms have specific capacities, but sections may have lower capacity (unused seats). • Studentsenroll in sections.

  42. ER Model • Course: dept. name, course number, title, credit hours, description, … • Section: sectionId, semester, professor name, Student: studentId, name, major, … • EnrolledIn: relationship between Student-Section • Offered: relationship between Section-Course • Relationship attributes: building name, room number, room capacity

  43. Add Multiplicity • Student – Section • * : * • Course – Section • 1 : *

  44. Map to Tables • Course: dept. name, course number, title, credit hours, description, … • Section: sectionId, semester, professor name, • Student: studentId, name, major, … • Enrollment: studentID, sectionID • Room: building name, room number, room capacity • - or add these attributes to section

  45. Complete Logical Model • Add • Primary keys • Foreign keys • Constraints • Domains • etc

More Related