UNIVERSITY EXAMINATIONS: 2010/2011
SECOND YEAR EXAMINATION FOR THE DEGREE OF BACHELOR OF
SCIENCE IN INFORMATION TECHNOLOGY
BIT 2104: DATABASE SYSTEMS DESIGN AND DEVELOPMENT
DATE: AUGUST 2011 TIME: 2 HOURS
INSTRUCTIONS: Answer question ONE and any other TWO questions
Question One (30 Marks)
a) Define the term ‘database manager’ and list three functions of this component (4 Marks)
b) Define the following types or relational attributes
i) Derived attribute (2 Marks)
ii) Multi-valued attribute (2 Marks)
iii) Composite attribute (2 Marks)
c) List five properties of a typical relation (5 Marks)
d) Identify four strengths of the object-oriented data model (4 Marks)
e) i) What is the effect of integrity design during the development of a database? (3 Marks)
ii) List five integrity constraints that can be built into a database (5 Marks)
f) Discuss the benefits of providing views in a database (3 Marks)
Question Two (20 Marks)
a) Identify the strengths of the relational data model (5 Marks)
b) Consider a “Library Management System” which keeps the following tables:
Book (isbn-no, book-title, author, publisher, edition, year-of-copyright)
Members (m-id, m-name, m-address, m-phone).
Borrowing (issue-id, m-id, isbn-no, expected-date-of-return)
Return (return-id, issue-id, expected-date-of-return, actual-date-of-return)
The actual-date-of-return is kept blank for the books that have not been returned. Write the following
SQL queries on the tables:
i) Find the m-id and m-name of the members who have got borrowed books. (3 Marks)
ii)List the number of copies that exist for the book ‘Database Systems’ in the library. Call this
output ‘Quantity’ (3 Marks)
iii) Find the details of the student who has been issued with the book of isbn “DB9088” (3 Marks)
iv) Find the borrowing details of books that are expected to be returned today. (3 Marks)
v)Delete the details of all returns (3 Marks)
Question Three (20 Marks)
a) With examples, explain the purpose of primary and foreign indices (4 Marks)
b) Consider the Relation PATIENT_APPOINTMENTS which is in 1NF
i) With reason, identify the key for PATIENT_APPOINTMENTS (2 Marks)
ii)Assuming this is the only relation in the database, use a suitable example to discuss the insertion,
Deletion and Update anomalies that may affect the data (6 Marks)
iii) Decompose the relation into 2NF and further to 3NF, explaining how this is done (8 Marks)
Question Four (20 Marks)
a) i) Distinguish between a DDBMS and a centralized DBMS (4 Marks)
ii) Describe two components of a distributed database environment (2 Marks)
b) Describe ‘fragmentation’ in the context of a DDBMS and explain two ways of achieving this
(6 Marks)
c) i) Identify the advantages of having a three-tier database architecture (3 Marks)
ii) How are these tiers related to data independence? Explain using an example (5 Marks)
Question Five (20 Marks)
a) i) Discuss the concept of locks with reference to concurrent transactions (2 Marks)
ii) Using a transaction example, discuss two types of locks (4 Marks)
b) Identify four properties of transactions (4 Marks)
c) i) List activities that can be conducted during conceptual data modeling (5 Marks)
ii) Identify five components of a database environment (5 Marks)
