Enhanced Entity Relationship Database Assignment Help

This sample solution of enhanced entity relationship database assignment help is project based in which described the daily working of airport with database

Section I. Task Description

Task 1: Conceptual design

EER diagram for the conceptual design of the database

Enhanced Entity Relationship

Figure 1 EERD

Assumptions

  • We have assumed that there must be an airport table and there will be only one airport and each city.
  • There is a Flight_schedule table that has an attribute ‘available seats’ in order to determine available seats for the passengers.
  • There is a table with name emergency contract in which along with postal code I have included city and state.
  • Foreign keys are applied wherever needed for example Flight_number is the primary key in the flight table and must be needed in the flight_schedule table as well hence is a foreign key in the in it.
  • I have assumed that all the data values should not be null hence not null constraint is applied.
  • We have assumed the date format must be dd/mm/yy and hence data should be inserted accordingly.

Task 2: ERD to table translation

All the data that is inserted is relevant to the table and overall database. Proper identification of entities and attributes has been done along with applied constraints and following the business rules. The data type assigned and length of data is as per the requirements of the database. [Also Read: – Entity Relationship Diagram Database Assignment Help]

Tables

Airplane

Enhanced Entity Relationship

Figure 2 schema

Enhanced Entity Relationship

Figure 3 data

Airpot:

Enhanced Entity Relationship

Figure 4 data

Enhanced Entity Relationship

Figure 5 schema

Emergency contact:

Enhanced Entity Relationship

Figure 6 schema

Enhanced Entity Relationship

Figure 7 data

Flight:

Enhanced Entity Relationship

Figure 8 schema

Enhanced Entity Relationship

Figure 9 data

Flight attendant:

Enhanced Entity Relationship

Figure 10 schema

Top Grade Assignment Help, Assignment Help Australia, Top Grade Assignment, Top Grade Assignments, Top Grade Assignments Help, Java Assignment Help, Online Assignment, Python Assignment Help, Database Assignment Help, Php Assignment Help, C Programming Assignment Help, Android Assignment Help, Sql Assignment Help, Microsoft Access Assignment Help, Technical Assignment Help, Web Programming Assignment Help, Dot Net Assignment Help, Oops Assignment Help

Figure 11 data

Flight booking:

Top Grade Assignment Help, Assignment Help Australia, Top Grade Assignment, Top Grade Assignments, Top Grade Assignments Help, Java Assignment Help, Online Assignment, Python Assignment Help, Database Assignment Help, Php Assignment Help, C Programming Assignment Help, Android Assignment Help, Sql Assignment Help, Microsoft Access Assignment Help, Technical Assignment Help, Web Programming Assignment Help, Dot Net Assignment Help, Oops Assignment Help

Figure 12 schema

Database Assignment Help

Figure 13 data

Flight schedule:

Database Assignment Help

Figure 14 schema

Database Assignment Help

Figure 15 data

Passenger:

Database Assignment Help

Figure 16 schema

Database Assignment Help

Figure 17 data

Pilot:

Database Assignment Help

Figure 18 schema

Database Assignment Help

Figure 19 data

Staff:

Database Assignment Help

Figure 20 schema

Sql Assignment Help

Figure 21 data

Training:

Assignment Help Australia

Figure 22 schema

Assignment Help Australia

Figure 23 data

Work experience:

Assignment Help Australia

Figure 24 schema

Assignment Help Australia

Figure 25 data

Task 3: Schema refinement and documentation

All the tables that I created are in 3NF. I have created the database keeping in mind the normalization steps and ensure that every table must follow 3NF. [Also Read: – ERD Database Management System Assignment]

Entity Attributes Datatype Length Description
AIRPLANE  

SERIAL_NO

INT 11  PRIMARY  KEY
TYPE_CODE
VARCHAR 200
  TYPE_DESCRIPTION VARCHAR 255
   CAPACITY INT 11
   FLIGHT_RANGE VARCHAR 100
  MANUFACTURE_DATE DATE
   PURCHASE_DATE DATE
   NEXT_SERVICE_DATE DATE
AIRPORT AIRPORT_ID  INT 11  PRIMARY KEY
AIRPORT_NAME VARCHAR 100
CITY  VARCHAR 100
STATE  VARCHAR 100
DESCRIPTION TEXT
EMERGENCY_CONTACT STAFF_ID  INT 11  PRIMARY KEY

FOREIGN KEY

PERSON_NAME  VARCHAR 200
POSTAL_ADDRESS VARCHAR 255
CITY VARCHAR 100
STATE VARCHAR 100
PHONE INT 11
EMAIL VARCHAR 200
RELATIONSHIP VARCHAR 50
FLIGHT_ATTENDANT FLIGHT_ATTENDANT_ID  INT 11  PRIMARY KEY
STAFF_ID INT 11 FOREIGN KEY
TRAINING_ID VARCHAR 5 FOREIGN KEY
TRAINING TRAINING_ID  INT 11  PRIMARY KEY
PROGRAM_NAME  VARCHAR 255
START_DATE DATE
FINISH_DATE  DATE
DESCRIPTION  TEXT
FLIGHT FLIGHT_NUMBER  VARCHAR 255  PRIMARY KEY
SOURCE_CITY  INT 11 FOREIGN KEY
DESTINATION_ID  INT FOREIGN KEY
STOP_OVER_CITY  INT 11 FOREIGN KEY
DEPARTURE_TIME  TIME 11
ARRIVAL_TIME  TIME
STOP_OVER_ DEPARTURE_TIME  TIME 11
STOP_OVER_ ARRIVAL_TIME TIME
WORK_EXPERIENCE STAFF_ID  ID 11  PRIMARY KEY

FOREIGN KEY

COMPANY_NAME  VARCHAR 255
COMPANY_POSITION VARCHAR 200
START_DATE  DATE 45
FINISH_DATE  DATE 11
FLIGHT_BOOKING BOOKING_ID  INT 11  PRIMARY KEY
TICKET_NUMBER  VARCHAR 50
PURCHASE_DATE  DATE 11
PAYMENT_TYPE VARCHAR  50
FLIGHT_NUMBER  VARCHAR 255 FOREIGN KEY
FLIGHT_DATE  DATE
TICKET_TYPE  VARCHAR 200
SOURCE_AIRPORT_ID  INT 11  FOREIGN KEY
DESTINATION_AIRPORT_ID INT 11 FOREIGN KEY
TICKET_PRICE INT 11
PASSENGER_ID INT 11 FOREIGN KEY
FLIGHT_SCHEDULE SERIAL_NO  INT 11 FOREIGN KEY
DATE  DATE PRIMARY KEY
FLIGHT_NUMBER  VARCHAR 255 PRIMARY KEY
CAPTAIN_ID  INT 45 FOREIGN KEY
CO_PILOT_ID  INT 11  FOREIGN KEY
FLIGHT_ATTENDANT_1 INT  11
FLIGHT_ATTENDANT_2 INT 11
FLIGHT_ATTENDANT_3 INT 11
FLIGHT_ATTENDANT_4 INT 11
FLIGHT_ATTENDANT_5 INT 11
FLIGHT_ATTENDANT_6 INT 11
FLIGHT_ATTENDANT_7 INT 11
FLIGHT_ATTENDANT_8 INT 11
AVAILABLE_SEATS INT 11
PASSENGER PASSENGER _ID  INT 11  PRIMARY KEY
ID_TYPE VARCHAR 50
FIRST_NAME VARCHAR  100
LAST_NAME  VARCHAR 100
SEX  VARCHAR 10
DOB  DATE
ADDRESS  VARCHAR 255
CITY  VARCHAR 200
STATE VARCHAR 200
POSTCODE INT 11
CONTACT_PHONE INT 11
EMAIL_ADDRESS VARCHAR 200
PILOT PILOT_ID  INT 11 PRIMARY KEY
STAFF_ID  INT 11 FOREIGN KEY
CERTIFICATION_DATE  DATE
SERIAL_NO  INT 11 FOREIGN KEY
STAFF STAFF_ID INT 11 PRIMARY KEY
STAFF_NAME  VARCHAR 200
DOB  DATE
JOIN_DATE  DATE
ADDRESS VARCHAR 100
CITY VARCHAR 100
STATE  VARCHAR 100
ZIPCODE  INT 11

Task 4: SQL Script files

Query 1:

For each airplane (given the serial number), list the type code, type description, capacity and flight range, manufacture date, purchase date, and the next service date. [Also Read: – IT Programming Database Assignment Help]

Assignment Help Australia

Figure 26 Query 1

Query 5:

For each city (given the city name), produce a list of the flights scheduled to arrive or depart within the next 24 hours. The list should include the flight number, the time of arrival or departure in ascending order of time [Also Read: – Office Solution Development Assignment Help]

Assignment Help Australia

Figure 27 Query 5

Query 8:

For each scheduled flight and a departure city, prepare a customer call list, which includes the ID type, ID number, full name, date of birth, address and contact phone number of the passengers booked to board the flight. [Also Read: – Staff Payroll System Database Assignment Help]

Assignment Help Australia

Figure 28 Query 8

Query 9:

Given a date, an origination city and a destination city, list all scheduled flights and the number of available seats on each flight

Assignment Help Australia

Figure 29 Query 9

Query 10:

For a given passenger (identified by ID type and ID number), list the flights he or she has been booked on, including the departure/arrival city and time. [Also Read: – Translation Memory Database Assignment Help]

Assignment Help Australia

Figure 30 Query 10

References

Techopedia.com. (2016). What is a Relational Database (RDB)? – Definition from Techopedia. [online] Available at: https://www.techopedia.com/definition/1234/relational-database-rdb [Accessed 5 May 2016].

Techterms.com. (2016). Flat File Definition. [online] Available at: http://techterms.com/definition/flatfile [Accessed 5 May 2016].

Top Grade Assignment provide technical assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us, order now 

top grade assignment

About the Author

Alice