May – Aug. 2020 Online Assessment CC116/CB203 DBMS/DB Systems
Page 1 of 4
Question 1
Scenario
“UCSI Travel” is a worldwide holiday company that maintains its own hotels in a number
of countries around the world. The company uses the following relational database to
manage its own business affairs. The relational database schema consists of relations
such as COUNTRY, REGION, RESORT, HOTEL and FACILITY. These relations are
shown in the descriptions below (please take not of the primary keys are underlined and
foreign key are in emboldened italics):
• A relation about the countries in which the company operates holidays:
COUNTRY ( CountryName string,
Language string,
Currency string,
Continent string,
TimeZone integer)
• A relation about the regions of different countries in which the holiday resorts are
located:
REGION ( RegionName string,
Landtype string,
Scenery string,
CountryName string)
• A relation about the resorts in which hotels are located:
RESORT ( ResortName string,
BeachType string,
NumOfBeaches integer,
RegionName string)
• A relation about hotels in different resorts:
HOTEL ( HotelId integer,
HotelName string,
Rating integer,
TotalRooms integer,
AvailRooms integer,
WebPage string,
ResortName string)
• A relation about the kinds of facilities in hotels and resorts:
FACILITY ( FacId integer,
Description string,
FacType char,
Category char)
• A relation about the facilities of each hotel:
May – Aug. 2020 Online Assessment CC116/CB203 DBMS/DB Systems
Page 2 of 4
FACILITY_IN_ HOTEL ( HotelId integer,
FacId integer)
This relation represents a many-many relationship between FACILITY and HOTEL
relations.
• A relation about the facilities of each resort:
FACILITY_IN_ RESORT ( ResortName string,
FacId integer)
This relation represents a many-many relationship between FACILITY and RESORT
relations. There is a constraint that the type of facilities permitted for a resort must not
be related to accommodation i.e., the value of the FacType attribute of the facilities
associated with a resort must be ‘O’ (meaning other facilities).
• A relation about bookings :
BOOKING ( BookId integer,
BookDate date,
ArrivDate date,
DepartDate date,
NumOfRooms integer,
HotelId integer
Cost float,
PayMethod string,
BookGuestId integer,
Status char)
• A relation about hotel guests :
GUEST ( GuestId integer,
GuestName string,
Address string,
BookId integer)
The names of most attributes explain what kind of data it is and they hold. Here are a
few explanatory notes for some (not all) attributes to supplement what is implied by their
names.
TimeZone The number of hours the country’s time is before or after USA
time. Assume the same difference in both summer and winter, as
most countries change between winter and summer time on almost
the same date.
Landtype The type of the land associated with a region. Following are some
of the land types:
‘isle’ = Island
‘main’ = Mainland
Rating The number of stars in a hotel’s rating.
May – Aug. 2020 Online Assessment CC116/CB203 DBMS/DB Systems
Page 3 of 4
AvailRooms The number of rooms that are available for booking. The
maximum value of AvailRooms is equal to TotalRooms and the
minimum value is zero.
WebPage The URL of the web page of a hotel.
FacType The facility type. This is either ‘A’ for accommodation or ‘O’ for
other.
Category Within each facility type, there are a number of categories of
facility.
For example, type ‘A’ has the following categories:
‘m’ = Meals
‘c’ = Children
‘b’ = Bedroom
‘a’ = Accommodation
For example, type ‘O’ has the following categories:
‘s’ = Sport
‘e’ = Entertainment
Description This describes each facility. For example, a hotel might have two
‘O’ type facilities with category ‘s’ described as “swimming
pools” and another described as “horse riding”.
BookGuestId This identifies which of the guests made the booking on behalf of a
family or group.
GuestId This identifies any guest.
Status This is used by transactions and identifies the status of the
booking. It can be one of the following values:
‘B’ = Booked
‘A’ = Arrived
‘C’ = Cancelled
‘D’ = Departed/Checked-out
‘P’ = Postponed
Task: The management of “UCSI Travel” requires you to get the answers to the
following queries from their database. In each case, consult the relational database
schema, write SQL statements that returns the required data.
May – Aug. 2020 Online Assessment CC116/CB203 DBMS/DB Systems
Page 4 of 4
(a) Write Data Definition Language (DDL) statements to create all the tables in the
relational database schema. (20 marks)
(b) Write Data Manipulation Language (DML) statements to populate data. Verify
the creation of table (eg. describe COUNTRY) and insertion of data (eg.
SELECT * FROM COUNTRY). You are required to display query results in the
report. (20 marks)
(c) Write SQL statement to display names and beach types of the Spanish island
resorts. Display query results in the report. (10 marks)
(d) Write SQL statement to display names and identifiers of those hotels that have
type ‘O’ facilities. Display query results in the report. (10 marks)
[Total: 60 Marks]
Question 2
(a) Describe what database security is. (5 marks)
(b) Describe FIVE (5) types of threats to database. (15 marks)
(c) Discuss how to prevent ransomware infection to database.[Hint: you can include
good security practices in your answer] (20 marks)
Note: Please ensure all answers for question 2 must be cited with references and
reference list must also be provided. [Total: 40 Marks]
END OF QUESTION PAPER