# Activity: Creating a Database and Tables in MySQL with Dummy Data ### Check our module on how to install MySQL on Linux or Windows, or watch our previous online class for guidance. In this activity, you will create a MySQL database named `denormalized_db` and define 5 tables (`employeetbl`, `studenttbl`, `producttbl`, `ordertbl`, and `salestbl`). You'll also insert the dummy data provided above into these tables using only the command line (no GUI). ### **1\. employeeData** | 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** | 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** | 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** | 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** | 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: Documen # Create a new blog post documenting the entire process of: * * Logging into MySQL via the command line. * Creating the database `denormalized_db`. * Defining the 5 tables (`employeetbl`, `studenttbl`, `producttbl`, `ordertbl`, `salestbl`). * Inserting the dummy data into each table. * Provide code snippets and brief explanations for each step. * Share the Hashnode link on the svfc portal once published. ### **Goal:** By completing this activity, you will have: * Created a MySQL database and defined 5 tables using the command line. * Inserted dummy data into each table. * Documented the process