DATABASE SYSTEMS

UNIT OF COMPETENCY: Manage Database Systems

UNIT CODE: 0612551/11/A

CLASS: ICT6/25M2A

PREPARATION DATE: 2/5/2025

DOCUMENT NAME: NOTES SESSION 1

LECTURER NAME: MADAM MALONZA ESTHER TEL NO: 0115019129

INSTITUTION: THE NYERI NATIONAL POLYTECHNIC

Unit Introduction: Manage Database Systems

Critical Questions to Spark Thinking (Leveraging MS Access Experience):

1. "In MS Access, you’ve created tables and forms—but how do you ensure your database

scales for 10,000 users without crashing?"

2. "What happens if two users edit the same record simultaneously? How would you

prevent data corruption?"

3. "You’ve used queries to retrieve data—but how do you optimize them for speed when

dealing with millions of records?"

4. "How would you recover your database if a hacker deletes critical tables or a server

fails?"

Why This Unit Matters

 Beyond MS Access: Transition from basic desktop databases (like Access)

to enterprise-level systems (e.g., MySQL) that handle complex transactions, security,

and scalability.

 Real-World Relevance: Learn to design, secure, and optimize databases for industries

like healthcare (patient records), e-commerce (transaction logs), and banking (ACID

compliance).

What You’ll Master

1. Design Like a Pro:

o Create ER diagrams and relational models.

o Define data relationships (one-to-many, many-to-many).

 2. Code with Confidence:

o Write advanced SQL queries (JOIN, GROUP BY, subqueries).

o Implement indexing and triggers for automation.

3. Secure & Protect:

o Apply role-based access control (e.g., admin vs. guest permissions).

o Execute backup/recovery plans to prevent data loss.

4. Optimize Performance:

o Troubleshoot slow queries and deadlocks.

o Stress-test databases under heavy loads.

Industry Alignment

 Skills Employers Want: Database administrators (DBAs), data analysts, and backend

developers rely on these competencies daily.

 Certification Pathways: Prepares you for certifications like MySQL

Professional or Microsoft SQL Server.

Define Database Management System (DBMS) & Terminologies

1. What is a Database Management System (DBMS)?

A DBMS is software that enables users to create, manage, and interact with databases. It acts

as an intermediary between the database and end-users/applications, ensuring data is organized,

secure, and efficiently accessible.

Key Functions of a DBMS:

 Data Storage: Stores structured data (e.g., tables) and unstructured data (e.g., logs).

 Data Retrieval: Allows users to query data using languages like SQL.

 Data Security: Implements access controls, encryption, and auditing.

 Concurrency Control: Manages simultaneous data access by multiple users.

 Backup & Recovery: Safeguards against data loss.

Examples: MySQL, Microsoft SQL Server, Oracle, MongoDB.

2. Core Terminologies

Term Definition Example

A structured collection of data

A hospital’s patient records
Database organized for efficient access and
database.
management.

A set of data elements organized into A Customers table with

Table
rows (records) and columns (fields). columns: ID, Name.
Term Definition Example

A single entry in a table representing a A row with ID=101,

Record (Row)
unique entity. Name="Alice".

Field Email, Phone_Number in

A specific attribute of a record.
(Column) a Users table.

The blueprint defining the structure of An ER diagram showing tables

Schema
the database (tables, relationships). and links.

A request to retrieve or manipulate data Fetching all customers from

Query
(e.g., SQL SELECT * FROM Users). New York.

Organizing data to reduce redundancy Splitting a table

Normalization
and improve integrity. into Orders and Items.

3. Establishing Database Requirements Based on User Needs

Steps to Define Requirements:

1. Stakeholder Interviews:

o Ask users: “What tasks will this database support?”

o Example: A library needs to track books, borrowers, and due dates.

2. Use Case Analysis:

o Identify scenarios: “How will employees search for a book?”

3. Data Modeling:
 o Define entities (e.g., Books, Members) and relationships (e.g., a member borrows

many books).

4. Functional vs. Non-Functional Requirements:

o Functional: “The system must allow adding new books.”

o Non-Functional: “The system must handle 1,000 concurrent users.”

Example User Story:

“As a librarian, I need to search for books by title or author so that I can quickly assist

members.”

4. Identifying Main Database Features for Expected Output

Key Features to Align with User Needs:

User Need DBMS Feature Expected Output

Quick data
Indexing Fast search results for queries.
retrieval

Constraints (e.g., primary

Data accuracy No duplicate customer IDs.
keys)

No conflicts when two users edit the

Multi-user access Concurrency Control
same record.

Secure sensitive Role-Based Access Control

Only admins can delete records.
data (RBAC)
User Need DBMS Feature Expected Output

Restore customer data after a server

Prevent data loss Automated Backups
crash.

Case Study:

 Requirement: A sales team needs real-time access to customer orders.

 Features:

o Cloud-Based DBMS (e.g., AWS RDS) for scalability.

o Triggers to auto-update inventory when an order is placed.

5. Common Pitfalls to Avoid

 Ignoring User Feedback: Design the schema without input from end-users.

 Overlooking Scalability: Build a system that can’t handle future data growth.

 Poor Security Practices: Failing to encrypt sensitive data (e.g., passwords).

Summary

1. A DBMS is the backbone of data-driven systems, ensuring organized, secure, and

efficient data management.

2. Terminologies like tables, schemas, and queries form the foundation of database design.

3. User needs drive database requirements (e.g., “We need to track inventory in real-

time”).

4. DBMS features (indexing, RBAC, backups) are selected to meet specific outputs (speed,

security, reliability).