RowHill Airport Management System

Business Rules

In a database management system, business rules are the statements used to constrain the database being developed and managed. Regarding the scenario given for RowHill Airport, the following are some of the business rules.

• Each TECHNICIAN is an EXPERT on AT LEAST ONE of the PlaneModels;
• TrafficController MUST have AnnualMedicalExamination.
• The most recent DATE of AnnualMedicalExamination must be stored against each TrafficController.
• All Employees 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.

Entity Relationship Diagram

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)

Normalization

The above relational schema satisfies the 1^st and 2^nd Normal Form. However, the following is the schema after the 3rd 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)

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 specifications of the specific plane. In this query, INNER JOIN is used as an assumption, assuming that the 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 of the technician employees belonging to a specific Union.