BIT231 MPoly Airlines

This assignment is centered on ER modelling, a graphical tool used in database design and Normalization which is a text based tool to remove unnecessary redundancy in a database. At the completion of these assessments students will be able to:

• Identify the different components of an ERD
• Recognize some business rules from the relationships contained in an ERD
• Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and their for the linking of tables in the RDM ((relational data model)
• To develop relational models (RDM) where each table has a primary key (entity integrity) and some tables may have foreign keys (referential integrity)

Assessment Instructions

1. Convert M:N relationships into 1:M and M:1 relationships

2. Convert the conceptual ER model , given a problem scenario, into a RDM with appropriate primary keys (PKs) and foreign keys (FKs)

3. Only use Crow's Foot notation

4. Draw dependency diagram to use the 1NF, 2NF and 3NF

Airlines Case Study

MPoly Airlines has asked you to create a database for their airline operations i.e. its flight and airplane history.

The database requirements are as follows.

A MPoly Airline flight is uniquely identified by the combination of a flight number and a date. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on a MPoly flight has a unique passenger number plus their name, address, and telephone number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.

A pilot is identified by a unique pilot (or employee) number, first name, last name, date of birth, and date of hire. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet.

Each airplane has a unique serial number, a model, manufacturer name, passenger capacity, and year built. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.

MPoly Airline also wants to maintain data about its airplanes' maintenance history. A maintenance procedure has a unique procedure number, a procedure name and the frequency with which it is to be performed on every airplane. For each such event it wants to know the date of the event, a maintenance location and the duration.

Tasks List

Task 1: Knowledge test

The knowledge test - Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modeling and Normalization.

Task 2 : Database Modelling and Implementation

Question 1 - ER Modelling

1. Business rules - Write Business rules to create ERD

2. Entities and Attributes - List Entities, Attributes - Include all attributes that you believe would be useful

3. ER Diagram - Design an ER diagram for the above case study.

• Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software. Hand-drawn diagrams will not be accepted
• Only use "crow's foot notation"
• ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.
• When completed, insert your ER Diagram into a Word document either by taking a screenshot of the diagram and pasting it in the word document, or by exporting the diagram as an image and the inserting it in the word document

Question 2 - Data Dictionary

• Create a data dictionary for the above ER Diagram using the following sample format.
• All data items must have suitable data types and all tables must have appropriate primary key and foreign key

Question 3 - Dependency Diagram

Draw dependency diagram (using the following sample format) for your final ER Diagram, in the MS Word document, showing where necessary that you have progressed through to 3NF. see image.

Question 4 - SQL Statements DDL and DML

Based on the specification you have provided in the data dictionary:

i. CREATE TABLE - Write the SQL code to create all tables (in the ER Diagram and Data dictionary) to implement the relational data logical model (in ONE script).

ii. Constraints - In part i, define primary keys, foreign keys and NOT NULL constraints in the CREATE TABLE statement ONLY. [Note: a foreign key constraint requires the existence of the referenced table].

iii. DEFAULT clause - In part I, choose an appropriate column to use the DEFAULT clause. Explain what is meant by DEFAULT and why this column is suitable to take such values.

iv. Write INSERT INTO statements to populate each table. (at least 3 rows per table)

Please Note, for part i, ii and iii, you only need to write CREATE TABLE statement ONCE for each table.