SIT103 Database: RowHill Airport Management System Project SIT103

This document presents a database project for the RowHill Airport Management System, including business rules, an Entity Relationship Diagram (ERD), a relational schema, normalization steps, and SQL queries. The business rules define relationships between models, technicians, airplanes, and traffic controllers, emphasizing annual medical examinations and airworthiness tests. The ERD visually represents these entities and their relationships. The relational schema outlines the database structure with tables for Model, Airplane, Technician, Test, and Traffic Controller, including primary and foreign keys. Normalization is applied to the Test and Traffic Controller tables to reduce redundancy. Finally, SQL queries are provided to retrieve specific information from the database, such as traffic controller exam dates, technician test details, and model and airplane information. Desklib provides a platform for students to access similar solved assignments and past papers.

Running Head: SIT103 DATABASE
SIT103 Database: RowHill Airport Management System
[Student Name]
[University Name]

1SIT103 DATABASE
Table of Contents
Business Rules.................................................................................................................................2
Entity Relationship Diagram...........................................................................................................2
Relational Schema...........................................................................................................................3
Normalization..................................................................................................................................4
SQL query........................................................................................................................................5

2SIT103 DATABASE
Business Rules
1. A model can be managing by one or more technician.
2. An airplane can be control by one or more traffic controller.
3. A traffic controller must have an annual medical examination.
4. every airplane must be tested for checking it are still airworthy.
5. Each test must have a technician.
6. Each employee should belong to a union.
Entity Relationship Diagram
Figure 1: Entity Relationship Diagram

3SIT103 DATABASE
(Source: Created by author)
Relational Schema
Model (ModelNO: VARCHAR2, Capacity: VARCHAR2(5), Weight: VARCHAR2 (5))
Airplane (RegistrationNo:VARCHAR2(10), ModelNo: VARCHAR2(5))
Technician (SSN: VARCHAR2(10), Name: VARCHAR2(50), Address: VARCHAR2(50),
PhoneNo:Number(10), Salary: VARCHAR2(10), ModelNo: VARCHAR2(5), UMN:
VARCHAR2(10))
Test (FAANo: VARCHAR2(10), Name: VARCHAR2(10), MPS: VARCHAR2(10),
TechnicianSSN: VARCHAR2(10), Date:Date, HoursTaken: Time)
Traffic Controller (SSN: VARCHAR2(10), Name: VARCHAR2(10), Examdate: Date, UMN:
VARCHAR2(10), PlainNO: VARCHAR2(10))

4SIT103 DATABASE
Normalization
After analyzing the above relational schema, it is found that only two tables can be
normalized. Other tables are fully normalized. Test table can be normalized because it contains
redundancy.
After normalization
Test
Test (FAANo: VARCHAR2(10), Name: VARCHAR2(10), MPS: VARCHAR2(10))

5SIT103 DATABASE
TestCase (TestCaseID: VARCHAR2(5), FAANo: VARCHAR2(10), TechnicianSSN:
VARCHAR2(10), Date:Date, HoursTaken: Time)
Traffic Controller
Traffic Controller (SSN: VARCHAR2(10), Name: VARCHAR2(10), Examdate: Date, UMN:
VARCHAR2(10))
TrafficControlCase (TrafficControllerSSN: VARCHAR2(10), PlainNO: VARCHAR2(10))
SQL query
SELECT Name, Examdate, FROM Traffic_controller WHERE SSN = ‘’;
SELECT name, TechnicianSSN, Date, Time FROM TestCase WHERE TechnicianSSN = ‘’;
SELECT * FROM Model WHERE ModelNo = ‘’;
SELECT * FROM Airplane WHERE RegistrationNo = ‘’;
SELECT * FROM Technician WHERE SSN = ‘’;