Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

The knowledge test - Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modeling and Normalization.

Business rules - Write Business rules to create ERD 
2. Entities and Attributes - List Entities, Attributes - Include all attributes that you believe would be useful
3. ER Diagram - Design an ER diagram for the above case study. 
o Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software.


Hand-drawn diagrams will not be accepted
o Only use “crow’s foot notation”
o ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.
o When completed, insert your ER Diagram into a Word document either by taking a
screenshot of the diagram and pasting it in the word document, or by exporting the diagram as an image and the inserting it in the word document

Flight

The business rules from the case study are;

  • MPloy airline has many flights.
  • A flight has many passengers on board.
  • A flight uses one and only one airplane.
  • A flight has one and only one pilot who flies the single plane.
  • The airline has many maintenance procedures that are done on airplanes.
  • A maintenance event involves one airplane undergoing one procedure.

Entities and attributes

Based on the business rules and the case study, the following entities with their attributes can be derived;

  • Flight (flightNO, date, departureTime, arrivalTime, pilotNO, serialNO)
  • Passenger (passengerNO, name, address, telNO)
  • Flight_passengers (passengerNO, flightNO, fare, reservationDate)
  • Pilot (pilotNO, firname, lastName, dob, dateOfHire)
  • Airplace (serialNO, model, manufacturer, yearBuilt)
  • Procedure (procedureNO, procedureName, frequency )
  • Maintenance_event (eventID, location, duration, procedureNO, serialNO)

Entity relationship diagram

Table Name

Attribute

Attribute description

Date type

Date format

range

mandatory

Primary key/ foreign key

Foreign key reference table

Flight

FlightNO

Unique value identifying a flight

CHAR(5)

F001

Y

PK

Date

The date the flight is set to happen

Date

dd/mm/yyyy

Y

PK

departureTime

The time will the plane will take off

CHAR(10)

12:00pm

6-10

Y

arrivalTime

The time the plane will land at the destination

CHAR(10)

02:00AM

6-10

Y

pilotNO

Unique identifier of the pilot who will fly the plane

CHAR(4)

E001

Y

FK

Pilot

serialNO

Unique identifier of the plane that will be used

CHAR(4)

A001

Y

FK

Airplane

Passenger

PasserngerNO

Unique identifier of a passenger

CHAR(4)

P001

Y

PK

Name

The names of a passenger

VARCHAR(100)

John Doe

25-50

Y

Address

The address of the passenger

VARCHAR(250)

1st street kings landing

50-100

Y

telNO

The tel of the passenger

VARHCHAR(15)

+ 23 23 323

10-15

Y

Flight_passengers

passengerNO

Unique identifier of the passenger in the flight

CHAR(4)

Y

PK, FK

Passenger

flightNO

Unique identifier of the flight

CHAR(4)

Y

PK,FK

Flight

flightDate

Unique date of a flight

PK,FK

flight

Fare

The fare paid by the passenger

DECIMAL

100.99

Y

P

reservationDate

The date the passenger made a reservation

date

DD/MM/YYYY

Y

Pilot

PilotNO

Unique identifier of a pilot

CHAR(4)

E001

Y

PK

firstName

The first name of the pilot

VARCHAR(50)

John

8-15

Y

lastName

The last name of the pilot

VARCHAR(50)

Doe

8-15

Y

dob

The date of birth of the pilot

Date

DD/MM/YYYY

Y

dateOfHire

The date the pilot was hired

date

DD/MM/YYYY

Y

Airplane

serialNO

Unique identifier of an airplane

CHAR(4)

A001

Y

PK

Model

The model of the airplace

VARCHAR(25)

Boeing 737 F

15-20

Y

Manufacturer

Manufacturer name

VARCHAR(50)

Tesla Motors

15-35

Y

yearBuilt

The year the airplane was manufactured

INTEGER(4)

2005

4

Y

procedures

procedureNO

Unique identifier of a procedure

CHAR(4)

PR01

Y

PK

procedureName

Name of the procedure

VARCHAR(50)

Engine checkup

10-30

Y

frequency

Frequency the procedure has to be done on a plane

INTEGER(9)

5

1-5

Y

Maintenance_event

eventID

Unique key identifying a maintenance event

CHAR(4)

M001

Y

PK

Location

Location the maintenance event took place

VARCHAR(25)

Melbourne

5-25

Y

Duration

The duration in hours it took to complete the event

INTEGER(5)

24

N

procedureNO

Foreign key referencing a unique procedure

CHAR(4)

Y

FK

Procedure

serialNO

Foreign key referencing a unique plane

CHAR(4)

Y

FK

airplane

  • Table flight

Primary key: (flightNO, date)

Foreign key: pilotNO, serialNO

Normal Form:3NF

  • Table passenger

Primary key : (passengerNO)

Foreign key: None

Normal Form: 3NF

  • Table flight_passengers

Primary key: (passengerNO, flightNO, date)

Foreign key: passengerNO, flightNO, date

Normal Form : 3NF

  • Table pilot

Primary key : ( pilotNO )

Foreign key : None

Normal Form : 3NF

  • Table airplane

Primary key : SerialNO

Foreign key: None

Normal Form: 3NF

  • Table procedure

Primary key: procedureNO

Foreign key: None

Normal form: 3NF

  • Table maintenance_event

Primary key: eventID

Foreign key:procedureNO, serialNO

Normal Form: 3NF

SQL: DDL and DML statements

DDL

create table passenger(

     passengerNO char(4) primary key,

     name varchar(100) not null,

     address varchar(250) not null,

     telNO varchar(15) not null

);

create table pilot(

     pilotNO char(4) primary key,

     firstName varchar(50) not null,

     lastName varchar(50) not null,

     dob date not null,

     dateOfHire date not null

create table airplane (

     serialNO char(4) primary key,

     model varchar(25) not null,

     manufatucturer varchar(50) not null,

     yearBuilt integer(4) not null

create table procedures (

     procedureNO char(4) primary key,

     procedureName varchar(50) not null,

     frequency integer(9) not null

create table flight(

     flightNO char(4) not null,

     flightDate date not null,

     departureTime char(10) not null,

     arrivalTime char(10) not null,

     pilotNO char(4) not null,

     serialNO char(4) not null,

     primary key (flightNO,flightDate),

     foreign key (pilotNO) references pilot (pilotNO),

     foreign key (serialNO) references airplane (serialNO)

 create table flight_passengers (

     passengerNO char(4)  not null,

     flightNO char(4) not null,

     flightDate date not null,

     fare decimal not null,

     reservationDate date not null,

     primary key (passengerNO,flightNO,flightDate),

     foreign key (passengerNO) references passenger (passengerNO),

     foreign key (flightNO,flightDate) references flight (flightNO,flightDate)

 create table maintenance_event (

     eventID char(4) primary key,

     location varchar(25) default 'melbourne' not null,

     duration integer(5) not null,

     procedureNO char(4) not null,

     serialNO char(4) not null,

     foreign key (procedureNO) references procedures (procedureNO),

     foreign key (serialNO) references airplane (serialNO)

DML Scripts

INSERT INTO `airplane` (`serialNO`, `model`, `manufatucturer`, `yearBuilt`) VALUES

('A001', 'Boieng 737', 'Boieng', 2012),

('A002', 'Airbus 3243', 'Airbus', 2012),

('A003', 'Boeing 335', 'Boeing ', 2014)

INSERT INTO `passenger` (`passengerNO`, `name`, `address`, `telNO`) VALUES

('P001', 'Jon Snow', 'winterfell', '+ 434 3434'),

('P002', 'Arya stark', 'winterfell', '+34432 324324'),

('P003', 'Brianne of Tarth', 'kings landing', '+3 433443')

INSERT INTO `pilot` (`pilotNO`, `firstName`, `lastName`, `dob`, `dateOfHire`) VALUES

('E001', 'Peter', 'Griffin', '2018-05-22', '2019-02-23'),

('E002', 'Cleveland', 'Brown', '2018-03-05', '2018-09-11'),

('E003', 'Lois', 'Griffin', '2012-03-05', '2014-03-05')

INSERT INTO `procedures` (`procedureNO`, `procedureName`, `frequency`) VALUES

('PR01', 'Engine checkup', 36),

('PR02', 'Seats checkup', 24),

('PR03', 'Pilot bay controls checkup', 48);

INSERT INTO `flight` (`flightNO`, `flightDate`, `departureTime`, `arrivalTime`, `pilotNO`, `serialNO`) VALUES

('F001', '2018-09-11', '10:00PM', ')8:00AM', 'E001', 'A001'),

('F002', '2018-09-18', '12:00AM', '12:00PM', 'E002', 'A002'),

('F003', '2018-09-18', '03:00AM', '12:00PM', 'E003', 'A003');

INSERT INTO `flight_passengers` (`passengerNO`, `flightNO`, `flightDate`, `fare`, `reservationDate`) VALUES

('P001', 'F001', '2018-09-11', '234', '2018-09-12'),

('P002', 'F002', '2018-09-18', '433', '2018-09-18'),

('P003', 'F003', '2018-09-18', '2434', '2018-09-18');

INSERT INTO `maintenance_event` (`eventID`, `location`, `duration`, `procedureNO`, `serialNO`) VALUES

('M001', 'melbourne', 24, 'PR01', 'A001'),

('M002', 'melbourne', 45, 'PR02', 'A002'),

('M003', 'melbourne', 34, 'PR03', 'A003');

Default clause

Using default for a column means that the column will always have a default value during data insertion unless the default vaue is changed. From the database the location column in maintenance_events table is set to default meaning if it is not specified then the default value is inserted

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Essay: Entities And Attributes For Flight Reservation System." (70 Characters). Retrieved from https://myassignmenthelp.com/free-samples/bit231-database-systems/events.html.

"Essay: Entities And Attributes For Flight Reservation System." (70 Characters)." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/bit231-database-systems/events.html.

My Assignment Help (2021) Essay: Entities And Attributes For Flight Reservation System." (70 Characters) [Online]. Available from: https://myassignmenthelp.com/free-samples/bit231-database-systems/events.html
[Accessed 28 March 2024].

My Assignment Help. 'Essay: Entities And Attributes For Flight Reservation System." (70 Characters)' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/bit231-database-systems/events.html> accessed 28 March 2024.

My Assignment Help. Essay: Entities And Attributes For Flight Reservation System." (70 Characters) [Internet]. My Assignment Help. 2021 [cited 28 March 2024]. Available from: https://myassignmenthelp.com/free-samples/bit231-database-systems/events.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close