This project is to design and implement a suitable database for Qatar Tour Agency $($QTA$).$

This should be prepared in a report to present to the company. Following this, you will need

to manipulate data in the database.

The details of some basic information that must be considered in your design for the QTA

database are given below. Based on your understanding of the case study, there may be some

other relevant details to consider in order to create a normalised model $(3$rd Normal Form$).$

QTA is a local tour company in Qatar that offers internal tours to main attractions places i$.$e$.$

Pearl, Simaisma, Qatar Museum, Banana Island, etc. QTA offers various tours throughout the

year which includes picking up customers from$/$to their hotel or place of stay. QTA wants to

create a database that will be used to reserve and book tour packages. QTA offers many tour

packages like Entertainment Trip, Site Seeing, Island Visits etc. QTA system saves site

information and the nearest landmark to each tour site. There are various tour trips for the

same tour package. Every tour trip which belongs to a single tour package provides

information about the tour trip destination, price, date, start time, duration, and location of the

tour etc. Tour trip reservations can be done by QTA$$s tour agent. Tour reservation can be

done for several tour trips and reservation must be made $7$ days prior to the tour trip date.

Reservation status can be either confirmed or cancelled. A tour guide who can speak at least

two languages will be available during the journey. The tour guide is handed a weekly

schedule illustrating the daily tour trips assigned to him$/$her and highlighting the stop points

in the journey. A tour guide will be assigned for only one tour trip a day.

The System will need the following constraints:

$-$ Primary Key and Foreign Key.

$-$ Language $($Arabic$,$English$,$French$,$Urdu$).$

$-$ Customer$$s information is not NULL.

$-$ Tour Sites $($Pearl$,$Simaisma$,$Qatar Museum$,$Banana Island$).$

$-$ Reservation status $($Confirmed$,$Cancelled$).$

$-$ Add additional constraints as needed.

$[$Phase # $1]$ Due to $9$th of March, $2024$

Part A:

Steps to draw the ERD:

a$.$ Identify$/$list all Entities and attributes

b$.$ Identify$/$list the Relationships & Cardinalities between

Entities.

c$.$ Note that you will need two tables for Login and

Transaction. Read part D for more details.

d$.$ Draw the ERD using any Data Modelling Software.

Part B:

Perform the following steps:

a$.$ Mapping ERD to Relational Schema.

b$.$ Adding Foreign Keys.

c$.$ Resolve any M:N relationship between the entities.

d$.$ Draw the Relations using any Modelling Software.