AJA(X) Flight Systems: A Database Project

Project by: Andrew Lam, JiaSheng Ma, Antonio Aliberti

Welcome to our group's database project, where we simulate an airline flight reservation system. Thanks for taking your time to look at our project. Without further ado, below is a sample setup to quickly get this system working. A bit further details current capabilities and potential tweaks you may add. Sample pictures are in the images directory. Take a peek!

Setup

• Download maven and place the bin directory into the environment variables
• Download 'MySQL Community Server' from 'MySQL Community Downloads' (not MySQL Workbench)
• Proceed through download process until you hit the 'Select Product and Features Page'
  • Select 'MySQL Servers' -> 'MySQL Server' -> latest version
  • Feel free to select any other products you may deem useful
  • Proceed to fill in user details and finish
• Setting up the database
  • log into the MySQL account and run the following commands (high-tech security)
    • create user 'ajaxadmin'@'localhost' identified by 'passwordajax305';
    • grant all privileges on ajax305.* to 'ajaxadmin'@'localhost';
    • CREATE DATABASE ajax305;
    • use ajax305;
    • source .\src\main\resources\localdump.sql
  • For PowerShell users, may also run
    • cmd.exe /c "mysql -u ajaxadmin -p ajax305 < .\src\main\resources\localdump.sql"
  • For macOS or Linux, run the following command
    • mysql -u ajaxadmin -p ajax305 < .\src\main\resources\localdump.sql

Using AJA(X) Flight System Program

• git clone project
  • To easily view our code -> Open Intellij and open the 'pom.xml' which will auto import everything
• Once again, we are using maven so to run
  • mvn clean tomcat7:run
  • head over to localhost / server and run gui application

Possible error messages and corresponding solutions

- 'Cannot establish database connection.'
	- Refer to # Setup -> 'Setting up the database'
	- or try restarting MySQL
- Project does not compile under maven
	- Consider adding this block into plugins section of the pom.xml file
	    <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.1</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>

changing database

mysql -u ajaxadmin -P ajax305
password: passwordajax305

verify tables with 'show tables;'
this default password is on par with the one above (when creating the database)

TODO

0 Programmers Start: move up as needed [x] notify user to login when they try to book a flight (in handler for booking form submission: get session, check if session("user")==null) [x] add registration validation (use javax validation + hibernate validator) [x] create a different registration portal for admin, i.e. use different registration handler, and set the privilege bit manually in database [x] check email and password using jquery in front end [x] customers - search flights [x] check email and password using jquery in front end [x] customers - search flights [x] query database for direct or indirect flights between a pair of cities for a given date and "approximate" time. [x] reverse auctions - price customers are willing to pay for a seat [/] customers cancel an existing reservation (reservations from auction can also be canceled? interesting)

1 System Users [x] customers - make flight reservation [x] customer may partake in any number of flight reservation transactions [x] customer's rating should reflect how active a ticket buyer he or she is (+1 to customer's rating)

3.1 Manager Transactions [x] Add, Edit and Delete information for an employee [x] Obtain a sales report for a month [x] Produce a comprehensive listing of all flights [x] Produce a list of reservations by flight number or by customer name [x] Produce a summary listing of revenue generated by a flight, destination city, or customer [x] Determine which customer representative generated most total revenue [x] Determine which customer generated most total revenue [x] Produce a list of most active flights [x] Produce a list of all customers who have seats reserved on a given flight [x] Produce a list of all flights for a given airport [/] Produce a list of all flights whose arrival and departure times are on-time/delayed

3.2 Customer-Representative-Level Transactions [x] Record a reservation [x] Add, Edit and Delete information for a customer [x] Produce customer mailing lists [x] Produce a list of flight suggestions for a given customer (based on that customer's past reservations)

3.3 Customer-Level Transactions [x] One-Way [] Round-Trip [/] Multi-City [x] Domestic or International [x] Flexible Date/time [x] A customer's current reservations [x] Travel itinerary for a given reservation [x] A customer's current bid on a given reverse auction [x] The bid history for a given reverse auction [x] A history of all current and past reservations a customer has made [x] Best-Seller list of flights [x] Personalized flight suggestion list

4 User Access Control [x] Customer Representatives cannot perform manager-level transactions, but can read employee info except pay rate. [x] Customer Representatives should be able to record the receipt of an order from a customer. [x] A customer cannot access to other customers' account information, or to any employee information.

5 Utilities [x] Allowing the manager to add and delete users [x] Backing up the database files [x] A comprehensive Help facility, including a topic-driven pull-down Help menu

6 Other [x] Prompt user to select customer representative after auction (default if no selection)

99 Notes [x] Reservation have booking fees, which is how your company makes money, and an associated customer representative

GPL v3