UNIVERSITY EXAMINATIONS: 2018/2019
EXAMINATION FOR THE DIPLOMA IN INFORMATION TECHNOLOGY
DIT205 PRINCIPLES OF DATABASE DESIGN
FULLTIME/PARTITIME
DATE: AUGUST, 2019 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE
a) State four properties of relations [4Marks]
b) Explain the following database relational concepts
i) Super key [2Marks]
ii) Candidate key [2Marks]
iii) Tuple [2Marks]
c) State four ways that a database could be used by KCA university [4Marks]
d) An author may have written one or more books. A book may have one or more authors. Draw the Entity
Relationship Diagram (ERD) for the above scenario and show how the entities are related
[4Marks]
e) With the aid of an example explain the following mapping cardinalities
i) Many to one
ii) One to many
iii) One to one
iv) Many to one [4Marks]
f) Explain four functions of database management systems [4Marks]
g) State any four types of attributes used when constructing relational databases [4Marks]
QUESTION TWO
a) Normalization is a database design technique which organizes tables in a manner that reduces
redundancy and dependency of data. Explain the following types of dependencies
i) Full Dependency [2Marks]
ii) Partial Dependency [2Marks]
iii) Transitive Dependency [2Marks]
b) Normalize the data in the table below to its third normal form [10Marks]
STUDNO – Student number
STUDNAME – Student name
STUDCSECODE – Student course code
STUDCSENAME – Student course name
LECTID – Lecturer ID
LECTNAME – Lecturer Name
c) Give the meaning of the following states of a database transaction
i) Active state
ii) Partially committed state
iii) Failed state
iv) Terminated state [4Marks]
QUESTION THREE
a) With the aid of a diagram describe the three-schema database architecture that is used to separate the user
applications from the physical database. [10Marks]
b) Briefly explain the three data anomalies that can be experienced when manipulating data
[6Marks]
c) State the four potential problems of concurrency data manipulation in databases [4Marks]
QUESTION FOUR
a) Describe the three main relational constraints [6Marks]
b) Define the term database schema and explain two types of database schema [6Marks]
c) The Customer table below is part of a data table.
i) Write a query that would output the names and phone numbers of all customers who are not
creditworthy. Give the results table [5Marks]
ii) State the output from the following query if applied to the table.
SELECT customer_number, customer_name
FROM Customer
WHERE customer_number<30 AND creditworthy [3Marks]
QUESTION FIVE
a) Briefly explain the four desirable properties of transactions [8Marks]
b) Briefly describe the phases of database design [6Marks]
c) Define the term deadlock and explain three general techniques for handling deadlock [6Marks]
