CSC220 Midterm 2010

--D. Thiebaut 00:58, 21 October 2010 (UTC)

---

This midterm examination is given under the rules of the honor code. You cannot discuss any of the material on this exam with anybody except your instructor. Your work has to be done individually. You can ask questions to your instructor only in class or via email.

You have access to all material that is published or on the Web.

The midterm is due on Thursday, Oct 28th, at 11:59 p.m. + 1 minute.

Erratum

• --D. Thiebaut 17:58, 21 October 2010 (UTC)

Alex found a discrepancy in the phone table and the student table. I fixed the dump and changed the student table and replaced all the entries equal to 1 by 10 in the field `phone`. Please reload your database with the dump if you had already done so before now.

Problem 1

• For this problem, you have to create a series of queries and store them in a text file called test.sql, the same way you did in the previous homework.
• Your assignment is to create 4 tables in your database named DataPhone, Student, Trip, and Weather (the dump of which is available here) and to generate queries that answer the 10 questions below.
• You have to figure out how to use the dump of the 4 tables to generate copies of them in your database. You have several options.

Some Explanations

• DataPhone Table
  • Everything should be self explanatory
• Student Table
  • Status:
    • F for first year
    • O for sophomore
    • J for junior
    • S for senior
  • Phone: whichever model the student is using
• Trip Table
  • Date: the date the trip was recorded. DATE is a type supported by MySQL. It's not a string. Not a VARCHAR.
  • KML: the contents of the KML data sent by the phone. The type of this field is TEXT, a type also supported by MySQL.
  • Xmin, Xmax, Ymin, Ymax represent the coordinates taken from the KML (by some program that doesn't exist yet) that define the surrounding rectangle.
  • StudentId is the Id of the student who recorded the trip.
• Weather Table
  • This table contains the average temperature recorded on a given date, for several dates.

Requirements

• Use one or several views to help answer some of these questions.

Questions

Write queries and add them to a file called test.sql in the same sequence as the questions. You may have to use several queries to answer a question.

Question 1

What day was the coldest as recorded in Weather?

Question 2

Generate a list of the names of all the students who submitted trips, and for each one show the number of trips submitted.

Question 3

Generate a list of all the names of the students and the brand of phone they use.

Question 4

When was the first trip submitted?

Question 5

How many days has the experiment been going on? In other words how many days are there between the oldest and most recent trip?

Question 6

Missie just lost her phone and decided to replace it by a Nokia. Write a query that uses only the constants Missie and Nokia and that changes her phone to a Nokia. Write another query that shows that the change was correctly implemented.

Question 7

What phone (brand name please) do first year and sophomore students use?

Question 8

Generate a list of all the users (by name), their phone (brand name), their status (shown as "First Year", "Sophomore", "Junior", or "Senior", not 'F', 'O', 'J', or 'S', and the date they submitted each trip. Below is an example

              Marie  |  iPhone   |  First Year  |   2010-10-20

There should be one entry per trip.

Question 9

Claire decided that she wanted to remove all her trips from the system. Write a query (or several queries) that use only the constant Claire and that remove her trips from the table Trip. Write an additional query that shows the contents of the table after the deletion.

Question 10

Same as Question 3, but organize the output by status, i.e. all the first-years together, all the sophomores together, all the juniors together, and all the seniors together. The order in which the years are listed is not important.

Question 11 (Optional and Extra Credit)

Same as Question 10, but figure out a way that does not require to modify the orginal dump of the tables, and that allows you to list the first years first, sophomore next, then juniors, and finally seniors, in that order.

Submission

• Remember the rule "Do not edit before you submit!"
• Store the dump of the four tables in a text file called test.sql
• Add a header to this file that includes your name and account number.
• Add your queries with comments at the end of the file.
• Use the testSql.php program of Homework 5 to check that your queries all execute correctly.
• Submit the file as follows:

   submit   midterm   test.sql

My Recommendations

1. Get the job done: I am interested more in the result than in the efficiency of how you get the result. I'd rather see a collection of 10 queries that generate the correct answer to a question, than a very sophisticated nested query that does not generate the expected result. Also, if you cannot generate the correct answer, generate a result that is close to it, in some way. It will very likely be worth more than no answer at all.
   
2. Present your work well: Organize your answer file in a clean, readable way. It will be printed, so wrap the lines yourself at logical places in your queries and comments rather than rely on the printer to do so.
3. Cite your sources: You will likely find some hints for the answers in books or on the Web. Indicate where you found the information, either in one block at the end of your file (in a comment), or in a comment accompanying the answer.
4. Do not edit before you submit!: You know why!