Difference between revisions of "CSC220 Midterm 2010"
(→Problem 1) |
(→Some Explanations) |
||
| Line 8: | Line 8: | ||
==Some Explanations== | ==Some Explanations== | ||
| − | * DataPhone Table | + | * '''DataPhone''' Table |
** Everything should be self explanatory | ** Everything should be self explanatory | ||
| − | * Student Table | + | * '''Student''' Table |
** Status: | ** Status: | ||
*** '''F''' for first year | *** '''F''' for first year | ||
| Line 17: | Line 17: | ||
*** '''S''' for senior | *** '''S''' for senior | ||
*** Phone: whichever model the student is using | *** Phone: whichever model the student is using | ||
| − | * Trip Table | + | * '''Trip''' Table |
** '''Date''': the date the trip was recorded. '''DATE''' is a type supported by MySQL. It's not a string. Not a VARCHAR. | ** '''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. | ** '''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. | ** '''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. | ** '''StudentId''' is the Id of the student who recorded the trip. | ||
| + | * '''Weather''' Table | ||
| + | ** This table contains the average temperature recorded on a given date. | ||
Revision as of 20:14, 20 October 2010
--D. Thiebaut 00:58, 21 October 2010 (UTC)
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.
- You have to generate queries that answer the different questions, assuming that you have 4 tables in your database named DataPhone, Student, Trip, and Weather, and the dump of which is available here.
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
- Status:
- 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.
