1NF
TEACHER TABLE
TeacherID Name SubjectID Subject Hours
T- 001 Claire S-01 Chemistry 2
T- 001 Claire S-01 Physics 3
T-002 George S-02 Biology 1
T-002 George S-02 Science 5
T-003 Marissa S-03 Mathematic 4
T-004 Chester S-04 English 2
T-004 Chester S-04 Filipino 2
2NF
STUDENT TABLE
StudentID SUBJECTID TeacherId S_Name
S-001 S-01 T-001 Claire
S-002 S-02 T-005 George
S-003 S-03 T-006 Marissa
S-004 S-04 T-001 Chester
SUBJECTID Subject Rooms
S-01 Chemistry Room 1
S-02 Biology Room 5
S-03 Mathematic Room 2
S-04 Chemistry Room 4
TeacherId T_Name T_AGE
T-001 Marilyn 45
T-005 Gregorio 55
T-006 Arnold 34
T-001 Chemistry 45
3NF
JOBCODE JOB
CITYCODE CITY
J-01 Clerk
C-001 Quezon
J-02 Manager
C-002 Pasig
J-03 Cashier
C-003 Makati
J-04 Utility
C-004 Taguig
EMPLOYEE TABLE
EmployeeID Name JOBCODE CITYCODE
E-001 Claire J-01 C-001
E-002 George J-02 C-002
E-003 Marissa J-03 C-003
E-004 Chester J-04 C-004
RESEARCH TASK: DATABASE DENORMALIZATION
1. What is your understanding about Database Denormalization? (10pts)
Denormalization trades some write performance for faster reads by strategically adding
duplicate data to an already well-organized database.
2. What is the main purpose of Denormalization? (10pts)
The main purpose of denormalization is to significantly improve read performance in relational
databases, especially for frequently used queries involving joins between multiple tables. It does
this by sacrificing some write performance and introducing controlled redundancy.
3. Create an example of Database Denormalization using a database tables. (25pts)
Example: Denormalizing Orders and Products
Orders table: order_id (primary key) customer_id (foreign key) date_placed total_price
Products table: product_id (primary key) name, price, category
Frequent query: Display order details including product names and prices.
Normalized execution: Join Orders and Products tables on order_id and product_id. Retrieve
required data from both tables. This join operation can be slow, especially for large datasets.
Denormalized approach: Add a product_name column to the Orders table.
When placing an order, copy the corresponding product's name from the Products table into the
product_name column of the Order entry.
4. In your example in #3, explain why this table is in Denormalization. (15pts)
This table is denormalized because it adds the "product_name" information to the "Orders"
table, duplicating data already present in the "Products" table. This redundancy sacrifices write
performance for significantly faster reads of frequent queries that need both order and product
information.
