DIT 205
PRINCIPLES OF DATABASE DESIGN
QUESTION 1 (COMPULSORY)
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 2
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 3
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]
QUESTION 4
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.
customer_number customer_name customer_phone creditworthy
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 5
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]
