RowHill Airport Management System

Business Rules

In database management system, Business Rules are the statements used for putting constraints on the database being developed and managed. Respective to the scenario given for RowHill Airport, following are some of the business rules.

• Each TECHNICIAN is an EXPERT on AT LEAST ONE of the PlaneModels;
• TrafficController MUST have AnnualMedicalExamination.
• The recent most DATE of AnnualMedicalExamination must be Stored against each TrafficController.
• All AirportEmployees must have an assigned UnionNumber.
• Each AirprotEmployee has a Unique SSN.
• For each PLANE, TEST details must be updated.
• TestDetails should contain, FAATestNumber, Name, MaxScore.

1. Entity Relationship Diagram

1. Relational Schema

PLANE (RegNumber, PlaneModel)

PlaneModel (ModelNumber, Capacity, Weight)

EMPLOYEE (SSN, Name, Salary, Address, PhoneNumber, UnionNumber)

TECHNICIAN (ExpertIn)

TrafficController (MedicalExaminationStatus, MedicalExaminationDate)

FAATest (TestNumber, TestName, MaxScore, Date, Hours)

1. Normalization

The above relational schema satisfies 1^st and 2^nd Normal Form. However, following is the schema after 3^rd Normalization.

PLANE (RegNumber, PlaneModel, ModelNumber, Capacity, Weight)

EMPLOYEE (SSN, Name, Salary, Address, PhoneNumber, UnionNumber)

TECHNICIAN (SSN, ExpertIn)

TrafficController (SSN, MedicalExaminationStatus, MedicalExaminationDate)

FAATest (TestNumber, TestName, MaxScore, Date, Hours)

1. Queries

qryPlane SELECT (Airplane.Model, Airport.RowHill, Airport.Address FROM Airplane INNER JOIN Airport ON Airplane.RegNumber = Airport.RegNumber WHERE (((Airplane.Model)=”A787″));

This query can be generated to display all the details related to the model and speifications of the specific plane. In this query INNER JOIN is used as an assumption, assuming that there Airport will have a different table/database from where the specifications for the airplane will be acquired.

qryModelNumber SELECT Airport.Rowhill, Airport.PlaneModel, Airport.PlaneModel, Model.ModelNumber FROM Model INNER JOIN (Airplane INNER JOIN Airport ON Airplane.RegNumber = Airport.RegNumber) ON Model.ModelNumber = Airplane.ModelNumber WHERE (((Model.ModelNumber)=”AB”));

This query can be generated to acquire the airplane details using the PlaneModel specifications. The information that it will display will be regarding the plane model and the airport it is using.

qryTechnician SELECT Technician.Name, Technician.Salary, Technician.SSN, Union.UnionNumber FROM Technician INNER JOIN (Employee INNER JOIN Union ON Employee.SSN = Union.UnionNumber) ON Technicians.TechnicianID = Union.TechnicianID WHERE (((Technician.Name) Like “A*”));

This query can be generated to display all the details and specifications of the employees that are Technicians and have the name starting with the letter “A”.

qryTest SELECT Test.TestNumber, Test.Name, Test.Score, Test.Date FROM TestEvent INNER JOIN Test ON TestEvent.TestEventID = Test.TestEventID WHERE (((TestEvent.Date)=#4/23/2018#));

This query will be generated to display the results and specifications of the test that were generated on 23^rd April, 2018.

qryUnion SELECT Union.UnionID, Technician.Name, Technician.PhoneNumber FROM Technician INNER JOIN Union ON Technician.TechnicianID = Union.TechnicianID WHERE (((Union.UnionID)=”UA”));

This query will be generated to display the details and specifications about the technician employees belonging to a specific Union.