TOM MBOYA UNIVERSITY
FACULTY OF BIOLOGY & PHYSICAL SCIENCES
BACHELOR OF INFORMATION TECHNOLOGY
ACADEMIC YEAR 2023/2024
SEMESTER I
EXAMINATION
CIT 215 DATABASE SYSTEMS
TIME: 2 HOURS
INSTRUCTIONS
Answer all questions in SECTION A and any other two questions in SECTION B
SECTION A [30 MARKS]
QUESTION 1
a) Briefly explain any four advantages and four disadvantages of the database approach.
[8 Marks]
b) Name and explain any three components of the database environment. [6 Marks]
c) For each of the following cases connect the entities together with relationships. Show
optionality and describe the relationship against the lines.
i) A LECTURER may be a subject leader for one or more SUBJECTS and a SUBJECT
must have only one LECTURER as subject. A COURSE must contain one or more
SUBJECTS and a SUBJECT must belong to only one COURSE. [5 Marks]
ii) A COURSE must have only one LECTURER as a course leader and a LECTURER
may lead only one COURSE. A STUDENT must have personal tutor and a
LECTURER may be personal tutor to one or more STUDENTS.
[5
Marks]
d)
i) Define the term normalization [2 Marks]
ii) Explain the stage to stage process in the normalization up to third normal
form. [4 Marks]
SECTION B [40 MARKS]
1
QUESTION TWO
(a) Normalize the staff allocation sheet
below to the Third Normal Form.
The sheet indicates which staff is allocated to a project. A staff allocation sheet is drawn up
for each project as the contract is awarded. The No. Of days column relates to the planned
number of days that an employee will spend on the project. A project is assigned a unique
code and each project will be carried out for a single customer. [8 Marks]
Proj. code 3411 Proj. Desc. New Accounts
Cust. No. 3475 Cust. Name British Bakers
Staff No. Name Grade No. of Days
34 Bloggs Sen. Prog. 12
12 Jones Analyst 3
23 Brown Manager 9
Proj. code 2356 Proj. Desc. Betting System
Cust. No. 5234 Cust. Name Bobs Bookies
Staff No. Name Grade No. of Days
34 Bloggs Sen. Prog. 3
12 Jones Analyst 32
45 Williams Teaboy
b. Briefly explain the following types of database failures.
i. Aborted transactions [2 Marks]
ii. Incorrect data [2 Marks]
iii. System failure [2 Marks]
iv. Database destruction [2 Marks]
c. Distinguish between horizontal partitioning and vertical partitioning, giving examples of each
[4 Marks]
QUESTION THREE
(a) Briefly explain the term SQL
[2
Marks]
(b) Given the following data for customers
of KANGO furniture dealers, write SQL statements to perform the following:
CUSTOMER
Cust. ID Cust - name Cust -address Cust -city
001 Heritage 23436 Nairobi
002 Impressions 58382 Kisumu
2
003 Victoria 1236 Mombasa
004 Supreme 8769 Nairobi
005 Home 34564 Nakuru
006 Eastern 68779 Mombasa
007 Contemporary 12234 Nairobi
008 Value 2345 Thika
009 Period 4567 Eldoret
1. Create the customer table [2 Marks]
2. List Customers from Nairobi or Thika city [2 Marks]
3. Delete customers from Kisumu city [2 Marks]
4. Remove the table for the database [2 Marks]
5. Display the customer table in ascending order based on the customer's names.
[2 Marks]
c) Briefly explain the function of the following data definition commands:
i) Drop Table
ii) Alter Table
iii) Create Index
iv) Create View [8 Marks]
QUESTION FOUR
a) Explain the following stages in the life cycle of a typical database system:
i) Database Planning [2 Marks]
ii) Database Analysis [2 Marks]
iii) Database Design [2 Marks]
iv) Database Implementation [2 Marks]
v) Creation and Maintenance [2 Marks]
vi) Growth and Change [2 Marks]
b) Outline any four functions of a data administrator. [4 Marks]
c) Briefly discuss the use of the following data administration tools.
i) Repositories [2 Marks]
ii) CASE tools [2 Marks]
QUESTION FIVE
3
a) Explain the term concurrency control. [2 Marks]
b) Distinguish between the two types of locks that can be implemented by a database
administrator.
[4
Marks]
c) Explain the following terms as used in database security.
i. Authorization rules
ii. Encryption
iii. Authentication schemes
[6 Marks]
d) Briefly explain the following facilities for recovery of a database provided by the DBMS.
i. Backup facilities
ii. Journalizing facilities
iii. Checkpoint facility
iv. Recovery manager [8 Marks]
