INSTRUCTIONS TO CANDIDATES
Maximum marks for each question are indicated in brackets ( ). This paper consists of TWO sections: A and B. Answer questions as per instructions in each section.
You are provided with a separate answer booklet. This paper consists of FIVE (5) printed pages
Candidates should check the question paper to ascertain that all pages are printed as Indicated and that no questions are missing
SECTION A (40 MARKS)
Answer all the questions in this section
1. Define the following terms
i) Database schema (1 mark)
ii) Database Instance (1 mark)
iii) Database Management System (1 mark)
iv) Relation (1 mark)
2. Explain FOUR ways in how databases can be classified based on number of sites over which Database is Distributed (4marks)
3. Elaborate any FOUR advantages that database management systems offer in order to overcome the disadvantages of file systems in data storage (4marks)
4. Differentiate between Data Definition Language versus a Data Manipulation Language and use at least one example of each to illustrate your explanation. (4 Marks)
5. Explain the following transaction concept.
i. Grant (1 mark)
ii. Revoke (1 mark)
iii. Commit (1 mark)
iv. Rollback (1 mark)
(4marks)
6. Explain FOUR benefits of normalizing database containing relations (4marks)
7. Describe any FOUR constraints used in SQL (4marks)
8. Explain FOUR techniques that are used to perform Database Testing. (4marks)
9. Describe the following types of keys as used in database management system
i. Primary Key
ii. Foreign Key
iii. Candidate Key
iv. Super Key (4marks)
10. Distinguish between a weak entity and a derived entity as used in Entity Relationship diagram (4marks)
SECTION B (60 MARKS)
(Answer any THREE (3) questions in this section).
QUESTION 11 (20mks)
i. A process in a transaction is said to successfully terminate after attaining “acidity”.
Describe the ACID properties of a transaction illustrating with examples for each state (8marks)
ii. Study the following scenario then attempt the question part that follow:-
Mombasa port Authority is a company that maintains first-hand information on the processing and current whereabouts of each shipped goods. It has a computerized system for shipped goods. The shipped goods are recorded into the system by considering their item number which is unique, weight, dimensions, insurance amount, destination, and final delivery date. The shipped goods are keyed in into the computerized system in a single retail center. The retail centers are categorized by their type, uniqueID, and address. Shipped goods are transported to their main destination by the use of Mombasa
transportation events (i.e., flights, truck deliveries). The transportation events are characterized by a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute. Create an Entity Relationship diagram that captures this information about the Mombasa Computerized System. Indicate identifiers and cardinality constraints. (12marks)
QUESTION 12 (20mks)
a) Outline six features of a database management system. (6 marks)
i. Distinguish between logical database designer and a physical database designer. (4 marks)
ii. Table 2 shows details of furniture stored in a database. Use it to answer the questions that follow.
b) Write SQL statements that would;
(i) Extract details of items whose item name starts with letter c; (2marks)
(ii) Determine the cost of each item and store them in a field named total cost. (2marks)
(iii)extract all the details whose item price is greater than 15000 and the item name ends with s; (2marks)
(iv)Sort items according to item code in ascending order. (2marks)
(v) Delete the item whose item code if f0014 from the table; (2marks)
QUESTION 13 (20marks)
i. Securing a database aims to achieve the following objectives
– Data Confidentiality
– Data Integrity
– Availability
Briefly describe what is meant by each of the above objectives. In each name one security controls that can be applied in Database (9marks)
ii. Discuss THREE methods of backups used in database (6marks)
iii. Discuss FIVE Properties of a Primary Key (5marks)
QUESTION 14 (20marks)
i. Describe three cardinalities used in database (6marks)
ii. With the aid of a well labelled diagram, discuss the three levels of abstraction in a database (8marks)
iii. Discuss any three major components of a database (6marks