Information Technologyquestionseries 5 / Item: 15

Information TechnologyQuestionSeries 5 / Item: 15

Name: Date:

Topic: RELATIONAL DATABASES

______

Create and save a database called XXBALI, replacing XX with your initials.

(1)

Create a table called HOTELS, which will be used to store data about various hotels in Bali. Use the following fields for your table:

HotelID - ID used to identify each hotel (autonumber)
Name - hotel name (text)
Cost - rate per night (currency)
Air - whether the hotel has air-conditioning (yes or no)
Region - location (integer)

Make the HotelID the primary key

(3)

Enter the following data into your table.

HotelID / Name / Cost / Air / Region
1 / Bali Roni / $60 / Y / 1
2 / Bountry / $54 / N / 2
3 / Puri Santrian / $49 / N / 4
4 / Dynusty / $66 / Y / 1
5 / Patri Bali / $45 / Y / 3
6 / Nusa Inda / $66 / Y / 3
7 / New BaliBeach / $59 / Y / 4
8 / Patra Jaysa / $69 / Y / 1
9 / MastafaGardens / $25 / N / 2

(3)

Create and save the following queries - show only those fields indicated in the brackets. Place the results of each query into a single word-processed document. Save each query as 4a, 4b, 4c etc.

a)Produce a list of all hotels sorted by price. (name, cost)

b)Produce a list of all hotels with air-conditioning. (name, cost, air)

c)Produce a list of all hotels in region 3. (name, region)

d)Produce a list of all hotels between $45 and $60 a night, inclusive. (name,

cost)

e)Produce a list of all hotels that cost less than $60 a night and have air-

conditioning. (name, cost, air)

(5 x 2 = 10)

Create a new table called REGION that contains the following fields:

Region / Name / Description
1 / Tuban / A quieter area just south of Kuta with loads of restaurants and great shopping.
2 / Kuta / The tourist Mecca of Bali and a surfer's paradise.
3 / Nusa Dua / An exclusive resort haven situated on the southern side of Bali.
4 / Sanur / Shimmering white sands and clear waters away from the hectic Kuta.

(2)

a)Create and save a query that shows the name of each hotel, its cost per night, the name of the region it is in and a description of the region.

b)Create and save a query that shows the name of each hotel, it's cost per night and the name of the region it is in. Arrange the list so that their region groups the hotels.

Copy the results of these queries into the word-processed document, save and print.

(3 + 3 = 6)

1 + 3 + 3 + 10 + 2 + 6 = Total: 25 marks