MURANG’A UNIVERSITY OF TECHNOLOGY
SCHOOL OF COMPUTING AND INFORMATION
TECHNOLOGY
DEPARTMENT OF INFORMATION TECHNOLOGY
UNIVERSITY ORDINARY EXAMINATION
2018/2019 ACADEMIC YEAR
SECOND YEAR FIRST SEMESTER EXAMINATION FOR
BACHELOR OF SCIENCE IN COMPUTER SCIENCE, BACHELOR OF
SCIENCE IN COMPUTER TECHNOLOGY, BACHELOR OF SCIENCE IN
INFORMATION TECHNOLOGY, BACHELOR OF SCIENCE IN SOFTWARE
ENGINEERING, BACHELOR OF SCIENCE IN BUSINESS INFORMATION
TECHNOLOGY
SIT 200– DATABASE MANAGEMENT SYSTEMS
DURATION: 2 HOURS
DATE:
TIME:
Instructions to candidates:
1. Answer question One and Any Other Two questions
2. Mobile phones are not allowed in the examination room.
3. You are not allowed to write on this examination question paper.
Page 1 of 4
SECTION A: ANSWER ALL QUESTIONS IN THIS SECTION
QUESTION ONE (30 MARKS)
a) Explain the following terms as used in database system
i. Entity (1 mark)
ii. Null value (1 mark)
iii. Database Management system (1 mark)
iv. Data model (1 mark)
v. Transaction (1 mark)
b) Illustrate the structure of the hierarchical data model and the rules governing it
(5 marks)
c) Explain five facilities/functions provided by database management system
(DBMS) (5 marks)
d) Describe the “ACID” properties of a transaction (4 marks)
e) Describe with aid of an example SQL code how data may be inserted into a table by
selecting and copying data from one or more existing tables hence avoiding the use of
many “INSERT” statements (6 marks)
f) Explain briefly what is meant by a weak entity type. Provide an example of a Weak
Entity Type using an example of your choice (5 marks)
SECTION B – ANSWER ANY TWO QUESTIONS IN THIS SECTION
QUESTION TWO (20 MARKS)
a) A car company dealing with importation of cars on behalf of clients is based in Mombasa
shipped vehicles are characterized by unique car identification number, car weight, car
type, and destination and delivery date. Shipped vehicles are received at the company
godowns. Each go down is characterized by a unique go-downId, location and telephone
number; shipped vehicles make their way to their clients via standard gauge railways or
by road. Each client name, address and the types of transport used to deliver the cargo.
Draw an entity relationship diagram for the above information (10 marks)
b) With the aid of an example distinguish between a composite attribute and a derived
attribute as used in ER diagrams (6 marks)
c) A database administrator experienced a system failure as he was using a database
management system. Explain TWO likely causes of this failure (4 marks)
QUESTION THREE (20 MARKS)
a) Distinguish between internal and external schemes as used in database design (2 marks)
b) The following is a table named hostel workers in a database. Use it to answer the
questions that follow (8 marks)
Page 2 of 4
ID Name Hostel Age Salary Year of service
500 Patrick Red 44 22000 7
510 Dolly Blue 30 45000 15
520 Sindy Orange 35 26000 10
530 Lucy Greene 20 44000 12
Write an SQL statement that would
i. Sort the hostel workers table by the name and salary in descending order
ii. Find the average salary for all employees
iii. Find salary more than twenty one thousand and less than thirty thousand and in red
hostel
iv. Display all the records having names with letters “dy”
v. Increase salary for all employees by 5%
c) An Embassy records details of Visa applicants in the table below.
Applicant No. Interview Data Interview Time Staff No. Room No.
13-May-2016 10:30 S5 R101
AP 1
13-May-2016 12:00 S5 R101
AP 2
13-May-2016 12:00 S9 R200
AP 3
22-Sep=2016 10:30 S5 R200
AP 2
Interviews are conducted by members of staff in some of the embassy rooms. In a given
day, a member of staff tends to use the same room throughout that day. An applicant
cannot have two interviews in the same day.
i. Explain the term candidate key (2 marks)
ii. List three candidate keys for the above (6 marks)
d) Explain what is meant by a functional dependency in a relation.
Give an example (2 marks)
QUESTION FOUR (20 MARKS)
a) A company places order for items. Each order is placed on a given date and may include
a variety of items in different quantities. The following table shows a sample of orders.
The primary Key is (OrderNo, ItemNo)
Page 3 of 4
Order No Item No Item Description Date Quantity
1 Screw 6-June-2017 100
1
2 Bolt 6-Jan-2017
1 50
3 Flange 2-Feb-2017
2 10
2 Bolt 2-Feb-2017
2 40
1 Screw 2-Feb 2017
2 80
i. Explain why this table is not in 2nd normal form (2 marks)
ii. Describe Two types of anomaly that could be caused by update, insert or delete
operations giving an example of each, with reference to the above table (4 marks)
iii. Transform the table into 2nd normal form. show the structures of the resultant
table (4 mark)
b) For each of the following two relational concepts explain the key ideas behind them and
using a sample relation of your own choosing provide suitable examples
i. Entity integrity (5 marks)
ii. Referential integrity (5 marks)
Page 4 of 4
