UNIVERSITY EXAMINATIONS: 2017/2018
EXAMINATION FOR DEGREE OF BACHELOR IN BUSINESS
INFORMATION TECHNOLOGY
BBIT304 ADVANCED DATABASE SYSTEMS
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) Database recovery is the process of restoring the database to a correct state in the event of a
failure. In this regard, discuss the following database recovery techniques.
i. Shadow paging recovery
ii. Deferred update recovery
iii. Immediate update recovery
6 Marks
b) Consider a database schema below, then normalize to third normal form and draw the
resultant ERD.
StudentID->(StudentID, name,Gender, grades)
CourseID->(CourseID, CourseTitle,LecturerName)
6 Marks
c) 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.
d) 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
e) 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
QUESTION TWO [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) A transaction is a series of operations carried out by a single user or application program that
must be treated as a logical unit of work. Describe the five transaction states.
5 Marks
d) Differentiate between vertical fragmentation and horizontal fragmentations as used in
distributed databases.
2 Marks
QUESTION THREE [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 FOUR [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) A distributed DBMS typically uses three steps to develop query processing plan. Discuss
these three steps.
6 Marks
QUESTION FIVE [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
