450 likes | 454 Views
Chapter 12. Entity-Relationship Modeling. 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).
E N D
Chapter 12 Entity-Relationship Modeling
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.
Concepts of the ER Model • Entity types (Rectangles) • Relationship types (Links) • Attributes (Names inside rectangles)
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.
Examples of Entity Types (Nouns; appear in middle of box if alone, at top of box if attributes are listed)
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
Relationship Types • Degree of a Relationship • Number of participating entities in relationship. • Relationship of degree : • two is binary • three is ternary • four is quaternary.
Ternary relationship called Registers (add diamond instead of label)
Quaternary relationship called Arranges (add diamond instead of label)
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)
Entities associated through two distinct relationships with role names
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.
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
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
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
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.
ER diagram of Staff and Branch entities and their attributes
Relationship called Advertises with attributes (Note: No name on the box for relationship attributes)
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.
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 (*:*)
Semantic net of Staff Oversees PropertyForRent relationship type
Multiplicity of Staff OverseesPropertyForRent(0:*) relationship type
Semantic net of Newspaper Advertises PropertyForRent relationship type
Multiplicity of Newspaper Advertises PropertyForRent (*:*) relationship
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.
Structural Constraints • Multiplicity is made up of two types of restrictions on relationships: cardinality and participation.
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.
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.
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
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.
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.
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.
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
Add Multiplicity • Student – Section • * : * • Course – Section • 1 : *
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
Complete Logical Model • Add • Primary keys • Foreign keys • Constraints • Domains • etc