UNIVERSITY EXAMINATIONS: 2016/2017
EXAMINATION FOR THE DEGREE OF BACHELOR OF SCIENCE IN
INFORMATION TECHNOLOGY
BIT2104 DATABASE DESIGN AND DEVELOPMENT
FULL TIME/PART TIME/DISTANCE LEARNING
SPECIAL/SUPPLEMENTARY EXAMINATION
DATE: JULY, 2017 TIME: 2 HOURS
INSTRUCTIONS: Answer Question One & ANY OTHER TWO questions.
QUESTION ONE
a) Define the following terms
i) Entity integrity (2Marks)
ii) Referential integrity (2 Marks)
iii) Database schema (2 Marks)
iv) Normalization (2 Marks)
b) Explain the following
i) Data manipulation language (2 Marks)
ii) Data Control Language (2 Marks)
iii) Data Definition Language (2 Marks)
c) Describe the types of update anomalies that may occur on a table that has redundant
data. (4 Marks)
d) i) Describe the purpose of a design methodology. (2 Marks)
ii) What factors can be used to measure efficiency? (6 Marks)
e) Explain what is meant by a DDBMS, and discuss the motivation in providing such a
system. (4 Marks)
QUESTION TWO
a) Explain the following database architectures
i) Single database
ii) Replicated database
iii) partitioned database
iv) cloud based database (8 Marks)
b) Draw an entity relationship diagram for the following scenario. A student can take only
one course at a time. A lecturer can teach many students. A course must belong to a
faculty. A lecturer can belong to only one department though can teach in several
faculties. Identify the suitable attributes in each entity type. (12 Marks)
QUESTION THREE
a) What is the purpose of open database connectivity (ODBC) (2 Marks)
b) A employee table has the following fields empno, empname, dateofbirth, deptcode,
basicsalary. You are required to write the SQL command for
i) Add a new field marital status (2 Marks)
ii) Add a new record with a sample record (3 Marks)
iii) List records of employees with salary range between 50,000 and 100,000
(3 Marks)
iv) List the employees in descending order of their empno (2 Marks)
c) State a necessary and sufficient condition for a table to be in the Third Normal Form
( 2 Marks)
d) List the disadvantages of distributed databases (6 Marks)
QUESTION FOUR
A combined medical and herbal centre wants to create a database to keep track of its medics,
patients, herbal medicine, and herbal prescriptions. For simplicity, both a herbal medic and a
patient will be recorded for his or
her name, a herbal medicine will be recorded for its name, unit and the price per unit, and a
prescription will be recorded for its date, the patient and medic, and all the herbs included in the
prescription.
a) For the conceptual database design phase, design your database in terms of an EntityRelationship (ER) diagram. This ER diagram should include explicitly the primary keys, the
participation multiplicities for the relationship type/s. State the additional assumptions, if
any, that are crucial to the design in your final ER diagram. (4 Marks)
b) For the logical database design phase, map the final ER diagram into the global relation
diagram. Indicate all the primary keys, foreign keys, multiplicity constraints, as well as at
least the crucial attributes. (Here “crucial” is in regard to the database design, not necessarily
to the needs of business information.) (3 Marks)
c) Write an SQL script (of statements) that generates at least 2 tables for your designed database
in above with proper primary keys and foreign keys if any. At least one of these TWO tables
should contain a foreign key that is related to at least one of the tables you created via SQL
here. (2 Marks)
QUESTION FIVE
a) Explain why it is necessary to select the target DBMS before beginning the physical
database design phase. (4 Marks)
b) Discuss the two main activities associated with application design. (2 Marks)
c) Describe the potential benefits of developing a prototype database system. (3Marks)
d) Discuss the main activities associated with the implementation stage. (3 Marks)
e) Describe the purpose of creating a mission statement and mission objectives for the
required database during the database planning stage. (2 Marks)
f) Discuss what a user view represents when designing a database system. (2 Marks)
g) Compare and contrast the centralized approach and view integration approach to
managing the design of a database system with multiple user views. (4 Marks)
