UNIVERSITY EXAMINATIONS: 2011/2012
SECOND YEAR EXAMINATION FOR THE DEGREE IN BACHELOR
OF SCIENCE IN INFORMATION TECHNOLOGY
BIT 2104 DATABASE SYSTEMS DESIGN AND DEVELOPMENT
DATE:AUGUST, 2012 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO Questions
QUESTION ONE
a) Define the following terms; [4 Marks]
i. Foreign Key
ii.Candidate key
b) Briefly explain Three component of a database environment; [6 Marks]
c) How would you describe a condition in which one attribute is dependent on another
attribute and neither attribute is part of the primary key? Reduction to which normal
form is designed to eliminate such dependencies? [ 4 Marks]
d) With appropriate examples discuss at least three benefits three limitations of usage of
database systems [6 Marks]
e) Describe the different forms of security which might be used in a PC system database
to provide back-up facilities or prevent unauthorized access to the computer files.
[6 Marks]
f) Differentiate between data analysis and functional analysis
[4 Marks]
QUESTION TWO
(a) Give a brief explanation of the following E-R concepts:
[4 Marks]
i. Entity
ii. Attribute
iii. Cardinality
iv. Relationship
(b) Come up with at least three attributes for each of the entities below and choose one of
your attributes as the identifier for the entities,
i. Customer [3 Marks]
ii. Organization [3 Marks]
(c) Identify any three entities in a typical lecturer room and draw a complete E-R Diagram for
these entities using the UML notation [10 Marks]
QUESTION THREE
(a) (i) State any three data description and three data manipulation commands as used in
SQL [6 Marks]
(ii) Describe the components which constitute an SQL Environment [6 Marks]
(b) Company X has an employee entity with the following attributes: name, PfNo, DoB,
deptNo. Where name refers to the name of the employee, PfNo refers to the personal file
number of that employee, DoB is the date of birth of the employee and deptNo is the
number of the department where the employee belongs.
(i) Identify metadata and a primary key for the employee entity. [2 Marks]
(ii) Issue SQL commands for creating a database for the company and a table containing
information on the company’s employees. Use your own field sizes. [6 Marks]
QUESTION FOUR
a) i) What is Normalization? [ 2 Marks]
b) (ii) Katumba is a Database manager, his customer Namwamba wants a database that
is very fast, advice katumba on what he should do. [6 Marks]
c) Consider the case of a simple video library. Each video has a title, director, and serial
number. Customers have a name, address, and membership number. Assume only one
copy of each video exists in the library. We are given:
i) What normal form is this?
Justify your reasoning [6 Marks]
ii) Convert from 2NF to 3NF. [6 Marks]
QUESTION FIVE
a) Discuss the advantages and disadvantages of a database system over traditional file
system. [10 Marks]
b) Given relations Customers, and PhoneNumbers below:
i. Write SQL statement that will retrieve fields contactid and lastname from
customers and phonetype and number from PhoneNumbers where contactid is
common between the two relations [ 4 Marks]
ii. Write SQL statement to create table PhoneNumbers. Remember to use
appropriate data types for the fields [4 Marks]
iii. What is the difference between composite and key field [2 Marks]
