UNIVERSITY EXAMINATIONS: 2016/2017
EXAMINATION FOR THE DIPLOMA IN BUSINESS INFORMATION
TECHNOLOGY & DIPLOMA IN INFORMATION TECHNOLOGY
DBIT301 DIT 205 PRINCIPLES OF DATABASE DESIGN
DATE: AUGUST, 2017 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE [30 MARKS] – COMPULSORY
(a) Distinguish between the following computer file concepts:-
(i) A filed & record
(ii) A file & database
(iii)Logical & physical database design [6 Marks]
(b) Wanguru Company Limited intends to introduce a new computerized system in order the
management of its employee’s records. Outline five good features you would want to see in the
implemented Wanguru database system. [5 Marks]
(c) Outline three advantages and two disadvantages of database approach over traditional file
approach [5 Marks]
(d)With the aid of diagrams, describe the following database models:-
(i) Hierarchical model [4 Marks]
(ii) Relational model [4 Marks]
(e)Define the following terms: – [6 Marks]
(i) Entity
(ii) DBMS
(iii)Database normalization
(iv)Primary key
(v) Relationship
(vi)Foreign key
QUESTION TWO [20 MARKS]
(a) Write SQL/MYSQL statement that could be used to arrange records in a table named
LibBooks in descending order by a column named BookID [3 Marks]
(b) The following is a registration form used by students at KCA University for course
registration:
Required:-Using the information provided on the form, normalize the relation to the 3rd Normal
Form [10 Marks]
(c) Explain two limitations of computerized database systems. [4 Marks]
(d) Outline three applications of Database Management systems. [3 Marks]
QUESTION THREE [20 MARKS]
(a)Study the table1 provided below and answer the questions that follow:-
Publishers table
Table1
Required:-Write DDL MYSQL/ SQL statements to create the table structure represented above
[3 Marks]
(b)Assuming that the table has some populated records, write SQL/ MYSQL command that
performs the following:-
(i) Displays all the records from the publishers table [3 Marks]
(ii) Returns the total number of records from the table [2 Marks]
(iii)Delete all the records from the publishers table [3 Marks]
(c) Outline five key responsibilities of Database Administrators (DBA) [5 Marks]
(d) Outline two advantages and two disadvantages of Entity Relationship Diagrams (ERD)
[4 Marks]
QUESTION FOUR [20 MARKS]
(a) Explain four objectives of database normalization [8 Marks]
(b) A local hospital consists of many wards, each of which is assigned many patients. Each
patient is assigned to one doctor, who has overall responsibility for the patients in his or her care.
Other doctors are assigned on an advisory basis. The doctor responsible for that patient
prescribes each patient drug. Each nurse is assigned to a ward and nurses all patients in the ward,
though is given special responsibility for some patients. One of the doctors is attached to each
ward as an overall medical advisor.
Required: Draw an Entity Relation Diagram to represent the above information [8 Marks]
(c) Outline the steps of designing a database system [4 Marks]
QUESTION FIVE [20 MARKS]
(a) Explain three security controls that can be used to safeguard a computerized database system
[6 Marks]
(b) Outline four functions of a DBMS [4 Marks]
(c) Explain four challenges faced by organizations while implementing database systems.
[4 Marks]
(d) List and briefly describe any three components of database system environment.
[6 Marks]
