ERD Fundamentals

1
ERD Fundamentals
2
Objectives

• Define entity, relationship, attribute,
  cardinality
• Create ER diagrams with correct notation
• Map ER diagrams to tables

3
Agenda

• Discuss ERD basics
• ERD problems review
• Mapping ERDs to actual tables
• Quiz

4
Building Models

• Why are models useful?
• Entity relationship diagram (ERD) is one of most
  popular approaches to data modeling
• Shows entities, attributes, and relationships
  among entities

5
Entities

• Entity is any object or concept about which we
  want to store data
• Person (Customer, Supplier)
• Thing (Order, Item)
• Event
• Place
• Test for entities
• More than one instance
• More than one attribute

6
Relationships

• Naturally occurring associations between entities
• Customer orders Order
• Order has Item
• Supplier supplies Item

7
Simple ERD
8
Attributes

• Characteristics that describe entities
• Customer Number, Name, Phone
• Supplier Number, Name, Phone
• Order Number, Date
• Item Number, Description, Price

9
Primary Keys

• Primary key (PK) is an attribute or set of
  attributes that can be used to identify
  individual instances of an entity
• The value of a primary key is unique for each
  instance

10
Diagramming Attributes
Entity Name
Attributes
11
Cardinality

• Maximum number of occurrences of one entity for a
  single occurrence of the related entity
• E.g. Customers to orders, customers to invoices

12
Cardinality Symbols

• What do the following represent?

13
Cardinality Symbols

• What do the following represent?

14
Cardinality

• Example
• Think Each instance of ENTITY1 can
  RELATIONSHIP at most how many instances of
  ENTITY2s?
• Each CUSTOMER can ORDER many ORDERS, and each
  ORDER is ORDERED BY at most one CUSTOMER

15
Cardinality Exercise
Item
Order
supplied by
has
Item
Supplier
16
Cardinality Exercise Solution
Item
Order
supplied by
has
Item
Supplier
17
MM Relationships

• What is the problem with MM relationships in the
  database?

Order
has
Item
18
Order System ERD
Item ItemNo(PK) Desc Price
Order OrderNo(PK) OrderDate
Has
Supplies
Orders
Supplier SupplierNo(PK) SupplierName SupplierPhone
Customer CustomerNo(PK) CustomerName CustomerPhone
19
Relational Entities
has
Order-Item
has
20
Dons Donuts Example

• Dons Donuts orders several products from various
  vendors. They have a contact person at each
  vendor site, along with all address information.
  Each purchase order has the order number, date,
  tax, and total. Product information includes
  stock number, description and price. Dons
  Donuts sends a check for payment and records the
  check date.

21
Dons Donuts Simple ERD
Paid
Ordered from
Order
Filled
Payment
Vendor
Product
22
Dons Donuts ERD
Order OrderNo(PK) Tax Total OrderDate
Paid
Ordered from
Filled
Vendor VendorNo(PK) VendorName VendorAddress Vendo
rPhone VendorContact
Payment CheckNo(PK) CheckDate
Product StockNo(PK) Desc Price
23
Goldstar ERD
Employee SSN(PK) LName Fname Salary Gender Perform
ance
Works in
Holds
Position Title(PK) EdReqd MinSal MaxSal
Location City(PK) Street State ZIP Phone
24
Do-ahead review
25
Mapping an ERD to a Schema
26
Entity to Table
Customer
27
Notation
Customer
represented as
Customer(CustomerNo(PK), CustomerName,CustomerPhon
e)
28
Mapping Sequence

• Map entities
• Map relationships
• 1M
• 11
• MM

29
Map Entities

• Create a table for the entity
• Name of the table is the name of the entity
• Columns of the table are the attributes of the
  entity
• Primary key of the table is the primary key of
  the entity
• Note a foreign key is the PK from another
  table, to link two tables together

30
Examples of Entities

• Customer
• Order
• Item
• Supplier

31
Map Non-MM Relationships

• For each relationship in which one sides
  cardinality is 1 and the other is any cardinality
  (1 or M), you will use the primary key of one of
  the entities to create a new column in the other
  table
• if the cardinality is 1M, create the new column
  in the table on the M side (the primary key of
  the 1 side becomes a foreign key in the M side)
• if the cardinality is 11, check to see if there
  is optional participation on one side if so,
  create the new column in the table on the
  optional side (the primary key of the required
  side becomes a foreign key in the optional side)
• If a primary key of a table is used in another
  table, it is a foreign key of the second table.

32
Examples of Relationships

• Customer orders Order
• Order has Item
• Supplier supplies Item

33
Map MM Relationships

• For all other relationships
• table name is the name of the relationship
• columns of the table are the attributes of the
  relationship plus primary keys of all the
  entities participating in the relationship(these
  primary keys of the entities become foreign keys
  in the new table created from the relationship)
• primary key of the table is the primary keys of
  all participating entities

34
Order System ERD
Item ItemNo(PK) Desc Price
Order OrderNo(PK) OrderDate
Has
Supplies
Orders
Supplier SupplierNo(PK) SupplierName SupplierPhone
Customer CustomerNo(PK) CustomerName CustomerPhone
35
Order System Tables

• Customer(CustomerNo(PK), CustomerName,
  CustomerPhone)
• Order(OrderNo(PK), OrderDate, CustomerNo(FK))
• Item(ItemNo(PK), Desc, Price, SupplierNo(FK))
• Supplier(SupplierNo(PK), SupplierName,
  SupplierPhone)
• Order-Item(OrderNo(PK/FK), ItemNo(PK/FK))
• Use (PK) for primary keys, (FK) for foreign keys

36
Dons Donuts ERD
Order OrderNo(PK) Tax Total OrderDate
Paid
Ordered from
Filled
Vendor VendorNo(PK) VendorName VendorAddress Vendo
rPhone VendorContact
Payment CheckNo(PK) CheckDate
Product StockNo(PK) Desc Price
37
Dons Donuts Tables

• Vendor(VenderNo(PK), VenderName, VendorAddress,
  VendorPhone, VendorContact)
• Order(OrderNo(PK), Tax,Total, OrderDate,
  VenderNo(FK))
• Product(StockNo(PK), Desc, Price)
• Payment(CheckNo(PK), Check_date)
• Filled(OrderNo(PK/FK), StockNo(PK/FK))
• Paid(CheckNo(PK/FK), OrderNo(PK/FK) )

38
Goldstar ERD
Employee SSN(PK) LName Fname Salary Gender Perform
ance
Works in
Holds
Position Title(PK) EdReqd MinSal MaxSal
Location City(PK) Street State ZIP Phone
39
Goldstar Tables

• Position (Title(PK), EdReqd, MinSal, MaxSal)
• Employee (SSN(PK), LName, FName, Salary, Gender,
  Performance, Title(FK), City(FK))
• Location (City(PK), Street, State, ZIP, Phone)

40
Quiz