UNIVERSITY EXAMINATIONS: 2018/2019
EXAMINATION FOR BACHELOR OF SCIENCE IN INFORMATION
TECHNOLOGY
BIT 2104: DATABASE DESIGN AND DEVELOPMENT
MODE: FULL TIME/PART TIME/DISTANCE LEARNING
ORDINARY EXAMINATIONS
DATE: AUGUST.2019 TIME: 2 HOURS
INSTRUCTIONS: Answer question ONE and any other TWO
QUESTION ONE [30 MARKS]
a) Explain the following terms briefly:
i. Attribute
ii. Domain
iii. Relationship
6 Marks
b) Why would you choose a database system instead of simply storing data in operating system files?
3 Marks
c) Explain the difference between an exclusive lock and a shared lock
4 Marks
d) Describe any FOUR applications of database triggers.
4 Marks
e) Describe THREE responsibilities of a database administrator.
3 Marks
f) What are the TWO basic database access operations in a transaction
4 Marks
g) What is the purpose of concurrency control in a transaction processing system?
3 Marks
h) Discuss any THREE database design issues.
3 Marks
QUESTION TWO [20 MARKS]
a). Differentiate between the following concepts as used in databases.
i. Cardinality and degree of a relationship
ii. Primary key and surrogate key
4 Marks
b). Consider the flat database below:
i. Identify and discuss three anomalies that may occur in this data base.
6 Marks
ii. Convert this table into third normal form.
6 Marks
iii. Store the records into the resultant table(s).
4 Marks
QUESTION THREE [20 MARKS]
a) Discuss FOUR software components of a relational database management system
(RDBMS).
4 Marks
b) Describe the key steps followed when selecting a DBMS product.
4 Marks
c) Consider the table Menu below and answer the questions that follow.
i. Create a view called beverages to display all beverage items
3 Marks
ii. Create a stored procedure called PriceIncrease to increase prices by 2% whenever the
procedure is executed.
3 Marks
iii. Create a trigger to track changes made in the table PRODUCT whenever records are inserted.
3 Marks
iv. Create a trigger to track changes made in the table PRODUCT whenever records are deleted.
3 Marks
QUESTION FOUR [20 MARKS]
a) With an aid of an example, explain the difference between object privilege and system privilege as
used in databases.
4 Marks
b) Peter would like to create a database system. Explain THREE stages in the database design
development that the ER diagram would be most applicable.
6 Marks
c) Describe each of the following components of the 3-tier database architecture.
i. Application layer
ii. Presentation layer
iii. Data layer
6 Marks
d) Distinguish between database engine and a database schema as used in database management system.
4 Marks
QUESTION FIVE [20 MARKS]
a) Kioko would like to enforce security in a multiple user database environment using a data control
commands. Describe two command statements that he is likely to use giving an example in each case.
4 Marks
b) Describe the following data storage approaches used in distributed database.
i). Replication
ii). Fragmentation
6 Marks
c) In an organization, an employee is identified by a unique number, salary and telephone contact. Each
employee works in a department. Each department is managed by an employee and is identified by a
department number, name and its budgetary allocation. Each employee may have a dependent child
who is identified uniquely by employee’s unique number, name and age. Based on this narrative
create the following:
i. A conceptual data model complete with cardinalities and optionality.
5 Marks
ii. A logical data model complete with cardinalities and optionality
5 Marks
