Activity: Normalizing Denormalized Data Using Excel

In this activity, you will take 5 denormalized datasets and normalize them using Excel. You will generate 5 tables, each containing at least 25 columns. The goal is to split the data into multiple tables to follow database normalization rules using Excel sheets. This helps remove redundancy and ensure better data organization.

Steps for the Activity

Step 1: Create an Excel Workbook with 5 Sheets

Open Excel and create a new workbook.
Rename each sheet to correspond with the following datasets:
- Sheet 1: employeeData
- Sheet 2: studentData
- Sheet 3: productData
- Sheet 4: orderData
- Sheet 5: salesData

example:

1. employeeData (Denormalized)

employeeID	employeeName	departmentID	departmentName	managerID	managerName	projectID	projectName	salary	address	city	state	zipCode	phone	email	hireDate	jobTitle	managerPhone	managerEmail	projectDeadline	projectStatus
101	John Smith	D001	IT	M001	Lisa White	P001	Alpha	60000	123 Elm St	New York	NY	10001	555-1234	[email protected]	2020-01-15	Developer	555-3456	[email protected]	2022-12-01	Active
102	Jane Doe	D002	HR	M002	Bob Brown	P002	Beta	65000	456 Maple Ave	New York	NY	10002	555-5678	[email protected]	2019-11-05	Designer	555-8765	[email protected]	2023-01-15	Completed
103	Bob Brown	D003	Sales	M003	Emma Blue	P003	Gamma	55000	789 Oak Dr	New York	NY	10003	555-8765	[email protected]	2021-02-12	Analyst	555-9091	[email protected]	2022-11-20	Active
104	Lisa White	D001	IT	M001	Lisa White	P001	Alpha	70000	234 Pine St	New York	NY	10004	555-3456	[email protected]	2018-09-08	Manager	555-3456	[email protected]	2022-12-01	Active
105	Tom Green	D002	HR	M002	Bob Brown	P002	Beta	50000	567 Birch Blvd	New York	NY	10005	555-6543	[email protected]	2017-03-25	Developer	555-8765	[email protected]	2023-01-15	Completed
106	Lucy Black	D001	IT	M001	Lisa White	P004	Delta	62000	123 Elm St	New York	NY	10001	555-1299	[email protected]	2020-06-20	Developer	555-3456	[email protected]	2022-09-05	Active
107	Mike Grey	D002	HR	M002	Bob Brown	P002	Beta	54000	456 Maple Ave	New York	NY	10002	555-7801	[email protected]	2019-08-13	Designer	555-8765	[email protected]	2023-01-15	Completed
108	Emma Blue	D003	Sales	M003	Emma Blue	P005	Epsilon	58000	789 Oak Dr	New York	NY	10003	555-9091	[email protected]	2021-04-05	Analyst	555-9091	[email protected]	2023-05-01	Active
109	Liam Red	D003	Sales	M003	Emma Blue	P003	Gamma	59000	234 Pine St	New York	NY	10004	555-7890	[email protected]	2018-10-02	Manager	555-9091	[email protected]	2022-11-20	Active
110	Noah Yellow	D001	IT	M001	Lisa White	P004	Delta	61000	567 Birch Blvd	New York	NY	10005	555-6789	[email protected]	2017-07-18	Developer	555-3456	[email protected]	2022-09-05	Active

2. studentData (Denormalized)

studentID	studentName	classID	className	teacherID	teacherName	courseID	courseName	birthDate	grade	address	city	state	zipCode	phone	email	enrollmentDate	guardianName	guardianPhone	guardianEmail	attendanceRate	isGraduated
201	Alice Johnson	C101	Math 101	T001	Mr. Thompson	CO101	Algebra	2005-09-12	A	123 Cedar St	New York	NY	10001	555-1200	[email protected]	2021-08-10	Mary Johnson	555-1010	[email protected]	95%	No
202	Bob White	C102	History 202	T002	Mrs. Green	CO102	World History	2006-04-25	B	567 Cedar Blvd	New York	NY	10002	555-6754	[email protected]	2020-09-15	Paul White	555-1020	[email protected]	92%	No
203	Charlie Brown	C103	Science 303	T003	Dr. Smith	CO103	Physics	2005-11-07	C	789 Pine Ave	New York	NY	10003	555-9832	[email protected]	2019-07-05	Susan Brown	555-1030	[email protected]	89%	No
204	David Green	C104	English 404	T004	Ms. Jackson	CO104	English Literature	2007-06-03	B+	124 Oak Dr	New York	NY	10004	555-4621	[email protected]	2022-01-17	Kate Green	555-1040	[email protected]	94%	No
205	Emily Grey	C105	Art 505	T005	Mr. Robinson	CO105	Painting	2006-02-18	A-	455 Birch Rd	New York	NY	10005	555-9854	[email protected]	2021-03-13	Henry Grey	555-1050	[email protected]	97%	Yes
206	Fiona Black	C101	Math 101	T001	Mr. Thompson	CO101	Algebra	2005-08-09	B	156 Oak Dr	New York	NY	10006	555-7234	[email protected]	2019-09-05	Mary Johnson	555-1010	[email protected]	90%	No
207	George Blue	C103	Science 303	T003	Dr. Smith	CO103	Physics	2006-12-15	B-	786 Pine Ave	New York	NY	10003	555-1298	[email protected]	2020-04-20	Susan Brown	555-1030	[email protected]	88%	No
208	Hannah Red	C102	History 202	T002	Mrs. Green	CO102	World History	2005-05-25	C+	213 Birch Blvd	New York	NY	10004	555-3322	[email protected]	2019-10-02	Paul White	555-1020	[email protected]	91%	No
209	Ian Yellow	C105	Art 505	T005	Mr. Robinson	CO105	Painting	2007-03-12	A	412 Cedar St	New York	NY	10001	555-7899	[email protected]	2021-05-18	Henry Grey	555-1050	[email protected]	99%	Yes
210	Jacob White	C104	English 404	T004	Ms. Jackson	CO104	English Literature	2006-11-20	B	126 Pine Blvd	New York	NY	10005	555-9845	[email protected]	2020-12-04	Kate Green	555-1040	[email protected]	96%	No

3. productData (Denormalized)

productID	productName	categoryID	categoryName	supplierID	supplierName	price	stock	supplierPhone	supplierEmail	warehouseID	warehouseLocation	reorderLevel	manufacturerID	manufacturerName	manufacturerPhone	manufacturerEmail	dateAdded	salesAmount	lastRestocked	status	SKU	description
PR101	Laptop	CAT001	Electronics	SUP001	TechSupply	1200	50	555-4001	[email protected]	W001	NY Warehouse	10	MAN001	Dell	555-8001	[email protected]	2022-01-01	50000	2022-09-15	In Stock	LAP123	High-end laptop
PR102	Smartphone	CAT001	Electronics	SUP002	GadgetWorld	800	200	555-4002	[email protected]	W002	LA Warehouse	20	MAN002	Apple	555-8002	[email protected]	2022-01-15	160000	2022-10-01	In Stock	SMRT456	Latest model
PR103	Tablet	CAT001	Electronics	SUP001	TechSupply	600	100	555-4001	[email protected]	W003	TX Warehouse	15	MAN001	Dell	555-8001	[email protected]	2022-02-10	60000	2022-08-20	In Stock	TBL789	Lightweight tablet
PR104	Monitor	CAT001	Electronics	SUP003	OfficeDepot	250	75	555-4003	[email protected]	W001	NY Warehouse	5	MAN003	Samsung	555-8003	[email protected]	2022-03-05	18750	2022-09-10	In Stock	MON123	27-inch monitor
PR105	Keyboard	CAT002	Accessories	SUP002	GadgetWorld	50	150	555-4002	[email protected]	W002	LA Warehouse	10	MAN004	Logitech	555-8004	[email protected]	2022-04-01	7500	2022-08-30	In Stock	KYBD456	Mechanical keyboard
PR106	Mouse	CAT002	Accessories	SUP003	OfficeDepot	25	300	555-4003	[email protected]	W003	TX Warehouse	20	MAN004	Logitech	555-8004	[email protected]	2022-05-12	7500	2022-09-05	In Stock	MSE789	Wireless mouse
PR107	Printer	CAT001	Electronics	SUP001	TechSupply	100	40	555-4001	[email protected]	W001	NY Warehouse	5	MAN003	Samsung	555-8003	[email protected]	2022-06-15	4000	2022-09-20	In Stock	PRNT123	Laser printer
PR108	Headphones	CAT002	Accessories	SUP002	GadgetWorld	70	100	555-4002	[email protected]	W002	LA Warehouse	15	MAN004	Logitech	555-8004	[email protected]	2022-07-20	7000	2022-08-25	In Stock	HDPH456	Noise-cancelling
PR109	Speakers	CAT001	Electronics	SUP003	OfficeDepot	90	60	555-4003	[email protected]	W003	TX Warehouse	10	MAN003	Samsung	555-8003	[email protected]	2022-08-10	5400	2022-09-25	In Stock	SPKR789	Bluetooth speakers
PR110	Web Camera	CAT001	Electronics	SUP001	TechSupply	40	80	555-4001	[email protected]	W001	NY Warehouse	5	MAN001	Dell	555-8001	[email protected]	2022-09-01	3200	2022-09-30	In Stock	WBCM123	HD webcam

4. orderData (Denormalized)

orderID	customerID	customerName	productID	productName	quantity	orderDate	shippingDate	shippingMethod	shippingAddress	city	state	zipCode	phone	email	totalAmount	discount	salesRepID	salesRepName	salesRepPhone	salesRepEmail	paymentMethod	status	trackingNumber
1001	C001	Alice Brown	PR101	Laptop	1	2022-10-01	2022-10-05	UPS	123 Cedar St	New York	NY	10001	555-1212	[email protected]	1200	100	S001	John White	555-6789	[email protected]	Credit Card	Shipped	UPS123456789
1002	C002	Bob Green	PR102	Smartphone	2	2022-10-02	2022-10-06	FedEx	456 Maple Ave	New York	NY	10002	555-2323	[email protected]	1600	150	S002	Lisa Black	555-6790	[email protected]	PayPal	Shipped	FEDX987654321
1003	C003	Charlie Smith	PR103	Tablet	3	2022-10-03	2022-10-07	DHL	789 Oak Dr	New York	NY	10003	555-3434	[email protected]	1800	200	S003	Michael Blue	555-6791	[email protected]	Bank Transfer	Shipped	DHL123987654
1004	C004	David Johnson	PR104	Monitor	2	2022-10-04	2022-10-08	UPS	234 Pine St	New York	NY	10004	555-4545	[email protected]	500	50	S001	John White	555-6789	[email protected]	Credit Card	Processing	UPS876543210
1005	C005	Emily Brown	PR105	Keyboard	1	2022-10-05	2022-10-09	FedEx	567 Birch Blvd	New York	NY	10005	555-5656	[email protected]	50	10	S002	Lisa Black	555-6790	[email protected]	PayPal	Shipped	FEDX543216789
1006	C001	Alice Brown	PR106	Mouse	1	2022-10-06	2022-10-10	DHL	123 Cedar St	New York	NY	10001	555-1212	[email protected]	25	5	S001	John White	555-6789	[email protected]	Credit Card	Delivered	DHL789456321
1007	C002	Bob Green	PR107	Printer	1	2022-10-07	2022-10-11	UPS	456 Maple Ave	New York	NY	10002	555-2323	[email protected]	100	10	S002	Lisa Black	555-6790	[email protected]	PayPal	Delivered	UPS123654789
1008	C003	Charlie Smith	PR108	Headphones	2	2022-10-08	2022-10-12	FedEx	789 Oak Dr	New York	NY	10003	555-3434	[email protected]	140	20	S003	Michael Blue	555-6791	[email protected]	Bank Transfer	Shipped	FEDX987123654
1009	C004	David Johnson	PR109	Speakers	1	2022-10-09	2022-10-13	DHL	234 Pine St	New York	NY	10004	555-4545	[email protected]	90	15	S001	John White	555-6789	[email protected]	Credit Card	Shipped	DHL321654987
1010	C005	Emily Brown	PR110	Web Camera	1	2022-10-10	2022-10-14	UPS	567 Birch Blvd	New York	NY	10005	555-5656	[email protected]	40	5	S002	Lisa Black	555-6790	[email protected]	PayPal	Processing	UPS987321456

5. salesData (Denormalized)

salesID	productID	productName	customerID	customerName	salesDate	amount	quantity	regionID	regionName	salesRepID	salesRepName	commission	tax	discount	totalRevenue	paymentMethod	invoiceID	invoiceDate	invoiceAmount	salesStatus	regionManager	regionPhone	regionEmail
2001	PR101	Laptop	C001	Alice Brown	2022-10-01	1200	1	R001	North	S001	John White	100	100	50	1150	Credit Card	INV1001	2022-10-02	1200	Completed	David Grey	555-1212	[email protected]
2002	PR102	Smartphone	C002	Bob Green	2022-10-02	1600	2	R002	East	S002	Lisa Black	150	150	100	1550	PayPal	INV1002	2022-10-03	1600	Completed	Emma White	555-2323	[email protected]
2003	PR103	Tablet	C003	Charlie Smith	2022-10-03	1800	3	R003	South	S003	Michael Blue	200	200	150	1700	Bank Transfer	INV1003	2022-10-04	1800	Completed	John Brown	555-3434	[email protected]
2004	PR104	Monitor	C004	David Johnson	2022-10-04	500	2	R001	North	S001	John White	50	50	25	475	Credit Card	INV1004	2022-10-05	500	In Progress	David Grey	555-1212	[email protected]
2005	PR105	Keyboard	C005	Emily Brown	2022-10-05	50	1	R002	East	S002	Lisa Black	10	10	5	45	PayPal	INV1005	2022-10-06	50	Completed	Emma White	555-2323	[email protected]
2006	PR106	Mouse	C001	Alice Brown	2022-10-06	25	1	R001	North	S001	John White	5	5	2	23	Credit Card	INV1006	2022-10-07	25	Completed	David Grey	555-1212	[email protected]
2007	PR107	Printer	C002	Bob Green	2022-10-07	100	1	R002	East	S002	Lisa Black	10	10	5	95	PayPal	INV1007	2022-10-08	100	Completed	Emma White	555-2323	[email protected]
2008	PR108	Headphones	C003	Charlie Smith	2022-10-08	140	2	R003	South	S003	Michael Blue	20	20	10	130	Bank Transfer	INV1008	2022-10-09	140	Completed	John Brown	555-3434	[email protected]
2009	PR109	Speakers	C004	David Johnson	2022-10-09	90	1	R001	North	S001	John White	15	10	5	85	Credit Card	INV1009	2022-10-10	90	In Progress	David Grey	555-1212	[email protected]
2010	PR110	Web Camera	C005	Emily Brown	2022-10-10	40	1	R002	East	S002	Lisa Black	5	5	2	38	PayPal	INV1010	2022-10-11	40	Completed	Emma White	555-2323	[email protected]

Step 2: Document Your Process using Hashnode

For each sheet, screenshot then upload it to hashnode.com:
1. How you split the data into multiple tables.
2. Which columns were redundant and how you removed those redundancies.

Activity: Normalizing Denormalized Data Using Excel

Steps for the Activity

Step 1: Create an Excel Workbook with 5 Sheets

1. employeeData (Denormalized)

2. studentData (Denormalized)

3. productData (Denormalized)

4. orderData (Denormalized)

5. salesData (Denormalized)

Step 2: Document Your Process using Hashnode

Activity

Activity: Research Normalization and Denormalization in Databases

More from this blog

Set Up Python fast API

Set Up React Typescript with Vite | Thirdy Gayares

Angular API

Yarn vs Npm

How to Create MYSQL Table with Foreign Key Constraint and add sample data

Command Palette

Steps for the Activity

Step 1: Create an Excel Workbook with 5 Sheets

1. employeeData (Denormalized)

2. studentData (Denormalized)

3. productData (Denormalized)

4. orderData (Denormalized)

5. salesData (Denormalized)

Step 2: Document Your Process using Hashnode

Activity

Activity: Research Normalization and Denormalization in Databases

More from this blog