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

ER Design (2)

CMPT 354: Database I -- ER Design (2) 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) 3

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 B C A B C

CMPT 354: Database I -- ER Design (2) 4

Mapping Cardinalities Affects Design

• We can make access-date an attribute of account, instead
• f a relationship attribute, if each account can have only
• ne 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) 5

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) 6

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) 7

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) 8

Sub-Classes and ISA Relationships

CMPT 354: Database I -- ER Design (2) 9

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) 10

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) 11

E-R Diagram with Aggregation

CMPT 354: Database I -- ER Design (2) 12

Case Study – Student Info Sys

• Basic entities

– Students: name, address, department – Courses: department, title, description – Instructor: name, rank, office

student course instructor name address department title description department name rank

• ffice

CMPT 354: Database I -- ER Design (2) 13

Relationships

• Students take courses
• Instructors teach courses

student course instructor name address department title description department name rank

• ffice

take-course semester classroom teach time semester classroom time

CMPT 354: Database I -- ER Design (2) 14

Problem: Missing Entities

• Course offering is missing

– Repeated information: semester, classroom, time – Missing constraint: each class has one instructor

student course instructor name address department title description department name rank

• ffice
• ffering

semester classroom take-course time session teach

CMPT 354: Database I -- ER Design (2) 15

Adding Information

• More information about departments should be kept

– Name, location, chair

student course instructor name address title description name rank

• ffice
• ffering

semester classroom take-course time session teach department name location chair member-of major home-dept

CMPT 354: Database I -- ER Design (2) 16

Using Classes

• Current students and alumni

current_student course instructor title description name rank

• ffice
• ffering

semester classroom take-course time session teach department name location chair member-of major home-dept alumni graduate student name address isa

CMPT 354: Database I -- ER Design (2) 17

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) 18

UML Class Diagram Notation

CMPT 354: Database I -- ER Design (2) 19

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) 20

To-Do List

• Read Chapter 6 (except for Section 6.9)
• Draw the ER diagram for the Mini_TPC

database