UNIVERSITY EXAMINATIONS: 2014/2015
ORDINARY EXAMINATION FOR THE BACHELOR OF SCIENCE
IN INFORMATION TECHNOLOGY
BIT 1207 PRINCIPLES OF DATABASE DESIGN DISTANCE
LEARNING
DATE: APRIL, 2015 TIME: 2 HOURS
INSTRUCTIONS: Answer Question ONE and any other TWO
QUESTION ONE [30 MARKS]
a). Distinguish between transient data and persistent data. [4 Marks]
b). Explain the purpose of the following SQL statements
i). START TRANSACTION
ii). COMMIT
iii). ROLLBACK. [6 Marks]
c). Define the term lock as used in database transactions, hence briefly explain the differences
between shared (S) and exclusive (X) locks. [8 Marks]
d). User interface design is an important component during application design. In this regard outline any six key elements considered when designing a form. [12 Marks]
QUESTION TWO [20 MARKS]
a). Explain the difference between volatile and nonvolatile storage. [6 Marks]
b). Explain why a database developer will decide to develop a prototype for the database application. [4 Marks]
c). Explain the three phases of database design [6 Marks]
d). Define the following types of attributes
i). Single valued attribute
ii). Multi valued attribute
iii). Simple attribute
iv). Composite attribute [4 Marks]
QUESTION THREE [20 MARKS]
The information below was extracted from a flat database for a bus company.
a). Normalize this database to 3NF [10 Marks]
b). Use the final normalized and key in the information correctly. [10 Marks]
QUESTION FOUR [20 MARKS]
a) Study the following table and answer the questions that follow.
i). Define the term data anomaly [2 Marks]
ii). Identify and explain any three data anomalies from the above table
[6 Marks]
b) Briefly explain the meaning of the ACID properties. Explain how concurrency control and recovery management support the ACID properties. [8 Marks]
a) In case study below, not all the information given is necessarily relevant. Some
information, while relevant to the organization concerned, might not be relevant as far
as database design is concerned. Use this case to design an entity relationship models
complete with cardinalities and optionalities for a conceptual database design.
Patients are treated in a single ward by the doctors assigned to them. A given ward
will have one or more patients to be treated. Usually each patient will be assigned a
single doctor, but in rare cases there will more than one doctor. Heath care
assistants also attend to the patients, a number of these are associated with each
ward. Each health assistant however, can only be assigned one and only one ward.
[4 Marks]
QUESTION FIVE [20 MARKS]
a). Define the term database model. Discuss the THREE database models. [8 Marks]
b). Database security refers to the process of protecting the database against unauthorized access
to or modification of the database, denial of service to authorized users and provision of service to authorized users. Any security measure put in place to protect the database must address three key database security characteristics. Identify and explain these characteristics.
[9 Marks]
c). Explain THREE components of a database management system(DBMS)
[3 Marks]
