QUESTION 11 (20mks) 1. Define the following terms i) Database schema (1 mark) ii) Database Instance (1 mark) iii) Database Management System (1 mark) iv) Relation (1 mark) 2. Explain FOUR ways in how databases can be classified based on number of sites over which Database is Distributed (4marks) 3. Ellaborate any FOUR advantages that database management systems offer in order to overcome the disadvantages of file systems in data storage (4marks) 4. Differentiate between Data Definition Language versus a Data Manipulation Language and use at least one example of each to illustrate your explanation. 5. Explain the following transaction concept. (4 Marks) i. Grant (1 mark) ii. Revoke (1 mark) iii. Commit (1 mark) iv. Rollback (1 mark) (4marks) 6. Explain FOUR benefits of normalizing database containing relations (4marks) 7. Describe any FOUR constraints used in SQL. (4marks) 8. Explain FOUR techniques that are used to perform Database Testing. (4marks) (4marks) 10. Distinguish between a weak entity and a derived entity as used in Entity Relationship diagram (4marks) @2022 The Kenya National Examination Council (Answer any THREE (3)questions in this section). i. A process in a transaction is said to successfully terminate after attaining "acidity". Describe the ACID properties with examples for each state (8marks) 9. Describe the following types of keys as used in database management system i. Primary Key ii. Foreign Key iii. Candidate Key iv. Super Key SECTION B (60 MARKS)

**1. Definitions:**<br /><br />i) **Database Schema:** A database schema is the structure that defines the organization of data in a database, including tables, fields, relationships, views, indexes, and other elements.<br /><br />ii) **Database Instance:** A database instance refers to the actual data stored in a database at a particular moment in time. It is a snapshot of the database.<br /><br />iii) **Database Management System (DBMS):** A DBMS is software that provides an interface for users to interact with databases, allowing for data storage, retrieval, and management.<br /><br />iv) **Relation:** In the context of databases, a relation is a table with columns and rows, where each row represents a unique record and each column represents a field within the record.<br /><br />**2. Classification of Databases Based on Distribution:**<br /><br />Databases can be classified based on the number of sites over which they are distributed as follows:<br /><br />- **Centralized Database:** All data is stored at a single site.<br />- **Distributed Database:** Data is distributed across multiple sites but appears as a single database to users.<br />- **Federated Database:** Multiple autonomous databases are integrated into a single federated system.<br />- **Cloud Database:** Hosted on cloud services, accessible over the internet, and can be distributed across various locations.<br /><br />**3. Advantages of Database Management Systems Over File Systems:**<br /><br />- **Data Redundancy Control:** DBMS minimizes data redundancy by integrating all data into a single database.<br />- **Data Integrity and Consistency:** Ensures data accuracy and consistency through constraints and rules.<br />- **Improved Data Security:** Provides robust security measures to protect data from unauthorized access.<br />- **Concurrent Access:** Allows multiple users to access and manipulate data simultaneously without conflicts.<br /><br />**4. Data Definition Language vs. Data Manipulation Language:**<br /><br />- **Data Definition Language (DDL):** Used to define and manage database structures. Example: `CREATE TABLE Students (ID INT, Name VARCHAR(50));`<br />- **Data Manipulation Language (DML):** Used to retrieve and manipulate data. Example: `SELECT * FROM Students WHERE ID = 1;`<br /><br />**5. Transaction Concepts:**<br /><br />i. **Grant:** Provides specific privileges to users or roles on database objects.<br /><br />ii. **Revoke:** Removes previously granted privileges from users or roles.<br /><br />iii. **Commit:** Saves all changes made during the current transaction permanently to the database.<br /><br />iv. **Rollback:** Undoes all changes made during the current transaction, reverting the database to its previous state.<br /><br />**6. Benefits of Normalizing Databases:**<br /><br />- **Reduces Data Redundancy:** Eliminates duplicate data, saving storage space.<br />- **Improves Data Integrity:** Ensures data consistency and accuracy.<br />- **Facilitates Easier Maintenance:** Simplifies updates and modifications.<br />- **Enhances Query Performance:** Optimizes query execution by organizing data efficiently.<br /><br />**7. SQL Constraints:**<br /><br />- **Primary Key Constraint:** Uniquely identifies each record in a table.<br />- **Foreign Key Constraint:** Ensures referential integrity between two tables.<br />- **Unique Constraint:** Ensures all values in a column are distinct.<br />- **Check Constraint:** Enforces a condition on data before it is entered into a table.<br /><br />**8. Database Testing Techniques:**<br /><br />- **Functional Testing:** Verifies that database operations perform according to requirements.<br />- **Performance Testing:** Assesses the speed and efficiency of database queries.<br />- **Security Testing:** Evaluates the database's protection against unauthorized access.<br />- **Load Testing:** Tests how the database handles large volumes of data and concurrent users.<br /><br />**10. Weak Entity vs. Derived Entity:**<br /><br />- **Weak Entity:** An entity that cannot exist without a related strong entity and lacks a primary key of its own.<br />- **Derived Entity:** An entity whose attributes are derived from other entities rather than being stored directly.<br /><br />**ACID Properties:**<br /><br />- **Atomicity:** Ensures that all parts of a transaction are completed successfully or none at all. Example: Transferring money between accounts should either complete fully or not at all.<br />- **Consistency:** Guarantees that a transaction brings the database from one valid state to another. Example: Ensuring account balances remain accurate after transactions.<br />- **Isolation:** Ensures that transactions occur independently without interference. Example: Two transactions updating the same data do not affect each other.<br />- **Durability:** Ensures that once a transaction is committed, it remains so even in the event of a system failure. Example: Completed transactions are saved to disk.<br /><br />**9. Types of Keys:**<br /><br />i. **Primary Key:** A unique identifier for records in a table.<br /><br />ii. **Foreign Key:** A field in one table that uniquely identifies a row of another table.<br /><br />iii. **Candidate Key:** A set of attributes that uniquely identify tuples in a table and could serve as a primary key.<br /><br />iv. **Super Key:** A set of one or more columns that can uniquely identify rows in a table.