Database Modelling For Amazing Charter Company

Firstly I understand the whole case study given in this assignment. Then find out the proper entities. Some attributes of entities are given in this case study and some attributes are assumed. These assumptions complete the entity relationship diagram. For example Employee, customer or passenger’s personal information. Then I draw the diagram on paper and establish the relationship between the entities. At last, I draw an entity relationship diagram according to the requirements.

I faced many issues when I draw an entity relationship diagram. But the main issue was how I established the relationship between Employee and charter Trip. We know that an employee has many charter trips and a charter trip also has many employees. There is many to many relationships between charter trip and employee. To resolve many to many relationships I create the Charter_Trip_Employee entity.

An Aircraft has one or many CharterTrip.
Each CharterTrip is related to one and only one Aircraft.
A charterTrip has one or many reservations by Customer.
Each Reservation is related to one and only one CharterTrip.

A Customer reserve one or many Reservation.
Each Reservation is related to one and only one Customer.

A Customer pays one or many Payments.
Each Payment related to one and only one Customer.

A CharterTrip has one or many Expenses.
Each Expense is related to one and only one CharterTrip.

A CharterTrip has many Passengers.
- A CharterTrip has one or many Charter_Trip_Passenger.
- Each Charter_Trip_Passenger is related to one and only one CharterTrip.
A passenger has many CharterTrip.
- A Passenger has one or many Charter_Trip_Passenger.
- Each Charter_Trip_Passenger is related to one and only one Passenger.

A CharterTrip has many Employees.
- A CharterTrip has one or many Charter_Trip_Employee.
- Each Charter_Trip_Employee is related to one and only one CharterTrip.
An Employee Work in many CharterTrip.
- An Employee Work in one or many Charter_Trip_Employee.
- Each Chater_Trip_Employee is related to one and only one Employee.

An Employee gave many Test.
- An Employee has one or many Test_Result.
- Each Test_Result is related to one and only one Employee.
A Test has many Employees.
- A test has one or many Test_Result.
- Each Test_Result is related to one and only one Test.

An Employee has many License_Certification.
- An Employee has one or many LS_Held_By_Employee.
- Each LS_Held_By_Employee is related to one and only one Employee.
A License_Certification has many Employees.
- A License_Certification has one or many LS_Held_By_Employee.
- Each LS_Held_By_Employee is related to one and only one License_Certification.

An Employee has Zero or many Complains.
Each Complain is related to one and only one Employee.

An Employee has Zero or many Requirements.
Each Requirement is related to one and only one Employee.

An Employee has one or many Recurrency_Training.
Each Recurrency_Training is related to one and only one Employee.

Employee work as one Pilot.
A Pilot is related to one and only one Employee.
Employee work as one Crew.
A Crew is related to one and only one Employee.

Assumption/justifications for optionality, connectivity constraints
Entity 1	Entity 2 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Optionality	Connectivity Constraints
CharterTrip	Reservation	Mandatory	One to many
Charter_Trip_Passenger	Mandatory	One to many
Reservation	Mandatory	One to many
Expenses	Mandatory	One to many
Charter_Trip_Employee	Mandatory	One to many
Aircraft	CharterTrip	Mandatory	One to many
Customer	Reservation	Mandatory	One to many
Payment	Mandatory	One to many
Passenger	Charter_Trip_Passenger	Mandatory	One to many
Employee	Charter_Trip_Employee	Mandatory	One to many
Complain	Mixed	Zero to many
requirements	Mixed	Zero to many
Recurrency_Training	Mandatory	One to many
Pilot	Mandatory	One to one
Crew	Mandatory	One to one
Test_Result	Mandatory	One to many
LS_Held_By_Employee	Mandatory	One to many
Test	Test_Result	Mandatory	One to many
License_Certification	LS_Held_By_Employee	Mandatory	One to many

Assumption/justifications for the entity, data type and data domain
Entity Name	Attribute name	Data Type	Data Domain
Employee	Employee_Id (PK)	int	10
	Name	varchar	100
	Address	varchar	200
	Phone	varchar	15
	Email	varchar	30
	Qualification	varchar	100
CharterTrip	CharterTrip_No (PK)	int	10
	Aircraft_No (FK)	int	10
	Source	varchar	30
	Destination	varchar	30
	Start_Datetime	DateTime	–
	End_Datetime	DateTime	–
	Hours	time	–
	Distance_Flown	double	–
	No_Of_Round	int	10
	Fuel_Usage	double	–
Aircraft	Aircraft_No(PK)	int	10
	Model_No	int	5
	Engine	varchar	50
	Take_Of_Weight	double	–
	No_Of_Pilot	int	5
	No_Of_Crew	int	5
	No_Of_Passenger	int	5
Reservation	Reservation_No (PK)	int	10
	Customer_Id (FK)	int	10
	CharterTrip_No (FK)	int	10
	DateTime	DateTime	–
	Special_Requirment	varchar	200
	No_Of_Passenger	int	5
Customer	Customer_Id (PK)	int	10
	Name	varchar	30
	Address	varchar	200
	Phone_Number	varchar	15
	Email	varchar	100
Payment	Payment_No (PK)	int	10
	Customer_Id (FK)	int	10
	Crew_Charges	double	–
	Pilot_Charges	double	–
	Distnace_Flown_Charges	double	–
	Other_Charges	double	–
	Total_Amount	double	–
	Pay_Amount	double	–
	Panding_Amonut	double	–
	Date	Date	–
Charter_Trip_Passenger	S_No (PK)	Int	10
	Passenger_ID (PK, FK)	int	10
	CharterTrip_No (PK,FK)	int	10
Passenger	Passenger_ID (PK)	Int	10
	Name	Varchar	30
	Address	varchar	100
	Phone_Number	varchar	15
	Email	varchar	100
Expenses	S_No (PK)	Int	10
	CharterTrip_No (FK)	int	10
	Date	date	–
	Description	Varchar	200
Charter_Trip_Employee	S_No (PK)	int	10
	CharterTrip_No (PK,FK)	int	10
	Employee_Id (PK, FK)	int	10
	Date	date	–
	Position	varchar	10
Complain	S_No (PK)	int	10
	Employee_Id (FK)	int	10
	Description	varchar	200
	Date	date	–
Recurrency_Training	Date_Time (PK)	DateTime	–
	Employee_Id (PK, FK)	Int	10
	Description	varchar	200
	Result	varchar	10
Pilot	Employee_Id (PK, FK)	int	10
	Rating	int	5
Crew	Employee_Id (PK, FK)	int	10
	Per_Hour_Rate	double	–
	Position	varchar	10
	Status	varchar	10
	Description	varchar	200
Requirement	S_No (PK)	int	10
	Employee_Id (FK)	int	10
	Description	varchar	200
	Date	date	–
Test_Result	Test_Code (PK,FK)	int	10
	Employee_Id (PK, FK)	int	10
	Date (PK)	date	–
	Result	varchar	10
Test	Test_Code (PK)	int	10
	Description	varchar	200
	Frequency	varchar	20
License_Certification	LS_Name (PK)	varchar	50
	Description	varchar	200
LS_Held_By_Employee	S_No (PK)	int	10
	LS_Name (FK)	varchar	50
	Employee_Id (FK)	int	10
	Date_Time	DateTime	–

Integrity issues create when user want to delete parent recode those already in the child table.
User tries to add the wrong data. For example, administrator set employee_Id as an integer but user tries to add varchar.

User transfer database one system to other system and the network will fail.

An Aircraft has one or many CharterTrip.
Each CharterTrip is related to one and only one Aircraft.

A charterTrip has one or many reservations by Customer.
Each Reservation is related to one and only one CharterTrip.

A Customer reserve one or many Reservation.
Each Reservation is related to one and only one Customer.

A Customer pays one or many Payments.
Each Payment related to one and only one Customer.

A CharterTrip has one or many Expenses.
Each Expense is related to one and only one CharterTrip.

A CharterTrip has many Passengers.
- A CharterTrip has one or many Charter_Trip_Passenger.
- Each Charter_Trip_Passenger is related to one and only one CharterTrip.
A passenger has many CharterTrip.
- A Passenger has one or many Charter_Trip_Passenger.
- Each Charter_Trip_Passenger is related to one and only one Passenger.

A CharterTrip has many Employees.
- A CharterTrip has one or many Charter_Trip_Employee.
- Each Charter_Trip_Employee is related to one and only one CharterTrip.
An Employee Work in many CharterTrip.
- An Employee Work in one or many Charter_Trip_Employee.
- Each Chater_Trip_Employee is related to one and only one Employee.

An Employee gave many Test.
- An Employee has one or many Test_Result.
- Each Test_Result is related to one and only one Employee.
A Test has many Employees.
- A test has one or many Test_Result.
- Each Test_Result is related to one and only one Test.

An Employee has many License_Certification.
- An Employee has one or many LS_Held_By_Employee.
- Each LS_Held_By_Employee is related to one and only one Employee.
A License_Certification has many Employees.
- A License_Certification has one or many LS_Held_By_Employee.
- Each LS_Held_By_Employee is related to one and only one License_Certification.

An Employee has Zero or many Complains.
Each Complain is related to one and only one Employee.

An Employee has Zero or many Requirements.
Each Requirement is related to one and only one Employee.

An Employee has one or many Recurrency_Training.
Each Recurrency_Training is related to one and only one Employee.

Employee work as one Pilot.
A Pilot is related to one and only one Employee.

Employee work as one Crew.
A Crew is related to one and only one Employee.

Assumption/justifications for optionality, connectivity constraints
Entity 1	Entity 2	Optionality	Connectivity Constraints
CharterTrip	Reservation	Mandatory	One to many
Charter_Trip_Passenger	Mandatory	One to many
Reservation	Mandatory	One to many
Expenses	Mandatory	One to many
Charter_Trip_Employee	Mandatory	One to many
Aircraft	CharterTrip	Mandatory	One to many
Customer	Reservation	Mandatory	One to many
Payment	Mandatory	One to many
Passenger	Charter_Trip_Passenger	Mandatory	One to many
Employee	Charter_Trip_Employee	Mandatory	One to many
Complain	Mixed	Zero to many
requirements	Mixed	Zero to many
Recurrency_Training	Mandatory	One to many
Pilot	Mandatory	One to one
Crew	Mandatory	One to one
Test_Result	Mandatory	One to many
LS_Held_By_Employee	Mandatory	One to many
Test	Test_Result	Mandatory	One to many
License_Certification	LS_Held_By_Employee	Mandatory	One to many

Assumption/justifications for the entity, data type and data domain
Entity Name	Attribute name	Data Type	Data Domain
Employee	Employee_Id (PK)	int	10
	Name	varchar	100
	Address	varchar	200
	Phone	varchar	15
	Email	varchar	30
	Qualification	varchar	100
CharterTrip	CharterTrip_No (PK)	int	10
	Aircraft_No (FK)	int	10
	Source	varchar	30
	Destination	varchar	30
	Start_Datetime	DateTime	–
	End_Datetime	DateTime	–
	Hours	time	–
	Distance_Flown	double	–
	No_Of_Round	int	10
	Fuel_Usage	double	–
Aircraft	Aircraft_No(PK)	int	10
	Model_No	int	5
	Engine	varchar	50
	Take_Of_Weight	double	–
	No_Of_Pilot	int	5
	No_Of_Crew	int	5
	No_Of_Passenger	int	5
Reservation	Reservation_No (PK)	int	10
	Customer_Id (FK)	int	10
	CharterTrip_No (FK)	int	10
	DateTime	DateTime	–
	Special_Requirment	varchar	200
	No_Of_Passenger	int	5
Customer	Customer_Id (PK)	int	10
	Name	varchar	30
	Address	varchar	200
	Phone_Number	varchar	15
	Email	varchar	100
Payment	Payment_No (PK)	int	10
	Customer_Id (FK)	int	10
	Crew_Charges	double	–
	Pilot_Charges	double	–
	Distnace_Flown_Charges	double	–
	Other_Charges	double	–
	Total_Amount	double	–
	Pay_Amount	double	–
	Panding_Amonut	double	–
	Date	Date	–
Charter_Trip_Passenger	S_No (PK)	Int	10
	Passenger_ID (PK, FK)	int	10
	CharterTrip_No (PK,FK)	int	10
Passenger	Passenger_ID (PK)	Int	10
	Name	Varchar	30
	Address	varchar	100
	Phone_Number	varchar	15
	Email	varchar	100
Expenses	S_No (PK)	Int	10
	CharterTrip_No (FK)	int	10
	Date	date	–
	Description	Varchar	200
Charter_Trip_Employee	S_No (PK)	int	10
	CharterTrip_No (PK,FK)	int	10
	Employee_Id (PK, FK)	int	10
	Date	date	–
	Position	varchar	10
Complain	S_No (PK)	int	10
	Employee_Id (FK)	int	10
	Description	varchar	200
	Date	date	–
Recurrency_Training	Date_Time (PK)	DateTime	–
	Employee_Id (PK, FK)	Int	10
	Description	varchar	200
	Result	varchar	10
Pilot	Employee_Id (PK, FK)	int	10
	Rating	int	5
Crew	Employee_Id (PK, FK)	int	10
	Per_Hour_Rate	double	–
	Position	varchar	10
	Status	varchar	10
	Description	varchar	200
Requirement	S_No (PK)	int	10
	Employee_Id (FK)	int	10
	Description	varchar	200
	Date	date	–
Test_Result	Test_Code (PK,FK)	int	10
	Employee_Id (PK, FK)	int	10
	Date (PK)	date	–
	Result	varchar	10
Test	Test_Code (PK)	int	10
	Description	varchar	200
	Frequency	varchar	20
License_Certification	LS_Name (PK)	varchar	50
	Description	varchar	200
LS_Held_By_Employee	S_No (PK)	int	10
	LS_Name (FK)	varchar	50
	Employee_Id (FK)	int	10
	Date_Time	DateTime	–

Integrity issues create when user want to delete parent recode those already in the child table.
User tries to add the wrong data. For example, administrator set employee_Id as an integer but user tries to add varchar.

User transfer database one system to other system and the network will fail.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Database Modelling For Amazing Charter Company ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order