COSC 250
CHUKA UNIVERSITY
UNIVERSITY EXAMINATIONS
SECOND YEAR EXAMINATION FOR THE AWARD OF
BACHELOR OF COMPUTER SCIENCE
COSC 250: DATABASE SYSTEMS
STREAMS: BSC COMP SCI TIME: 2 HOURS
DAY/DATE: TUESDAY 09/04/2019 2.30 P.M – 4.30 P.M
INSTRUCTIONS:
Answer question ONE and TWO other questions
Sketch maps and diagrams may be used whenever they help to illustrate your answer
Do not write anything on the question paper
This is a closed book exam, No reference materials are allowed in the examination room
There will be No use of mobile phones or any other unauthorized materials
Write your answers legibly and use your time wisely
SECTION A-COMPULSORY: 30 MARKS
QUESTION ONE
a) Give 4 advantages of using a computerized database system over traditional. (4 marks)
b) Differentiate between client/server and desktop database giving 2 examples of application
software each case. (2 marks)
c) Explain one advantage and two disadvantages of network database model. (3 marks)
d) Briefly describe the following database models structures.
i) Hierarchical (2 marks)
ii) Network (2 marks)
e) Differentiate between data mining and data warehouse. (2 marks)
f) Explain the importance of normalization. (3 marks)
g) Outline the stages of developing a database system. (4 marks)
Page 1 of 4
COSC 250
h) Differentiate between physical and logical view as applied in database. (4 marks)
i) Briefly describe the roles of the following people in client/ server database. (4 marks)
i) Administrator
ii) User
SECTION B (Answer Any two)
QUESTION TWO (20 MARKS)
a) An organization would like to store details of employees in a database. Using information in the
organization pay slip shown below to normalize the database to 3rd normal form.
(12 marks)
JULY 2010 PAY SLIP
Employee number:……………… Name …………………………………… Box …………..
Section code…………………….Section name:…………………………… Rank…………..
Basic Salary 50,000
House allowance 12,000
Travel Allowance 4,000
Medical allowance 2,000
PAYE 12,000
NSSF 2,000
NHIF 1,000
Loan 15,000
Net Salary 38,000
Head of section name:……………………………………. Head of Section sign:………………….
b) Use examples to explain the following database integrities. (6 marks)
i) Entity
ii) Validity
iii) Referential
c) Differentiate primary key and foreign key. (2 marks)
QUESTION THREE (20 MARKS)
a) The details below represent data stored in a retail shop about products and customer orders.
i) Admission ii) Treatment details iii) Discharge details
Admission number Treatment number Discharge number
Gender Admission number Admission number
First name Doctor name Medicine bill
Last name Diagnosis admission bill
Date of birth Recommendation other bills
Address Medication
Page 2 of 4
COSC 250
i) Identify the most appropriate key to be the primary key for each table then show
relationships among the tables. (6 marks)
ii) Describe any four field properties that can be used to enforce validity integrity in any four
fields in the tables designed in question 1 (a) above. (4 marks)
b) Discuss the following database models. (4 marks)
i) Hierarchical
ii) Network
c) Describe the ACID properties in distributed database systems. (6 marks)
QUESTION FOUR (20 MARKS)
a) The table below shows details of Students marks in a secondary school.
Stdno Fname lname Maths English Kiswahili Total
4352 Peter Mwangi 45 65 45
4535 Paul Mwiti 44 76 65
4536 Mary Atieno 54 35 55
4537 Sam Mutua 33 67 25
4538 Sarah Chepkoech 66 66 33
4539 Amina Abdi 43 55 78
Write an expression that will extract records that satisfy the following conditions.
i) List all students with lname first letter “m”. (2 marks)
ii) List all students who scored 60 and above in English. (2 marks)
iii) List all students with the fname second letter “a”. (2 marks)
iv) List all students who scored between 20 and 60 in Mathematics. (2 marks)
b) Write query expression to for the above table in question 3 to:
i) Calculate total marks for each student. (3 marks)
ii) Calculate average marks for each student. (3 marks)
c) Briefly explain the meaning of the following SQL statements. (4 marks)
i) ALTER TABLE employee ADD (netsalary float);
ii) ROLLBACK TO SP2;
d) Differentiate between a trigger and synonym. (2 marks)
Page 3 of 4
COSC 250
QUESTION FIVE (20 MARKS)
a) A bus company operates fleet of buses and would like to design the system. The
following information shows entities involved in the system.
Each passenger is booked in one bus.
A driver can drive more than one bus.
Buses travel to different destinations.
The buses can be services in any garage owned by the company.
i) Identify entities in the bus company fleet system. (2 marks)
ii) Use an Entity Relation Diagram (ERD) to show relationship among the
entities. (6 marks)
iii) Identify at least 4 attributes of each entity. ( 4 marks)
b) ABC Company would like to develop a relational database management system. Advice
the management on the stages of developing a database system (5 marks)
c) Explain the function of a database catalogue. (3 marks)
-------------------------------------------------------------------------------------------------------------------------
Page 4 of 4
