Assignment 1 - Modeling a System

In this assignment, you are to create a database design for a software system that will be used to manage an airport.

Each size of a gate at an airport has a price which is calculated based on the size of passenger waiting area multiplied by 20, and number of tunnel walkways to the aircraft multiplied by 100

For example, a gate that has 1 tunnel walkway and whose waiting area is 12m x 10m (120 sqm) in size will be priced as 2400 per aircraft visit PLUS 1x150 for a total of 2400+150=2550 per visit. If the same size gate has 3 tunnel walkways, it will cost 2400+450=2850 per visit.

The airport that we will model has services such as: refueling, catering, sewerage pump-out, vacuuming and laundry (for blankets, pillows). Each type of service has a standard price and not all aircraft will require all services. You would need to determine the cost of each service.

Aircraft companies are provided invoices at the end of each month for all the services that they have received throughout the month which is due for payment 10 days after the invoice is generated.

When the payment for the invoice has been received, the owner will be given a payment receipt.

The tables in your database must be in 3NF. You do not need to show the process of normalization or the 1NF or 2NF tables; only the final design.

When you are finished with your design, generate the create tables script from the MySQL Workbench and make sure that you can execute the script on your database.

What to Submit

The MySQL workbench file containing the model. The model needs to have consistent naming and must include all keys and attributes.

The SQL file that can be used to create the tables in your model.

Grading Rubric (20 points)

Entities (6 points)

Relationships (6 points)

Table Creation Script (4 points)

Normalized to 3NF (4 points)

Layout of a Typical Airport

Just for reference to give you an idea of some things that might be tracked

In this assignment, you are to create a database design for a software system that will be used to manage an airport. Each size of a gate at an airport has a price which is calculated based on the size of passenger waiting area multiplied by 20 , and number of tunnel walkways to the aircraft multiplied by 100 . For example, a gate that has 1 tunnel walkway and whose waiting area is 12m10m(120 sqm) in size will be priced as 2400 per aircraft visit PLUS 1150 for a total of 2400+150=2550 per visit. If the same size gate has 3 tunnel walkways, it will cost 2400+450=2850 per visit. The airport that we will model has services such as: refueling, catering, sewerage pump-out, vacuuming and laundry (for blankets, pillows). Each type of service has a standard price and not all aircraft will require all services. You would need to determine the cost of each service. Aircraft companies are provided invoices at the end of each month for all the services that they have received throughout the month which is due for payment 10 days after the invoice is generated. When the payment for the invoice has been received, the owner will be given a payment receipt. The tables in your database must be in 3NF. You do not need to show the process of normalization or the 1 NF or 2 NF tables; only the final design. When you are finished with your design, generate the 'create tables' script from the MYSQL Workbench and make sure that you can execute the script on your database. What to Submit - The MySQL workbench file containing the model. The model needs to have consistent naming and must include all keys and attributes. - The SQL file that can be used to create the tables in your model. Grading Rubric (20 points) - Entities (6 points) - Relationships (6 points) - Table Creation Script (4 points) - Normalized to 3NF (4 points) Layout of a Typical Airport