UNIVERSITY EXAMINATIONS: 2013/2014
ORDINARY EXAMINATION FOR THE BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY
BIT 2104 DATABASE DESIGN AND DEVELOPMENT
DATE: APRIL, 2014 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE
(a) Describe a spatial database and give three areas where spatial databases can be
applied. [5 Marks]
(b) Discuss any three applications of distributed database systems [3 Marks]
(c) Describe mobile databases and give two applications of mobile databases. [4 Marks]
(d) Discuss any three activities performed by database administrators in database
performance. [6 Marks]
(e) Over time the databases grow in size and unnecessarily use disk space. Additionally,
repeated modifications to the database file may result in data corruption. This risk
increases for databases shared by multiple users over a network. These issues may
necessitate database repair. However, before this repair is done, there is need to
consider a number of factors. Describe any four of these factors. [8 Marks]
(f) Countermeasures refer to action, process, device or system that can prevent or
mitigate the effects of threats on a database. In this respect describe the following
countermeasures to database threat.
(i) Recovery [2 Marks]
(ii) Data partitioning [2 Marks]
QUESTION TWO
(a) Outline the major activities performed during conceptual database design for a
relational database. [6 Marks]
(b) In a chemist drug dispensing system, a customer may buy one or several
prescriptions. But each prescription will only be bought by one and only one
customer. The chemist attendant will sell the prescription to several customers but
each customer will be served by one and only one attendant.
(i) Identify the possible entities and relationships in this case. [6 Marks]
(ii) Use chen’s notation to construct a conceptual data model. [8 Marks]
QUESTION THREE
(a) Define object oriented analysis as used in object oriented databases (OODB).
[5 Marks]
(b) Outline any five steps involved in object oriented analysis in object oriented
databases (OODB). [5 Marks]
(c) Using the scenario below, draw a well labeled class diagram.
A patient will normally be described by properties such as patient number, patient
name, address which are kept as strings of text and bill kept as currency. A patient
must be attended to by one or two doctors but a doctor may attend zero or more
patients in a day. The doctor is usually described by attributes such as doctor number
(string), name (string) and department (string).The patient details must be captured
by the system for him/her to be assigned a doctor and bill to be calculated.
(i) Identify possible objects in this case [2Marks]
(ii) For each object identify possible attributes (properties) and operations (methods).
[4 Marks]
(iii)Draw a well labeled object diagram for this case. [4 Marks]
QUESTION FOUR
(a) Describe the first three rules (normal forms) of normalization. [6 Marks]
(b) Outline any two reasons for normalizing a database. [2 Marks]
(c) You are given an un-normalized data base below for Haraka courier services.
Normalize this data base to third normal form.
[8 Marks]
(d) In a distributed database, a table can be fragmented into several tables table1,table2,
table 3…table n in such a way that the actual table could be reconstructed from the
fragments and then the fragments are scattered to different locations. In this regard,
discuss two types of fragmentation. [4 Marks]
QUESTION FIVE
Use the table below to write SQL DML statements to answer the questions that
follow. Assume the table name is SCORE.
(a) Display students whose First names begin with ‘M’ or ‘A’ [4 Marks]
(b) Display students whose surnames end with ‘a’ [3 Marks]
(c) Display students who scored less than A. [3 Marks]
(d) Display standard deviation in the student Exam scores under alias AVERAGE
SCORE. [4 Marks]
(e) Display all the student details in ascending of performance from the lowest to
highest total scores. [4 Marks]
(f) Upgrade all the total scores below 40 by adding 10 to the score. [2 Marks]
