Database Management Systems November 2016 Past Paper – KNEC Diploma

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

  1. (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;
Field Des      tion                                                 
Asset Category This field holds the category the asset belongs to.
Should not be null and hold not more than 55 characters.
  • 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)

  1. (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 Marksand 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.

  1. update anomaly;
  2. 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)
(iii)

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)

Share through

Leave a Reply

Your email address will not be published.