Practical of Normalization
Step 1: First Normal Form (1NF)
Goal: Ensure atomic values and no repeating groups.
Each column already contains atomic values. The table is in 1NF.
Initial Table: EventRegistrations
Registratio EventNa EventD ParticipantN ParticipantE TicketTy TicketP
nID me ate ame mail pe rice
1 Tech 2024- Alice
[email protected] VIP 200
Conferen 12-01 Johnson om
ce
2 Tech 2024- Bob Smith
[email protected] Standard 100
Conferen 12-01 m
ce
3 Music 2024- Charlie charlie@gmail Standard 150
Fest 12-05 Brown .com
4 Tech 2024- David Lee david@gmail. VIP 200
Conferen 12-01 com
ce
5 Music 2024- Eve Taylor
[email protected] VIP 300
Fest 12-05 m
Step 2: Second Normal Form (2NF)
Goal: Remove partial dependencies. A column should depend on the whole primary key, not
just part of it.
Problem: The combination of RegistrationID and TicketType determines TicketPrice.
However, TicketPrice depends only on TicketType.
Solution:
Create a separate table for tickets.
Tables after 2NF:
1) EventRegistrations:
RegistrationI EventNa EventDa ParticipantNa ParticipantEma TicketTy
D me te me il pe
1 Tech 2024-12- Alice Johnson
[email protected] VIP
Conferenc 01
e
2 Tech 2024-12- Bob Smith
[email protected] Standard
Conferenc 01
e
3 Music Fest 2024-12- Charlie Brown
[email protected] Standard
05 om
4 Tech 2024-12- David Lee
[email protected] VIP
Conferenc 01 m
e
5 Music Fest 2024-12- Eve Taylor
[email protected] VIP
05
2) Tickets:
TicketType TicketPrice TicketPrice
VIP 200
Standard 100
VIP 300
Step 3: Third Normal Form (3NF)
Goal: Eliminate transitive dependencies (where non-key attributes depend on other non-key
attributes).
Problem: EventName determines EventDate.
Solution:
Create a separate table for events.
Tables after 3NF:
EventRegistrations:
RegistrationID EventName ParticipantNam ParticipantEmail TicketType
e
1 Tech Alice Johnson
[email protected] VIP
Conference
2 Tech Bob Smith
[email protected] Standard
Conference
3 Music Fest Charlie Brown
[email protected] Standard
4 Tech David Lee
[email protected] VIP
Conference
5 Music Fest Eve Taylor
[email protected] VIP
Events:
EventName EventDate
Tech Conference 2024-12-01
Music Fest 2024-12-05
Tickets:
TicketType TicketPrice
VIP 200
Standard 100
VIP 300
Step 4: Boyce-Codd Normal Form (BCNF)
Goal: Every determinant must be a candidate key.
Problem: In the Tickets table, TicketType alone does not uniquely determine
TicketPrice (as VIP has two different prices depending on the event).
Solution:
Create a composite key using TicketType and EventName.
Final Tables in BCNF:
EventRegistrations:
RegistrationID EventName ParticipantNam ParticipantEmail TicketType
e
1 Tech Alice Johnson
[email protected] VIP
Conference
2 Tech Bob Smith
[email protected] Standard
Conference
3 Music Fest Charlie Brown
[email protected] Standard
4 Tech David Lee
[email protected] VIP
Conference
5 Music Fest Eve Taylor
[email protected] VIP
Events:
EventName EventDate
Tech Conference 2024-12-01
Music Fest 2024-12-05
Tickets:
TicketType TicketPrice
VIP 200
Standard 100
VIP 300
SQL Queries:
1. Create Tables:
CREATE TABLE EventRegistrations (
RegistrationID INT PRIMARY KEY,
EventName VARCHAR(100),
ParticipantName VARCHAR(100),
ParticipantEmail VARCHAR(100),
TicketType VARCHAR(50),
FOREIGN KEY (EventName) REFERENCES Events(EventName)
);
CREATE TABLE Events (
EventName VARCHAR(100) PRIMARY KEY,
EventDate DATE
);
CREATE TABLE Tickets (
TicketType VARCHAR(50),
EventName VARCHAR(100),
TicketPrice DECIMAL(10, 2),
PRIMARY KEY (TicketType, EventName),
FOREIGN KEY (EventName) REFERENCES Events(EventName)
);
2. Insert Data
INSERT INTO Events VALUES ('Tech Conference', '2024-12-01');
INSERT INTO Events VALUES ('Music Fest', '2024-12-05');
INSERT INTO EventRegistrations VALUES (1, 'Tech Conference', 'Alice Johnson',
'
[email protected]', 'VIP');
INSERT INTO EventRegistrations VALUES (2, 'Tech Conference', 'Bob Smith',
'
[email protected]', 'Standard');
INSERT INTO EventRegistrations VALUES (3, 'Music Fest', 'Charlie Brown',
'
[email protected]', 'Standard');
INSERT INTO EventRegistrations VALUES (4, 'Tech Conference', 'David Lee',
'
[email protected]', 'VIP');
INSERT INTO EventRegistrations VALUES (5, 'Music Fest', 'Eve Taylor', '
[email protected]',
'VIP');
INSERT INTO Tickets VALUES ('VIP', 'Tech Conference', 200);
INSERT INTO Tickets VALUES ('Standard', 'Tech Conference', 100);
INSERT INTO Tickets VALUES ('VIP', 'Music Fest', 300);
INSERT INTO Tickets VALUES ('Standard', 'Music Fest', 150);
Output :
EventRegistrations:
RegistrationID EventName ParticipantNam ParticipantEmail TicketType
e
1 Tech Alice Johnson
[email protected] VIP
Conference
2 Tech Bob Smith
[email protected] Standard
Conference
3 Music Fest Charlie Brown
[email protected] Standard
4 Tech David Lee
[email protected] VIP
Conference
5 Music Fest Eve Taylor
[email protected] VIP
Events:
EventName EventDate
Tech Conference 2024-12-01
Music Fest 2024-12-05
Tickets:
TicketType TicketPrice
VIP 200
Standard 100
VIP 300
