The Entity-Relationship Model

C SC 343 -- Introduction to Databases

The Entity-Relationship Model

Part II

The Entity-Relationship Model -- 1 C SC 343 -- Introduction to Databases

ER Cardinality Examples

The Entity-Relationship Model -- 2

1

C SC 343 -- Introduction to Databases

Textbook Notation

...for (1,1) cardinalities (key constraints)

Person

Residence

City

The Entity-Relationship Model -- 3

C SC 343 -- Introduction to Databases

Examples of Keys

(internal) single-attribute key (unary key)

Slides Part 1 (UML inspired) notation

AUTOMOBILE

Registration Model

Color

Text notation

The Entity-Relationship Model -- 4

2

C SC 343 -- Introduction to Databases

Examples of Keys

(Internal) multi-attribute (n-ary) key

PERSON

DateOfBirth Surname

FirstName Address

The Entity-Relationship Model -- 5

C SC 343 -- Introduction to Databases

Examples of Keys

foreign, multi-attribute key (aka weak entity set)

Registration

Year

STUDENT

ENROLMENT

UNIVERSITY

Name City

Surname

Address

?Arrow on line indicates max cardinality of 1 (key) ?Double (or thick) line indicates min cardinality of 1

?aka participation constraint

The Entity-Relationship Model -- 6

3

C SC 343 -- Introduction to Databases

An Entity Hierarchy

isA

isA isA isA

isA

The Entity-Relationship Model -- 7

Aggregation

C SC 343 -- Introduction to Databases

Used when we have to model a relationship involving (entity sets and) and a relationship set.

Aggregation allows us to treat a relationship set as an entity set for purposes of participation in other relationships.

The Entity-Relationship Model -- 8

4

An Example

C SC 343 -- Introduction to Databases

Aggregation vs. ternary relationship? Monitors is a distinct relationship, with a descriptive attribute. Also, can say that each sponsorship is monitored by at most one employee.

name

ssn

lot

Employees

Monitors (0,1)

until

started_on

since

pid

pbudget

did

dname budget

Projects

Sponsors

Departments

The Entity-Relationship Model -- 9

C SC 343 -- Introduction to Databases

Conceptual Design Using the ER Model

Design choices: Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Identifying relationships: Binary or ternary? Aggregation?

Note constraints of the ER Model: A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams. We'll refine things in our logical (relational) design

The Entity-Relationship Model -- 10

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download