For best success read over the problems listed below, return to the textbook, and make sure you have practiced these concepts and be sure to review my “Instructor Tutorials” for examples and video. You will see an additional resource for #2, allow time to review this resource.
- 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:
https://datacadamia.com/data/type/relation/modeling/cardinality).
Take a screenshot of your model and name the file Assignment5No1. Submit your discussion in a pdf file named Assignment5DataModel.pdf (or readme.md file if you are using GitHub). You will use this same pdf for #2.
- Normalize the following information
| StudentID | Student Name | Address | Classes | Major | |
| S1 | Joe Green | 124 Main St. | [email protected] | IT1025/MATH1200
IT1050 |
Programming |
| S2 | Sue Smith | 345 Second St. | [email protected] | IT1025/IT1050/IT2351 | Programming |
| S3 | Nick Green | 45 York Road | [email protected] | 1025 | Networking |
| S4 | Andy Andrews | 600 5th Ave. | [email protected] | 1025/1050 | Networking |
- Add three more rows of your own data.
- Identify the tables and primary and foreign keys.
- 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.
- 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.
- 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
- Save your code to a sql file named items_ordered_view
- Take a screenshot of output and name it Assignment5No3.png
- Upload your files to Blackboard or GitHub. Please upload individual files rather than a zip file (unless you are including your GitHub zip as a backup to the URL) to avoid delays in grading.