UNIVERSITY EXAMINATIONS: 2014/2015
ORDINARY EXAMINATION FOR THE BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY
BIT 2104 DATABASE DESIGN AND DEVELOPMENT – DISTANCE
LEARNING
DATE: DECEMBER, 2014 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE [30 MARKS]
a) Define index as used in databases and give one limitation of indexes. [2 Marks]
b) Define the term view and explain three uses of database views. [4 Marks]
c) Discuss any four components of a database management system(DBMS). [8 Marks]
d) Compare and contrast between hierarchical and network database models. [4 Marks]
e) State and explain three ways of classifying relationships in a database [3 Marks]
f) Discuss any four concurrency control problems typical in two or more concurrently
executing transactions. [4 Marks]
g) Define the term object oriented database. [1 Mark]
h) Briefly explain any four UML diagrams you can use when modelling an object oriented database system. [4 Marks]
QUESTION TWO [20 MARKS]
You are given the following two tables from a student management. Study the tables
carefully and write appropriate SQL statements to answer the questions that follow:
a) Display all students who take law or business. [2 Marks]
b) Display students who are not pursuing medicine. [2 Marks]
c) Display the average fee paid in arts, law and medicine. [2 Marks]
d) Display the total fee paid by the students grouped by programmes. [2 Marks]
e) Display student’s name, age and programme they are pursuing in ascending order.
[2 Marks]
f) Reduce the fee for law by 4% for all female students. [2 Marks]
g) Insert a new column in programmes table named Duration whose data type is varchar
and the length is 100. [2 Marks]
h) Remove all male students pursuing computing from the database. [2 Marks]
i) Rename the programme computing to computer science. [2 Marks]
j) Display all the female students’ firstname and last names, converted to uppercase and
lower case respectively. [2 Marks]
QUESTION THREE [20 MARKS]
Database concurrency controls ensure that transactions occur in an ordered fashion. The
main function of these controls is to protect transactions issued by different users and/or
applications from the effects of each other. They must preserve the four characteristics of
database transactions: atomicity, isolation, consistency and durability. In this regard:
a) State and explain any four problems that may occur if concurrent transactions with interleaving operations are allowed in an uncontrolled manner. [8 Marks]
b) Discuss any four ways that can be used to control the concurrency control problems
you explained in [a]. [8 Marks]
c) State and explain four major goals of concurrency control techniques discussed in [b].
[4 Marks]
QUESTION FOUR [20 MARKS]
A database program is the heart of a business information system and provides file creation, data entry, update, query and reporting functions. Database programming therefore
can be defined as the process of developing a database application to manage data and
information structured as fields, records and files.
In this respect:
a) State and discuss any six activities involved in database programming. [6 Marks]
b) State and explain four database connection technologies. [8 Marks]
c) Discuss any four best practices that must be adhered to by programmers in database
programming. [4 Marks]
d) Differentiate between a console and graphical user interface applications. [2 Marks]
QUESTION FIVE [20 MARKS]
a) One of the key responsibilities of a database administrator (DBA) is database tuning.
Database tuning can be a daunting task, particularly when working with large-scale
data where even the most minor change can have a dramatic (positive or negative)
impact on performance. In this respect:
i) Discuss the activities involved in database tuning [4 Marks]
ii) Explain any four factors that may motivate the database administrator to
perform database tuning. [8 Marks]
b) A distributed database is a single logical database that is spread physically across
computers in multiple locations that are connected by a data communications network:
i) Discuss four business conditions that encourage the use of distributed
databases. [4 Marks]
ii) Distinguish between heterogeneous and homogenous distributed database
environments. [4 Marks]
