2
SECTION A
ANSWER ALL QUESTIONS [40 marks]
1. Briefly discuss the meaning of each of the following terms: [5 marks]
1) Data
2) Database
3) Database management system
4) Data independence
5) Database instance
2. Briefly discuss what a user view represents when designing a database system. [2 marks]
3. Discuss the differences between the candidate keys and the primary key of a table. Explain what
is meant by a foreign key. How do foreign keys of tables relate to candidate keys? [5 marks]
4. What are the two major components of SQL and what function do they serve? [4 marks]
5. What is the difference between a subquery and a join in SQL? [4 marks]
6. Briefly describe the five components of the DBMS environment. [5 marks]
7. What is Data Mining, and how does it differ from Data warehousing? [4 marks]
8. The following tables form part of a database held in a relational DBMS. Use the Hotel schema
defined above write SQL queries to answer the following;
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
Where;
Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of bookings and (hotelNo, guestNo, dateFrom) forms the primary key;
Guest contains guest details and guestNo is the primary key.
i. How many hotels are in London?[2 marks]
ii. List the names/ addresses of all guests in London, ordered by name. [2 marks]
iii. What is the average number of bookings for each hotel in August? [2 marks]
iv. What is the total revenue per night from all double rooms? [3 marks]
9. Explain the SQL statement below; [2 marks]
SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) 1
ORDER BY branchNo;
