Querying and Updating Data.
In this project assignment, we will do queries and updates based on
the database schema we created in Project 2. We plan to give a sample
solution to Project 2 later; you may update your database schema based
on ours if you wish.
What to do? Write SQL statements that do the following
sequence of query and update operations. Your SQL statements should
be able to handle any database instance, i.e., for any set of users,
daily commutes, and other trips, etc.
- Delete all users, all daily commutes, all other trips, and all
related information. That is, empty your the database.
- Insert the following users and related information:
(u1, passwd1, Johnson, Mary, student resident, Steeley, A101,
biology, freshman, 631-111-2222, mary@yahoo.com, 17, F, owns Honda
Civic 2000, non smoker, share driving)
(u2, passwd2, Smith, John, student commuter, East Avenue, Good
Town, NY, 12345, Inter Street, computer science, senior, 111-222-2222,
john@yahoo.com, 21, M, owns Ford Taurus 2001, non smoker, drive only,
$80)
(u3, passwd3, Brown, Bill, faculty, East Avenue, Good Town, NY,
12345, Inter Street, computer science, professor, 111-222-3333,
bill@yahoo.com, 35, M, owns Toyota Camry 2001, non smoker, share
driving, $120)
(u4, passwd4, Kennedy, Lisa, staff, East Avenue, Good Town, NY,
12345, Inter Street, music, administrator, 111-222-4444,
lisa@yahoo.com, 55, F, owns Chevy Cavalier 1999 and Toyota Celica
1995, non smoker, share driving, $100)
- Insert the following daily commutes:
(u2, start 8:30am, end 5pm, within 40min earlier, within 30min
later, 2002, spring)
(u3, start 9am, end 4:30pm, within 60min earlier, within 60min
later, 2002, spring)
(u4, start 8:30am, end 4:30pm, within 30min earlier, within 30min
later, 2002, spring)
- Insert the following other trips:
(trip1, u1, 03/31/2002, visiting friends, from Steeley A101, to
East Avenue Good Town NY 12345 at Inter Street, depart at 4:30pm,
arrive at 5:30pm, within 30min earlier departure and 45min earlier
arrival, ride offered, 2 passengers capacity, $15)
(trip2, u2, 03/30/2002, festival, from East Avenue Good Town NY
12345 at Inter Street, to South Station Boston MA 01800 at South
Station, depart at 9am, arrive at 3pm, within 20min earlier departure
and 60min earlier arrival, ride offered, 3 passengers capacity, $20)
(trip3, u2, 03/31/2002, meeting classmates after festival, from
South Station Boston MA 01800 at South Station, to Steeley A102,
depart at 10:30am, arrive at 4:20pm, within 60min earlier departure
and 20min earlier arrival, ride offered, 3 passengers capacity, $20)
(trip4, u4, 03/30/2002, festival, from East Avenue Good Town NY
12345 at Inter Street, to South Station Boston MA 01800 at South
Station, depart at 9am, arrive at 3pm, within 60min earlier departure
and 60min earlier arrival, ride offered, 3 passengers capacity, ride
also requested, for 1 passenger, $25)
(trip5, u4, 03/31/2002, back from festival, from South Station
Boston MA 01800 at South Station, to East Avenue Good Town NY 12345 at
Inter Street, depart at 11am, arrive at 6pm, within 60min earlier
departure and 60min earlier arrival, ride offered, 3 passengers
capacity, ride also requested, for 1 passenger, $25)
- Return all required information and optional information about a
user (given login name, e.g., use u1).
- Return all information about the daily commute, if it exists,
posted by a user (given login name, e.g., use u2).
- Return all information about all other trips, if any, posted by a
user (given login name, e.g., use u4).
- Count the total numbers of users, students, faculty/staff, students
living on campus, users living off campus, daily commutes, and other
trips, respectively.
- Return all matching users (login names) for a daily commute (given
login name, i.e., use u3), where matching means with the same
residence address, within time constraints, and with compatible
preference for driving or riding; sort the result first by cost
compensation and then by the sum of minimum wait time, both in
increasing order.
Clarification: Given the daily commute of user A, suppose
it matches the daily commute of user B.
with compatible preference for driving or riding: if not
both A and B prefer "drive only" and not both A and B prefer "ride
only" (this interprets the preference of "share driving" as flexible).
within time constrains: if the allowed range of arrival
time of A (between the start time specified and the allowed minutes
earlier arrival specified) overlaps with the allowed range of arrival
time of B, and similarly for departure time.
sum of minimum wait time for A with respect to B: the
total time A must wait because of going with B for both starting and
ending at school. For example, if we have
A: start 9am, end 4:30pm, within 60min earlier, within 60min later
B: start 8:30am, end 5pm, within 40min earlier, within 30min later
then the sum of minimum wait time for A with respect to B is 1 hour
(30min for starting plus 30min for ending). For example, for
starting, B has range 7:50-8:30am, but A wants 9am, so the minimum
between B and A is 30min. On the other hand, if B is given, and A
matches it, then the sum of minimum wait time for B with respect to A
is 0min.
- Return all matching trips (trip id) for a given trip (given trip
id, e.g., use trip4), where matching means with the same date, same
from and to addresses (Stony Brook campus considered the same
address), within time constraints, and matching requests and offers;
sort the result first by cost compensation and then by sum of minimum
difference from the desired times, both in increasing order.
Clarification: offers and requests are explicit here so matches
are obvious; matching within time constraints is similar as for daily
commute; the sum of minimum difference from the desired times is also
similar as the sum of minimum wait time for daily commute. For
example, if we have
A: depart 9am, within 30min earlier, arrive 4:30pm, within 60min earlier
B: depart 8:30am, within 60min earlier, arrive 4pm, within 40min earlier
then the sum of minimum difference from the desired times for A with
respect to B is 1 hour (30min for departure plus 30min for arrival).
For example, for departure, B has range 7:30-8:30am, but A wants 9am,
so the minimum between B and A is 30min. On the other hand, if B is
given, and A matches it, then the sum of minimum difference from the
desired times for B with respect to A is 0min.
- Return all pairs of two consecutive trips (trip ids) that together
match a given trip (given trip id, e.g., use trip5). All other
aspects are similar to the query above.
- Compute the average age of freshmen, seniors, graduate students, and
faculty/staff, respectively.
- Return a list of pairs of the form (number of cars owned, number of
people own that many cars), sorted by increasing order of the number of
cars owned). For example, (1 100) (2 10) (3 1) means that 100 people own
1 car each, 10 people own 2 car each, and 1 person owns 3 cars.
- Return all users (login names) that live at the same residence
address as a given user (given login name, e.g., use u2).
- Delete a daily commute of a user (given login name, e.g., use u2).
- Delete a trip of a user (given trip id, e.g., use trip1).
- Delete a user (given login name, e.g., use u3) and all postings by
the user.
- Delete all obsolete postings of daily commutes and other trips. For
daily commutes, assume that spring semester ends on April 31, summer ends
on August 31, and Fall ends on December 31.
You need to run your SQL program to perform the queries and
updates.
What to deliver? Hand in printout of the following items:
your SQL code for the query and update operations (with comments for
anything not obvious), the results (dump) of executing each operation,
and the content of the tables at the end.
Team work: Continue working with the partner you had for
Project 2; if you did not try to look for a partner for Project 2, you
should do it now, for this project and the final project.
Each team should hand in only one copy of the assignment solution;
include identification information for both team members.
Bonus: (5% extra credit) Is it possible to write an SQL
query that finds all any-length consecutive trips that together match
a given trip, regardless of the database content? Justify your
answer.
(25% extra credit) As the bonus part in Project 2, copied below,
plus the only additional requirement is that you run the queries for
this assignment on the large data set as well and report results of
the queries (when the results are too big, report only statistics
about the results, including minimally the count of result sets); in
particular, report any interesting performance observations.
If you have done the bonus part of Project 2, you can improve it
and say how you improved; this allows you to get full extra credit if
you had not. You will also get 10% extra credit for running the
queries for this assignment and reporting the results on the large
data set.