1. Use MySQL Workbench to create an EER model for your guitar database (the administrators table is not related to the other six tables.) If you are using a virtual machine, the relationship lines will not show as in the textbook example which is OK. Explain the relationships between the tables listing the primary and foreign keys and, the cardinality between two tables of your choice (this concept is not in your textbook so, please use the following resource:
2. Normalize the following information
StudentID
Student Name
Address
Email
Classes
Major
S1
Joe Green
124 Main St.
Joe@school.edu
IT1025/MATH1200
IT1050
Programming
S2
Sue Smith
345 Second St.
Sue@school.edu
IT1025/IT1050/IT2351
Programming
S3
Nick Green
45 York Road
Nick@school.edu
1025
Networking
S4
Andy Andrews
600 5th Ave.
Andy@school.edu
1025/1050
Networking
a. Add three more rows of your own data.
b. Identify the tables and primary and foreign keys.
c. Explain in the same pdf or readme.md file, how you achieved each normal form (first – third). Be sure to go through the included resources to maximize your understanding of the concepts.
d. Create the database by either creating a script (you can edit the Guitar database script) or a model (see my video and the text). Save your script as
Assignment5No2.sql. If you created the database in the model GUI select “Database” “Forward Engineer” to create the script for upload. Once created, take a screenshot of your database in MySQL Workbench, name the file Assignment5No2.png.
3. Create a view using the guitar database named items_ordered that shows the item name, product name, item price and quantity for each item ordered. 10 points
a. Save your code to a sql file named items_ordered_view
