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 artist 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 Statement of Work for the Virtual Art Gallery 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 statement of work, 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 Statement of Work 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.
If you need help getting started with this part of the assignment watch this: Assignment Help
NOTE: If you watch the video you will see a reference to the art_gallery.mwb model file. You will not need this file since you will have created your database model in the previous task. If you would like to see the file feel free to download it art_gallery.mwb
Instructions
- Using your design from the previous step, forward Engineer the art_gallery ERD to create an empty database called art_gallery. Review the Workbench 4 (11:20 mins, “Workbench 4” Transcript) that we viewed in Week 2. Start at 0:00 to 2:16 if you need a reminder on how to forward engineer. Copy the forward engineered code that is created during this process as the first part of your assignment that you will submit.
- Using the data from the Art Flatfile you downloaded earlier, 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:
- Next Activity: Complete W01 Prove Quiz and W01 60-second Status Update in Canvas
- Previous Activity: Team Activity: CRUD Practice
- Go to: Week Index • Course Home • Canvas