UNIVERSITY EXAMINATIONS: 2018/2019
EXAMINATION FOR THE DEGREE OF BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY/APPLIED COMPUTING
BIT 2104: DATABASE DESIGN AND DEVELOPMENT
BAC 1301: DATABASE DESIGN & DEVELOPMENT
FULL TIME/PART TIME/DISTCNCE LEARNING
DATE: DECEMBER, 2018 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE [30 MARKS]
a) What factors can be used to measure database performance
6 Marks
b) Discuss the important role played by users in the process of database design.
6 Marks
c) Describe the main phases involved in database design.
6 Marks
d) Describe the characteristics of a table in third normal form (3NF).
3 Marks
e) How do the contents of a users’ requirements specification differ from a systems
specification?
5 Marks
f) Compare and contrast the main tasks carried out by the DA (data administrator) and
DBA(database administrator).
4 Marks
QUESTION TWO [20 MARKS]
a) What are the main activities associated with operational maintenance stage?
4 Marks
b) Explain the purpose of testing the database system.
4 Marks
c) Describe the purpose of the data conversion and loading stage.
4 Marks
d) Describe the potential benefits of developing a prototype database system.
4 Marks
e) Discuss the two main activities associated with application design.
4 Marks
QUESTION THREE [20 MARKS]
a) Explain briefly what is meant by database security and database integrity
within a relational database system and highlight the relationship between
them.
5 Marks
b) Explain briefly the differences between Entity Relationship modelling and
Normalisation.
5 Marks
c) You are given an enrolment database with two tables- PROGRAMME and
STUDENT. Write SQL statement to accomplish the tasks that follow.
i. Display full name of the student
2 Marks
ii. Display all students taking applied computing
2 Marks
iii. Increase the fee by 3% if the student is taking Bsc. software development.
2 Marks
iv. Display the total fee(cost) by department.
2 Marks
v. Remove the student Henry Mutua from the student table.
2 Marks
QUESTION FOUR [20 MARKS]
A theme park wishes to create a database about its activities. There are a number of areas
in the park with each area having several rides. Each rides has one supervisor who is in
charge of that ride alone (unless he is seconded to a development project for a period of
time). A ride is run by several staff, and staff may run different rides on different days,
although a staff member belongs to only one area. Each ride is maintained by one or more
maintenance staff who may maintain several rides. Area details include Area# (unique) and
Area Name. Ride details include Ride# (unique), Ride Name, Ride Type and Maximum
Capacity. Supervisor details include Supervisor Code (unique), Supervisor Name, Salary,
Phone#. Staff details include Staff# (unique), Staff Name, Grade, Salary and Date allocated
to on a particular day. Maintenance Staff details include Maintenance Code, Name, Shift,
Salary, Time spent maintaining a particular ride.
(a) State the entity types with their identifiers and all details of relationships (name,
degree and relevant membership classes).
10 Marks
(b) Draw an Entity Relationship diagram and use the relational mapping rules to
create a linked relational schema.
7 Marks
(c) Explain what is meant by an optional membership class and give an example.
3 Marks
QUESTION FIVE [20 MARKS]
The form below shows a representative form for a University Timetabling database.
ModuleCode: COM171J1 ModuleName: Database Development
Where Class# contains unique values and Type values are L – lecture,
T – tutorial and P – practical.
(a) State the first normal form (1NF), second normal form (2NF) and third normal form
(3NF) rules.
6 Marks
(b) Represent the above form as an un-normalised relational scheme and apply the rules of
normalisation to normalise up to third normal form (3NF)
14 Marks
