CC116/CB203代做、data代做、SQL编程语言调试、SQL代写

日期：2020-08-19 10:32

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