Database Application Development of Airport Using Object Oriented Programming

Database Application Development of Airport Using Object Oriented Programming

Time Estimate

Observing the ISBL Simulation : 10 min

Developing the Database : 40 min

Developing the Database Application   : 120 min

Database Application Development of Airport Using Object Oriented Programming (100 pts total)

The construction of a new airport terminal in a small town has recently been completed. The terminal has eight self-check-in machines, one clerk check-in, one ID check and two advanced imaging technology (AIT) units for scanning passengers and their luggage. You are hired as a software engineer to develop an information system using an object-oriented programming language. Your team is assigned to observe the airport location and create a database of the system and develop a database application of the system. You have to create the database and test it before developing a database application. After testing the database, you need to work on a database application by modifying a given database application code template. The following figure shows a simulation model of the airport, which we will treat as the "real" system in this assignment.

You will submit two documents for this assignment. Your project export (name your project AirportCRUD) as AirportCRUD.zip and your report Assignment2.docx or Assignment2.pdf. Your project export should include your existing database (created by flyway) and all the other necessary source code/libraries to compile and run the project successfully. It is a good idea to test your exported project zip file on another computer before submitting it and see if it is working correctly. In your report, you must show your work with screenshots and descriptions/captions about those screenshots.

By using the AddressBookDAO(fly) project as a template, create a CRUD application for an Airport database (AirportDb) with two tables.

CheckinLocation table

●  CheckinLocationID (number, PK)

●  StationName (String, not null)

Passenger table

●  PassengerID (number, PK)

●  FirstName (varchar(30), not null)

●  LastName (varchar(30), not null)

●  CheckInLocationID (number, foreign key, not null)

●  CheckInDateTime (datetime, not null)

1. Write a SQL code to create these two tables in a dummy database using NetBeans JavaDB network server (name your database testAirportdb). Put your screenshots showing that these two tables are created successfully by running your SQL code. 5 points.

2. Run the Airport simulation and record 3 passenger and their movement. Be sure to record the datetime when the passenger visits the check-in station. 5 points.

3. Insert the 11 stations (8 kiosks and 3 agents) from the simulation and set the stationname as labeled in the simulation. Show your INSERT SQLs and your final CheckinLocation table after inserts. Put screenshot(s) below. 2.5 points.

4. Insert 3 passengers from the simulation based on which station they checked in. Show your INSERT SQLs and your final Passenger table after inserts. Put screenshot(s) below. 2.5 points.

5. Try to add an existing checkinLocation and an existing passenger into the database. Show primary key violation. Put screenshot(s) below. 2.5 points each. 5 points total.

6. Try to add a new passenger into the database for a checkinLocation that does not exist in your database. Show foreign key violation. Put screenshot(s) below. 5 points.

7. Put those SQL code in a file named as V1_something.sql, V2_something.sql under db.migrations so you can create those tables via FlyWay. Try to give meaningful names to your SQL files. Show your work (put screenshot(s) of contents of the files and where you placed those files in the project). 5 points.

8. Name your database as airportdb so FlyWay will create a databasefolder as airportdb. Hint: check DB.java. Put screenshot(s) of your DB.java below. 5 points.

9. Modify entity classes. Put screenshot of your CheckinLocation.java and Passenger.java. 5 points each. 10 points total.

        Put screenshot(s) of CheckinLocation.java below.

        Put screenshot(s) of Passenger.java below.

10. Modify entityDAO classes. Put screenshot of your CheckinLocationDAO.java and PassengerDAO.java. 5 points each. (If you modified DAO.java, put screenshot of that one too.) 10 points total.

        Put screenshot(s) of CheckinLocationDAO.java below.

        Put screenshot(s) of Passenger.java below.

11. Modify the main method to test your CRUD application for the following tasks. If your application does not run, you will get zero points for this part. Show your work by putting screenshots from your running application. 5 points for each task. 25 points total.

        o  Add eleven stations from the simulation. Put screenshot(s) below.

        o  Add three passengers from the simulation. Put screenshot(s) below.

        o  Pick one passenger and update the CheckInLocationID to a different existing CheckInLocationID. Put screenshot(s) below.

        o  Pick one CheckInLocation and change its stationName. Put screenshot(s) below.

        o  Pick one passenger and update his/her CheckInLocationID to a non-existing CheckInLocationID (foreign key violation). Put screenshot(s) below.

12. Modify the main method (keep the previous code from task 11) to add a user input logic for your CRUD. So, user can perform CRUD operations on two tables. Show your work by putting screenshots from your running application for each task. 2 points for each task (except option 9). 20 points total.

        o  Option 1 Create a check-in location. Put screenshot(s) below.

        o  Option 2 Update an existing check-in location. Put screenshot(s) below.

        o  Option 3 Delete an existing check-in location. Put screenshot(s) below.

        o  Option 4 Print all check-in locations. Put screenshot(s) below.

        o  Option 5 Create a passenger. Put screenshot(s) below.

        o  Option 6 Update an existing passenger. Put screenshot(s) below.

        o  Option 7 Delete an existing passenger. Put screenshot(s) below.

        o  Option 8 Print all passengers. Put screenshot(s) below.

        o  Option 9 for Exit Application (4 points). Put screenshot(s) below.