Reference no: EM132371795

Assignment

LO1 Describe the techniques used in storing and retrieving data.

LO2 Evaluate data models and apply data modelling techniques to capture the data aspects of real- world situations

LO3 Design and develop relational databases by using SQL and a database management system

Please read the full assignment details that follow.

Computer Sciences Department frequent fliers have been complaining to RowHill Airport officials about the poor organization at the airport. As a result, the officials decided that all information related to the airport should be organized using a DBMS, and you have been hired to design the database.

Your first task is to organize the information about all the airplanes stationed and maintained at the airport. The relevant information is as follows:

• Every airplane has a registration number, and each airplane is of a specific model.

• The airport accommodates a number of airplane models, and each model is identified by a model number (e.g., DC-W) and has a capacity and a weight.

• A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.

• Each technician is an expert on one or more plane model(s).

• Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam.

• All airport employees (including technicians and traffic controllers) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by a social security number.

• The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Federal Aviation Administration (FAA) test number, a name, and a maximum possible score.

• The FAA requires the airport to keep track of each time that a given airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the air plane received on the test.

Use the description provided above about RowHill Airport. You can make your own assumptions based on the requirements given. However, the assumptions should be clearly indicated / defined.

Answer following questions. Provide the following:

Q1 Identify business rules. Business rules are important to define relevant constraint in the organisations. Some organisations have specific rules only practice by the organisation. (e.g. A business rule for a car sales company may be "for each car separate invoice should be issued."). You can make any appropriate assumptions and mention them in your answer.

Q2 ER diagram is based on the business rules, and should clearly label all entities, entity attributes, primary and foreign keys, relationship and connectivity. The cardinality is optional.
Instruction: Use professional software (e.g., M

S Office Visio) to draw the ER diagram. Crow's Foot notation is preferable.

Q3 Develop relational schemas. Relational schemas should be derived using the ERD. You should map cardinalities correctly from ERD to Relational Schema. You should clearly indicate the referential integrity constraints (primary and foreign key relationships) using arrows. Clearly indicate data type for each attribute. e.g.

Emp( eid: integer, ename: string(50), address: string(100), did: number)
4
Dept(did: number, dname : string (15))
moodle.deakincollege.edu.au

Q4 SQL commands

1. 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).

2. Alter the Traffic Controller table and add new field Phone Number for Traffic Controller table. Type for Phone Number should be number.

3. Increase the annual salary for all Technician member by 5%.

4. For each technician, display all plane model(s) he/she expert in.

5. Display the maximum score that each air plane received on the test(s) this year.

6. Create your own query. It must include a nested query. Submit the following:

i question your query is answering the SQL query

ii the mark for this question will depend on the complexity of the query.

iii higher marks will be given for queries that are more complex and/or innovative.

iv if you do not provide a description of what question the query is answering, you will get zero for this query.

Simple Example on how to submit Q4

Screen screenshots (Please provide clear screen shot showing your work)

Create employee table

CREATE TABLE EMPLOYEE( Ssn char(9) NOT NULL,

Fname varchar(40) NOT NULL, Minit varchar(1) NOT NULL, Lname varchar(40) NOT NULL, Bdate date ,

Address varchar(40) NOT NULL, City varchar(20) NOT NULL, Stat varchar(2) NOT NULL,

Sex varchar(1) NOT NULL, Super_SSN char(9),

DNO char(4) NOT NULL,

CONSTRAINT PK_Person PRIMARY KEY (Ssn),

CONSTRAINT FK_Emp FOREIGN KEY (DNO) REFERENCES Persons(DNO)

);