© 2023 TVET CDACC

061306T4CPM
COMPUTER PROGRAMMING LEVEL 6
IT/OS/CP/CR/04/6/A
DEMONSTRATE DATABASE DESIGN AND DEVELOPMENT
NOV / DEC 2023

TVET CURRICULUM DEVELOPMENT, ASSESSMENT AND CERTIFICATION

COUNCIL (TVET CDACC)

WRITTEN ASSESSMENT
TIME: 3 Hours

INSTRUCTIONS TO CANDIDATE:
1. The paper consists of two sections: A and B

2. Answer ALL questions in Section A and any Three from section B

3. Marks for each question are indicated in the brackets

4. A separate answer booklet will be provided

5. Do not write on the question paper

Candidates should answer the questions in English

This paper consists of 6 printed pages

Candidates should check the question paper to ascertain that all pages are printed as
indicated and that no questions are missing.

Page 1 of 6
 © 2023 TVET CDACC

SECTION A: (40 Marks)

(Answer ALL questions in this section)

1) List TWO advantages of using database systems (2 Marks)

2) Explain TWO components of a relational database (4 Marks)

3) Differentiate between COUNT DISTINCT and COUNT (*) as used in Structured Query
language (2 Marks)
4) List FOUR characteristics of a primary key in a database (4 Marks)

5) Explain each of the following approaches used in the design of a database

(i) Top Down

(ii) Bottom Up (4 Marks)
6) Differentiate between TRUNCATE and DROP commands as used in SQL (4 Marks)

7) List FOUR functions of views as used in Structured Query Language (4 Marks)

8) State FOUR anomalies eliminated by normalization (4 Marks)

9) Explain a relational database management system (2 Marks)

10) Distinguish between CASCADE and RESTRICT commands as used in database view

(4 Marks)
11) The following are SQL statements. Use them to identify the errors in the statements
(6 Marks)
Create Table (Staffno varchar (5), lname (varchar (15), salary
decimal ((7,2);
INSERT TO STAFF(‘ST678’,’Lenny’,”15700”);
SELECT staffno, lastname, salary
From staff
Where salary > 10000;

Page 2 of 6
 © 2023 TVET CDACC

SECTION B: (60 Marks)

(Answer any THREE questions in this section)

12. a) Explain each of the following terms as used in database (4 Marks)

i. Table
ii. View
b) Table 1 shows a database named hostelworkers. Use it to answer the question that follows
(6 Marks)

ID Name HostelName Age Salary YearsofService

20 Patrick Nakuru 44 32000 7
21 Dolly Elementatita 54 48000 15
45 Dennis Nairobi 34 20000 5
46 Patricia Eldoret 30 19000 3
47 Christine Kisumu 38 20500 6
63 Lucy Mombasa 43 45000 9
67 Susan Nyeri 36 26000 7
Table 1
Write an SQL statement that would:
(i) Sort the hostel table by the name and salary in descending order
(ii) Find the average for all employees
(iii)Increase salary for all employees by 15%
c) Explain the term identity column as used in database (2 Marks)
d) A database designer is in the process of designing a database. Explain FOUR phases that
she should consider during design (8 Marks)

13. a) With the aid of examples in each case, differentiate between Data Definition Language
(DLL) and Data Manipulation Language (DML) (6 Marks)
b) Explain the function of each of the following SQL Statements (6 Marks)
(i) FROM
(ii) ORDER BY
(iii) HAVING

Page 3 of 6
 © 2023 TVET CDACC

c) The following are tables created in a database. Use them to answer the questions that
follow
Table A

ROLL_NO NAME ADDRESS PHONE AGE

1 HARSH UGANDA XXXXXXXXXX 18
2 PRATIK KENYA XXXXXXXXXX 19
3 RIYANKA SUDAN XXXXXXXXXX 20
4 DEEP NIGERIA XXXXXXXXXX 18
5 SAPTARHI DRC XXXXXXXXXX 19
6 DHANRAJ EGYPT XXXXXXXXXX 20
7 ROHIT ZAMBIA XXXXXXXXXX 18
8 NIRAJ GHANA XXXXXXXXXX 19

Table B

COURSE_ID ROLL_NO
1 1
2 2
3 3
1 4
4 9
5 10
4 11

State the fields that would be generated when each of the following relational algebra operations
are performed. (8 Marks)
i. Inner join A and B
ii. Right outer join A and B
iii. Left outer join A and B
iv. Full outer join A and B

Page 4 of 6
 © 2023 TVET CDACC

14. a) Differentiate between the following cardinal relationships (6 Marks)

i. Many to One Relationship
ii. Many to many Relationship
iii. One to many Relationship
b) Study the scenario below:
A salesperson may manage many other salespeople. A salesperson is managed by only
one salespeople. A salesperson can be an agent for many customers. A customer is
managed by one salespeople. A customer can place many orders. An order can be placed
by one customer. An order lists many inventory items. An inventory item may be listed
on many orders. An inventory item is assembled from many parts. A part may be
assembled into many inventory items. Many employees assemble an inventory item
from many parts. A supplier supplies many parts. A part may be supplied by many
suppliers. Draw an ER diagram to represent the narrative. (7 Marks)
c) A learning institution maintains details of its lecturers who are teaching various units as
follows:
Lecturer No, Lecturer Name, grade, code, department name, subject code,
subject name, subject level.
Each lecturer may teach many subjects but may not belong to more than one department.
Normalize this data to 3rd Normal form. (7 Marks)

15 a) Table 2 shows the design of a table named Asset. Use it to answer the questions that follow.
Field Description
Asset_ID This number identifies the Asset. It is the Primary Key

Asset_Name Identifies the name of the Asset. The field should not allow null values.
Should hold not more than 20 characters.
Asset_Description Gives a brief description of the function of the Asset. Should hold not
more than 60 characters.
Year_of_Purchase When the Asset was procured. The field should not allow null values
Table 2

Page 5 of 6
 © 2023 TVET CDACC

Write and SQL statement that would

(i) Create the table (4 Marks)
(ii) Add the following column to the table Asset (2 Marks)
Field Description
Asset_Category This field holds the category the asset belongs to.
Should not be null and hold more than 55 characters

(iii) Add the following values to the table (4 Marks)

Asset_ID Asset_Name Asset_Description Year_of_Purchase Asset_Category

10 Mouse A mouse is a device 2017 Computer

used as an accessory accessory
in a computer

b) State FOUR differences between primary key and foreign key as used in database
(4 Marks)
c) Discuss the following database models (6 Marks)
i. Relational Model
ii. Hierarchical model
iii. Network model

THIS IS THE LAST PRINTED PAGE

Page 6 of 6