UNIVERSITY EXAMINATIONS: 2017/2018
EXAMINATION FOR DEGREE OF BAC/Bsc IT/Bsc ICT
BIT2104 DATABASE DESIGN AND DEVELOPMENT
MODE: FULL TIME/PART TIME/DISTANCE LEARNING
ORDINARY EXAMINATIONS
DATE: APRIL, 2018 DURATION: 2 HOURS
INSTRUCTIONS: Answer question ONE and any other TWO questions
QUESTION ONE [30 MARKS]
a) The process of managing simultaneous execution of transactions in a shared database, to
ensure the serializability of transactions, is known as concurrency control. Discuss two
concurrency control techniques in database management systems.
6 Marks.
b) The database architecture is the set of specifications, rules, and processes that dictate how
data is stored in a database and how data is accessed by components of a system. Discuss the
different levels of database architecture according to ANSI PARC.
6 Marks
c) One of the ways in which concurrently executing transactions can interfere with one answer
and compromise the integrity and consistency of the database is the inconsistency analysis
problem. Discuss any other three problems associated with concurrently executing
transactions.
6 Marks
d) Describe TWO responsibilities of a database administrator.
2 Marks
e) Describe TWO the advantages and TWO disadvantages of a database approach.
4 Marks
f) Consider a database schema below, then normalize to third normal form and draw the
resultant ERD.
EMPLOYEE->(EmpID, name,Gender, Allowances,Project)
DEPARTMENT->(DeptID, Departmentname,supervisor)
6 Marks
QUESTION TWO [20 MARKS]
a) Study the case below and construct an ERD complete with cardinalities.
An organization makes many models of cars, where a model is characterized by a name
and an engine size. Each model is made up from many parts and each part may be used
in the manufacture of more than one model. Each part has a description and an id code.
Each model of car is produced at just one of the firm’s factories, which are in different
cities. Each city has one factory. A factory produces many models of car and many types
of part although each type of part is produced at one factory only.
8 Marks
b) Study the data dictionary below and answer the questions that follow.
i. Write an SQL DDL statement to create CUSTOMER table.
3 Marks
ii. Write SQL DDL statement to create PRODUCT table
3 Marks
iii. Write SQL DML statements to increase the price of the product by 20%
3 Marks
iv. Write a SQL DML statement to display the total price for all products bought by a
customer
3 Marks
QUESTION THREE [20 MARKS]
a) A trigger is a stored program executed automatically to respond to a specific event e.g.,
insert, update or delete occurred in a table. Discuss three advantages and three disadvantages
of a database trigger.
6 Marks
b) A stored procedure is segment of declarative SQL statement stored in the database
catalog/server that can perform some tasks when called. Given a database schema:
PRODUCT->(ProductID, Description, Price, Quantity). Create a stored procedure to increase
price by 15% whenever quantity is less than 100.
8 Marks
c) Discuss any THREE database security threats and possible counter measures.
6 Marks
QUESTION FOUR [20 MARKS]
a) List and explain FOUR different types of JOIN clauses supported in ANSI-standard SQL.
8 Marks
b) A view can be defined as a stored query that when invoked produce a result set. Explain
TWO advantages and TWO disadvantages of views in a database.
4 Marks
c) Given the table schema below, write an SQL DDL statement that creates a view to display
suppliers located in Nairobi.
SUPPLIER->(SupplierID, suppliername, city)
6 Marks
d) Describe any TWO activities that may improve the database performance.
2 Marks
QUESTION FIVE [20 MARKS]
a) Discuss the following types of advanced database applications
i. Spatial databases
ii. Mobile databases
iii. XML databases
9 Marks
b) A privilege can be defined as a permission to access a named object in a prescribed manner.
In this regard, state and explain two types of database privileges.
4 Marks
c) Query processing is the retrieval of information from a database according to a set of
retrieval criteria while query optimization is the overall process of choosing the most
efficient means of executing a SQL statement. From this background, discuss the steps
involved in query processing and optimization.
5 Marks
d) Differentiate between vertical fragmentation and horizontal fragmentations as used in
distributed databases.
2 Marks
