UNIVERSITY EXAMINATIONS: 2011/2012
YEAR 2 EXAMINATION FOR THE BACHELOR OF SCIENCE IN
INFORMATION TECHNOLOGY
BIT 2104 DATABASE SYSTEMS DESIGN AND DEVELOPMENT
DATE: APRIL 2012 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One and Any other Two Questions
QUESTION ONE (30 MARKS)
a) State the use of the following clauses in a typical SQL statement
i) REFERENCES (2Marks)
ii)ROLLBACK (2Marks)
iii) DISTINCT (2Marks)
iv) ALTER TABLE (2Marks)
v) COMMIT (2Marks)
b) Outline four functions of a database management system (4Marks)
c) Define the term ‘database transaction’ and identify four properties exhibited by database
transactions (10Marks)
d) Describe three levels of the database architecture (6Marks)
QUESTION TWO (20 MARKS)
a) Define the following terms as used in the control of concurrent database transactions;
i) Shared lock
ii) Timestamp
iii) Deadlock (6Marks)
b) Consider the CUSTOMER relation with fields as follows;
CustomerId Names Address City Age
Write SQL statements to perform the following transactions;
i) Return details of all customers aged below 30 years who reside in Nairobi city (3Marks)
ii)Insert the data (5001, Mary Mutuku, Box 56 Machakos, Machakos and 34 years) into the
respective columns (3Marks)
iii) Amend the city data from ‘Machakos’ to ‘Mombasa’ for a Customer Id ‘5001’ (3Marks)
Hint: Assume that CustomerId and Age are number fields
iv) Empty the CUSTOMER relation of all data (2Marks)
c) List three functions of the data dictionary (3Marks)
QUESTION THREE (20 MARKS)
a) Identify two properties that a primary key must possess; (2Marks)
b) Explain the benefits of the object-oriented data modeling (6Marks)
c) A popular method of implementing concurrency control in a multi-user database environment is
through the use of data locking mechanisms.
i) Define the concept of data locking (2Marks)
ii) Identify and describe five data locking levels (10Marks)
QUESTION FOUR (20 MARKS)
a) Define the term ‘Query by example’ (QBE) (2Marks)
b) The following diagram illustrates a QBE interface
LECTURERS
staffNo staffName status deptName salary
p. p. Computer Studies >50,000
i) State the result of the following above execution (4Marks)
ii)Write a corresponding SQL statement that would yield the same result (4Marks)
c) Define the following attributes in relational database design;
i) Multi-valued attribute (2Marks)
ii) Composite attribute (2Marks)
iii) Derived attribute (2Marks)
d) Identify the steps during database design that will result in an entity-relationship diagram
(4Marks)
QUESTION FIVE (20 MARKS)
a) i) Define the term ‘Normalization’ with respect to logical data design (2Marks)
ii) Describe any three anomalies that are associated with bad database design (6Marks)
b) Below is a table illustrating the renting information for customers of DreamHome real estate
company;
CUSTOMER RENTAL TABLE
i) The table above violates the rule of the first normal form (1NF). Explain how and identify a
possible consequence of this inconsistency (4Marks)
ii) With explanations organize the data in the table above into the 1NF, then into 2NF (8Marks)
