[ ]

LEARNING OUTCOMES
At the end of the lesson, the students should be able
to;

1. Explain the normalization in DBMS.

2. Understand concepts of data modeling and its
purpose;
3. Learn how relationships between entities are defined
and refined, and how such relationships are
incorporated into the database design process;
4. Learn how ERD components affect database design
and implementation;
LEARNING OUTCOMES
5. Learn how to interpret the modeling symbols;
6. Learn how entity clusters are used to represent
multiple entities and relationships;
7. Learn the characteristics of good primary keys and
how to select them; and
8. Learn how to use flexible solutions for special data-
modeling cases.
Data Normalization
❑Normalization in DBMS is the process of
structuring the database.
❑It covers 1NF, 2NF, and 3NF with real-world
examples and tables.
Database Normalization
Why Normalization is Required in DBMS

❑Normalization in DBMS isn’t just

“good practice” — it’s often a
lifesaver.
1NF (First Normal Form) – Definition, Rules, Example

❑A table is in 1NF if:

❑Each cell has atomic values (no lists or
sets or arrays).
❑No repeating groups or arrays.
❑Each record is unique.
1NF (First Normal Form)

❑ Example (Before)
employee_id emp_name emp_job_codes address
E001 Rowena J01, J02 Isulan
E002 Paul J02, J03 Tacurong
Problems: emp_job_codes contains multiple values in one cell.

❑ Example (After)
employee_id emp_name emp_job_codes address
E001 Rowena J01 Isulan
E001 Rowena J02 Isulan
E002 Paul J02 Tacurong
E002 Paul J03 Tacurong
Now, each cell holds only one value and queries become straightforward.
2NF (Second Normal Form) – Definition, Rules, Example

❑A table is in 2NF if:

❑ It’s already in 1NF.
❑ No partial dependency exists (non-key
attributes must depend on the entire primary
key, not part of it).
 2NF (Second Normal Form)

❑ Example (Before)
employee_id emp_job_codes emp_name address

E001 J01 Rowena Isulan

E001 J02 Rowena Isulan
Here, name and home_state depend only on employee_id — part of the composite key (employee_id, job_code).

Problem: The primary key here is (employee_id, emp_job_code).

name and address depend only on employee_id, not on the full key.

❑ Solution: Split into separate tables.

employee_id emp_name address employee_id emp_job_codes
E001 Rowena Isulan E001 J01
E002 Paul Tacurong E001 J02
employees Table employees_roles Table
3NF (Third Normal Form) – Definition, Rules, Example

❑A table is in 3NF if:

❑ It’s already in 2NF.
❑ No transitive dependency (non-key attributes
shouldn’t depend on other non-key
attributes).

❑ Why 3NF matters:

❑ If one non-key column depends on another non-key column,
changes in one place can cause errors in multiple rows, leading
to inconsistent data
 3NF (Third Normal Form)

❑ Example (Before)
employee_id emp_name postal_code address
E001 Rowena 12 Isulan
E002 Paul 26 Tacurong
Here, address depends on postal_code, not directly on the primary key.

Problem: If “12” changes from “Isulan” to “Isu” in some rows but not others, inconsistencies arise.

❑ Solution: Create a separate postal table.

postal _code address employee_id emp_name postal_code
12 Isusilan E001 Rowena Isulan
26 Tacurong E002 Paul 26

Now postal_code is stored in one place, and any change automatically reflects for all related rows.
Database Normalization
Data Model
❑Model: an abstraction of a real-world object
or event
▪ Useful in understanding complexities of
the real-world environment
❑Data model
▪ A diagram that displays a set of tables and
the relationships between them.
Access Data Model using ERD
What is an Entity Relationship Diagram (ERD)?

❑ ERD is a data modeling technique used in

software engineering to produce a conceptual
data model of an information system.
❑ So, ERDs illustrate the logical structure of
databases.
❑ ERD development using a CASE tool
▪ Powerdesigner by SAP
▪ Data Modeler by Oracle
The Importance of Data Model
❑ Blue print: official documentation
▪ Blue print of house
❑ Employee’s w/o DB knowledge can understand
▪ a data model diagram vs. a list of tables
▪ Used as an effective Communication Tool
▪ Improve interaction among the managers, the
designers, and the end users
❑ Independence from a particular DBMS
▪ Network DB, Object-oriented DB, etc.
Data Model (con’t)
❑ The data modeling revolves around discovering
and analyzing organizational and users data
requirements.
❑ Requirements based on policies, meetings,
procedures, system specifications, etc.
▪ Identify what data is important
▪ Identify what data should be maintained
Three Data Models
The Data Modeling Process

Step 1. Identifying entities

Step 2. Identifying connections between
entities
Step 3. Identifying entities’ attributes
Step 4. Deciding attributes’ specific data types
Step 5. Identifying many-to-many relationships
and implementing junction tables
Step 6. Creating database constraints, indices,
triggers, and other database-specific objects
Basic ERD Components
❑ The major activity of this phase is identifying
entities, attributes, and their relationships to
construct model using the Entity Relationship
Diagram.
▪ Entity → table
▪ Attribute → column
▪ Relationship → line
▪ Cardinality → degrees of relationship
How to find entities?
❑ Entity:
▪ "...anything (people, places, objects, events, etc.)
about which we store information (e.g. supplier,
machine tool, employee, utility pole, airline seat,
etc.).”
▪ Tangible: customer, product
▪ Intangible: order, accounting receivable
▪ Look for singular nouns (beginner)
▪ BUT a proper noun is not a good candidate….
Entity Instance
❑ Entity instance: a single occurrence of an entity.
▪ 6 instances
Entity: student Student Last First
ID Name Name
2144 Arnold Betty
3122 Taylor John
3843 Simmons Lisa
instance
9844 Macy Bill
2837 Leath Heather
2293 Wrench Tim
How to find attributes?
❑ Attribute:
▪ Attributes are data objects that either identify or
describe entities (property of an entity).
▪ In other words, it is a descriptor whose values are
associated with individual entities of a specific entity
type
▪ The process for identifying attributes is similar except
now you want to look for and extract those names that
appear to be descriptive noun phrases.
How to find relationships?
❑ Relationship:
▪ Relationships are associations between entities.
▪ Typically, a relationship is indicated by a verb
connecting two or more entities.
▪ Employees are assigned to projects
▪ Relationships should be classified in terms of
cardinality.
▪ One-to-one, one-to-many, etc.
How to find cardinalities?
❑ Cardinality:
▪ The cardinality is the number of occurrences in one
entity which are associated to the number of
occurrences in another.
▪ There are three basic cardinalities (degrees of
relationship).
▪ one-to-one (1:1), one-to-many (1:M), and many-to-
one (M:1) or;
▪ one-to-one (1..1), one-to-many (1..*), many-to-one
(*..1)
Identifier
❑ “attributes that uniquely identify entity
instances”
▪ Becomes a PK in RDS
▪ Composite identifiers are identifiers that
consist of two or more attributes
▪ Identifiers are represented by underlying the
name of the attribute(s)
▪ Employee (Employee_ID), student
(Student_ID)
Crow’s Foot Notation
❑ Known as IE notation (most popular)
❑ Entity:
▪ Represented by a rectangle, with its name on
the top. The name is singular (entity) rather
than plural (entities).
Attributes
❑ Identifiers are represented by underlying the
name of the attribute(s)
Basic Cardinality Type

❑ 1-to-1 relationship

❑ 1-to-M relationship

❑ M-to-M relationship
Cardinality con’t
Cardinality con’t
Example Model
Data Model by Peter Chen’ Notation
(first - original)
Business Rule Example 1
❑ Finalized business rules must be
bi-directional.
▪ Draft: one sentence
▪ Finalized: two sentences
❑ A professor advises many
students (professor to student).
Each student is advised by one
professor (student to professor).
❑ A professor must teach many
classes. Each class must be
taught by one professor.
Business Rule Example 1
❑ Business Rules are used to define entities, attributes,
relationships and constraints.
❑ Usually though they are used for the organization that
stores or uses data to be an explanation of a policy,
procedure, or principle.
❑ The data can be considered significant only after
business rules are defined.
▪ W/o them it cannot be considered as data for RDS
but just records.
Business Rule 2
❑ When creating business rules, keep them simple, easy
to understand, and keep them broad.
▪ so that everyone can have a similar understanding
and interpretation.
❑ Sources of business rules:
▪ Direct interviews with internal & external
stakeholders
▪ Site visitations (collect data) and observation of the
work process or procedure
▪ Review and study of documents (Policies,
Procedures, Forms, Operation manuals, etc..)
Discovering Business Rules
❑ Real world example on the class website
▪ After reviewing and studying the interview and
various forms, develop a draft business rules -
does not need to be bi-directional and less
precise wording…
▪ Keep on going until “optimized”
▪ Then, finalize Business Rules: bi-directional.
Business Rule Example 2
❑ A sales representative must write
many invoices. Each invoice has
to be written by one sales
representative.
❑ Each sales representative must
be assigned to many department.
Each department has only one
sales representative.
❑ A customer has to generate many
invoices. An invoice is generated
by only one customer.
Attributes
❑ “Describe detail information about an entity ”
▪ Entity: Employee
▪ Attributes:
▪ Employee-Name
▪ Address (composite)
▪ Phone Extension
▪ Date-Of-Hire
▪ Job-Skill-Code
▪ Salary
 Entities and their Relationship with Cardinalities

Entities Cardinalities Relationships

Student → Fine Record 1:N (One to Many) A student can have multiple fine
records.
Fine Record → Fine Type N:1 (Many to One) Multiple fine records belong to one
fine type.
Fine Record → Payment 1:1 (One to One) Each fine record has only one
payment (if paid).
Payment → Payment Method N:1 (Many to One) Multiple payments use one
payment method.
Student → Payment 1:N (One to Many) A student can make multiple
payments.
Classes of attributes
❑Simple attribute
❑Composite attribute
❑Derived attributes
❑Single-valued attribute
❑Multi-valued attribute
Simple/Composite attribute
A simple attribute cannot be subdivided.
Examples: Age, Gender, and Marital status
A composite attribute can be further subdivided to
yield additional attributes.
Examples:
ADDRESS --→ Street, City, State, Zip
PHONE NUMBER --→ Area code, Exchange number
Derived attribute
❑ is not physically stored within the database
❑ instead, it is derived by using an algorithm.
▪ Example 1: Late Charge of 2%
▪ MS Access: InvoiceAmt * 0.02
▪ Example 2: AGE can be derived from the date
of birth and the current date.
▪ MS Access: int(Date() – Emp_Dob)/365)
Single-valued attribute
❑ can have only a single (atomic) value.
▪ Examples:
A person can have only one social security number.
A manufactured part can have only one serial
number.
▪ A single-valued attribute is not necessarily
a simple attribute.
Part No: CA-08-02-189935
Location: CA, Factory#:08, shift#: 02, part#: 189935
Multi-valued attributes
❑ can have many values.
▪ Examples:
▪ A person may have several college
degrees.
▪ A household may have several phones with
different numbers
▪ A car color
Example - “Movie Database”
❑ Entity:
▪ Movie Star
❑ Attributes:
▪ SS#: “123-45-6789” (single-valued)
▪ Cell Phone: “(661)123-4567, (661)234-5678” (multi-
valued)
▪ Name: “Harrison Ford” (composite)
▪ Address: “123 Main Str., LA, CA” (composite)
▪ Gender: “Female” (simple)
▪ Age: 24 (derived)
Procedure of ERD
❑ Relatively simple representations of
complex real-world data structures
❑ Data modeling is iterative process.
❑ “complete” and “100% error free” model is
not possible!
❑ Only “Optimized” model is possible….
Extended Entity Relationship Model (EERM)
❑ Result of adding more semantic constructs to the
original entity relationship (ER) model
❑ EER diagram (EERD): Uses the EER model
Figure 5.5 - Tiny College ERD Using Entity
Clusters
Primary Keys
❑Single attribute or a combination of
attributes, which uniquely identifies each
entity instance
▪ Guarantees entity integrity
▪ Works with foreign keys to implement
relationships
Use of Composite Primary Keys
❑ When used as identifiers of weak entities,
represent a real-world object that is:
▪ Existence-dependent on another real-world
object
▪ Represented in the data model as two separate
entities in a strong identifying relationship
Figure 5.6 - The M:N Relationship between STUDENT
and CLASS
Design Case 1: Implementing 1:1 Relationships
❑ Foreign keys work with primary keys to properly
implement relationships in relational model
❑ Rule
▪ Put primary key of the parent entity on the
dependent entity as foreign key
❑ Options for selecting and placing the foreign key:
▪ Place a foreign key in both entities
▪ Place a foreign key in one of the entities
Figure 5.7 - The 1:1 Relationship between Department
and Employee
Figure 5.8 - Maintaining Salary History
Figure 5.9 - Maintaining Manager History
Figure 5.10 - Maintaining Job History
Make a Data Modeling of a Retail Sales Management
System
Entities and Relationships
❑ Customer: Stores customer information.
❑ Product: Represents items available for sale.
❑ Order: Tracks orders placed by customers.
❑ Order_Detail: Line items in an order (many products
per order).
❑ Inventory: Tracks stock levels of products.
❑ Employee: Represents employees managing sales
and inventory.
❑ Supplier: Information about suppliers of products.
JAY MARK F. ARENDAIN, MIS
Subject Instructor