Scribd
Upload
5 views3 pages

Assignment4 Database Normalization

The document outlines the process of normalizing a database for a Smart Electronics Store Management System, detailing the transformation from Unnormalized Form (UNF) through various normal forms (1NF to 5NF). It identifies issues in the UNF, applies normalization rules to eliminate redundancy and anomalies, and presents the final structure of the database with separate tables for customers, orders, products, suppliers, categories, delivery, order details, and product features. Assumptions regarding the relationships between orders, products, suppliers, and features are also stated.

Uploaded by

maillaptop572
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

Assignment4 Database Normalization

The document outlines the process of normalizing a database for a Smart Electronics Store Management System, detailing the transformation from Unnormalized Form (UNF) through various normal forms (1NF to 5NF). It identifies issues in the UNF, applies normalization rules to eliminate redundancy and anomalies, and presents the final structure of the database with separate tables for customers, orders, products, suppliers, categories, delivery, order details, and product features. Assumptions regarding the relationships between orders, products, suppliers, and features are also stated.

Uploaded by

maillaptop572
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Assignment #4: Database Normalization

Smart Electronics Store Management System

1. Unnormalized Form (UNF)

Problems in UNF

The table contains:


• Repeating groups
• Multiple values in one field (ProductFeatures)
• Data redundancy
• Update anomalies
• Insertion anomalies
• Deletion anomalies

UNF Table

| OrderID | CustomerName | CustomerCity | ProductID | ProductName | Category |


SupplierName | SupplierContact | DeliveryService | DeliveryPhone | Quantity |
ProductFeatures |
| O101 | Ali | Lahore | P11 | Laptop | Electronics | Tech Supplier | 03001234567 | TCS | 111-
222 | 2 | Touchscreen, SSD |
| O101 | Ali | Lahore | P12 | Mouse | Accessories | IT Traders | 03009876543 | TCS | 111-222
| 1 | Wireless |
| O102 | Sara | Multan | P13 | Printer | Electronics | Print World | 03112223344 | Leopard |
333-444 | 1 | Color, WiFi |

2. First Normal Form (1NF)

Rules Applied
• Removed repeating groups
• Made all attributes atomic
• Split multi-valued ProductFeatures

Candidate Key:
(OrderID, ProductID, ProductFeature)

3. Second Normal Form (2NF)

Rules Applied
• Removed partial dependencies
• Attributes depending on only part of composite key were separated

Customer Table
C01 | Ali | Lahore
C02 | Sara | Multan

Orders Table
O101 | C01 | D01
O102 | C02 | D02

Products Table
P11 | Laptop | Electronics | S01
P12 | Mouse | Accessories | S02
P13 | Printer | Electronics | S03

Suppliers Table
S01 | Tech Supplier | 03001234567
S02 | IT Traders | 03009876543
S03 | Print World | 03112223344

Delivery Table
D01 | TCS | 111-222
D02 | Leopard | 333-444

OrderDetails Table
O101 | P11 | 2
O101 | P12 | 1
O102 | P13 | 1

ProductFeatures Table
P11 | Touchscreen
P11 | SSD
P12 | Wireless
P13 | Color
P13 | WiFi

4. Third Normal Form (3NF)

Rules Applied
• Removed transitive dependencies
• Category moved into separate table

Category Table
CAT01 | Electronics
CAT02 | Accessories

Updated Product Table


P11 | Laptop | CAT01 | S01
P12 | Mouse | CAT02 | S02
P13 | Printer | CAT01 | S03

5. Boyce-Codd Normal Form (BCNF)

Every determinant must be a candidate key.


All tables satisfy BCNF.

6. Fourth Normal Form (4NF)

Removed multivalued dependencies.


ProductFeatures table handles multiple product features.

7. Fifth Normal Form (5NF)

Removed join dependencies.


Database divided into smaller related tables.

Final Database Tables


1. Customers
2. Orders
3. Products
4. Suppliers
5. Categories
6. Delivery
7. OrderDetails
8. ProductFeatures

Assumptions
1. Each order can contain multiple products.
2. One product belongs to one category.
3. One supplier supplies one or more products.
4. One delivery service can deliver many orders.
5. One product can have multiple features.
6. Customer IDs, Product IDs, Supplier IDs, and Delivery IDs are uniquely generated.

You might also like