Database Management Systems November 2016 Past Examination Question Paper – KNEC
This Past Paper examination was examined by the Kenya National Examination Council (KNEC) and it applies to the following courses:
-
Diploma in Information Communication Technology – Module II
Note: To easily navigate through the KNEC Past Examination Paper Pdf below, Mobile phone users are advised to use Mozilla or Chrome browsers
THE KENYA NATIONAL EXAMINATIONS COUNCIL
DIPLOMA IN INFORMATION COMMUNICATION TECHNOLOGY
MODULE II
DATABASE MANAGEMENT SYSTEMS
November 2016
TIME: 3 Hours
- (a) Define the following terms.
- Database
- Database Management (4 marks)
Table 1 shows a student’s results slip. Use it to answer the question that follows.
| Student No: 1022567 Student Name. Alex James Course code: F105 Course Title: ICT |
|||||
| SubjectCode | Subject Title | Number of
Hours |
Grade
|
Result
code |
Results |
| Bus 119 | Business
operations |
20 | 10 | PA0l | Pass |
| COM1 l0 | Introduction to
computers |
2fi | 8 | PA02 | Pass |
| COM112 | Application
Development |
20
|
2 | RE01 | Refer
Exam |
| COM1 14 | HCI | 10 | 7 | RE0 | Refer Exam |
Table 1. Nomalize to 3NF
c) The following SQL statement has errors.
SELECT Name, location, FROM EMPLOYEE,
Where location=Nairobi;
Identify the errors and correct the statements.
(d) Differentiate between fully functional and transitive dependency as used in databases. (4 marks)
2. (a) Explain the function of the following in a Database management systems.’
- Data Manipulations Language;
- Data Definition
- With the aid of an example in each case distinguish between binary and shared locks inconcurrent (4 marks)
Table 2 shows the design details of a table named Asset. Use it to answer the questions that follow.
| Field | Description |
| Asset ID | This number identifies the Axset. It is the Primary Key |
| Asset Name | Identifies the name of the Asset. The
field should not allow null values. Should hold not more than 20 characters. |
| Asset Description | Gives a brief description of the function of the Asset. Should hold not more than 60 characters. |
| Year of Purchase | When the Asset was procured. The field should not allow null values |
Table 2.
Write an SQL statement that would:
- Create the table;
|
- Add the following column to the table i’seL’
- Add the following values to the
| Asset Id | Asset
Name |
Asset Description | Year of Purchase | Asset Category |
| 10 | Mouse | A mouse is a | 2016 | Computer |
| device used as | accessory | |||
| an accessory in | ||||
| a computer |
(d) A database administrator has defined the data type of a field as Dcci»tal (6,2). With the aid of an example interpret this data type. (2 marks)
- (a) Define the term n›eak entity as used in databases. (2 marks)
- Explain two circumstances under which a hospital woaJd use a database management system. (4 marks)
- Study the scenario
.4 computer compnity undertakes a number of pt ojecls. Either, an external clienl or on fiiferro/ dapartmenl handles n pt oject. An enema client or internal department may have several pFojects at one go. A consultant manages each project.
Draw an entity re/aiionshfp diagram to represent the scenario showing the Felcitionship cardinality. (6 marks)
- Table 3 shows details of Use it to answer the questions that follow.
| Student | Td | StudentName | D.O.B | Gender | Course id | Marks |
| N001 | Jane | 23.06.98 | Female | 004 | 70 | |
| N002 | Andrew | 22.05.96 | Male | 003 | 75 | |
| N003 | Catherine | 18.09.98 | Female | 004 | 90 | |
| N004 | Agnes | 16.06.99 | Female | 002 | 92 | |
| N005 | Noel | 26.04.98 | Male | 004 | 65 | |
| N006 | Martin | 14.07.98 | Male | 001 | 63 | |
4. (a) Table 3.
Write an algebraic expression that would:
- Display all records of female students who attained more than 70 marks; (2 marks)
Display all thc course Id of the students; (2 marks)
Rename the relation Students to ‘Student Marks’ and the attribute Student Id to
Outline two functions of a system analyst during database dcvclopmcnt. (2 marks)
Explain two advantages of a distributed database systems. (4 marks)
Explain two benefits of normalization in databases. (4 marks)
Table 4 shows an extract of Employees details at Faraja Company. Use it to answer the questions that follow:
| Employee Number |
Employee Name |
Department Number |
Department Name |
Department Manager |
| K9870678 | Jane Abraham | 101 _ | Marketing | Alex Stephen |
| K8767599 | Stephy Jacobs | 102 | Production | Grace Johns |
| K2345908 | Frank Edward | 103 | Accounts | Evans |
| K235b907 | Josephine Ted | 106 | Marketing | Alex Stephen |
| K2567890 | Stephen Max | 109 | Accounts | Evans |
Table 4.
Explain a scenario in the table that may lead to occurrence of each of the following anomalies.
- update anomaly;
- insertion anomaly;
III deletion anomaly.
- Distinguish between the output of SQL Statement A and B
- SELECT Distinct (Grade) (ii) SELECT Grade FROM employees; FROM employees;
5. (a) Outline four components of a database management system. (4 marks)
(b) Explain two circumstances that would make an organisation to implement a client server database architecture. (4 marks)
Using the following tables, differentiate between the output generated from the operations .4 B and ABB operations. (6 marks)
Table A Table B
(d) (i) A database administrator wants to apply the union, intersection and difference operation to a relation, explain two conditions he must check that the relations fulfil before applying the opemtions. (4 marks)
- State the meaning of ACID in Database Management (2 marks)
6. (a) Define the roles each of the following personnel in databases:
- Specialised end user:
- Database (4 marks)
State ttvo differences between the la based approach and the database approach. (4 marks)
Table S is a table named Results in a database. Use it to answer the questions that
| FirstName | SirName | IDNo | DOB | Marks |
| Alice | Smith | 123980 | 23-06-1984 | 95 |
| Alex | Maps | 879060 | 14-04-1989 | 60 |
| Banice | Young | 879067 | 12-03-1990 | 75 |
| John | Wallace | 312689 | 18-06-1994 | 55 |
Table 5: Results
Write an SQL statement thai would perform each of the following:
- display all records from the fields Maine, Sii‘/\lame and IDVO, (2 marks)
- display the DOB value for John Wallace, (3 marks)
- display all records whose marks range from 50 to 90,‘ (3 Inarks)
- sort all the records from highest to lowest based on marks; (2 mans)
- display all records whosefrs/urine start with letter (2 marks)
(a) Outline two differences between homogeneous and heterogeneous distributed database systems. (4 marks)
Explain two criteria that may be used to choose physical design in database. (4 marks)
Interpret the following entity relationship diaq•rain as used in database design. (4 marks)
- Use the following tables C’ and D to answer the questions that
| Sid | Sname |
| 001 | English |
| 002 | Kiswahili |
| 003 | Science |
| Pno | Name | Sid | flours |
| K0088769 | Cate | 002 | 10 |
| K0099221 | Jonathan | 001 | 20 |
| K0008702 | Andrew | 002 | 30 |
- Write an algebraic expression that would join the tables C and (2 mans)
- Write the output generated from the expression in (i) (3 marks)
- WrJte the output of the following expression: (3 marks)
8 (a)Outline four types of database threats a database administrator is likely to encounter. (4 marks)
- Describe each of the following phases of the database life cycle:
| (i)
(ii) |
requirements phase; conceptual phase; physical design phase. | (2 marks) (2 marks) (2 marks) |
|
- A database has a table named Use the table to ans cr the questions below. Employees
| EmpNo. | Employee Name |
Salary | DeptNo. |
| K000l | Jane | 10000 | 03 |
| K0002 | Esther | 1 5000 | 02 |
| K0003 | Alex | 30000 | 02 |
| K0004 | John | 25000 | 04 |
| K0005 | Elvis | 30000 | 01 |
| K(1006 | Hope | 75000 | 02 |
Write an SQL statement that would:
- count all the employees in Fc›ptN‹› 02; (2 marks)
lii) calculate the average salary of all employees: (2 marks)
(lii) calculate the total salary for all employees; (2 marks)
(iv) display all records for employees whose salary is less or equal to the average salary of all employees. (4 marks)