MODULE IIDATABASE MANAGEMENT SYSTEMS MARCH 2023

THE KENYA NATIONAL EXAMINATIONS COUNCIL

DIPLOMA IN INFORMATION COMMUNICATION TECHNOLOGY
MODULE II
DATABASE MANAGEMENT SYSTEMS
Time: 3 hours

INSTRUCTIONS TO CANDIDATES:
This paper consists of EIGHT questions.
Answer FIVE of the EIGHT questions in the spaces provided.
Candidates should answer the questions in English.

This paper consists of 6 printed pages.
Candidates should check the question paper to ensure that all the pages are printed as indicated and that no questions are missing.

© 2024 The Kenya National Examinations Council

QUESTION 1

  1. Outline four characteristics of a primary key in a database. (4 marks)
    • Uniqueness: Ensures each record is unique.
    • Not Null: Cannot have null values.
    • Immutable: Values cannot be changed once set.
    • Single Column or Composite: Can be one column or a combination of columns.
  2. Distinguish between an associative record and a regular record as used in a database. (4 marks)
    • Associative Record: Connects two tables with a many-to-many relationship.
    • Regular Record: Represents a single entity and its attributes.
  3. Describe each of the following terms as used in a database: (6 marks)
    (i) Data Entity: A distinct object or concept, represented by a table in a database.
    (ii) Data Dictionary: A centralized repository of information about database objects, such as tables, columns, and indexes.
    (iii) Database Instance: A specific realization of the database data at a given point in time.
  4. Explain, with examples, the need for entity-relationship diagrams in database design. (6 marks)
    • ER diagrams help visualize data relationships, define database structure, and identify entities, attributes, and keys, improving design accuracy and communication among stakeholders.
  5. Outline four challenges posed by integrating Artificial Intelligence into databases. (4 marks)
    • Data Privacy Concerns
    • High Computational Requirements
    • Complex Integration Processes
    • Data Quality and Bias Issues
  6. Distinguish between relational and non-relational models used in database systems. (4 marks)
    • Relational Model: Uses tables with rows and columns, ideal for structured data.
    • Non-Relational Model: Uses formats like key-value pairs, documents, or graphs, suitable for unstructured data.

QUESTION 2

  1. Identify the entities labeled (i) and (ii). (2 marks)
    • Entity (i): Book
    • Entity (ii): Author
  2. Write SQL statements for: (6 marks)
    (i) Creating the table for Book:
  3. CREATE TABLE Book ( BookID INT PRIMARY KEY, Title VARCHAR(255), ISBN VARCHAR(20), PublishedDate DATE );
  4. (ii) Adding the column “Author” to the table “Book”:
  5. ALTER TABLE Book ADD Author VARCHAR(255);

QUESTION 3

  1. Explain the function of the marker (⊆) as used in the diagram. (2 marks)
    • The marker (⊆) indicates a subset relationship, showing that an entity is a subset of another entity or class.
  2. Describe two cross-features that have been used in the diagram. (2 marks)
    • Attribute Linking: Attributes are directly linked to entities.
    • Primary and Foreign Keys: Primary keys uniquely identify records, while foreign keys establish relationships between tables.
  3. State the meaning of null value as used in databases. (1 mark)
    • A null value represents missing or unknown data in a field.
  4. Outline three circumstances that may lead to the occurrence of null values in a table. (3 marks)
    • Missing Data Entry
    • Not Applicable Field
    • Uninitialized Column during Table Creation
  5. Write the SQL for each question, explaining the following conditions: (4 marks)
    (i) Optional Qualifier: Allows fields to accept null values, providing flexibility in data entry.
    (ii) Universal Qualifier: Ensures all rows meet a specific condition before executing a query.
  6. Distinguish between WHERE and HAVING clauses as used in Structured Query Language. (2 marks)
    • WHERE filters rows before grouping occurs, while HAVING filters groups after the aggregation.
  7. Explain two reasons why the provided table violates the 2NF. (2 marks)
    • It has partial dependencies.
    • Non-key attributes depend only on part of the composite key.
  8. Represent the Student relation in 2NF. (3 marks)
    • Separate the relation into two tables: one for Students and another for Marks, eliminating partial dependencies.