In this project, you are asked to design and implement a database that will store all the necessary information for a flight management system. You can form groups up to $3$ students. Each group will be assigned to a project domain by the course coordinator. Please add your group member names to the provided Google spreadsheet on the Moodle Webpage.

After designing the database, you will implement a software which will insert, delete and update data.

Your database design should contain at least $6$ tables. You are free to design the database anyway you like, but the following requirements should be considered:

Your database should have a user table that will allow users to login the system. This table will be used for authentication purposes. The system should also support creating user groups such as admin, employee, customers, technical staff, etc. Each group should have some specific role in the system. For example, employee prepare some items and have full acces to the database. Administrators manage systems, and so on$.$

Design foreign keys to other tables, in order to$,$ visualize and relate information from different tables.

Write queries to insert, delete or update data from tables.

Write PL$/$SQL blocks such as procedures, functions or triggers. Check the details below.

The project will have $3$ phases:

DESIGN: The database should be designed and should be demonstrated with ER Diagrams. Foreign keys, primary keys, constraints, default values should also be indicated with relational model.

Use any database that you want $($MySQL$,$ Oracle, MSSQL$,$ etc.$),$ to implement your design.

DEVELOP: Write a software $($with any language such as JAVA, C#$,$ PHP$,$ etc.$)$ to insert$/$update$/$delete and show data from your database. You should develop at least $3$ windows to manage $3$ tables, for insert$/$update and delete record from the database. When the project is completed you should have at least $5$ records inserted in those $3$ selected tables.

QUERY: Write at least $5-7$ SQL queries retrieving, displaying and showing statistical information to help management $($use join operations, subqueries, group by$,$ order by$,$ sum, average, count, etc.$)$ and $3-5$ PL$/$SQL blocks $($procedures$,$ functions or triggers$)$

At the end of this project, you will submit a report that should contain the following items as well as present it in the class on a date do be announced later:

Introduction

An introduction giving brief information and assumptions about the system

Database

ER Diagram,

Data Definition Language Details $($DDL$),$ including all necessary constraints,

Data Manipulation Language $($DML$),$

$5$ SQL and $5$ PL$/$SQL questions and answers$/$outputs$.$

Software

Provide your DB connection code

Provide screenshots of all your working application interface as well as their codes and give some brief information about screen shot.

Individual or group work $($max $3$ people$)$ is accepted.