THE ENTITY–RELATIONSHIP MODEL

THE

ENTITY–RELATIONSHIP MODEL

Developing the ERD

ER Diagrams, Naming Conventions, and Design Issues Example of Other Notation: UML Class Diagrams Relationship Types of Degree Higher than Two

Entity-Relationship Model 1

ER Diagrams, Naming Conventions,

and Design Issues

Entity-Relationship Model

Cardinality and Participation:

Alternate Representation

⚫

Original Notation

⚫

Alternate Notation: (min, max)

⚫ Min represents participation (0 partial, >0 total).

⚫ Max represents cardinality.

Employee Work

s

Project

Employee Work

s

Project 1

n

(1,1) (0,n)

Relationship Types of Degree Higher than Two

⚫

Degree of a relationship type

◦ Number of participating entity types

⚫

Binary

◦ Relationship type of degree two

⚫

Ternary

◦ Relationship type of degree three

Entity-Relationship Model 5

Relationships of Higher Degree

●

Relationship types of degree 2 are called binary

●

Relationship types of degree 3 are called ternary and of degree n are called n-ary

●

In general, an n-ary relationship is not equivalent

to n binary relationships

Instances in SUPPLY Ternary

Relationship

Entity-Relationship Model 7

Problem with constraints on higher order relationship types

SUPPLIER

PROJECT

PART SUP

PLI ES

What does it mean to put m:n:p on the three arms of the relationship ? It is essentially meaningless.

m n

p

Constraints on Ternary (or Higher-Degree) Relationships

⚫

Notations for specifying structural constraints on n-ary relationships

◦ Should both be used if it is important to fully

specify structural constraints

Entity-Relationship Model 9

The (min,max) notation for

higher order relationship type constraints

A Teacher can offer min 1 and max 2 Offerings A Course may have 1 to 3 Offerings

A Student may enroll in from 1 to 5 Offerings

TEACHER

STUDENT

COURS E OFF

ER

(1,2) (1,3)

(1,5)

Choosing between Binary and Ternary (or Higher-Degree) Relationships

⚫

Some database design tools permit only binary relationships

◦ Ternary relationship must be represented as a weak entity type

◦ No partial key and three identifying relationships

⚫

Represent ternary relationship as a regular entity type

◦ By introducing an artificial or surrogate key

Entity-Relationship Model 11

TERNARY RELATIONSHIPS

© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, Fundamentals of Database Systems, Second Edition

Entity-Relationship Model 12

Data Modeling Tools

A number of popular tools that cover conceptual modeling and mapping into relational schema design.

POSITIVES: Serves as documentation of application requirements, easy user

interface - mostly graphics editor support

Example of Other Notation:

UML Class Diagrams

⚫

UML methodology

◦ Used extensively in software design, esp OOP

◦ Many types of diagrams for various software design purposes

⚫

UML class diagrams

◦ Entity in ER corresponds to an object in UML

Example of Other Notation:

UML Class Diagrams (cont’d.)

⚫

Class includes three sections:

◦ Top section gives the class name

◦ Middle section includes the attributes;

◦ Last section includes operations that can be

applied to individual objects

Example of Other Notation:

UML Class Diagrams (cont’d.)

⚫

Associations: relationship types

⚫

Relationship instances: links

⚫

Binary association

◦ Represented as a line connecting participating classes

◦ May optionally have a name

⚫

Link attribute

◦ Placed in a box connected to the association’s

line by a dashed line

Example of Other Notation:

UML Class Diagrams (cont’d.)

⚫

Multiplicities: min..max, asterisk (*) indicates no maximum limit on

participation

⚫

Types of relationships: association and aggregation

⚫

Model weak entities using qualified

association

1/19/2017 18

Computer-Aided System Engineering (CASE)

⚫ Automated tools to improve the speed and quality of system development work

⚫ Maintains a repository

◦ Database of information about system

◦ Diagrams, text, prototypes, etc.

⚫ Uses knowledge of a particular methodology

◦ Draws models

◦ Checks correctness and consistency of models

◦ May automatically generate DB schemas, or even code

◦ Rational Rose is an OO Development CASE tool

1/19/2017 19

CASE Tool Repository

Contains all System Information

1/19/2017 20

Rational Rose Showing OO Diagrams

Summary

⚫

Basic ER model concepts of entities and their attributes

◦ Different types of attributes

◦ Structural constraints on relationships

⚫

ER diagrams represent E-R schemas

⚫

UML class diagrams are OOA models

….But how do you develop an ERD?

Database Design

⚫

Requirements collection and analysis

◦ Database designers interview prospective

database users to understand and document data requirements

◦ Result: data requirements

◦ Functional requirements of the application

Continental Palms Retail

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory.

⚫ CPR has several warehouses in several cities across the country.

⚫ Each warehouse is uniquely named.

⚫ CPR also wants to record the location, city, state, zip, and space (in cubic meters).

⚫ There are at most ten warehouses in any single city.

⚫ CPR stores various kinds of products in the warehouses.

⚫ A product may be stored in different warehouses.

⚫ Every product has a unique UPC number.

⚫ Other information about a product includes a name, a buying price, an approximate selling price, a size (in cubic meters), and a total quantity.

⚫ CPR also keeps track of the information about the manufacturers of products.

⚫ Every product has a single manufacturer.

⚫ Each manufacturer has a name, an address (street, city, state, zip), and a phone number.

23

Proper Naming of Schema Constructs

⚫

Choose names that convey meanings

attached to different constructs in schema

⚫

Nouns give rise to entity type names

⚫

Verbs indicate names of relationship types

⚫

Choose binary relationship names to

make ER diagram readable from left to

right and from top to bottom

Nouns → possible entities or attributes

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory.

⚫ CPR has several warehouses in several cities across the country.

⚫ Each warehouse is uniquely named.

⚫ CPR also wants to record the location, city, state, zip, and space (in cubic meters).

⚫ There are at most ten warehouses in any single city.

⚫ CPR stores various kinds of products in the warehouses.

⚫ A product may be stored in different warehouses.

⚫ Every product has a unique UPC number.

⚫ Other information about a product includes a name, a buying price, an approximate selling price, a size (in cubic meters), and a total quantity.

⚫ CPR also keeps track of the information about the manufacturers of products.

⚫ Every product has a single manufacturer.

⚫ Each manufacturer has a name, an address (street, city, state, zip), and a phone number.

25

Verbs → relationships

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory.

⚫ CPR has several warehouses in several cities across the country.

⚫ Each warehouse is uniquely named.

⚫ CPR also wants to record the location, city, state, zip, and space (in cubic meters).

⚫ There are at most ten warehouses in any single city.

⚫ CPR stores various kinds of products in the warehouses.

⚫ A product may be stored in different warehouses.

⚫ Every product has a unique UPC number.

⚫ Other information about a product includes a name, a buying price, an approximate selling price, a size (in cubic meters), and a total quantity.

⚫ CPR also keeps track of the information about the manufacturers of products.

⚫ Every product has a single manufacturer.

⚫ Each manufacturer has a name, an address (street, city, state, zip), and a phone number.

26

Preliminary notes on ERD

Distinguish between entity and attribute

WAREHOUSE (Wname, address, city, zip, cubic-meters)

CITY (cname, state)

PRODUCT (UPC, pname, buy-price, sell-price, size, qty, manufacturer)

MANUFACTURER (mname, address, city, zip, phone)

27

ER Conceptual Design

⚫

Attribute that exists in several entity

types may be elevated to an independent entity type

◦ Change attributes that represent relationships into relationship types

WAREHOUSE (Wname, address, ^city, zip, cubic-meters) CITY (cname, state)

PRODUCT (UPC, pname, buy-price, sell-price, size, qty,

manufacturer)

MANUFACTURER (mname, address, ^city, zip, phone)

Can also be applied in the inverse

Refining the ER Design

1.

Identify key attributes

WAREHOUSE (Wname, address, zip, cubic-meters) CITY (cname, state)

PRODUCT (UPC, pname, buy-price, sell-price, size, qty) MANUFACTURER (mname, address, zip, phone)

2.

Determine cardinality and participation of each relationship

Does a City have to have a Warehouse?

Does a Product have to have a Manufacturer?

Draw the diagram

30

:

Entities

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

Draw the diagram

31

:

Relationships

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

LOCATED

LOCATED STORED

MADE BY

Draw the diagram

32

:

Attributes

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

LOCATED

LOCATED STORED

MADE BY pname

buy-$

Sell-$

UPC

qty size

wname size cname

address zip

state

phone address zip

mname

Draw the diagram

33

:

Participation

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

LOCATED

LOCATED STORED

MADE BY pname

buy-$

Sell-$

UPC

qty size

wname size cname

address zip

state

phone address zip

mname

Draw the diagram

34

:

Cardinality

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

LOCATED

LOCATED STORED

MADE BY pname

buy-$

Sell-$

UPC

qty size

wname size cname

address zip

state

phone address zip

mname

n n

1 0 m

1

1

1 n

Improve the diagram?

35

WAREHOUS E

CIT Y

PRODUCT MANUFACTURE

R

LOCATED

LOCATED STORED

MADE BY pname

buy-$

Sell-$

UPC

qty size

wname size cname

address zip

state

phone address zip

mname

n n

1 0 m

1

1

1 n

Composite attribute?

Derived attribute?

Weak entity?

What Now

⚫

Complete ERII worksheet

◦ Turn-in

⚫

Checkpoint 01 due Feb 2

(Feb 3

)

◦ Will include an ERD of B&B

◦ Worksheets are there to help you

⚫

Checkpoint 02 due Feb 21

(Feb 22

)

◦ Updated ERD

◦ Plus, relational schema, relational algebra

36

37

CUSTOME

R ADDRESS

SERVICE APPT

MAT’L/

SUPPLY EMPLOYEE

make

for have

get

provide use

email

name

date

time

street

city

state

zip

id

amt id

name

type

amt

n

n n

m m

n m

n

1

n

n 1

n have m