Expanded Course Notes: Database and SQL

Course: Database and SQL (SWE 233 - Partial Content)

Target Audience: Higher National Diploma in Software Engineering
Students
Author: Manus AI
Date: December 2025
Module 1: Relational Database Conception Principles
This module provides a foundational understanding of the theoretical underpinnings of the
Relational Database Model, which is essential for any software engineer designing robust
and efficient data storage solutions.
1.1 Functional Dependence and Armstrong's Axioms
Functional Dependency (FD) is a core concept in relational database theory, describing
the relationship between attributes in a relation (table). An attribute or set of attributes $A$
is said to functionally determine another attribute or set of attributes $B$ (written as $A
\rightarrow B$) if, for any two tuples (rows) in the relation, having the same value for $A$
implies having the same value for $B$. FDs are classified as Trivial if the determined set of
attributes ($B$) is a subset of the determining set ($A$), and Non-Trivial otherwise. Non-
trivial FDs are the key drivers for the normalization process.
The set of all functional dependencies that can be logically inferred from a given set $F$ is
called the Closure of F ($F^+$). This closure is calculated using Armstrong's Axioms, a set
of sound and complete inference rules: Reflexivity (if $Y \subseteq X$, then $X \rightarrow
Y$), Augmentation (if $X \rightarrow Y$, then $XZ \rightarrow YZ$), and Transitivity (if $X
\rightarrow Y$ and $Y \rightarrow Z$, then $X \rightarrow Z$). These axioms form the basis
for deriving other useful rules like Decomposition and Union, which are critical for
analyzing and decomposing relations during database design.
Axiom Rule Description
Reflexivity If $Y \subseteq X$, then $X If $Y$ is a subset of $X$, then
\rightarrow Y$ $X$ determines $Y$.
 If $X \rightarrow Y$, then $XZ Adding the same attributes to
Augmentation \rightarrow YZ$ both sides of a dependency
maintains the dependency.
If $X \rightarrow Y$ and $Y Dependencies can be chained
Transitivity \rightarrow Z$, then $X together.
\rightarrow Z$

1.2 Algorithms and Normalization

Normalization is a systematic process of decomposing a relation into smaller, well-
structured relations to minimize redundancy and eliminate data anomalies, such as
insertion, deletion, and update anomalies. The primary goals of normalization are to
eliminate redundancy, ensure data integrity, and ultimately improve query efficiency
by creating smaller, more focused tables.
The process of replacing a relation $R$ with multiple relations $R_1, R_2, \dots, R_n$ is
called Decomposition. For a decomposition to be considered valid, it must satisfy two
crucial properties: Lossless-Join Decomposition, which ensures that the natural join of
the decomposed relations yields the original relation without loss or spurious generation of
data, and Dependency-Preserving Decomposition, which ensures that all functional
dependencies of the original relation can be enforced by simply enforcing the
dependencies in the new, smaller relations.
1.3 Normal Forms (NFs)
Normal forms are a series of progressive guidelines used to determine the quality of a
database design. Each normal form addresses specific types of data anomalies:
• First Normal Form (1NF): Requires that all attribute values must be atomic
(indivisible), eliminating multi-valued attributes and nested relations.
• Second Normal Form (2NF): Requires the relation to be in 1NF, and all non-key
attributes must be fully functionally dependent on the primary key. This form
addresses anomalies caused by partial dependencies, which only occur with composite
keys.
• Third Normal Form (3NF): Requires the relation to be in 2NF, and prohibits any
transitive dependency, meaning no non-key attribute can be functionally dependent
on another non-key attribute.
• Boyce-Codd Normal Form (BCNF): A stricter version of 3NF, requiring that every
determinant (an attribute or set of attributes that determines another attribute) must
be a candidate key. BCNF is generally the desired level of normalization for most
 commercial database designs, as it resolves anomalies related to overlapping
candidate keys.
1.4 Integrity Constraints
Integrity constraints are rules that restrict the values that can be entered into the database
to ensure the data remains accurate and consistent.
The Basic Relational Integrity Constraints include Entity Integrity, which mandates that
the primary key of a relation must not contain any null values to ensure unique tuple
identification; Referential Integrity, which maintains consistency between related tables
by requiring that a foreign key value must either be null or match an existing primary key
value in the referenced table; and Domain Integrity, which ensures all values in a column
are drawn from the same defined set of allowed values.
Constraints can also be categorized by when they are checked. Static Constraints (e.g.,
Primary Key, Foreign Key, NOT NULL) must hold for every valid state of the database and
are checked at the time of data modification. In contrast, Dynamic Constraints (or
Transition Constraints) specify rules for valid state transitions, defining how the database
state can change over time. These are often implemented using database triggers or
application-level logic, such as a rule stating that an employee's salary can only increase.

Module 2: Database Administration

Database Administration (DBA) encompasses the set of activities and responsibilities
involved in managing and maintaining a database system to ensure its availability,
performance, security, and integrity.
2.1 Physical Implementation and File/Index Structure
The Physical Implementation layer dictates how data is stored on physical devices. The
DBMS manages the Storage Hierarchy, moving data between fast main memory (RAM) and
slower secondary storage (disk) in fixed-size units called pages or blocks. The File
Organization method, such as Heap Files, Sequential Files, or Hashed Files, determines
how records are physically stored and accessed.
An Index is a critical data structure that significantly improves the speed of data retrieval.
Indexes are classified as Primary (on the primary key), Secondary (on a non-key field), or
Clustering (where the physical order matches the index order). Modern DBMSs
predominantly use B-Tree and B+-Tree structures for indexing due to their efficiency in
range queries and consistent performance.
2.2 Control of Concurrent Access
Concurrency Control is vital for multi-user environments, ensuring that simultaneous
operations do not interfere with one another, thereby maintaining data consistency. A
Transaction, the single logical unit of work, must adhere to the ACID properties: Atomicity
(all or nothing), Consistency (maintaining valid state), Isolation (non-interference), and
Durability (permanence of committed changes).
Concurrency is managed through various Techniques, including Locking Protocols (such
as Two-Phase Locking - 2PL), where transactions acquire and release locks on data items;
Timestamp-Based Protocols, which order transactions by their timestamps; and
Validation-Based Protocols (Optimistic Concurrency Control), where transactions proceed
without locking and are validated before commitment.
2.3 Breakdown Resistance and Restoration
Breakdown Resistance (or Database Recovery) refers to the mechanisms that restore the
database to a consistent state after a system failure. The DBMS maintains a Log/Journal to
record all transaction operations. Checkpoints are periodically created to reduce recovery
time by ensuring modified data blocks are written to disk.
Recovery is achieved using techniques like Deferred Update or Immediate Update, and
the UNDO/REDO mechanism, which uses the log to roll back uncommitted transactions
and reapply committed ones. Restoration involves loading a consistent backup and
applying the transaction log to bring the database to the point of failure.
2.4 Security and Protection of Data
Database security protects the database from unauthorized access, modification, or
destruction. This is achieved through Authentication (verifying user identity),
Authorization (specifying user privileges via GRANT and REVOKE ), and robust Access
Control mechanisms. Additional layers of protection include Data Encryption for data at
rest and in transit, and comprehensive Auditing to track all database activities for security
analysis and compliance.
2.5 Parameter Setting, Start, Stop, Save, Restoration
These are the core operational tasks of a Database Administrator. Parameter Setting
involves tuning the DBMS configuration (e.g., buffer size, connection limits) for optimal
performance. Start/Stop procedures manage the database server's lifecycle, with a
graceful shutdown being essential to ensure data integrity. Save refers to creating
consistent Backups (full, differential, or incremental), which are then used in the
Restoration process to recover the database after a failure.
2.6 Distributed Database and Processing
A Distributed Database System (DDBS) involves a logically interrelated collection of
databases spread across a network. This requires managing the Distributed Database
through Data Fragmentation (dividing relations) and Data Replication (copying data for
availability). Distributed Processing ensures that transactions maintain ACID properties
across multiple sites, often utilizing protocols like Two-Phase Commit (2PC), and
optimizes Distributed Query Processing to minimize network communication costs.
2.7 Auditing and Optimization
Auditing is the systematic recording and review of database activities, which is essential for
security, compliance, and forensic analysis. This involves maintaining detailed audit logs of
events like logins, DDL/DML statements, and privilege changes.
Optimization (Performance Tuning) focuses on improving the efficiency of database
operations. This includes Query Optimization, where the DBMS selects the best execution
plan; strategic Indexing; and sometimes controlled Denormalization to boost read
performance. Furthermore, DBAs perform Parameter Tuning and Storage Optimization to
manage disk space and data placement effectively.

References
[1] Functional Dependencies And Normalization For Relational Databases. (Source:
[Link]
[Link]/[Link]/u10A57/242009/Functional%20Dependencies%20And%20Nor
malization%20For%20Relational%[Link] )
[2] Fundamentals Of Relational Database Design. (Source:
[Link]
files/wfLdoY/0OK016/[Link] )
[3] Database Management Systems Lecture Notes. (Source:
[Link] )
[4] Concurrency Control in Distributed Database Systems. (Source:
[Link] )
[5] Database Auditing: Best Practices for Securing Your Data. (Source:
[Link] )
[6] 11 Database Optimization Techniques. (Source: [Link]
database-optimization-techniques-97fdbed1b627 )
[7] Database systems: Volume 1. (Source:
[Link] )