[Solved] CSE344-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.
Open WhatsApp
- Design a database of your customers and the flights they book
- Complete a working prototype of your flight booking application that connects to the database (in Azure) then allows customers to use a CLI (command line interface) to search, book, cancel, etc. flights
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 use the HW3 specification for importing Flights data. 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.flightapp.server_url = SERVER_URL# TODO: Enter your database name.flightapp.database_name = DATABASE_NAME # TODO: Enter the admin username of your server. flightapp.username = USERNAME # TODO: Add your admin password.flightapp.password = PASSWORD |
- The server URL will be of the form [your_server_name].database.windows.net
- The database name can be found at the top of the page when you open your database in the Azure console
- The username and password are the same credentials you use to login to your database/server when you open the query editor in the Azure console
- If the connection isnt working for some reason, try using the fully qualified username: flightapp.username = [email protected]_NAME
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: $ mvn clean compile assembly:single This second command will run the main method from FlightService.java, the interface logic for what you will implement in Query.java: $ java -jar target/FlightApp-1.0-jar-with-dependencies.jar If you want to run directly without creating the jar, you can run: $ mvn compile exec : java 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> > quitData 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.- Flights / Carriers / Months / Weekdays: modeled the same way as HW3.
- Users: A user has a username (varchar), password (varbinary), and balance (int) in their account. All usernames should be unique in the system. Each user can have any number of reservations. Usernames are case insensitive (this is the default for SQL Server). Since we are salting and hashing our passwords through the Java application, passwords are case sensitive. You can assume that all usernames and passwords have at most 20 characters.
- Itineraries: An itinerary is either a direct flight (consisting of one flight: origin > destination) or a one-hop flight (consisting of two flights: origin > stopover city, stopover city > destination). Itineraries are returned by the search command.
- Reservations: A booking for an itinerary, which may consist of one (direct) or two (one-hop) flights. Each reservation can either be paid or unpaid, cancelled or not, and has a unique ID.
Requirements
The following are the functional specifications for the flight service system, to be implemented in Query.java The methods you need to provide are indicated in the starter code, where they exist as stubs, skeletons that you will fill out as you develop your implementation. Refer to Query.java for the complete specification, including what conditions to handle and what error messages to return, etc.- create takes in a new username, password, and initial account balance as input. It creates a new user account with the initial balance. Create() should return an error if it is passed an initial balance that is a negative dollar amount, or if the username already exists. When validating Usernames, please ensure that they are not case-sensitive. In other words, UserId1, USERID1, and userid1 all map to the same User ID. You can assume that all usernames and passwords have at most 20 characters. We will store the salted password hash and the salt itself to avoid storing passwords in plain text. Use the following code snippet to as a template for computing the hash given a password string:
| // 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();} |
- login takes in a username and password, and checks that the user exists in the database and that the password matches. To compute the hash, adapt the above code. Within a single session (that is, a single instance of your program), only one user should be logged in. A good practice is for every test case to begin with a login request. Make sure you log the User out when the program terminates. To keep things simple, you can track the login status of a User using a local variable in your program. You should not need to track a users login status inside the database. If a second login attempt is made, please return User already logged in.
- search takes as input an origin city (string), a destination city (string), a flag for only direct flights or not (0 or 1), the date (int), and the maximum number of itineraries to be returned (int). For the date, we only need the day of the month, since our dataset comes from July 2015. Return only flights that are not canceled, ignoring the capacity and number of seats available. If the user requests n itineraries to be returned, there are a number of possibilities:
- book lets a user book an itinerary by providing the itinerary number as returned by a previous search. The user must be logged in to book an itinerary, and must enter a valid itinerary id that was returned in the last search that was performed within the same login session. Make sure you make the corresponding changes to the tables in case of asuccessful booking. Once the user logs out (by quitting the application), logs in (if they previously were not logged in), or performs another search within the same login session, then all previously returned itineraries are invalidated and cannot be booked. A user cannot book a flight if the flights maximum capacity would be exceeded. Each
- pay allows a user to pay for an existing unpaid reservation. It first checks whether the user has enough money to pay for all the flights in the given reservation. If successful, it updates the reservation to be paid.
- reservations lists all reservations for the currently logged-in user. Each reservation must have a unique identifier (which is different for each itinerary) in the entire system , starting from 1 and increasing by 1 after each reservation is made.
- cancel(extra credit) lets a user cancel an existing uncanceled reservation. The user must be logged in to cancel reservations and must provide a valid reservation ID. Make sure you make the corresponding changes to the tables in case of a successful cancellation (e.g., if a reservation is already paid, then the customer should be refunded).
- quit leaves the interactive system and logs out the current user (if logged in).
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. 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 HW2 data using the schema above, and then running your createTables.sql. So make sure your file is runnable on SQL Azure through the Azure query editor web interface. 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. Please use unqualified table names in all of your SQL queries (e.g. say SELECT * FROM Flights rather than SELECT * FROM [dbo].[Flights]) Otherwise, the grading scripts wont be able to run using your code. We expect that you use Prepared Statement s when you execute queries that include user input. Since we will be looking at your code, it is important to make your code easy to read. Use descriptive variable names, for instance. Take a look at the Flight class we provide, a class that serves as a container for your flight data, as an example to follow. In methods like search, for example, you will see that you need to add a method similar to the toString method that we provided in the Flight class. Use our toString as a style guide. We have also provided a sample helper method checkFlightCapacity that uses a prepared statement.checkFlightCapacity is also intended as an example that outlines the way prepared statements should be used in this assignment (creating a constant SQL string, preparing it using the prepareStatements method, and then, ultimately, executing 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 Flightstable. 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, you should pass the following test: mvn test -Dtest.cases=cases/no_transaction/search mvn test -Dtest.cases=cases/no_transaction/login mvn test -Dtest.cases=cases/no_transaction/create Or you can run as a single command: mvn test -Dtest.cases=cases/no_transaction/search:cases/no_transaction/login:cases/ no_transaction/createStep 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.cases=cases/mycases, you should now also pass your newly created test cases. Milestone 2: Step 4: Implement book, pay, reservations, cancel (extra credit) , 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? mvn test -Dtest.cases= cases/no_transaction/search mvn test -Dtest.cases= cases/no_transaction/pay mvn test -Dtest.cases= cases/no_transaction/cancel Or you can run all non transaction test: mvn test -Dtest.cases= cases/no_transaction/ 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. 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: 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 need to use setAutoCommit and commit or rollback:| // 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 // execute updates and queries.conn.commit();// OR conn.rollback(); |
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:| [command 1 for user1][command 2 for user1] *[expected output line 1 for user1][expected output line 2 for user1] *[command 1 for user2][command 2 for user2] *[expected output line 1 for user2][expected output line 2 for user2] * |