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: APRIL.2019 DURATION: 2 HOURS
INSTRUCTIONS: Answer question ONE and any other TWO
QUESTION ONE [30 Marks]
a) Discuss the concept of data modelling as used in database design.
3 Marks.
b) Briefly explain the conceptual, logical and physical data models as used in database design.
6 Marks.
c) Define the concept of a surrogate key and explain how different it is from a primary key.
4 Marks
d) Briefly explain the significance of metadata during database design.
6 Marks
e) Describe any three characteristics of database approach.
3 Marks
f) Consider the scenario given below and construct a conceptual data model complete with cardinalities
and optionalities using chen’s notation:
A division operates many departments, but each department is operated by only one division. Each
division must have at least one department and each department must belong to a division. A
department employs many employees, but there may be a department with no employees. Majority of
employees called “dept_emps” are employed by a department. However, there are also employees
known as “free_lance_emps” who are not assigned to any department. A department may own many
projects, but there may be a department with no projects. Each project is existentially and
identification dependent on only one department. Both dept_emps and free_lance_emps can work on
many projects, but there are employees who do not work on any project. A project has at least one
employee working on it. One of dept_emps ménages a department. Each department must have a
manager. One of the employees runs a division. Each division must have someone running it.
8 Marks.
QUESTION TWO [20 MARKS]
a) Define the concept of database normalization
2 Marks
b) Differentiate between transitive dependency and partial dependency.
6 Marks
c) Briefly outline any three anomalies that may occur in the table shown below.
6 Marks
d) Describe the process of normalizing the table above up to 3NF.
6 Marks
QUESTION THREE [20 MARKS]
a) Discuss any FOUR advantages of using a stored procedure in a database.
8 Marks
b) Discuss any THREE major differences between triggers and stored procedures.
6 Marks
c) Consider the schema for table called bookings below and answer the questions that follow.
i. Create a view to display all reservations made between 2/2/2019 and 2/3/2019.
3 Marks
ii. Write a query to update details of a reservation record whose so that the check in time
was changed from 1/4/2019 to 11/4/2019.
3 Marks
QUESTION FOUR [20 MARKS]
a) Almost every database system provides a mechanism for enforcing a password policy that has the
following features:
Complexity
Failed attempts
Expired passwords
Password reuse
Briefly describe each of the above features and explain how they help protect the database. (8 marks)
b) State the emerging roles and responsibilities of a datawarehouse administrator (4 marks)
c) A medical surgery keeps patients’ records in a database server located in a dedicated
room. These records can be accessed remotely by the staff working at the surgery
(doctors and office staff).
(i) Describe an example of physical security control for protecting this database. (2
mark)
d) (ii) Describe one way of preventing the office staff from viewing sensitive medical data
about patients that should be viewed only by doctors. (2 marks)
e)
f) Using your own examples and/or suitable diagrams, explain the following transaction
processing concepts and their role in enforcing data integrity and consistency…
i) COMMIT (2 Marks)
ii) ROLLBACK (2 Marks)
QUESTION FIVE [20 MARKS]
a) Database security aims to minimise the loss of:
Data Confidentiality
Data Integrity
Data Availability
Describe each of the above concepts (6 marks)
b) Explain how each of the following mechanisms contribute to providing security for a database:
Authorisation
Views
Backup and Recovery
Encryption (8 marks)
c) define the following database terms…
i) ACID properties (2 Marks)
ii) Isolation levels (2 Marks)
iii) Locking levels (2 Marks)
