CSCI A114 / INFO I111 Final Exam First semester 2001-2002

Please write your name or username here: ____________________

This exam has two parts.

1. Read the problem below and provide answers, turn them in within 50 minutes.

2. Take this exam home and turn in your revised answers (on paper only) in lab tomorrow.

• Part 1 is mandatory.
• Part 2 is optional.
• Your grade will be based on the average of your grades on Part 1 and Part 2.

• The exam is open-book, open-notes.

The nominees for the Performance, Variety or Music Program Emmy Awards 2001 are listed below. They have been rated by three judges, the omnipresent Larry, Michael, and Toni. The ratings are on a scale from 1 to 10, with 1 being the lowest, and 10 being the highest rating. A judge must rate at least one nominee, but can rate as many as the judge wants to rate. Here are the (final) ratings.

	Judge One (Larry )	Judge Two (Michael)	Judge Three (Toni)
Steve Martin	8	3	6
Barbra Streisand	7	-	8
Ellen DeGeneres	-	-	-
David Letterman	9	-	2
Will Ferrell	-	5	4
Wayne Brady	6	7	-

A dash (-) in the table above means no rating (NULL).

Please turn the page to find out what is being asked of you for this exam.

Please accomplish the following tasks:

1. Design a database to hold the data for such a circumstance (tables, columns, keys).

2. Draw your tables and populate your tables with the data above.

3. SQL: List nominees with at least three votes (ratings).

4. SQL: List nominees that did not receive any votes (ratings).

5. SQL: List all the nominees.

6. SQL: List all nominees sorted by their average rating (descending).

Please note:

• Each question carries the same weight (16/100 points each).

• For those questions marked (SQL) you need to write SQL queries.

• Your SQL queries should refer to the tables you defined in the answers to questions 1, 2.