W03 Assignment: Design and Populate Art Database
- Purpose 1: To design a simple database from data that has not been normalized.
- Task 1: Create an ERD from data about artists and their artwork and keywords that go with each piece of art.
- Purpose 2: To populate (place data into) our database.
- Task 2: Provide code above the INSERT statements to recreate an empty database. Use the correct INSERT syntax and run these statements that will populate the database.
Design Art Database
Instructions
- Read through the Art Gallery Requirements to understand the database you will be designing and to understand the project we are starting with this homework.
- Using the Art Flat File Data, determine the entities you will use for each table of your database.
- Determine which attributes will belong to which entity. Also, according to the requirements, determine which data might be entities and which can remain as attributes. Don't forget to add a primary key to each entity.
- Determine how the tables will be related to each other. Resolve any many-to-many relationships with a linking table.
- Create an ERD model in workbench and give it the name of 'art'.
- Add the tables for each entity. There should be four total tables including linking tables. Use lower-case and no spaces for all table and attribute names. If you need help figuring out what tables to include, refer to the requirements in Step 1.
- Add the attributes and appropriate data types for each table. You will not add foreign keys yourself as column or attribute names. They will be added by Workbench when you determine the relationship in the next step. You don't have to auto-increment the primary key if you don't want to. Some attributes may need to have the option of being left null.
- Add the relationships between the tables and you will see the foreign keys added automatically. Make sure the many sides of the relationship has the foreign key. Remember, you shouldn't have table prefixes on foreign key names (meaning the foreign key and primary key should have the same name) since we changed the Workbench preferences. If the two names are different, refer back to the videos in Week 4.
Populate the Art Database
For the next part of the assignment you will add data to the art database you just created. Watch the following video that demonstrates how to create a database from your ERD. This is called forward engineering in MySQL Workbench.
Instructions
- Using your design from the previous step, forward Engineer the art-gallery ERD to create an empty database called art-gallery.
- Using the data from art-flatfile.xlxs, insert data into all the tables of your database. Create a file with all the SQL INSERT commands and then run them to populate your database.
Submission
Submit two files in Canvas as follows:
-
A screenshot or image of the Entity-Relationship Diagram (ERD). To save the model you can save with a File - Save Model, it will create an mwb file for you. Do not turn in the .mwb file, however, it does not always open properly for the grader. Please submit the screenshot or a png file using File - Export - Export as PNG.
Use the video for reference if needed.
- A sql file containing the forward engineering code and your SQL INSERT commands.
Useful Links:
- Go to: Week Index • Course Home • Canvas