Entity Relationship Diagrams Used for conceptual data modeling

Entity Relationship Diagrams • Used for conceptual data modeling - part of database design • Represents: – the data that is important – the structure of the data – the relationships between different pieces of data • Does NOT show process, roles, users, etc.

Basic Elements • Entities – people, places, things – entity type is a set of things all of which have the same data properties – entity instance is one concrete example of the entity type – things that the system keeps track of, NOT things that are part of the system

Basic Elements (cont. ) • Relationships – show instances of one entity are related to instances of another entity – have cardinality and degree • Attributes – single pieces of data that describe an instance of an entity – like data elements

street SSN name city CUSTOMER owner type Has account number ACCOUNT balance Basic ER Diagram

Last Maiden Middle Title First Suffix Name CUSTOMER Composite Attribute

Last Maiden Middle Title First Suffix Name Deductions EMPLOYEE Multivalued Attribute

Effective Date EMPLOYEE Fills POSITION Relationship with an Attribute

Effective Date EMPLOYEE Employment History POSITION Associative Entity

child parent person parent Unary Relationship

type Talent training Show dates name Performer address Performs location Ternary Relationship

type Talent name training Has Used in Performer Show address Performs in dates location Three Binary Relationships

Effective Date EMPLOYEE Fills POSITION Cardinality Constraints

account# balance U savings account U account checking account max overdraft interest rate A supertype and subtypes

model number make price LENS fits uses CAMERA film type FLASH requires quantity BATTERY size type

model number price PHOTO EQUIPMENT U LENS fits U U make CAMERA uses film type FLASH requires quantity BATTERY size type

SSN Date. Enrolled Degree STUDENT GRADUATE Graduation Date Grade TRANSCRIPT Date COURSE

Date. Enrolled SSN Grade TRANSCRIPT STUDENT Date COURSE U U Degree CURRENT FORMER Generalization Graduation Date

SUPPLIER Wastage Supplied. By Has Ingredients ITEM Quantity Part of a grocery store database

Quantity Has Ingredients ITEM U U U BAKERY ITEM SUPPLIED ITEM Supplied. By PERISHABLE ITEM Specialization SUPPLIER Wastage

DOB PRIMATE Origin Lives. In BIRD HABITAT Lives. In Food Lives. In 4 -LEGGED ANIMAL REPTILE Origin DOB Origin Food Part of a Zoo database Origin

Origin Food DOB HABITAT ANIMAL U U U Lives. In U PRIMATE BIRD REPTILE 4 -LEGGED ANIMAL Specialization

Origin DOB Food HABITAT ANIMAL Lives. In Class Replacing a set of subtypes with an attribute

model number price PHOTO EQUIPMENT d “lens” “camera” fits U U “flash” U LENS make type = type uses CAMERA film type requires Disjoint specialization with discriminator FLASH quantity BATTERY size type

MOTHER Has OFFSPRING U U d BAD CHILDREN GOOD CHILDREN Total, disjoint specialization

MOTHER Has Sent. Card OFFSPRING Sent. Card = U GOOD CHILDREN d “No” U “Yes” BAD CHILDREN Total, disjoint specialization with discriminator

Date. Enrolled SSN Grade Enrolled? TRANSCRIPT STUDENT Status Date Status: Graduated? COURSE o Enrolled=“Y” U CURRENT Graduated=“Y” U FORMER Degree Graduation Date Overlapping specialization with discriminator