Home‎ > ‎CIS 54‎ > ‎

Lab 7 - Populate your Schema

In this lab you'll populate your resaurants schema using the rows in the inspection table. I have recreated the table with violations broken out into individual columns.

Introduction

In the last assignment you created a schema for the inspection data. Because the data has changed you should use the schema provided in this assignment. But, in order for you to populate your new schema two things must happen: 
  1. You must write a query that fixes the "Camp Hammer" problem.
  2. You must insert the fixed data into your schema.

Reference Schema 

Here's a schema that I came up with to hold the data. You may use it for this assignment. 

    Restaurant ( Name, Address, City )
    Inspection ( Name, Address, Date, Major..., Minor..., General, Verified )

The Major... and Minor... columns shown above are a shorthand for: 

    major_ft, major_fp, major_fs, major_vi, major_ws, major_eh, major_hw, 
    minor_ft, minor_fp, minor_fs, minor_vi, minor_ws, minor_eh, minor_hw, 

Each of those columns holds the number of those violation codes found during the corresponding inspection. In this schema I don't use surrogate keys. Write queries to create these tables, with the foreign key constraints and submit them on canvas. 

Fix the Data

In order to fix the data you need to group data entries that have the same name, address and date. We have no way of knowing what the real meaning of the data is so I've come up with a way to do the fix that is probably valid. When a restaurant has multiple entries you should:
  • Sum the Major, Minor and General counts
  • The Verified attribute should be
    • NULL if all the group is NULL
    • "Pending" if there are any pending inspections in the group
    • Yes if all non-NULL values are Yes (Hint… this is alphabetical order).
To  complete this part you must write a single (big and ugly query). If your query is correct it will combine this data:

MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
6/19/20150FT5n/a
MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
6/19/20150HW5n/a
MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
6/12/2015000n/a
MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
6/12/2015003n/a
Into this data: 

MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
6/19/20150FT, HW10n/a
 MOUNT HERMON CAMP & CONFERENCE CTR 
CONFERENCE DR/FOREST RD, MOUNT HERMON
 6/12/2015 0  0   3  n/a
NOTE: Your query should combine the numbers in the FT and HW columns as would fit in the schema shown above. I've shown the change as it would look on the website for clarity. 

The output of your query should have exactly the same number of columns as the input table. Test your query by inserting the data into a new table using the INSERT/SELECT syntax shown below. First create a new table that matches the webdata table: 

create table fixed_inspection like public_restaurant.inspection ; 

With the table created you can insert like this: 

insert into fixed_inspection <your-big-ugly-query-here> ; 

Verify that you have the right number of rows in your my_webdata table.

Populate Your Tables

Now that you have a clean copy of the data you can use the INSERT/SELECT syntax to add the data to the separate tables in your schema. For example if you're using the inspection table from the reference schema you can add values to your table like this: 

insert into Restaurant (Name, Address, City) 
    select Name, substring_index(address, ',', 1), substring_index(address, ',', -1) 
    from fixed_inspection;

Once you are done export your schema into the file my_restaurant.sql and upload it to Canvas. 

Helpful Hints

If you find that you created a table incorrectly you can delete it and start again with the command:

drop table <table-name-here>

This deletes all the data too. If you just want to delete the data you can run the command: 

delete from <table-name-here>

The "delete" syntax above does not work in MySQL Workbench. MySQL Workbench is designed to protect you from that kind of reckless delete all command. You can disable safe mode or you can simply drop and recreate the table.

Turn In
  1. The SQL queries you used to create tables.
  2. The SQL query you used to fix the data. 
  3. The SQL queries you used to populate your tables. 
  4. A dump of your schema named my_restaurant.sql
Submit your homework on canvas.

Grading

  • 10 points for your fix-it query
  • 10 points for a populated schema
Comments