Normalization Assignment Database
Uploaded by
ammaraahm299Normalization Assignment Database
Uploaded by
ammaraahm2991.
Project Objective
The primary objective of this assignment is to analyze an unnormalized data source,
systematically identify its underlying Functional Dependencies (FDs), and progressively
apply normalization rules. By transitioning the dataset through the First (1NF), Second
(2NF), and Third (3NF) Normal Forms, we aim to eliminate data redundancy, mitigate
insertion, update, and deletion anomalies, and optimize overall data integrity.
2. Given Attributes
The unnormalized relation consists of the following 9 attributes extracted from an
unnormalized Event Booking and Venue Ledger:
• EventID (Unique identifier for each scheduled event event)
• EventName (The title or name of the hosted event)
• ClientID (Unique identifier for the booking client)
• ClientType (The tier of the client, e.g., Corporate, VIP, Standard)
• ClientName (The full name of the client)
• BaseDiscount (The standard discount rate determined automatically by the
client tier)
• CateringTicketNo (Unique reference number for an assigned catering service
voucher)
• CateringType (The style of catering, e.g., Buffet, Plated, Cocktail)
• CateringServiceDate (The specific date scheduled for the catering service
execution)
3. Identified Functional Dependencies (FDs)
Functional dependencies dictate how attributes relate to one another within the
schema. Based on an analysis of the structural relationships of this ledger, the following
FDs have been established:
• FD1: EventID->EventName
(The Event ID uniquely determines the specific title/name of the event.)
• FD2: ClientID->ClientName, ClientType, BaseDiscount, EventID
• (Each client is uniquely identified by a Client ID, which determines their profile,
membership/client tier, standard base discount, and their main associated event
reservation.)
• FD3: CateringTicketNo-> CateringType, CateringServiceDate
(A specific catering ticket number explicitly dictates its service operational style and its
scheduled execution date.)
• FD4: ClientType ->BaseDiscount
(The baseline discount percentage is completely dependent on the designated tier or
classification of the client.)
Candidate Key Determination
• Primary Key Selection: ClientID is designated as the core identifier for the
relational records in this specific ledger segment.
• However, because a single client can pull or book distinct catering tickets over
time, the fully flattened, atomic relation relies on a composite configuration or
direct foreign key references to link secondary transactional entities like catering
vouchers.
4. The Step-by-Step Normalization Process
First Normal Form (1NF)
• Rule: A relation is in 1NF if and only if the domain of each attribute contains only
atomic (indivisible) values, and there are no repeating groups or multi-valued
attributes.
• Action taken: The merged rows, blank cell blocks, and visual nesting hierarchies
present in the source ledger sheet were flattened. Every row now contains a
distinct, single scalar value for each attribute, successfully satisfying 1NF
requirements.
Second Normal Form (2NF)
• Rule: A relation is in 2NF if it is in 1NF and every non-prime attribute is fully
functionally dependent on the primary key (i.e., there are no partial
dependencies where an attribute depends on only a subset of a composite key
setup).
• Action taken: To eliminate partial dependencies, attributes that do not
structurally depend on the core client identifier were isolated. The overarching
event profile attributes were split into a dedicated Event relation, ensuring that
EventName is only mapped via EventID.
Third Normal Form (3NF)
• Rule: A relation is in 3NF if it is in 2NF and contains no transitive dependencies.
This means non-prime attributes must not depend on other non-prime
attributes; they must depend only on the superkey.
• Action taken: Two explicit transitive anomalies were resolved:
1. In the client record, ClientType uniquely determined BaseDiscount. This
was fixed by separating discounts into a standalone Discount_Tier
reference table.
2. Catering data tracked independent transactional attributes
(CateringType, CateringServiceDate) that relied entirely on
CateringTicketNo rather than the client's direct personal profile. This data
was isolated into its own Catering_Service table with a foreign key
pointing back to the client.
5. Final Normalized Relational Schema (3NF)
Below is the structure of the decoupled database schema, optimized to ensure
relational integrity.
1. Event Table
Stores standalone event organization profile data.
• EventID (PK)
• EventName
2. Discount_Tier Table
A reference table that dictates pricing discounts based on client type tiers.
• ClientType (PK)
• BaseDiscount
3. Client Table
The central entity tracking client registrations, linking them cleanly to events and client
tiers.
• ClientID (PK)
• ClientName
• ClientType (FK referencing Discount_Tier)
• EventID (FK referencing Event)
4. Catering_Service Table
Tracks transactional catering voucher assignments tied back to specific clients.
• CateringTicketNo (PK)
• CateringType
• CateringServiceDate
• ClientID (FK referencing Client)
6. Conclusion
By breaking down the initial monolithic booking sheet into four distinct, logical tables,
structural redundancy has been eradicated. With this Third Normal Form (3NF)
architecture, updating an event's name, modifying a global tier discount rate, or
changing a catering service timeframe can now be executed in exactly one place
without risking data anomalies or structural inconsistencies.