Concepts of Database Management Seventh Edition

1
Concepts of Database ManagementSeventh Edition

• Chapter 6
• Database Design ERD Model

2
Objectives

• Discuss the general process and goals of database
  design
• Identify the different symbols used in ERD
• Identify cardinality symbols to used for
  different entity relationship types
• Create an entity-relationship (E-R) diagram to
  visually represent a database design

3
Objectives (continued)

• Explain the physical-level design process
• Discuss top-down and bottom-up approaches to
  database design and examine the advantages and
  disadvantages of both methods
• Use a survey form to obtain information from
  users prior to beginning the database design
  process
• Review existing documents to obtain information
  prior to beginning the database design

4
The Entity-Relationship Model

• is modeling tool used to depict graphically a
  database design before it is actually
  implemented.
• It has three basic components, namely, an Entity,
  Relationship and an Attribute.
• And Relationship has Cardinality (as we will see
  more in a moment)

5
Introduction

• Two-step process for database design
• Information-level design completed independently
  of any particular DBMS
• Physical-level design information-level design
  adapted for the specific DBMS that will be used
• Must consider characteristics of the particular
  DBMS

6
Building Blocks of ERD
Type English Grammar Equivalent Example
Entity Proper Noun Student, Employee, Instructor, Courses, Room
Relationship Verb has, teaches, belongs, handles
Attribute Adjective Height, Age, Gender, Nationality, First name
7
ERD Popular Notation

• Chen Notation
• Crows Foot Notation

8
Chen Notation - Symbol
Rectangle represents an Entity
Diamond represents a Relationship
1
M
Lines with labels represents Cardinality
9
Entity (Chen Notation)

• is a real-world object distinguishable or unique
  from other objects.
• An entity can be a concrete or physical object
  like employee, student, faculty, customer etc. Or
  it could also be conceptual or abstract like
  transaction, order, course, subjects etc.
• It can be thought of as a noun like student,
  employee etc.
• It is normally represented by a rectangle shape.

10
Database Background

• Remember in Chapter 1 Entity could be a

Person
(ex. Teacher, Student, Physician)
Place
(ex. School, Hotel, Store )
Object
(ex. Mouse, Books, Bulding )
Event
(ex. Enroll, Withdraw, Order )
Idea or Concept
(ex. Courses, Account, Delivery )
11
Entity - Example

• For example in our Premiere Database the
  different Entities are the following

Customer
Sales Rep
Order
Parts
12
Relationship

• is a way of relating one entity to another.
  Entities can therefore participate in a
  relationship.
• it is commonly thought as a verb connecting the
  entities or nouns.
• It is normally represented by a diamond shape.

13
Relationship - Example

• For example in our Premiere Database again we
  have this relationships among entities

represents
Customer
Sales Rep
has
Could be read as A Sales Rep Represents a
Customer. And a Customer has an Order.
Order
14
Cardinality

• Cardinality number of items that must be
  included in a relationship
• An entity in a relationship with minimum
  cardinality of zero plays an optional role in the
  relationship
• An entity with a minimum cardinality of one plays
  a mandatory role in the relationship

15
Cardinality - Symbols
1
M
One-is-to-many Relationship
M
N
Many-to-many Relationship
16
Cardinality Symbols - Example
represents
Sales Rep
1
M
Customer
Could be read as A Sales Rep could represent 1
or Many Customers.
17
Cardinality Symbols Example (Contd)
has
Order
M
N
Parts
Could be read as An Order could have many Parts
(e.g. Products Ordered) and a Part could have
many Orders.
18
Degree of Relationship

• There are three Degree of Relationships in ERD
  notation, namely
• Unary
• Binary
• Ternary

19
Degree of Relationship (Contd)
Unary
Binary
Ternary
20
Degree of Relationship (Contd)
Manages
Unary
Employee
makes
Customer
Orders
Binary
Vendor
Warehouse
Ternary
supplies
Part
21
Attribute

• Refers to the characteristic or basic fact or
  field of an Entity or Relationship.
• For example a Student entity could have the
  following attributes ID Number, Last Name, First
  Name, Address, Birth Date etc.
• A relationship could also have an attribute for
  example an Entity name Student enrolls
  (relationship) to a Course/Program. Now, when you
  enroll you enroll on a certain date so you will
  have an attribute of Enrollment Date under Enroll
  relationship.
• It is normally represented by an oval.

22
Attribute - Example
Lastname
Firstname
RepNum
Street
Sales Rep
City
State
Take note that a Primary Key is underlined.
Rate
Zip
Commission
23
Attribute More Example
CustomerName
CustomerNum
Lastname
Firstname
RepNum
Street
Street
represents
1
M
City
Sales Rep
City
Customer
State
State
Rate
CreditLimit
Zip
Zip
Commission
Balance
24
Crows Foot notation - Symbol
Entity name
Attribute 1 Attribute 2 Attribute 3 Attribute 4
25
Crows Foot notation - Example
Entity
Student
StudentID Firstname Lastname Gender Program
Attributes
26
Crows Foot notation - Keys
Student
PK Primary Key
StudentID (PK) Firstname Lastname Gender ProgramID
(FK)
FK Foreign Key
27
Crows Foot Cardinality - Symbols
One and only one included in the relationship
Zero or many could be included in the
relationship. This is optional mode.
One or many could be included in the
relationship. This is mandatory mode.
28
Crows Foot notation with Cardinality
Customer
Rep
Customernum(PK) Customername Street City State Zip
Balance CreditLimit Repnum (FK)
Repnum (PK) Firstname Lastname CommissionRate
29
Non-Graded Seatwork

• Using Premier Database
• Create a Chen notation on at least two tables
  that is not part of our example and tables that
  has relationship
• Create a Crows Foot notation on at least two
  tables that is not part of our example and tables
  that has relationship

30
Graded Seatwork

• Create both Chen and Crows foot notation on all
  tables and relationship
• Alexamara Marina
• Henry Books

31
End