ER Design (2) What Can We Do? We Already Know How to Model Roles - PDF document

ER Design (2)

What Can We Do? • We Already Know How to Model – Roles – Cardinality constraints – Participation – Choice of entity sets attributes, or relationship sets – Binary and non-binary relationships • However, there are more complicated situations … CMPT 354: Database I -- ER Design (2) 2

Translating Constraints • Translating all constraints may not be possible – there may be instances in the translated schema that cannot correspond to any instance of R – Many-to-one from A and B to C cannot be translated if the ternary relationship is converted into 3 binary ones A A B C B C CMPT 354: Database I -- ER Design (2) 3

Mapping Cardinalities Affects Design • We can make access-date an attribute of account, instead of a relationship attribute, if each account can have only one customer – Then, the relationship from account to customer is many to one, or equivalently, customer to account is one to many CMPT 354: Database I -- ER Design (2) 4

Weak Entity Sets • An entity set that does not have a primary key – The existence of a weak entity set depends on the existence of an identifying entity set CMPT 354: Database I -- ER Design (2) 5

Weak Entity Sets (Cont.) • A weak entity set must relate to the identifying entity set via a total, one-to-many relationship set from the identifying relationship set to the weak entity set – The identifying relationship set depicted using a double diamond • The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set • The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the discriminator of the weak entity set CMPT 354: Database I -- ER Design (2) 6

One More Example • In a university, a course is a strong entity and a course_offering can be modeled as a weak entity – The discriminator of course_offering would be semester (including year) and section_number (if there is more than one section) • How to draw the ER diagram? CMPT 354: Database I -- ER Design (2) 7

Sub-Classes and ISA Relationships CMPT 354: Database I -- ER Design (2) 8

Aggregation: Motivation • Consider the ternary relationship works_on , which we saw earlier. Suppose we want to record managers for tasks performed by an employee at a branch CMPT 354: Database I -- ER Design (2) 9

Aggregation • Relationship sets works_on and manages represent overlapping information – Every manages relationship corresponds to a works_on relationship – However, some works_on relationships may not correspond to any manages relationships • So we can’t discard the works_on relationship • Eliminate this redundancy via aggregation – Treat relationship as an abstract entity – Allows relationships between relationships – Abstraction of relationship into new entity CMPT 354: Database I -- ER Design (2) 10

E-R Diagram with Aggregation CMPT 354: Database I -- ER Design (2) 11

Case Study – Student Info Sys • Basic entities – Students: name, address, department – Courses: department, title, description – Instructor: name, rank, office name address department department title description student name rank office course instructor CMPT 354: Database I -- ER Design (2) 12

Relationships • Students take courses • Instructors teach courses name address department department title description student name rank office take-course course teach instructor semester classroom time semester classroom time CMPT 354: Database I -- ER Design (2) 13

Problem: Missing Entities • Course offering is missing – Repeated information: semester, classroom, time – Missing constraint: each class has one instructor department title description name address department course name rank office student take-course session teach offering instructor semester classroom time CMPT 354: Database I -- ER Design (2) 14

Adding Information • More information about departments should be kept – Name, location, chair name location chair title description department home-dept course name address member-of major take-course student session teach offering instructor name rank office semester classroom time CMPT 354: Database I -- ER Design (2) 15

Using Classes • Current students and alumni home-dept name location chair title description graduate department name address course alumni isa student member-of major take-course current_student session teach offering instructor name rank office semester classroom time CMPT 354: Database I -- ER Design (2) 16

UML • UML: Unified Modeling Language • UML has many components to graphically model different aspects of an entire software system • UML Class Diagrams correspond to E-R Diagram, but several differences CMPT 354: Database I -- ER Design (2) 17

UML Class Diagram Notation CMPT 354: Database I -- ER Design (2) 18

Summary • Model real world data using entities and relationships • The ER model • ER diagrams – Entities, relationships, attributes – Constraints, keys, cardinalities – Weak entities CMPT 354: Database I -- ER Design (2) 19

To-Do List • Read Chapter 6 (except for Section 6.9) • Draw the ER diagram for the Mini_TPC database CMPT 354: Database I -- ER Design (2) 20