UNIVERSITY EXAMINATIONS: 2018/2019
EXAMINATION FOR THE DIPLOMA IN INFORMATION TECHNOLOGY
DIT205 PRINCIPALS OF DATABASE DESIGN
FULLTIME/PARTTIME
DATE: NOVEMBER, 2018 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One and Any other Two.
QUESTION ONE
a) Define the term database and outline four features why organizations would prefer database systems to
the traditional file management systems [5Marks]
b) Relational data model is the primary data model, which is used widely around the world for data storage
and processing. Define the following relational concepts
i) Attribute [2Marks]
ii) Tuple [2Marks]
iii) Relational schema [2Marks]
c) Outline four data manipulation commands in SQL [4Marks]
d) Define the three mapping cardinalities that define the number of association between two entities
[3Marks]
e) Define data independence with reference to the database system schema and briefly explain the two types
of data independence [5Marks]
f) Briefly explain the four desirable properties of transactions [4Marks]
g) Explain the terms first, second and third normal forms as used in database normalization [3Marks]
QUESTION TWO
a) Describe the three main relational constraints [6Marks]
b) Identify four ways that databases could be used by a university [4Marks]
c) A gardening company allocates equipment to its staff. A member of staff can be allocated up to seven
different types of equipment at a time. Equipment types can be allocated to every member of staff. Draw
the Entity Relationship model for the above scenario [5Marks]
d) Define the term deadlock and explain three general techniques for handling deadlock [5Marks]
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 following data anomalies
i) Deletion anomalies [2Marks]
ii) Update anomalies [2Marks]
iii) Insertion anomalies [2Marks]
c) A data table is required to store details about the customers of a bank. The following details must be
stored. For each of the fields, identify the correct data type that would be used to store the data [4Marks]
QUESTION FOUR
a) Consider the following tables for a tool hire company that operates largely on-line and needs to keep a
record of its customers and orders.
i) Write the SQL statements to create the above tables. [8Marks]
ii) Write an SQL statement to change the name of the tool ‘Hand Electric Drill’ to ‘Electric Hand Drill’.
[4Marks]
iii) Write the SQL query that will list the tool id, tool name and price for all tools of the type
“Home DIY” and draw the results table [4Marks]
b) Briefly explain the difference between simple attributes and composite attributes. Provide ONE (1)
example of a simple attribute and ONE (1) example of a composite attribute. [4Marks]
QUESTION FIVE
a) State five properties of a relation [5Marks]
b) Briefly discuss THREE (3) phases of database design [6Marks]
c) What is meant by the concurrent execution of database transactions in a multiuser system? State three
reasons why concurrency control is needed [5Marks]
d) Explain four functions of database management systems [4Marks]
