THE ENTITY– RELATIONSHIP MODEL

(1)

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

(2)

ER Diagrams, Naming

Conventions, and Design Issues

(3)

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 Works Project

1 n

(1,1) (0,n)

(4)

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

(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

(6)

Instances in SUPPLY Ternary

Relationship

(7)

Problem with constraints on

higher order relationship types

SUPPLIER

PROJECT

PART SUPPLIES

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

m n

p

(8)

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

(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

COURSE OFFER

(1,2) (1,3)

(1,5)

(10)

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

(11)

TERNARY RELATIONSHIPS

(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

(13)

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

(14)

(15)

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

(16)

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

(17)

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

(18)

12/06/2021 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

(19)

12/06/2021 19

CASE Tool Repository

Contains all System Information

(20)

12/06/2021 20

Rational Rose Showing OO Diagrams

(21)

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?

(22)

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

(23)

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

(24)

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

(25)

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 also wants to record the location, city, state, zip, and space (in cubic meters).

 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

(26)

Verbs  relationships

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

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

 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

(27)

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

(28)

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

(29)

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?

(30)

Draw the diagram

30

: Entities

WAREHOUSE CITY

PRODUCT MANUFACTURER

(31)

Draw the diagram

31

: Relationships

WAREHOUSE CITY

LOCATED

LOCATED STORED

MADE BY

(32)

Draw the diagram

32

: Attributes

WAREHOUSE CITY

LOCATED

LOCATED STORED

MADE BY pnam

e

buy-$

Sell-$

UPC

qty size

wnam e

cnam e size

addre zip ss

state

phone addre zip

ss mnam

e

(33)

Draw the diagram

33

: Participation

WAREHOUSE CITY

LOCATED

LOCATED STORED

MADE BY pnam

e

buy-$

Sell-$

UPC

qty size

wnam e

cnam e size

addre zip ss

state

phone addre zip

ss mnam

e

(34)

Draw the diagram

34

: Cardinality

WAREHOUSE CITY

LOCATED

LOCATED STORED

MADE BY pnam

e

buy-$

Sell-$

UPC

qty size

wnam e

cnam e size

addre zip ss

state

phone addre zip

ss mnam

e

n n

10 m

1

1 1

n

(35)

Improve the diagram?

35

WAREHOUSE CITY

LOCATED

LOCATED STORED

MADE BY pnam

e

buy-$

Sell-$

UPC

qty size

wnam e

cnam e size

addre zip ss

state

phone addre zip

ss mnam

e

n n

10 m

1

1 1

n

Composite attribute?

Derived attribute?

Weak entity?

(36)

What Now



Complete ERII worksheet

◦Turn-in



Checkpoint 01 due Feb 2

^nd

(Feb 3

^rd

)

◦Will include an ERD of B&B

◦Worksheets are there to help you



Checkpoint 02 due Feb 21

^st

(Feb 22

^nd

)

◦Updated ERD

◦Plus, relational schema, relational

algebra ³⁶

(37)

37

(38)

CUSTOME

R ADDRESS

SERVICE APPT

MAT’L/

SUPPLY EMPLOYEE

mak e

for hav

e

get

provid use

e ema

il

nam e

date

time

stree t

city

state

zip

id

amt id

nam e

type

amt

n

n n

m m

n m

n

1

n

n 1

hav e

n m