DFC 20283
DATABASE FUNDAMENTALS
3.2 E-R Diagrams (ERD) in database
development
DOA
Learning OUTCOME
Upon completion of this topic, students should be able to:
CLO 1 : Apply fundamental of Database Management System (DBMS), relational data
model and normalization.( C3, PLO 2 )
CLO 2 : Show a well-structured database using the database query to manipulate a
database with an appropriate commercial Database Management System
(DBMS) in solving an organization’s requirements. ( P2, PLO 3 )
SubTopic
3.2 E-R Diagrams (ERD) in database development
3.2.1 Entity, attribute and relationship
3.2.2 Relationship cardinality.
a. One to one (1:1)
b. One to many (1:M)
c. Many to many (M:N)
3.2.3 Relate entities by applying the rules of cardinality
3.2.4 E-R diagram notations for:
a. Entity
b. Weak entity
c. Attributes
d. Key attribute
e. Multivalued attribute
f. Derived attribute
g. Relationships
h. Cardinality
3.2.5 Steps in creating E-R diagram
3.2.6 Construct ER diagram components that represent entities and attributes
according to diagramming conventions based on a given scenario or problem statement
3.2.7 Transform an E-R diagram into physical database design
3.2.1 Entity, attribute and relationship
Entity Relationship Diagram (ERD) ER Diagram can be construct using
- A detailed, logical representation of the various notation such as:
entities, associations and data
elements for an organization or •Chen’s Model
business
•Crow Foot
Notation uses three main constructs:
•Martin
•Entity types
•Bachman, etc
•Relationships
•Attributes
•Identifier (Keys)
3.2.1 Entity, attribute and relationship
Name
(ATTRIBUTE)
Student
(ENTITY)
Enrolled Course
(RELATIONSHIP) (ENTITY)
3.2.1 Entity, attribute and relationship
Using Chen’s Model notation
EntityName Verb Phrase AttributeName
Person, place, object, event Association between Named property or
or concept about which data the instances of one or characteristic of an
is to be maintained more entity types entity
Represents a set or collection
of objects in the real world
that share the same
properties
3.2.1 Entity, attribute and relationship
Relationship between TEAM and PLAYER
3.2.1 Entity, attribute and relationship
i) Entity
example of Entity types according to its identity:
Entity Identity Example
Person STAFF, STUDENT, LECTURER, EMPLOYEE
Place DISTRICT, TOWN, STATE
Object BUILDING, TOOL, PRODUCT
Event SALE, REGISTRATION, APPLICATION
Concept ACCOUNT, COURSE, QUALIFICATION, STOCK
Guidelines for naming and defining entity types:
An entity type name is a singular noun
An entity type should be descriptive(jelas) and specific
An entity name should be concise(ringkas)
3.2.1 Entity, attribute and relationship
Types of Entity :
i. Strong entities ii.Weak entities
Exist independently of other types of Dependent on a strong entities…cannot exist
entities on its own
Has its own unique identifier Does not have a unique identifier
Represented with single-line rectangle Represented with double-line rectangle
Identifying relationship
Links strong entities to weak entities
Represented with double line diamond
3.2.1 Entity, attribute and relationship
•Types of Entity
3.2.1 Entity, attribute and relationship - Associative Entities
• Also known as Bridge Entities or Composite Entities
• It’s an entity – it has attributes
• AND it’s a relationship – it links entities together
When should a relationship with attributes instead be an
associative entity?
• The relationship should be many-to-many.
• Composed of the primary keys of each of the entities to be connected
• May also contain additional attributes that play no role in the
connective process
3.2.1 Entity, attribute and relationship
Example :
Associative
Entities
Associative entity involves a rectangle with diamond inside.
Note: many-to-many connectivity symbols face toward the
Associative entity and not toward the other entities.
3.2.1 Entity, attribute and relationship
ii) Attribute
3.2.1 Entity, attribute and relationship
Types of Attribute : i.Simple vs. Composite Attribute
•Simple Attribute – cannot broken into smaller components
•Composite Attribute – can broken into component parts
ii.Single-valued vs. Multivalued Attribute
•Single-valued – each of the attributes has one value
•Multivalued – attribute more than one value
iii.Stored vs. Derived Attributes
•Stored – data input or set
•Derived – attribute whose values can be calculated from related
attribute values
3.2.1 Entity, attribute and relationship
•Types of Attribute
Exercise
3.2.1 Entity, attribute and relationship
iii) Relationships
• the association among the entities
• become the glue that holds the database together
• given a name that describe its function
• use active or passive verb
Activity
Students needs to identify all the information below based
on business rule given :
i. entities
ii. attributes
iii. relationships
iv. Cardinality
v. connectivity
Business Rule 1
A company has several departments. Each department has
a supervisor and at least one employee. Employees must be
assigned to only one department. Project will be given to
the employee and it will be done in a group. At least one
employee is assigned to one project or more. The important
data fields are the names of the departments, projects,
supervisors and employees, as well as the supervisor and
employee number, department code and a unique project
number
Business Rule 2
A doctor can be scheduled for many appointments, but may not have any
scheduled at all. Each appointment is scheduled with exactly 1 doctor. A patient
can schedule 1 or more appointments. One appointment is scheduled with exactly 1
patient. An appointment must generate exactly 1 bill, a bill is generated by only 1
appointment. One payment is applied to exactly 1 bill, and 1 bill can be paid off
over time by several payments. A bill can be outstanding, having nothing yet paid on
it at all. One patient can make many payments, but a single payment is made by
only 1 patient. Some patients are insured by an insurance company. If they are
insured, they can only carry insurance with one company. An insurance company
can have many patients carry their policies. For patients that carry insurance, the
insurance company will make payments, each single payment is made by exactly1
insurance company.
REVIEW PREVIOUS CLASS
ERD ????
Entity?
Attribute?
Relationship?
3.2.2 Relationship cardinality
relationship between a row of one table and a row of another table
minimum and maximum number of record in Entity B that can (or must be)
associated with each instance in Entity A.
can be either mandatory or optional
Minimum cardinality: Mandatory relationship - where there must be
• If zero, then optional at least one matching record in each entity
• If one or more, then mandatory Optional relationship - where there may or may
not be a matching record in each entity
Mandatory cardinality:
• The maximum number
3.2.2 Relationship cardinality
3 types of relationship cardinality:
a. One to one (1:1)
b. One to many (1:M)
c. Many to many (M:N)
a. One to one (1:1)
b. One to many (1:M)
c. Many to many (M:N)
3.2.3 Relate entities by applying the rules of cardinality
in a relationship, it determines the degree to which one entity is related to
another by answering the question, “How many?”
Examples:
1.Each EMPLOYEE must hold one and only one JOB
2.Each JOB may be held by one or more EMPLOYEE
3.Each PRODUCT must be classified by one and only one PRODUCT TYPE
4.Each PRODUCT TYPE may classify one or more PRODUCT
3.2.3 Relate entities by applying the rules of cardinality
3.2.3 Relate entities by applying the rules of cardinality
3.2.3 Relate entities by applying the rules of cardinality
What is the cardinality for the scenarios below?
3.2.3 Relate entities by applying the rules of cardinality
For each pair of sentences, identify entities and the relationship representing
the sentences.
i. “Each Student must take one or more Modules”
ii. “Each Module must be taken by one or more Students”
iii. “Each Customer must receive at least one Delivery”
iv. A Client may not have more than 1 Manager”
v. “Each Manager has only one Client”
EXERCISE 1 : Relationship cardinality
1. One customer can have at most one account
One account cannot be owned by more than one customer
2. One customer can have many accounts
One account cannot be owned by more than one customer
3. One customer can have many accounts
One account may be owned by many customer
EXERCISE 2 : Relationship cardinality
1. Each customer may generate one or more invoices
Each invoice is generate by one customer
2. Each invoice contains one or more invoice lines
Each invoice line is contained in one invoice
3. Each invoice line references one product
Each product may be referenced in one or more invoice lines
3.2.4 The E-R diagram notations
3.2.6 Steps in ER modelling
3.2.5 Steps in ER modelling
1.Identify the entities. Put them into boxes.
2.Add attributes into each entity.
3.Specify the key attributes for each entity by underlining them.
4.Identify the relationship among entities.
5.Add attributes into relationship if any.
6.For each relationship, add on the connectivity ratio and cardinality.
3.2.5 Steps in ER modelling
AN ENTITY RELATIONSHIP DIAGRAM METHODOLOGY: (One way of doing it)
Exercise 1 : Draw an ER diagram
• A company has several departments.
• Each department has a supervisor and at least one employee.
• Employees must be assigned to only one department.
• Project will be given to the employee and it will be done in a group.
• At least one employee is assigned to one project or more.
• The important data fields are the names of the departments, projects,
supervisors and employees, as well as the supervisor and employee
number, department code and a unique project number.
Exercise 2 : Draw an ER diagram
• A doctor can be scheduled for many appointments, but may not have any scheduled at all.
Each appointment is scheduled with exactly 1 doctor.
• A patient can schedule 1 or more appointments. One appointment is scheduled with
exactly 1 patient.
• An appointment must generate exactly 1 bill, a bill is generated by only 1 appointment.
One payment is applied to exactly 1 bill, and 1 bill can be paid off over time by several
payments.
• A bill can be outstanding, having nothing yet paid on it at all. One patient can make many
payments, but a single payment is made by only 1 patient.
• Some patients are insured by an insurance company. If they are insured, they can only
carry insurance with one company. An insurance company can have many patients carry
their policies.
• For patients that carry insurance, the insurance company will make payments, each single
payment is made by exactly1 insurance company.
Exercise 3 : Draw an ER diagram
• An automobile insurance company needs to keep track of information
about vehicle policyholders. The company has to store information of
customer, car and accident. Customer information is License_no, name
and address. Customer can own one or more cars, where the car
information is Plate_no, model and year. A car not involved or involved in
many accidents. If the car is involved in accident the information that will
be stored is Report_number, location and date. Each car that is involved
in accident the damage amount will be estimate.
Interactive Learning
1. Microsoft board teams
2. Nearpod
3. Kahoot
