Assignment Chef icon Assignment Chef

[Solved] CSE 344 Homework 5: Database Application and Transaction Management

5.0 1 customer review Digital download

Digital download

$25.00

Availability
In stock
Checkout
One item

Need a hand?

Message us on WhatsApp for payment or download support.

WhatsApp QR code Open WhatsApp
Objectives: To gain experience with database application development and transaction management. To learn how to use SQL from within Java via JDBC. Assignment tools:

Assignment Details

Read through this whole section before starting this project. There is a lot of valuable information here, and some implementation details depend on others. Congratulations, you are opening your own flight booking service! In this homework, you have two main tasks: You will also be writing a few test cases and explaining your design in a short writeup. We have already provided code for a UI (FlightService.java) and partial backend (Query.java). For this homework, your task is to implement the rest of the backend. In real life, you would develop a web-based interface instead of a CLI, but we use a CLI to simplify this homework. For this lab, you can use any of the classes from the Java 11 standard JDK.

Connect your application to your database

You will need to access your Flights database on SQL Azure from HW3. Alternatively, you may create a new database and define the Flights data as follows. Connect to our external Flights tables To improve performance and make sure you dont alter the flights data, reference the Flights tables as EXTERNAL TABLEs. Every query to the Flights tables from your database will be redirected to a faster class server. Configure your JDBC Connection You need to configure the appropriate information to connect Query.java to your database. In the top level directory, create a file named dbconn.properties with the following contents: 
# Database connection settings# TODO: Enter the server URL.hw5.server_url = SERVER_URL# TODO: Enter your database name.hw5.database_name = DATABASE_NAME# TODO: Enter the admin username of your server.hw5.username = USERNAME# TODO: Add your admin password.hw5.password = PASSWORD
Check your Azure server and fill in the connection details:

Build the application

Make sure your application can run by entering the following commands in the directory of the starter code and pom.xml file. This first command will package the application files and any dependencies into a single .jar file: This second command will run the main method from FlightService.java, the interface logic for what you will implement in Query.java: If you want to run directly without creating the jar, you can run: If you get our UI below, you are good to go for the rest of the lab! 
*** Please enter one of the following commands ***> create <username> <password> <initial amount>> login <username> <password>> search <origin city> <destination city> <direct> <day> <num itineraries>> book <itinerary id>> pay <reservation id>> reservations> cancel <reservation id>> quit

Data Model

The flight service system consists of the following logical entities. These entities are not necessarily database tables. It is up to you to decide what entities to store persistently and create a physical schema design that has the ability to run the operations below, which make use of these entities. Create other tables or indexes you need for this assignment in createTables.sql (see below).

Requirements

The following are the functional specifications for the flight service system, to be implemented in Query.java (see the method stubs in the starter code for full specification as to what error message to return, etc): Refer to the Javadoc in Query.java for full specification and the expected responses of the commands above. CAUTION: Make sure your code produces outputs in the same formats as prescribed! (see test cases and Javadoc for what to expect)

Testing:

To test that your application works correctly, we have provided a test harness using the JUnit framework. Our test harness will compile your code and run all the test cases in the provided cases/ folder. To run the harness, execute in the project directory: If you want to run a single test file or run files from a different directory (recursively), you can run the following command: For every test case it will either print pass or fail, and for all failed cases it will dump out what the implementation returned, and you can compare it with the expected output in the corresponding case file. Each test case file is of the following format: While weve provided test cases for most of the methods, the testing we provide is partial (although significant). It is up to you to implement your solutions so that they completely follow the provided specification. For this homework, youre required to write test cases for each of the commands (you dont need to test quit). Separate each test case in its own file and name it <command name>_<some descriptive name for the test case>.txt and turn them in. Its fine to turn in test cases for erroneous conditions (e.g., booking on a full flight, logging in with a non-existent username).

Milestone 1:

Database design

Your first task is to design and add tables to your flights database. You should decide on the relational tables given the logical data model described above. Feel free to use your E/R diagram from HW4 as a starting point. You may discover you need to adjust your design over time as you discover new requirements. You can add other tables to your database as well. You should fill the provided createTables.sql file with CREATE TABLE and any INSERT statements (and optionally any CREATE INDEX statements) needed to implement the logical data model above. We will test your implementation with the flights table populated with HW3 data using the schema above, and then running your createTables.sql. So make sure your file is runnable on SQL Azure through SQL Server Management Studio or the Azure web interface. Please note that due to the nature of EXTERNAL TABLE, you will not be able to create foreign keys that reference flights (FOREIGN KEY REFERENCE Flights) or INDEXes over the Flights table. For those foreign keys, just make them normal columns in your tables. You also do not need to keep CREATE EXTERNAL TABLE statements in this file. NOTE: You may want to write a separate script file with DROP TABLE or DELETE FROM statements; its useful to run it whenever you find a bug in your schema or data. You dont need to turn in anything for this.

Java customer application

Your second task is to start writing the Java application that your customers will use. To make your life easier, weve broken down this process into 5 different steps across the two milestones (see details below). You only need to modify Query.java. Do not modify FlightService.java. We expect that you use Prepared Statements where applicable. Please make your code reasonably easy to read. To keep things neat we have provided you with the Flight inner class that acts as a container for your flight data. The toString method in the Flight class matches what is needed in methods like search. We have also provided a sample helper method checkFlightCapacity that uses a prepared statement. checkFlightCapacity outlines the way we think forming prepared statements should go for this assignment (creating a constant SQL string, preparing it in the prepareStatements method, and then finally using it).

Step 1: Implement clearTables

Implement the clearTables method in Query.java to clear the contents of any tables you have created for this assignment (e.g., reservations). However, do NOT drop any of them and do NOT modify the contents or drop the Flights table. Any attempt to modify the Flights table will result in a harsh penalty in your score of this homework. After calling this method the database should be in the same state as the beginning, i.e., with the flights table populated and createTables.sql called. This method is for running the test harness where each test case is assumed to start with a clean database. You will see how this works after running the test harness. clearTables should not take more than a minute. Make sure your database schema is designed with this in mind.

Step 2: Implement create, login, and search

Implement the create, login and search commands in Query.java. Using mvn test, you should now pass our provided test cases that only involve these three commands. After implementation of these class, you should pass the following test:

Step 3: Write some test cases

Write at least 1 test case for each of the three commands you just implemented. Follow the same format as our provided test cases. Include your written test files in the provided cases/mycases/ folder together with our provided test files. Using mvn test -Dtest.casess="cases/mycases", you should now also pass your newly created test cases.

What to turn in:

Grading:

This milestone is worth 16 points (about 10% of the total homework grade) based on whether your create, login, and search methods pass the provided test cases.

Milestone 2:

Step 4: Implement book, pay, reservations, cancel, and add transactions!

Implement the book, pay , reservations and cancel commands in Query.java. While implementing & trying out these commands, youll notice that there are problems when multiple users try to use your service concurrently. To resolve this challenge, you will need to implement transactions that ensure concurrent commands do not conflict. Think carefully as to which commands need transaction handling. Do the create, login and search commands need transaction handling? Why or why not? Or you can run all non transaction test: Transaction management You must use SQL transactions to guarantee ACID properties: we have set the isolation level for your Connection, and you need to define begin-transaction 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. Each flights capacity is stored in the Flights table as in HW3, and you should have records as to how many seats remain on each flight based on the reservations. C2: A customer may have at most one reservation on any given day, but they can be on more than 1 flight on the same day. (i.e., a customer can have one reservation on a given day that includes two flights, because the reservation is for a one-hop itinerary). 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, dont 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. Your executeQuery call will throw a SQLException when an error occurs (e.g., multiple customers try to book the same flight concurrently). Make sure you handle the SQLException appropriately. For instance, if a seat is still available, the booking should eventually go through (even though you might need to retry due to SQLExceptions being thrown). If no seat is available, the booking should be rolled back, etc. When one uses a DBMS, recall that by default each statement executes in its own transaction. As discussed in lecture, to group multiple statements into a transaction, we use: 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 these approaches: Approach 1: Execute the SQL code for BEGIN TRANSACTION and friends directly, using the SQL code below (also check out SQL Azures transactions documentation): Please do not use PreparedStatement for these query, since SQLServer JDBC update, PreparedStatement must fully commit to execute (i.e. have the same number of BEGIN and COMMIT). Second style below is preferred. Approach 2: When auto-commit 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. The total amount of code to add transaction handling is in fact small, but getting everything to work harmoniously may take some time. Debugging transactions can be a pain, but print statements are your friend! Now you should pass all the provided test in cases during mvn test

Step 5: Write more (transaction) test cases

Write at least 1 test case for each of the four commands you just implemented. Follow the same format as our provided test cases. In addition, write at least 1 parallel test case for each of the 7 commands. By parallel, we mean concurrent users interfacing with your database, with each user in a seperate application instance. Remember that each test case file is in the following format: The * separates between commands and the expected output. To test with multiple concurrent users, simply add more [command...] * [expected output...] pairs to the file, for instance: Each user is expected to start concurrently in the beginning. If there are multiple output possibilities due to transactional behavior, then separate each group of expected output with |. See book_2UsersSameFlight.txt for an example. Put your written test files in the cases/mycases/ folder. Using mvn test -Dtest.cases="cases", you should now also pass ALL the test cases in the cases folder it will recursively run the provided test cases as well as your own. Congratulations! You now finish the entire flight booking application and is ready to launch your flight booking business 🙂

Write down your design

Please describe and/or draw your database design. This is so we can understand your implementation as close to what you were thinking. Explain your design choices in creating new tables. Also, describe your thought process in deciding what needs to be persisted on the database and what can be implemented in-memory (not persisted on the database). Please be concise in your writeup (< half a page). Save this file in writeup.md in the same folder of createTables.sql. You can add images to markdown by using ![imagename](./relative/path/to/image.png). Make sure any images is also pushed to git.

What to turn in:

5/5 - (1 vote) 
DROP TABLE IF EXISTS Flights;DROP TABLE IF EXISTS Carriers;DROP TABLE IF EXISTS Weekdays;DROP TABLE IF EXISTS Months;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CsE344RaNdOm$Key';CREATE DATABASE SCOPEDCREDENTIAL QueryCredentialWITH IDENTITY = 'reader2020', SECRET = '20wiUWcse()';
CREATE EXTERNAL DATA SOURCE CSE344_EXTERNALWITH( TYPE = RDBMS,  LOCATION='khangishandsome.database.windows.net',  DATABASE_NAME = 'cse344_readonly',  CREDENTIAL = QueryCredential);
CREATE EXTERNAL TABLE Flights(  fid int,  month_id int,  day_of_month int,  day_of_week_id int,  carrier_id varchar(7),  flight_num int,  origin_city varchar(34),  origin_state varchar(47),  dest_city varchar(34),  dest_state varchar(46),  departure_delay int,  taxi_out int,  arrival_delay int,  canceled int,  actual_time int,  distance int,  capacity int,  price int) WITH (DATA_SOURCE = CSE344_EXTERNAL);CREATE EXTERNAL TABLE Carriers(  cid varchar(7),  name varchar(83)) WITH (DATA_SOURCE = CSE344_EXTERNAL);CREATE EXTERNAL TABLE Weekdays(  did int,  day_of_week varchar(9)) WITH (DATA_SOURCE = CSE344_EXTERNAL);CREATE EXTERNAL TABLE Months(  mid int,  month varchar(9)) WITH (DATA_SOURCE = CSE344_EXTERNAL);
SELECT COUNT(*) FROM Flights; -- expect count of 1148675
$ mvn clean compile assembly:single
$ java -jar target/FlightApp-1.0-jar-with-dependencies.jar
$ mvn compile exec:java
// Generate a random cryptographic saltSecureRandom random = new SecureRandom();byte[] salt = new byte[16];random.nextBytes(salt);// Specify the hash parametersKeySpec spec = new PBEKeySpec(password.toCharArray(), salt, HASH_STRENGTH, KEY_LENGTH);// Generate the hashSecretKeyFactory factory = null;byte[] hash = null;try {  factory = SecretKeyFactory.getInstance("PBKDF2WithHmacSHA1");  hash = factory.generateSecret(spec).getEncoded();} catch (NoSuchAlgorithmException | InvalidKeySpecException ex) {  throw new IllegalStateException();}
$ mvn test
$ mvn test -Dtest.cases="folder_name_or_file_name_here"
[command 1][command 2]...*[expected output line 1][expected output line 2]...*# everything following # is a comment on the same line
mvn test -Dtest.cases="cases/no_transaction/search"mvn test -Dtest.cases="cases/no_transaction/login"mvn test -Dtest.cases="cases/no_transaction/create"
mvn test -Dtest.cases="cases/no_transaction/search"mvn test -Dtest.cases="cases/no_transaction/pay"mvn test -Dtest.cases="cases/no_transaction/cancel"
mvn test -Dtest.cases="cases/no_transaction/"
BEGIN TRANSACTION....COMMIT or ROLLBACK
private static final String BEGIN_TRANSACTION_SQL = "BEGIN TRANSACTION;";private static final String COMMIT_SQL = "COMMIT TRANSACTION";private static final String ROLLBACK_SQL = "ROLLBACK TRANSACTION";Statement generalStatement;// When you start the database upConnection conn = [...]conn.setAutoCommit(true); // This is the default setting, actuallyconn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);generalStatement = conn.createStatement();// Transaction begin heregeneralStatement.execute(BEGIN_TRANSACTION_SQL);// ... execute updates and queries.generalStatement.execute(COMMIT_SQL);// ORgeneralStatement.execute(ROLLBACK_SQL);
// When you start the database upConnection conn = [...]conn.setAutoCommit(true); // This is the default setting, actuallyconn.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// ... execute updates and queries.conn.commit();// ORconn.rollback();conn.setAutoCommit(true);// You MUST do this to make sure that future statements execute as their own transactions.
[command 1][command 2]...*[expected output line 1][expected output line 2]...*# everything following # is a comment on the same line