Assigned: Wednesday, April 12
Due: Wednesday, April 26 11:59 PM PDT
In this lab, you will be designing a relational database schema for modeling a real-world application: a carpool (ride-sharing) application. You will be responsible for designing the schema to capture the constraints of the application, discussed in more detail below. You will also be tasked with writing several SQL queries to support required functionality for this carpool application. As part of this lab, you will need to be sure that your schema and queries operate correctly using the RDBMS SQLite.
You will be modeling a carpooling application: a group of people participate in a carpool program, sharing rides in their cars driving to and from work. By carpooling, they can take advantage of being able to drive in the carpool lane on the highway (at least two passengers are required for a car to drive in that lane). Each day, a subset of the people drive, while the other people are passengers in their cars. In particular, the application has the following semantics:
The driving schedule for one day might look something like this:
November 20, 2015: 6:45am -- Driver: Alice (in Toyota Prius), Passengers: Bob, Carl, Denise 8:00am -- Driver: Ellen (in Chevy Volt), Passengers: Francis, Greg 5:40pm -- Driver: Ellen (in Chevy Volt), Passengers: Bob, Francis 6:30pm -- Driver: Alice (in Toyota Prius), Passengers: Carl, Denise, Greg
In this lab, you will use SQLite 3 to run queries. SQLlite is a lightweight database system that supports a reasonably large subset of SQL; a SQLite database is maintained in a single file. SQLite is available on knuth, but can also be installed on your own computer if desired (and might already be installed, see resources). After you log into knuth, you can issue the following command to use SQLite:
sqlite3 myLab5.dbThis will create a new database with the filename myLab5.db if the database does not already exist, and you will now see the sqlite> prompt. If you have previously created a database with this filename, this command will load that database.
We discussed getting started with SQLite in class, you may wish to refer back to the notes from Lectures 1 and 21. Feel free to also consult the example files for the Sailors and College databases.
SQLite.org has a plethora of information about syntax, data types, etc. You may find the following pages particularly useful:
Design a relational schema to model the semantics of the carpool application. You should consider which relations, attributes, and primary and foreign keys are appropriate. You may start by modeling the application with an Entity/Relationship diagram, but that is not required. You do not need to create indexes. You may wish to skim Exercises 4-6 to see what SQL queries you will eventually have to write, and note that Exercise 7 asks you to create sample data.
Now it is time to reflect on your relational schema, possibly refining it if necessary. You should think about what functional dependencies (FDs) make sense for this application, how those FDs may or not indicate data redundancy in your schema, and what integrity constraints may exist but are not currently captured by the constraints in your schema.
Every weekend, one of the carpool participants (called the "carpool czar") sets a driving schedule for each day of the upcoming week. This is a complex process that includes balancing each person's preferences regarding the times they arrive at work and leave each day. While you will not need to devise an algorithm to construct driving schedules, you will write queries to help the czar validate that he or she did not make an error when creating the week's schedules.
For Exercises 4-6, write an SQL query, or sequence of queries, that will accomplish the following tasks. Where appropriate, denote if a sequence of queries should be run as a single transaction, using BEGIN TRANSACTION; and END TRANSACTION;.
Note: for these queries, it is not sufficient to give the user two sets of query results and ask him/her to visually compare the two sets.
Of course, people sometimes have issues that arise, and they need to amend the established schedule for the week. They may realize they need to stay at work later in the day than originally planned, or need access to their own car during the day.
Possibly helpful: Deferring foreign key checking (see comment in Section 1.2) and creating temporary tables in SQlite (see example here).
Of course, people often want to read their schedule for the day so that they know when they are going to work. Also, it is sometimes necessary to investigate the fairness of the driving schedules over time. The following two features support these functionalities.
You should create some sample data to sanity-check your SQL queries. You can import data into your database in SQLite using a series of INSERT statements, or you may prefer to use the SQLite import command (see https://www.sqlite.org/cli.html#csv for more details).
You should include this sample data, as well as the output of your queries from Ex. 4-6, in your submission. Some helpful commands for generating output files:
You can specify in the SQLite console that output should be written to a specified file using the command .output. For example, to write the result of a query to the file query1output.sql, you could do the following:
sqlite> .output query1output.sql sqlite> SELECT * FROM MyCoolRelation;Note: This will overwrite any contents of query1output.sql if the file exists already.
sqlite> .output myDbContents.sql sqlite> .dump
sqlite> .output stdout
The files you need to submit are (you can zip them if desired):
Submit your files under the "Lab 5" assignment on Sakai.
We will be running your SQL queries using your relational schema in SQLite, so be sure that they are syntactically correct. You will be evaluated on how well your design captures the semantics and constraints of the real-world application, as well as the correctness of your queries.