Data Modeling with ERD

1
Data Modeling with ERD

• ISYS 363

2
Entities

• An entity is a person, place, object, event, or
  concept in the user environment about which the
  organization wishes to maintain data.
• Person Employee, Student, patient
• Place Warehouse, Store
• Object Product, Machine.
• Event Registration, Sale, Renewal
• Concept Account, Course
• Physical existence
• Customer, student, product, etc.
• Conceptual existence
• Bank accounts, sale

3
Entity Type

• A collection of entities that share common
  properties or characteristics.
• An entity type represents a collection of
  entities.
• A business environment may involve many entity
  types.
• University Faculty, Student, Course
• Department, Employee, Dependent
• Sales person, Customer, Order

4
Relationship

• Relationship Interaction between entity types.
• Faculty teach Course, Faculty advise Student
• Customer open Account, Customer purchase Product.
• Binary relationship A relationship involves two
  entity types.
• Three types of binary relationship
• 11, 1M, MM

5
Entity-Relationship Diagram

• ER modeling begins by identifying the entities
  and relationships between entities that must be
  represented in the model.
• In an ERD, an entity type is represented by a
  rectangle labeled with a singular name.
• A relationship has a verb phrase name
• Faculty teach Course, Faculty advise Student

6
MM Relationship
Peter Paul John Woody Alan
Mary Linda Nancy Mia Pia
Girl
Boy
A boy may date 0, 1, or many girls. A girl may
date 0, 1, or many boys. Note Many boys date
many girls is not a correct interpretation.
7
11 Relationship
Peter Paul John Woody Alan
Mary Linda Nancy Mia Pia
Man
Woman
A man may marry 0 or 1 woman. A woman may marry 0
or 1 man.
8
1M Relationship
Peter Paul John Woody Alan
Mary Brian Linda Aron Nancy Ronald Mia Pia
Father
Child
A father has 1 or many children. A child has 1
father.
9
Other Examples

• 11
• State, State Governor
• Order, Invoice
• 1M
• Department, Employee
• Customer, Order
• MM
• Bank customer, Bank account
• Student, Student organization

10
ERD Notations
1
1
Student
Account
Has
M
M
M
Enroll
Advise
1
M
Faculty
1
Course
Teach
11
Alternative Notations
Has
Student
Account
Enroll
Advise
Faculty
Teach
Course
12
Attributes

• Properties of an entity or a relationship.
• Simple attributes
• AddressStreet address, City, State, ZipCode
• Single-valued attributes
• Derived attributes
• Primary key

13
FullName
DateOfBirth
SID
Lname
Fname
Age
Student
14
Domains of Attributes

• The set of allowable values for one or more
  attributes.
• Input validation
• Examples
• Sex F, M
• EmpHourlyWage Between 6 and 300
• EmpName 50 charcters

15
Introduction to Relational Database

• Data is logically structured within relations.
• Each relation is a table (file) with named
  columns (attributes, fields) and rows (records).

16
Properties of a Relation

• Simple attribute
• No composite, no multivalued attribute
• Each relation must have a primary key
• Simple or composite key
• May have other keys (candidate keys)
• Key cannot be null
• Cannot be duplicated

17
Relational Database Design

• Create a table for each entity that includes all
  simple attributes
• Relationship
• 11, 1M
• Relationship table
• Foreign key
• MM relationship table

18
Database Design Example

• University
• 11 Relationship
• Student and Account
• What is Peters account balance
• Find students that owe more than 2000 dollars.
• 1M Relationship
• Faculty Advisor and student
• Who is Peters advisor?
• How many students advised by Chao?
• MM Relationship
• Student and course
• How many units Peter takes?
• Find students that are taking 363.

19
Database Design Example

• Bank
• Customer, Account, bank employee

20
Database Design Example

• Order Processing
• Customer, Order, Product

21
Advanced Topics

• Composite key
• Multivalued attribute
• Students Major attribute
• Facultys DegreeEarned attribute
• Vehicles Color attribute
• Others PhoneNumber, EmailAddress
• Create a table for each multi-valued attribute
• Key attribute
• Attributes on a relation

22
Online Shopping Cart
Addr
CartID
Date
CID
Cname
Phone
ShoppingCart
Has
M
Customer
1
M
Has
M
Product
Price
PID
Pname
23
Online Shopping Cart
Addr
CartID
Date
CID
Cname
Phone
ShoppingCart
Has
M
Customer
1
M
Qty
Has
M
Product
Price
PID
Pname
24
Attributes on Relationship

• Examples
• Student/Course Grade
• Order/Product Quantity