Deakin University SIT103: RowHill Airport Management System Database

This assignment solution addresses the design and implementation of a database for the RowHill Airport Management System. It begins by identifying key business rules governing the airport's operations, such as airplane models, employee roles, and test information. An Entity-Relationship (ER) diagram is then presented using Crow's Foot notation, visually representing the database's structure and relationships between entities like technicians, airplanes, and tests. The solution further develops relational schemas, defining the tables and their attributes, including primary and foreign keys. Subsequently, the solution details the creation of tables within a Deakin Oracle DBMS, including sample data population for each table, demonstrating the practical application of SQL commands. Queries are provided to demonstrate database querying and manipulation, including examples of table alterations and data retrieval. The assignment covers topics such as database design, SQL implementation, ER diagrams, and relational schemas, providing a comprehensive guide for students. The assignment also includes quiz questions and their solutions, testing the understanding of the database concepts. The final part of the assignment provides a complex SQL query to retrieve specific information from multiple tables based on certain conditions.

Question one
Identified business rules
1. Each airplane belong to only one model
2. SSN number identifies employees
3. Test information should not have multiple test.
4. One technician can perform test on multiple airplane belonging to different models
5. Test information can contain one airplane only.
6. Test information must contain technician number.

Question Two
ER diagram in Crow’s Foot notation

ER description
The following assumptions were made from the ER diagram
Technician and traffic controllers are part of employees. Each employee have an SSN number that’s
uniquely identifies them. All employee have a union membership number thus they all belong to a
union.
One technicians is an expertise of one or more models. Airplane belongs to one type of model and they
are identified by unique registration number. Test is performed on each plane at certain date and the
test information are saved for each test. The test information save the expertise number, registration
number and Federal Aviation Administration (FAA) test number for each test done.
Question 3
Develop relational schemas
Traffic Controller(TC_NO: integer, name: string(50), exam_date: date, salary: number,)
Technician(tech_no: integer, ssn: number, name: string(50), address: string(100), phone_number:
number, salary: number, union_mem_no: number)
Model(model_no:string(6), weight: number, capacity: number, tech_no: integer)
Airplane(reg_no:number, model_no:string(6))
Test(F AA_NO: number, max_score: integer, name: string(25))
Test information(id: integer, tech_no: integer, reg_no: number, FAA_NO: number, hours: integer,
date: Date, scores: integer)
The primary key constraint are marked with red color while the foreign key constraint are marked with
blue brown.

Question 4
Create all tables in Deakin Oracle DBMS (about six tables including
composite tables) and Populate the tables with sample data (10
records in each table is recommended).
When creating tables it is good to start with a table that has no foreign key constraint.
Creating Technician table
Create table technician(
Tech_no integer not null,
Ssn number not null,
Name varcha2(40) not null,
Address varchar(20) not null,
Salary number not null,
Constraint tech_pk primary key(tech_no)
);
Insert data
Insert into technician values(‘1’,’100’,’ken lee’,’street 1’, ‘100’,’3000’);

i
Populating data in technician table
Select * from technician;
Model table
Description for model table
Inserting data to table model.

Inserting multiple data using insert all
Populating model table
Select * from model;

Creating plane table
Create table plane(reg_no number not null, model number not null, constraint plane_pk primary
key(reg_no), constraint fk_plane foreign key(model_no) references model);
Inserting data to plane table
Insert all
Into plane values(‘1’, ‘KQ-10’)
Into plane values(‘2’,’DC-10’)
Select 1 from dual;

Populate data in the plane table
Creating test table
Create table test(FAA_no number not null, name varchar(20) not null, score number not null,
Constraint test_pk primary key(FAA_no) );
Describing data
Inserting data to test table
WE USE INSERT ALL TO INSERT MULTIPLE DATA TO DATABASE

Populating test data

Creating test information table
Describing test info table
Inserting data using
Insert into test_info values(‘6’,’12’, ‘20/10/2039’, ‘95’,’2’, ‘5’);
Desc test_info

Populating test_info table
Traffic controller table
Create table traffic_controller(
TC_NO number,
Name varchar2(25),
Ssn number,
Salary number,
Constraint tc_pk primary key(TC_NO));

Insert data
Insert into traffic_controller values(‘1’, ‘sammy’, ‘101’, ‘12/12/2019’, ‘2000’);
Quiz 2
Alter traffic controller table
Alter table traffic_controller
Add phone_number number;