- Case Narration Section 1 Happy Travel Hat Is A Web Based Company That Has Decided To Expand Its Business By Providing 1 (50.5 KiB) Viewed 18 times
Case Narration Section 1. Happy Travel (HAT) is a web-based company that has decided to expand its business by providing its registered customers a one-stop shop flight information system to any destination in the world. The database will assist the customer in planning his/her trip in advance as every registered airline with Happy Travel sends its entire fleet's flight information, which then be posted on the website. This information is updated regularly to ensure the accuracy of the information. 2. However, the company is also accepting transactions over the counter to serve customers who do not have access to the Internet. They can fill-up forms to populate company database at the end of the day. These forms contain passengers' data and information for the flight they wish to avail. 3. On any given day, several airlines have flights to the same destination. On these flights, information about the plane such as the plane model, seating capacity, and amenities on board, are provided. 4. Information for each flight includes the flight schedule; the customer can view in advance the departure and arrival time of the flight, where the flight originated from and its destination. 5. The customer can also check whether the flight is fully booked by looking at the seat availability of the chosen flight. The seats are classified into 2 classes, namely first and economy class. The number of seats per class differs on every plane. The seat price is determined by the seat class it belongs to, the first class costs more than the economy class. The airline companies has agreed to make the prices uniform so whichever airline the customer chooses, the price is the same. The customer flying first class gets to board from the lounge section of the boarding area, whereas the customer flying on economy class will board through the normal check-in desk.
Query Description Section - (HAT Information Management Requirements) QD-Sec1. Route and Destination of a flight QD-Sec2. First Class Seat/s available in a particular flight QD-Sec3. Flights scheduled on a given date QD-Sec4. Flights going to a given destination QD-Sec5. Price of a given flight QD-Sec6. Plane to use for a given flight QD-Sec7. Planes that will fly on a given schedule/s. QD-Sec8. Arrival time of certain type of planes. QD-Sec9. Departure time of a flight in a particular destination. QD-Sec10. Number of seats available for a particular flight. QD-Sec11. List of Passengers boarding to lounge section. QD-Sec12. List of Passengers who make first class seats reservations. QD-Sec13. Seating plan of a given flight.
1 Enumerate the data that need to be accommodated in your database system. entity name: flight,counter, destination, passenger, first class passennger. attributes of flight are flight number,flight name, departure date, departure time,price,to from, arrival date, number of seats. attributes of counter are counter number,counter name. attributes of passenger are passenger id,name,from, destination, boarding time, boarding date. attributes of first class passenger are passenger id, name,from, destination, boarding time, boarding date. attributes of lounge are name and lounge id. attributes of destination are country,city,name, destination.
country city name destination id name lounge id 2. Draw the Conceptual Schema using the ER-Diagram. to flight number arrival time from arrival date (number of seat destination lounge flight_name (departure date departure time) flight 1..* can have 1.." Search and book flight search and book flight access to 1.* price passanger id first class passenger fill form name counter number from 1 name counter 1..* passanger id) passenger destination boarding time (boarding date) name from destination (boarding date boarding time)
3. Draw the Logical Design (Conceptual Schema + attributes) using the ERD. Flight Flight number Counter Counter Counter number name Passenger Flight Departure Departure price To name date time Passenger name from id Lounge First class passenger Passenger name from destination Boarding id date Lounge name id id Destination Destination name city destination Boarding date country from Boarding time Boarding time Arrival time No of seat Arrival date
4. Transform your Database Logical Design to its equivalent Relational Schema. Country City FLIGHT Number of Seat Arrival Date Arrival Time From To Price Departure time Departure date Flight name Flight Number CanH ave Name Destinatio Destination ID Lounge Name Lounge ID Access to Search & Book Flight Search & Book Flight Boarding time Boarding Data Destination Passenger Passenger ID Name Boarding Time Counter Number Name Fill Form This study source was downloaded by 100000847580346 from CourseHero.com on 06.29.-2022. 23-52-25 GMT-05:00 Counter First Class Passenger Passenger ID Name From Destination Boarding Date From
5. Determine and specify the functional dependencies that may exist in your relations. FLIGHT ID -> DEPARTURE DATE, BOARDING DATE, ARRIVAL DATE represents a partial dependency, because DEPARTURE DATE, BOARDING DATE, AND ARRIVAL DATE rather than on the entire primary key composed of FLIGHT ID AND PASSENGER ID PRICE -> NO. OF SEAT represents a transitive dependency, because PRICE depends on an attribute (NO. OF SEAT) that is not part of a primary key. FLIGHT ID, PASSENGER ID -> DEPARTURE DATE, BOARDING DATE, ARRIVAL DATE, DESTINATION ID represents a set of proper functional dependencies, because DEPARTURE DATE, BOARDING DATE, ARRIVAL DATE, and DESTINATION ID depend on the primary key composed of FLIGHT ID and PASSENGER ID.
6. Perform Normalization to relations with functional dependencies. PRIMARY KEY FOREIGN KEY DEPARTURE BOARDING DATE DATE FLIGHT ID DEPARTURE DATE DEPARTURE TIME BOARDING DATE BOARDING TIME ARRIVAL DATE ID PASSENGER DESTINATION FROM NAME DESTINATION ID COUNTER ID ID LOUNGE ID ARRIVAL TIME BOARDING PASS DESTINATION COUNTER NAME LOUNGE NAME ARRIVAL DATE NAME COUNTRY FLIGHT NAME PRICE LOUNGE TO ID CITY NO. OF SEAT
7. List all the derived final well-structured relations. 8. Write the SQL query using the final well-structured relations to generate the information requirements in the Query Description Section.