Home‎ > ‎CIS 54‎ > ‎

Midterm Review

This lecture is dedicated to giving you time to review before the midterm. The midterm will ask you to take a dataset like the one in this ZIP file: 


The data is a spreadsheet with the following columns: 
  1. DVD_Title
  2. Studio
  3. Released
  4. Status
  5. Sound
  6. Versions
  7. Price
  8. Rating
  9. Year
  10. Genre
  11. Aspect
  12. UPC
  13. DVD_ReleaseDate
  14. ID
  15. Timestamp
Work alone or with a team to answer the following: 
  1. What are the FDs in this data? Be specific and clear, justify each FD you find. 
  2. Given your FDs design a schema for this data. 
    1. How does your schema handle DVDs with multiple aspect ratios? 
    2. How would your schema change if a movie could be in more than one genre? 
    3. What NULL values are the most problematic in this data set? Why? 
Once you're satisfied with your answers create tables to implement your schema. Be sure to include foreign and primary keys. 

Practice Queries

The following queries are for the public_art_class database. 
  1. Write a query that shows the courses that cost less than $400 
  2. Write a query that selects only the Pastel courses from public_art_class 
  3. Write a query that produces just one row, the course in public_art_class that has the latest starting date.
  4. Write a query that prints how many people are enrolled in each course. You don't have to print the course name, only the course number. Name your columns CourseNumber and Enrollment
The following queries are for the public_garden_glory database.
  1. Write a query that prints all of the available services in the order from least to most expensive. 
  2. Write a query that shows how many properties are in each city served by Garden Glory. 
  3. Write a query that shows service dates where there are three or more total combined hours worked. 
The following queries are for the public_restaurant database.
  1. Write a query that shows how many restaurants have more "Generals" on their current inspection than their previous inspection. 
  2. Write a query that sums each of the major and minor columns in the current inspection into the totals that you see in the previous inspection. 
  3. Write a query that shows restaurants that have more major AND minor violations on their current inspection than they did on their previous inspection
Subpages (1): Practice Query Solutions
ċ
dvd_csv.zip
(10588k)
Michael Matera,
Mar 16, 2016, 5:11 PM
Comments