setup.sql,
completed version of the Query.java starter code file, and the dbconn.properties file.Read this whole document before starting this project. There is a lot of valuable information here, including the Final Comments section at the bottom.
Congratulations, you are opening your own flight booking service!
You have access to a flights database. You will provide a service that lets users search through the database and book flights.
In this homework, you have two main tasks. The first is to design a database of your customers and the flights they book. The second task is to complete a working prototype of your flight booking application that connects to the database then allows customers to use a command-line interface to search, book, and cancel flights. We have already provided code for a complete UI and partial back end; you will implement the rest of the back end. In real-life, you would develop a Web-based interface instead of a command-line interface, but we use a command-line interface to simplify this homework.
Your system will be a Java application. Download the starter code files; you will see the following files:
FlightService.java: the command-line interface to your flight-booking service.
Calls into Query.java to run customer transactions.
Query.java: code to run customer transactions against
your database, such as booking flights and reviewing reservations.
dbconn.properties: a file containing settings to connect
to your database. You need to edit it before running the starter code.
sqljdbc4.jar: the JDBC to SQL Server driver.
This tells Java how to connect to a SQL Azure database server, and needs to be
in your CLASSPATH (see below)
sqljdbc.jar: the JDBC to SQL Server driver for older versions of Java. Use this driver only if the other one does not work for you.
To run the starter code, you can simply run javac and java from the command line.
Please follow the instructions for your platform as shown in the table below. The last command launches the starter code. Normally, you run it like this:
java FlightService
| Windows | Linux or Mac |
|---|---|
cd \where\you\unzipped\the\starter\code [replace the directory below with your JDK's bin\ directory] path C:\Program Files\Java\jdk1.7.0_67\bin;%path% set CLASSPATH=.;sqljdbc4.jar javac -g FlightService.java Query.java java FlightService |
cd /where/you/unzipped/the/starter/code export CLASSPATH=.:sqljdbc4.jar javac -g FlightService.java Query.java java FlightService |
If you got an error message about the JDBC driver when running the above, try to use the older driver sqljdbc.jar instead of sqljdbc4.jar.
Or you can use Eclipse
You also need to access your Flights database on SQL Azure from Homework 3. Modify dbconn.properties with your servername, username, and password for SQL Azure. This allows your java program to connect to the database on SQL Azure. In practice, in is not safe to store passwords openly in text files, but it is ok for this assignment. Also, you can use the small version of the flights dataset. We are assuming that all queries search and book flights for July 2015, which is in the small dataset. It's ok if your database contains more flight data.
Now you should see the command-line prompt for your Filght service:
*** Please enter one of the following commands *** > login <username> <password> > search <origin_city> <destination_city> <direct> <date> <nb itineraries> > book <itinerary_id> > reservations > cancel <reservation_id> > quit >
The search command works (sort of). Try typing:
search "Seattle WA" "Boston MA" 1 14 10
The user is requesting to list the top 10 direct flights from Seattle to Boston on July 14th 2015 ordered by flight time, which is the sum of the actual_time values for all flights in the itinerary.
You should see a list of flights between the origin and destination city for your desired data. The starter code only lists direct flights.
The requirements for the flight service system are the following:
Your first task is to design and add tables to your flights database. You need to create at least a Customer table. For each customer, the table should store a unique identifier, a full name, a zipcode, and a password. You should also create one or more tables to hold the reservations made by customers. The design of these tables are up to you. You can add other tables to your database as well.
What to turn in: a single text file called setup.sql with CREATE TABLE and INSERT statements for your customer database that populate each table with a few tuples (minimum 8 tuples): you will turn in this file. This file should be runnable on SQL Azure through SQL Server Management Studio. Write a separate script file with DROP TABLE statements; it's useful to run it whenever you find a bug in your schema or data (don't turn in this file).
Your second task is to write the Java application that your customers will
use, by completing the starter code.
You need to modify only Query.java. Except for the small change needed in Task 2A, do not modify FlightService.java because we will test your homework using the current version of FlightService.java.
Actually, you can modify FlightService.java if you want to improve your application, but make sure we can still test your Query.java on the original FlightService.java
What to turn in: the Java file Query.java.
When your application starts, it shows the above menu of options to the user. Your task is to implement the functionality behind these options.
Be sure to use SQL transactions when appropriate. The same user can log in multiple times from different terminals. Different users can also use the same application at the same time from different terminals.
Consider the search function.
Now type this at the prompt:
search "Seattle WA" "Boston MA' and actual_time > 300 and dest_city='Boston MA" 1 14 10
What happens? You get only flights longer than 300 min. Now type this:
search "Seattle WA" "Boston MA'; create table Foo(a int); SELECT year, month_id,day_of_month,carrier_id,flight_num,origin_city,actual_time from Flights where origin_city = 'Seattle WA" 1 14 10
Check that this statement actually did successfully create a new table. Imagine if it did other things instead like drop tables or look up the list of customers and their passwords. This is SQL injection: hackers like to do it on Website interfaces to databases. We provide a correct, fixed function. Update FlightService.java to use the correct function.
You must use SQL transactions
in order to guarantee ACID properties: you must define begin- and end-transaction statements,
and insert them in appropriate places in Query.java. In particular, you must
ensure that the following constraints are always satisfied, even if multiple instances of your
application talk to the database at the same time.
C1. Each flight should have a maximum capacity that must not be exceeded. For this assignment, you can make it very small like 3. So only 3 people will be able to book any given flight.
C2. A customer may have at most one reservation for any given day. If you choose to use multiple reservations for an itinerary, then ensure that a customer may have at most one itinerary for any given day.
You must use transactions correctly such that race conditions introduced by concurrent execution cannot lead to an inconsistent state of the database. For example, multiple customers may try to book the same flight at the same time. Your properly designed transactions should prevent that.
Design transactions correctly. Avoid including user interaction inside a SQL transaction: that is, don't begin a transaction then wait for the user to decide what she wants to do (why?). The rule of thumb is that transactions need to be as short as possible, but not shorter.
When one uses a DBMS, by default each statement executes in its own transaction. To group multiple statements into a transaction, we use
BEGIN TRANSACTION
....
COMMIT or ROLLBACK
This is the same when executing transactions from Java, by default each SQL statement will be executed as its own transaction. To group multiple statements into one transaction in java, you can do one of three things:
Approach 1:
We provide you with three helper methods. So before your first statement in the transaction, simply execute
beginTransaction();
When you are done with the transaction, then call:
commitTransaction();
OR
rollbackTransaction();
Approach 2:
You can execute the SQL code for START TRANSACTION and friends directly, using the SQL we have provided in the starter code (also check out SQL Azure's transactions documentation):
// When you start the database up Connection conn = [...] conn.setAutoCommit(true); // This is the default setting, actually conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // In each operation that is to be a multi-statement SQL transaction: conn.setAutoCommit(false); // You MUST do this in order to tell JDBC that you are starting a multi-statement transaction beginTransactionStatement.executeUpdate(); [... execute updates and queries.] commitTransactionStatement.executeUpdate(); [OR] rollbackTransactionStatement.executeUpdate(); conn.setAutoCommit(true); // To make sure that future statements execute as their own transactions.
Approach 3:
// When you start the database up Connection conn = [...] conn.setAutoCommit(true); // This is the default setting, actually conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); // In each operation that is to be a multi-statement SQL transaction: conn.setAutoCommit(false); [... execute updates and queries.] conn.commit(); [OR] conn.rollback(); conn.setAutoCommit(true);
When auto comit is set to true, each statement executes in its own transaction. With auto-commit set to false, you can execute many statements within a single transaction. By default, on any new connection to a DB auto-commit is set to true.
To test that your transactions work correctly, we recommend the following (and this is how we will test your homework). Place a breakpoint in the middle of your transaction. Run two (or more?) instances of FlightService.java, say A and B. Let both reach the same point in the code then you decide which one you allow to proceed, and thus control the order in which the transactions are interleaved.
FINAL COMMENTS:
The starter code is designed to give you a gentle introduction to embedding SQL into Java. Start by running the starter code, examine it and make sure you understand the part that works. You will need to create new tables and slowly add code to the application.
The completed project has multiple simple SQL queries embedded in the Java code. Some queries are parameterized: a parameter is a constant that is known only at runtime, and therefore appears as a '?' in the SQL code in Java: you already have examples in the started code.