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	john@example.com	2020-01-15	Developer	555-3456	lisa.white@example.com	2022-12-01	Active
102	Jane Doe	D002	HR	M002	Bob Brown	P002	Beta	65000	456 Maple Ave	New York	NY	10002	555-5678	jane@example.com	2019-11-05	Designer	555-8765	bob.brown@example.com	2023-01-15	Completed
103	Bob Brown	D003	Sales	M003	Emma Blue	P003	Gamma	55000	789 Oak Dr	New York	NY	10003	555-8765	bob@example.com	2021-02-12	Analyst	555-9091	emma.blue@example.com	2022-11-20	Active
104	Lisa White	D001	IT	M001	Lisa White	P001	Alpha	70000	234 Pine St	New York	NY	10004	555-3456	lisa.white@example.com	2018-09-08	Manager	555-3456	lisa.white@example.com	2022-12-01	Active
105	Tom Green	D002	HR	M002	Bob Brown	P002	Beta	50000	567 Birch Blvd	New York	NY	10005	555-6543	tom.green@example.com	2017-03-25	Developer	555-8765	bob.brown@example.com	2023-01-15	Completed
106	Lucy Black	D001	IT	M001	Lisa White	P004	Delta	62000	123 Elm St	New York	NY	10001	555-1299	lucy.black@example.com	2020-06-20	Developer	555-3456	lisa.white@example.com	2022-09-05	Active
107	Mike Grey	D002	HR	M002	Bob Brown	P002	Beta	54000	456 Maple Ave	New York	NY	10002	555-7801	mike.grey@example.com	2019-08-13	Designer	555-8765	bob.brown@example.com	2023-01-15	Completed
108	Emma Blue	D003	Sales	M003	Emma Blue	P005	Epsilon	58000	789 Oak Dr	New York	NY	10003	555-9091	emma.blue@example.com	2021-04-05	Analyst	555-9091	emma.blue@example.com	2023-05-01	Active
109	Liam Red	D003	Sales	M003	Emma Blue	P003	Gamma	59000	234 Pine St	New York	NY	10004	555-7890	liam.red@example.com	2018-10-02	Manager	555-9091	emma.blue@example.com	2022-11-20	Active
110	Noah Yellow	D001	IT	M001	Lisa White	P004	Delta	61000	567 Birch Blvd	New York	NY	10005	555-6789	noah.yellow@example.com	2017-07-18	Developer	555-3456	lisa.white@example.com	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	alice@example.com	2021-08-10	Mary Johnson	555-1010	mary.j@example.com	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	bobw@example.com	2020-09-15	Paul White	555-1020	paul.w@example.com	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	charlieb@example.com	2019-07-05	Susan Brown	555-1030	susan.b@example.com	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	davidg@example.com	2022-01-17	Kate Green	555-1040	kate.g@example.com	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	emilyg@example.com	2021-03-13	Henry Grey	555-1050	henry.g@example.com	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	fionab@example.com	2019-09-05	Mary Johnson	555-1010	mary.j@example.com	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	georgeb@example.com	2020-04-20	Susan Brown	555-1030	susan.b@example.com	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	hannahr@example.com	2019-10-02	Paul White	555-1020	paul.w@example.com	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	iany@example.com	2021-05-18	Henry Grey	555-1050	henry.g@example.com	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	jacobw@example.com	2020-12-04	Kate Green	555-1040	kate.g@example.com	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	techsupply@example.com	W001	NY Warehouse	10	MAN001	Dell	555-8001	dell@example.com	2022-01-01	50000	2022-09-15	In Stock	LAP123	High-end laptop
PR102	Smartphone	CAT001	Electronics	SUP002	GadgetWorld	800	200	555-4002	gadgetworld@example.com	W002	LA Warehouse	20	MAN002	Apple	555-8002	apple@example.com	2022-01-15	160000	2022-10-01	In Stock	SMRT456	Latest model
PR103	Tablet	CAT001	Electronics	SUP001	TechSupply	600	100	555-4001	techsupply@example.com	W003	TX Warehouse	15	MAN001	Dell	555-8001	dell@example.com	2022-02-10	60000	2022-08-20	In Stock	TBL789	Lightweight tablet
PR104	Monitor	CAT001	Electronics	SUP003	OfficeDepot	250	75	555-4003	officedepot@example.com	W001	NY Warehouse	5	MAN003	Samsung	555-8003	samsung@example.com	2022-03-05	18750	2022-09-10	In Stock	MON123	27-inch monitor
PR105	Keyboard	CAT002	Accessories	SUP002	GadgetWorld	50	150	555-4002	gadgetworld@example.com	W002	LA Warehouse	10	MAN004	Logitech	555-8004	logitech@example.com	2022-04-01	7500	2022-08-30	In Stock	KYBD456	Mechanical keyboard
PR106	Mouse	CAT002	Accessories	SUP003	OfficeDepot	25	300	555-4003	officedepot@example.com	W003	TX Warehouse	20	MAN004	Logitech	555-8004	logitech@example.com	2022-05-12	7500	2022-09-05	In Stock	MSE789	Wireless mouse
PR107	Printer	CAT001	Electronics	SUP001	TechSupply	100	40	555-4001	techsupply@example.com	W001	NY Warehouse	5	MAN003	Samsung	555-8003	samsung@example.com	2022-06-15	4000	2022-09-20	In Stock	PRNT123	Laser printer
PR108	Headphones	CAT002	Accessories	SUP002	GadgetWorld	70	100	555-4002	gadgetworld@example.com	W002	LA Warehouse	15	MAN004	Logitech	555-8004	logitech@example.com	2022-07-20	7000	2022-08-25	In Stock	HDPH456	Noise-cancelling
PR109	Speakers	CAT001	Electronics	SUP003	OfficeDepot	90	60	555-4003	officedepot@example.com	W003	TX Warehouse	10	MAN003	Samsung	555-8003	samsung@example.com	2022-08-10	5400	2022-09-25	In Stock	SPKR789	Bluetooth speakers
PR110	Web Camera	CAT001	Electronics	SUP001	TechSupply	40	80	555-4001	techsupply@example.com	W001	NY Warehouse	5	MAN001	Dell	555-8001	dell@example.com	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	alice.b@example.com	1200	100	S001	John White	555-6789	john.white@example.com	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	bob.green@example.com	1600	150	S002	Lisa Black	555-6790	lisa.black@example.com	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	charlie.s@example.com	1800	200	S003	Michael Blue	555-6791	michael.blue@example.com	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	david.j@example.com	500	50	S001	John White	555-6789	john.white@example.com	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	emily.b@example.com	50	10	S002	Lisa Black	555-6790	lisa.black@example.com	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	alice.b@example.com	25	5	S001	John White	555-6789	john.white@example.com	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	bob.green@example.com	100	10	S002	Lisa Black	555-6790	lisa.black@example.com	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	charlie.s@example.com	140	20	S003	Michael Blue	555-6791	michael.blue@example.com	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	david.j@example.com	90	15	S001	John White	555-6789	john.white@example.com	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	emily.b@example.com	40	5	S002	Lisa Black	555-6790	lisa.black@example.com	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	david.grey@example.com
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	emma.white@example.com
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	john.brown@example.com
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	david.grey@example.com
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	emma.white@example.com
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	david.grey@example.com
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	emma.white@example.com
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	john.brown@example.com
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	david.grey@example.com
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	emma.white@example.com

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.