CitaHati Christian Senior School

Formal Assessment, March 2015

Topic: Database

 

Student’s name: _________________________   Class: ____________

 

Date: 19th March, 2015

Subject:  ___ Computer Science___                           Level: HL / SL

 

Time: 1 hour 30 minutes

 

 

OTA SYSTEM

MANDARA Inc. is one of the biggest OTA (Online Travel Agent) in India. It has a website that offers an online reservation system for its customers. The Extranet System has opened for B2B and B2C System, which means, other travel agents can make reservation through its system and a person or groups from corporate can also make a reservation. MANDARA offers an airlines ticket reservation that connect directly to the airline system, so even passengers can reserve the seat at the same time he/she make a booking. An Individual Traveller need to pay in advance, while passengers from corporate has given soft loan to pay after several weeks. As an airline regulation, MANDARA needs to put deposit total sum of money in order to make sure its system stay connected with the airline system. Beside airline tickets, MANDARA also offers Hotel Reservation Service, where its customer can make an online reservation. MANDARA open an access for its hotels, so that they can adjust room rates and allotments (note: allotment refers to room availability) To support its services, MANDARA has employed several people that need to be in charge in several department, such as ticketing, hotel and finance.

 

Construct a DFD (Data Flow Diagram) based on the case study!
You need to do these steps; identifying activities, put it in the same groups, create a context level and level 0!

 

Construct an ERD (Entity Relationship Diagram) from the DFD!

 

Construct SQL Query from the following:
Assuming that you have two tables called hotel and customer in your database!
customer: customer_id, customer_name, address, city, country
hotel: hotel_id, stars, hotel_name, address, city, country

 

Get all of your customers!
Get customer_id, customer_name and address only!
Get customer_name that begins with ‘Mr.’ and ‘Dr.’!
Get all of your customers that live in Indonesia!
Get all of you customers sorted by customer_name in descending order!
Get all of your hotels that contains string ‘Sheraton’!
Get hotel_name, address sorted by stars in ascending order!
Get all of your hotels that located in Singapore and having 3, 4 and 5 stars!
Get hotel_name and address in Bali!
Get the unique of city from table hotel!

 

Construct Flowchart for describing the process of hotel reservation sub system!

Tags: ,