Home‎ > ‎CIS 54‎ > ‎

Practice Midterm


This is the midterm from spring 2016.  

Functional Dependencies

Part 1:

You have the following table:

    T1 ( A, B, C, D, E, F, G, H )

The data has the functional dependencies:

    A -> (C, D, E)

    (A, B) -> (F, G, H)

Given the functional dependencies, normalize the table T1 into new tables that are in BCNF. For each of the tables you create briefly describe why you created the table and why the table is properly normalized. Your table definition should have primary keys underlined and foreign keys in italics.

Part 2:

You discover a multivalued dependency in your data:

    D ->-> E

Are your tables still normalized? If not redesign your tables so that they are in 4NF.

Where and Having

Describe in your own words the difference between the WHERE and the HAVING clauses. What restrictions are there, if any, on each clause. Give two examples of a query, one that requires a WHERE and one that requires a HAVING clause.

Your Schema

Download and import the schema in the SQL file linked below:

    courses.sql

Use the "Server->Data Import" function of MySQL Workbench to import the data into a personal schema. If you're using the command line on Opus you can create the table and data by executing the command:

    source /home/cis154/share/Midterm-Courses.sql;

You must have a schema selected prior to running the source command. Examine the data and find the functional dependencies in them.

Part 1

List the functional dependencies you find in the data. Check the data to verify that your FDs are correct. Justify each of your FDs with a sentence or two.

Part 2

Based on your FDs design a schema with tables that are in 4NF. If you don't have any multivalued dependencies then your tables should be in BCNF.

Write queries using the courses table to do the following:

  1. Write a query that returns all classes with "Database" in the title.
  2. Write a query that shows only the course with the largest difference between MaxUnits and MinUnits
  3. Write a query that shows how many courses have each TOPCode.

Extra Credit (+10)

Write a query starting with the #3 query from the previous question. Add to the query the number of units taught for each of the TOPCodes. Units taught is the number of sections offered times MaxUnits. Order your query from most to least units.

ċ
courses.sql
(1766k)
Michael Matera,
Mar 15, 2017, 8:48 AM
Comments