Activity: Normalizing Denormalized Data Using Excel
Published
•15 min readT
I am a dedicated and skilled Software Engineer specializing in mobile app development, backend systems, and creating secure APIs. With extensive experience in both SQL and NoSQL databases, I have a proven track record of delivering robust and scalable solutions.
Key Expertise:
Mobile App Development: I make high-quality apps for Android and iOS, ensuring they are easy to use and work well.
Backend Development: Skilled in designing and implementing backend systems using various frameworks and languages to support web and mobile applications.
Secure API Creation: Expertise in creating secure APIs, ensuring data integrity and protection across platforms.
Database Management: Experienced with SQL databases such as MySQL, and NoSQL databases like Firebase, managing data effectively and efficiently.
Technical Skills: Programming Languages: Java, Dart, Python, JavaScript, Kotlin, PHP
Frameworks: Angular, CodeIgniter, Flutter, Flask, Django
Database Systems: MySQL, Firebase
Cloud Platforms: AWS, Google Cloud Console
I love learning new things and taking on new challenges. I am always eager to work on projects that make a difference.
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:
employeeDataSheet 2:
studentDataSheet 3:
productDataSheet 4:
orderDataSheet 5:
salesData
example:
1. employeeData (Denormalized)
| employeeID | employeeName | departmentID | departmentName | managerID | managerName | projectID | projectName | salary | address | city | state | zipCode | phone | 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 | 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 | 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:
How you split the data into multiple tables.
Which columns were redundant and how you removed those redundancies.
34 views
