rhysbower / airbc Goto Github PK

Pick one query that finds some aggregated value for each group (eg. the average number of items purchased per customer) and then rerun with a different aggregation. Provide an interface for the user to specify whether the average, min, max or count is requested.

• Show the result of a meaningful query (eg. the average number of items purchased per customer)
• Show the actual query or view in the code
• Rerun with at least one other nested aggregation

Formal Specifications!
Based on the Grading Schemes from last term, it seems last term ER diagrams (what we did last milestone) and formal specifications were submitted together, so I'll just take the relevant grading keys as our TODOs.

• cover page
• >= 10 functionality specifications (can modify what we did for the first milestone?)
• >= 1 interesting functionality specifications that requires a moderately sophisticated SQL statement to update part of the database
• >= 1 interesting functionality specifications that requires a moderately sophisticated SQL statement to query (depending on the user's input) part of the database
• specify the platform to use (Didn't we do this in the first milestone ..)
• specify the data that is to be used in the final application Piazza
• specify the division of labour
• richness of design in terms of the features it exhibits

Final Project

• cover page
• All the code used in the application
• A script that could be used to create all tables and data in the database such as the one here
• A short description of what the project accomplished
• A description of how your final schema differed from the schema you turned in. If the final schema differed, why? Note that turning in a final schema that's different from what you planned is fine, we just want to know what changed and why.
• A list of the SQL queries used
• List all functional dependencies that are applicable to the table (including the ones involving the primary key). For each functional dependency, briefly describe its meaning in English.

Pick one query that requires the use of aggregation (min, max, average, or count are all fine). All aggregations (min, max, avg, count, sum) must be implemented in your project, but the TA will choose two at random to check

• Show the result of a meaningful query (eg. find the most expensive item)
• Rerun with at least one other aggregation, either on the same query (eg. the least
  expensive item), or a completely different query (eg. find the first customer)

What we can do:

• See the MAX/MIN/AVG route price
• Get the total price of a route arriving at/departing from every airport (SUM)
• Count the number of flights on a route (COUNT)

@RhysBower can you update the repo's name to airbc when you have time?

• Show the result of a meaningful query (eg. find all the customers who bought all the
  items)
• Prove that your division results change based on the data either by inserting a new
  tuple (eg. into the Items table), or deleting an existing tuple (eg. from the
  Transactions table)

The best place to write a division query for is to see which customer book all the tickets.

Pick one query of this category and provide an interface for the user

• user must be able to specify the selection constant (eg. select from Items where quantity >= __) and the attribute to be returned (eg. select ___ from Items)
• rerun with at least one other constant (eg. change from quantity to price) and
  attribute

On front end, the idea is to have either different input boxes, dropdown lists, or radio buttons to let user specify their conditions in WHERE and result fields in SELECT.
This query can be done to every page.

• A cover page
• An ER diagram
  • At least 5 entity sets.
    • There will likely be a numeric attribute, but not necessarily.
    • Identify candidate keys, and the primary key. Each entity must have a primary key.
    • At least 5 relationships with constraints.
• A schema
  • Tables
  • Specify the primary key, foreign keys, and other constraints that the table has to maintain
  • FDs
  • Candidate keys
• Nomalize each of your tables, if necessary, to be in 3NF or BCNF (you don't need to give a formal proof about 3NF or BCNF.) Give the list of tables, their primary keys, and foreign keys after normalization.
• The SQL DDL to create all the tables in SQL. All primary keys and foreign keys must be declared appropriately.
• Populate each table with at least 5 tuples. Show the instance of each relation after inserting the tuples.

• Show files have not changed
• Load schema
• Extra feature 1: Account system
  • Login as customer (adam, password), show no staff privileges
• Join Query
  • Show full customer table
• Extra feature 2: user privileges with login ID
  • Login as staff in separate window, show staff privileges
• Join Query Continued
  • Show full staff table
• Update Operation
  • Update staff title
• Selection and Projection Query
  • Aircrafts page
• Division Query
  • book all flights with adam
  • flightID 1024-1029
  • with staff account goto /staff/tickets/all
• Aggregation Query
  • with staff account goto /staff/routes/price
• Nested Aggregation Query
  • with staff account goto /staff/tickets/avg
• Delete Operation
  • with staff account delete ticket

• Graphical user interface
  • is demoed throughout the presentation
• Extra feature 3
  • Bootstrap to prettify UI

Delete operation: Implement a cascade-on-delete situation. Provide an interface for the user to specify some input for the deletion operation. Some input values would fail the cascade specification but others would successfully follow the cascade specification. Provide an interface for the user to display the relation instance after the operation.

• Case 1: deletion causing cascades
• Case 2: deletion without causing cascades

What we have:

• If an airport is removed, all the associated routes, flights, tickets are automatically removed.
  What we don't have, but I'm not sure what they mean:
• Provide an interface for the user to specify some input for the deletion operation
• Some input values would fail the cascade specification

Create unique features that your application supports.

• Using Bootstrap to create a nice UI could give you 5 points max
• An impressive UI with robust type checking and error handling everywhere could give you an
  additional 5 points max (I guess we do, but maybe not everywhere)
• Using a simple Trigger could give you 5 points max; using an interesting Trigger that would cause cascading effects could give you an additional 5 points max
• Implementing a login system could give you 5 points max
• Checking for user privileges using the login ID could give you an additional 5 points max (we have staff and loyalty member with different kinds of privileges)
• Using Privileges on each table in the database could give you 5 points max (we didn't learn privileges either)
• Using any databases in the cloud like Amazon Web Services could give you 10 points max

The marks in the above items are from summer term grading scheme so they may be different this term. But we really didn't learn triggers and privileges

• Accouts - Rhys
• Route - NOT RHYS
  • Search only arrival or departure
• Flight - Mandy
• Aircraft
• Airport - Alison
• Ticket - Harryson

... google docs

• Remove is unsafe, not sure how to change it atm (just send a GET through URI)
  should be DELETE hmm
• Remove link is visible when a user is logged in, should be only STAFF and not CUSTOMER account (not implemented yet?)
• Add airport should be POST i think oops (how come it still works hm...)
• Alert doesn't show when NOT added into database, it just alerts with inputs listed (I'm not sure when/how to show error on database insertion and stuff)

Create an easy to use GUI that allows the users to execute all the above operations and transactions. Use the menu facility provided by the graphics environment you use to define appropriate menus and submenus. Make sure to design your interface in such a way that all the error messages appear in separate pop-up boxes, or in a designated area of the main window, so that they do not interfere with the other activities. Whenever user input is requested, make sure that there is sufficient type checking.

• Type checking exists at least for some fields
• Error messages are located at visible and logical places
• Overall look of the UI

Type checking can be applied to add route, add flight, add airport and other things.

• Allow users to login
• Allow users to logout
• Allow public to create accounts
• Allow users to edit their information
• Add ability to restrict access to specific pages based on role

Pick one query of this category, which joins at least two tables and performs a meaningful query, and provide an interface for the user to choose this query (eg. join the Customers and the Transactions table to find the phone numbers of all customers who has purchased a specific item).

Right now the join queries we already have some join queries. But what we need to do is more to let user choose what to join.
What we have:

CREATE VIEW FullAircraft AS
  SELECT id, Aircraft.type, purchase_date, status, first_class_seats, business_seats, economy_seats
  FROM Aircraft JOIN Type WHERE Aircraft.type=Type.type;

CREATE VIEW FullStaff AS
  SELECT Account.id, name, email, username, password, title
  FROM Account, Staff WHERE Account.id=Staff.id;

CREATE VIEW FullCustomer AS
  SELECT Account.id, name, email, username, password, travel_document,
  billing_address, phone_number, seat_preference, payment_information
  FROM Account, Customer WHERE Account.id=Customer.id;

CREATE VIEW FullLoyaltyMember AS
  SELECT Account.id, name, email, username, password, travel_document,
  billing_address, phone_number, seat_preference, payment_information, points
  FROM Account, Customer, Loyalty_Member WHERE Account.id=Customer.id 
  AND Account.id=Loyalty_Member.id;

Implement a constraint using the check statement. Provide an interface for the user to specify some input for the update operation. Some input values would successfully satisfy a constraint while others would fail. Provide an interface for the user to display the relation relation after the operation (<– what does this even mean). Note that MySQL 5.5 does not support the Check constraint. If your team is using MySQL 5.5, keep the check in the SQL statement, but actually do the check at the application level (and comment it in the code).

• Update a value that violates some constraint (this is different from the type checking
  in the UI and must be checked in the database)
• Correct the value and update again

Account update is in progress 7b03f83
But we never learned check statements, but we are expected to figure things out by ourselves ok

There are a couple of problems with Windows support right now.

1. Windows does not support 64-bit integers so I'll need to rollback the change I made earlier.
2. PHP 7.1 is required for nullable type comments. XAMPP supports PHP 7.1 and appears to be the easiest solution to this problem.

I don't want to merge in my login functionality until everyone is able to run the code.