UNIVERSITY EXAMINATIONS: 2018/2019
EXAMINATION FOR THE DIPLOMA IN BUSINESS INFORMATION
TECHNOLOGY
DBIT301 DATABASE MANAGEMENT SYSTEMS
FULLTIME/PARTTIME
DATE: NOVEMBER, 2018 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE [30 MARKS]
(a) James, a DBA working for a software development company was required to design a
database management system.
Required:
(i) Outline two principle requirements he would consider when selecting the storage
media for the proposed database system [2 Marks]
(ii) Explain five benefits the proposed database system will deliver to the company
[5 Marks]
(iii)List and explain three challenges James is likely to encounter while implementing
the proposed database system [3 Marks]
(b) List and explain four DML commands supported by SQL/ MYSQL [4 Marks]
(c) Explain the following terms as used in database systems:- [4 Marks]
(i) Entity
(ii) Primary key
(iii)Relation
(iv)Foreign key
(d) Outline three advantages of Entity Relationship Diagrams [3 Marks]
(e) Table 1 shows details of examiners in a particular marking center. Use it to answer the
questions that follow:
Table Name: Examiners
Required:
Write SQL/ MYSQL statements that would be able to carry out each of the following task:
(i) Extract all Names and DateOfArrival of all examiners [3 Marks]
(ii) Extract all records for female examiners [3 Marks]
(iii)Delete all the records from examiners table [3 Marks]
QUESTION TWO [20 MARKS]
(a) Distinguish between alter and update table commands as applied in SQL [4 Marks]
(b) (i) Highlight three threats to a data/ information stored in a database system [3 Marks]
(ii) For each threats identified in 2(b) above recommend three measures that could be used
to protect the data / information stored in the database system [3 Marks]
(c) Using a simple illustration, explain the following components of a database system:-
(i) Field [2 Marks]
(ii) Record [2 Marks]
(iii)File [2 Marks]
(d) Outline four duties of a Database Administrator [4 Marks]
QUESTION THREE [20 MARKS]
(a) Explain three major limitations of traditional file based systems over database systems
[4 Marks]
(b) Use the information provided in the scenario below and answer the questions that follow:-
Mickmart company prides itself on having up-to-date information on the processing and current
location of each shipped item. To do this, Mickmart Company relies on a company-wide
information system. Shipped items are the heart of the Mickmart Company product tracking
information system. Shipped items can be characterized by item number (unique), weight,
dimensions, insurance amount, destination, and final delivery date. Shipped items are received
into the Mickmart Company system at a single retail center. Retail centers are characterized by
their type, uniqueID, and address. Shipped items make their way to their destination via one or
more standard Mickmart Company transportation events (i.e., flights, truck deliveries). These
transportation events are characterized by a unique scheduleNumber, a type (e.g, flight, truck),
and a deliveryRoute.
Required: using Chen notations, Create an Entity Relationship that depicts the above
information about the Mickmart Company system. Be certain to indicate identifiers and
cardinality constraints. [9 Marks]
(c) KCA University intends to automate its student’s records management system. The details
of students to be captured include: AdministrationNumber, sex, DateofBirth,
course,EmailAddress, courseLevel and feespayable.
Required: Assuming that SQL or MYSQL will be used to implement the required system,
state the most appropriate data type for each field [7 Marks]
QUESTION FOUR [20 MARKS]
(a) Table 2 shows details of patients in a certain hospital. Use it to answer the questions that
follow:
Table 2
Required:
Write MYSQL/ SQL statements that would be able to carry out each of the following:
(i) Return the average MedicalBill for all female patients [3 Marks]
(ii) Display the minimum MedicalBill for male patients [3 Marks]
(b) List and explain three components of a DBMS [6 Marks]
(c) Explain four qualities of a good database system [4 Marks]
(d) Give four examples of the commonly used DBMS in the market [4 Marks]
QUESTION FIVE [20 MARKS]
(a) Explain the following terms as used in database systems:- [4 Marks]
(i) Database
(ii) Data integrity
(iii)Candidate key
(iv)Relationship
(b) (i) Define the term “database normalization” [2 Marks]
(c) (ii) Discuss four benefits of database normalization [4 Marks]
(d) With the aid of appropriate illustrations, describe the following database models:-
(i) Relational model [4 Marks]
(ii) Hierarchical mode [4 Marks]
(e) Differentiate between the following database terms:-
(i) Logical design & physical design [2 Marks]
