[08:27, 17/06/2025] +250 793 108 627: 01.
Multiple Choice (2 marks)
I. c) Primary key attribute
II. b) To manage and manipulate data in a relational database
02. Matching Models (4 marks)
Model
Characteristic
1. Hierarchical Model
B. Parent-child tree structures
2. Relational Model
C. Uses tables (relations)
3. Network Model
D. Many-to-many relationships
4. Object-Oriented Model
A. Objects with attributes & methods
03. Multiple Choice (3 marks)
I. a) To hide unnecessary details and provide essential information
II. d) User passwords
III. c) Change schema at one level without affecting another
04. True or False (4 marks)
a) True
b) True
c) False — Strong entity is a single rectangle
d) True
05. Multiple Choice (3 marks)
I. c) Data security
II. b) Visually represent relationships between entities
III. a) Visually represent structure of the database
06. Fill-in (5 marks)
a) ALTER
b) /
c) ALTER
d) DELETE
e) AND
07. Multiple Choice (4 marks)
I. b) Protect data confidentiality, integrity, availability
II. d) User logging in with stolen credentials
III. c) Cyber-attack where malicious SQL code is inserted
IV. a) Running unauthorized software
08. Multiple Choice (2 marks)
I. b) Logical=structure; physical=storage
II. a) DBMS uses SQL to create/enforce schema which defines data organization
09. True/False (5 marks)
a) True
b) False — DBMS does act as interface
c) False — SQL is a language, not a DBMS
d) True
e) True
10. Multiple Choice (3 marks)
I. b) Visually represents entities/attributes/relationships
II. d) Entities, attributes, relationships
III. c) Provide high-level overview of structure
11. Matching (3 marks)
1. Database Security → C
2. Greater Fidelity → A
3. Improved Communication → B
12. SQL Differences (3 marks)
I. b) DELETE is row-by-row and rollback-able; TRUNCATE is instant and cannot be
rolled back
II. d) Changes data type of Name to VARCHAR(100)
III. b) Orders table and all its data are permanently deleted
13. SQL Updates (3 marks)
I. a) UPDATE Employees SET salary = 50000 WHERE Employee_ID = 103;
II. c) UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';
III. d) UPDATE Employees SET Position = 'HR Director' WHERE Name = 'Alice';
14. Arrange Steps (2 marks)
b → c → a → d
1. Identify required data (b)
2. Design ERD (c)
3. Implement in SQL (a)
4. Create user roles (d)
15. Fill in SQL (3 marks)
I. ALTER TABLE Employees ADD Department VARCHAR(100);
II. DROP DATABASE OldDB;
III. DROP TABLE TemporaryData;
16. Match ALTER Commands (3 marks)
1. ADD COLUMN … → B
2. MODIFY COLUMN … NOT NULL → C
3. DROP COLUMN … → A
17. Encryption Fill-in (3 marks)
I. A) Public, Private
II. B) Symmetric
III. D) Asymmetric
[08:27, 17/06/2025] +250 793 108 627: ⸻
🔶 Section B: Structured Answer Questions (30 Marks)
18. Data Inconsistency – total_amount as Strings
(10 Marks)
a) Problem Explanation
• total_amount column is stored as strings (e.g., '1000', '2000.50')
rather than numeric values.
• This causes:
• Wrong sorting (e.g., '10000' < '200').
• Inability to perform mathematical calculations (e.g., sum, avg).
b) Solution – Correct the Data Type
Step 1: Alter the column to numeric (e.g., DECIMAL)
ALTER TABLE Orders
MODIFY total_amount DECIMAL(10,2);
Step 2: Ensure existing data is clean (optional, if values were dirty)
UPDATE Orders
SET total_amount = CAST(total_amount AS DECIMAL(10,2))
WHERE total_amount REGEXP '^[0-9]+(\.[0-9]+)?$';
c) Enforce data validation (Only positive values)
ALTER TABLE Orders
ADD CONSTRAINT chk_total_amount CHECK (total_amount >= 0);
19. Database Normalization – Book Orders Table
(10 Marks)
a) Normalization Problem
A Book Orders table may contain repeated information, e.g.:
| Order_ID | Customer_Name | Book_Title | Quantity | Email |
|----------|----------------|------------|----------|---------------|
| 001 | Alice Smith | DB Basics | 1 | alice@mail.com |
| 002 | Alice Smith | SQL Master | 2 | alice@mail.com |
b) Issues
• Redundancy: Customer info repeated.
• Update Anomaly: Change email requires multiple updates.
• Insert Anomaly: Can’t store a book not yet ordered.
• Delete Anomaly: Deleting one order may remove all customer or book
data.
c) Normalization Process (To 3NF)
1st Normal Form (1NF):
• Ensure atomic values (no multi-values in cells).
2nd Normal Form (2NF):
• Remove partial dependencies.
3rd Normal Form (3NF):
• Remove transitive dependencies.
d) 3NF Table Structure
1. Customers(customer_id, name, email)
2. Books(book_id, title, author, price)
3. Orders(order_id, customer_id, order_date)
4. OrderItems(order_id, book_id, quantity, unit_price)
This removes redundancy, avoids anomalies, and supports scalability.
20. Data Security – Encryption and Backup
(10 Marks)
a) Symmetric vs. Asymmetric Encryption
Feature
Symmetric Encryption
Asymmetric Encryption
Keys
One shared secret key
Public key & private key pair
Speed
Faster
Slower
Key sharing
Risky (must be kept secret)
Safer (public key sharable)
Use case
Bulk data encryption
Secure communication, key exchange
b) Recommended Strategy: Hybrid Encryption
• Use symmetric encryption (e.g., AES) for encrypting large data.
• Encrypt the symmetric key using asymmetric encryption (e.g., RSA).
c) Why This is Better
• Combines efficiency (symmetric) with security & scalability
(asymmetric).
• Widely used in TLS/SSL, email security, backup systems.
Example:
1. Data → Encrypted with AES (fast).
2. AES key → Encrypted with RSA (secure).
3. Store both encrypted data & encrypted key.
[08:27, 17/06/2025] +250 793 108 627: 🔷 SECTION C: CASE STUDY – E-commerce Database
Design (30 Marks)
🟩 Scenario Recap:
You’re designing a relational database for an e-commerce platform. It includes:
• Products
• Customers
• Orders
• Order details
• Payment info
You must show design, relationships, keys, and sample queries.
