UNIVERSITY EXAMINATIONS: 2014/2015
ORDINARY EXAMINATION FOR THE BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY/BUSINESS INFORMATION
TECHNOLOGY
BIT 2104 DATABASE DESIGN AND DEVELOPMENT DISTANCE
LEARNING
DATE: APRIL, 2015 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE [30 MARKS]
a). Explain the difference between volatile and nonvolatile storage. [2 marks]
b). Explain what kind of redundant data is stored in a log and why the management of the
log is critical to data recovery. [6 Marks]
c). Discuss any four database models and clearly give an example of the application for
each model. [8 Marks]
d). In designing a distributed database, we have to decide where to allocate data. In this
regard, describe the four main strategies that can be used in order to allocate data to
different sites. [4 Marks]
e). There are several forms of transparency that a distributed database management system must offer. Describe what naming transparency is and the main approaches to
achieving it. [4 Marks]
f). You are the database administrator for certain supermarket in town. The supermarket
has plans to open ten new branches across the country before the end of the year. The
management has requested you to help them design and implement a distributed database for them. Describe any three benefits such a database will have to the supermarket. [6 Marks]
QUESTION TWO [20 MARKS]
a) An object oriented database (OODB) is a database designed and implemented to store
data and operations on the data using object oriented database management systems
(OODBMS). In this regard:
i). Differentiate the following terminologies as used in object oriented databases.
• Object identifier
• Encapsulation
• Inheritance [3 Marks]
ii). Describe any TWO advantages of object oriented databases (OODB).
[2 Marks]
b) Haraka enterprises limited has been keeping their records in flat databases. The database below was retrieved from their records.
i) Discuss any three possible anomalies in this database. [3 Marks]
ii) Normalize the above database to 3NF. [6 Marks]
c) Populate the normalized database with correct data as captured in the database above.
[3 Marks]
d) Using the resultant tables in ( c) write SQL statements for each of the following tasks:
i) To increase all employees salary by 5% if their current salary is
less than 200000.
[1 Mark]
ii) To display employees who do not work in Production. [1 Mark]
iii) Display the mean age for all employees. [1 Mark]
QUESTION THREE [20 MARKS]
a) Differentiate between correct, valid and consistent states in a database. [3 Marks]
b) Describe any four possible levels for database recovery. [8 Marks]
c) You have been working as a database administrator for ABC&co a company that has
been maintaining a large database of its clients and products. However, you have confirmed that the database has crashed and there is a risk of losing valuable information
that the company has. Define the term database recovery, hence discuss any four
techniques of database recovery you may use in this case. [9 Marks]
QUESTION FOUR [20 MARKS]
a) A transaction is defined as a unit of program execution that accesses and possibly updates various data in a database. In this regard, discuss the four important properties
of a transaction. [4 Marks]
b) Using an appropriate example, describe the following states of a transaction.
Active
Failed
Committed [6 Marks]
c) Differentiate between relational and multidimensional databases. [10 Marks]
QUESTION FIVE [20 MARKS]
A KCA University has adopted a personal identity card (PID) system to improve security
and to restrict access to certain groups of people (such as students, lecturers, secretaries,
managers etc) and at certain times and dates. A person is issued a PID card as soon as
they become part of the University community (either employed or on a course of study).
Each person belongs to only one group which determines what buildings they can access.
To enter a building, a person (each having a unique personID) must have permission
which is established when their PID card is swiped through a PID card reader outside the
building they wish to enter. A PID card reader is located outside the door of a building
users wish to access. Permission is granted only if their access credentials are successful.
If access is allowed, the captured data is logged, recording the date, personID (from the
PIDcard) and the PIDreaderID.
a) Produce an entity relationship diagram (ERD) complete with cardinalities of the
above scenario using the highlighted keywords as entity types. [4 Marks]
b) State the notation you used. [1 Mark]
c) Describe three ways of classifying relationships in this case. [6 Mark]
d) Derive from your ERD a set of normalized tables populated with sample data
[9 Marks]
