SEEM3430 Information Systems
Analysis and Design
Entity Relationship Diagram (ERD)
Chen, Qianyu
Department of Systems Engineering and Engineering
Management
The Chinese University of Hong Kong
November 6, 2024
OVERVIEW
What is ERD?
Definition
Building blocks
How to draw ERDs
An example
Database Normalization
Summary
2
DEFINITION
An approach for data modeling
A database is divided into two logical
parts:
Entities
Relationships
Diagrams created to design entities and
relationships are called entity-relationship
diagrams 3
SYMBOL SETS
4
AN ERD EXAMPLE
5
ENTITY
Real-world objects, e.g., student, book
Entity instance: a particular entity, e.g., Harry Potter
Represented by a named rectangle
Attributes and identifiers
6
ATTRIBUTE
Attribute describes the characteristics of an entity.
Primary key.
A key attribute is unique, distinguishing characteristic of the
entity.
Identifier: One or more attributes that can uniquely identify
one instance of an entity, which noted by an asterisk next to
the attribute name.
7
RELATIONSHIP
A relationship is an association among entities.
Relationships can be thought of as verbs, linking two
or more nouns.
Examples: an “owns” relationship between a
company and a computer, a “performs” relationship
between an artist and a song.
8
RELATIONSHIP
Strong Relationships(solid lines ): the FK (foreign key) of the
related table is also involved in its PK (primary key), along with
being the PK of another table.
Weak Relationships(dashed line): the relationship is not strong.
E.g. Class had CRS_CODE(the COURSE PK) as a FK, which is
not its PK.
9
CARDINALITY
Cardinality specifies how many instances of an entity relate to one instance
of another entity.
1:1 relationship: one instance of the parent entity is associated with one
instance of the child entity.
1:N relationship: a single instance of a parent entity is associated with
many instances of a child entity.
M:N relationship: many instances of a parent entity can relate to many
instances of a child entity.
10
CARDINALITY
One course can have multiple classes. Some courses may not have classes.
One course can have multiple classes. Each course must have at least one
class.
11
EXAMPLE
Prof. McGonagall found that the modern student
information management system convenient
She would like to build up one for students in Hogwarts
too. But she has been in the magic world for too long.
Can you help her draw the ERD?
Basic entities:
Students (SID, SNAME, HOUSE, COURSE, GRADE)
Courses (CID, CNAME, TEACHER)
Houses (HNAME, HEAD)
12
SOLUTION
13
DATABASE NORMALIZATION
Definition:
Normalization is the process of organizing data
in a database. This includes creating tables
and establishing relationships between
those tables according to rules designed both
to protect the data and to make the database
more flexible by eliminating redundancy
and inconsistent dependency.
14
NORMALIZATION PROCESS
15
NORMALIZATION PROCESS
16
NORMALIZATION PROCESS
After 1st Norm, the repeated attributes (product1, product2, product3) was
moved to a new table, Sales was created.
After 2nd Norm, the partial dependency of date on sales nr was removed
and Sales table was created.
After 3rd Norm, the transitive dependency of order code and unit price on
product nr was removed and Products table was created.
17
NORMALIZATION PROCESS
1NF: A single attribute is not allowed to contain
multiple values; no repeating/duplicated attributes.
2NF: If an attribute depend on the primary key, then it
must depend on the entire primary key (or super
key), i.e. no partial dependencies on a concatenated
primary key. But it does not ensure all attributes
depend on the primary key.
3NF: All attributes must directly depend on the entire
primary key (or super key), i.e. no dependencies on
non-key attributes.
18
SUMMARY
Identify Entities for your data modeling problem
Add Attributes for each entity and determine the
primary key
Establish Relationships between entities
Check the Cardinalities
Normalize your database via 1NF, 2NF, 3NF
Review your E-R diagram and Refine it
19
THANK YOU
20
