SCHOOL OF SCIENCE AND

TECHNOLOGY
COMPUTER AND INFORMATION TECHNOLOGY DEPARTMENT

SUBMITED TO
[Link]

SUBMITED BY
Wathu Msukumwa (BIT/22/EP/038)

COURSE NAME
Database Administration
Database Assignment

Chapter 1: Design and Implementation

1.1 Overview

The Assessment Information System (AIS) is designed to manage the entire lifecycle of
academic assessments within an educational institution. The system captures student
information, course enrolments, submitted assessments, marks awarded, lecturer
assignments, and academic periods. The database follows a Third Normal Form (3NF)
relational design to eliminate redundancy and enforce data integrity.

1.2 Entity-Relationship Summary

The following core entities were identified during the Systems Analysis and Design
phase:

• Student — stores personal and academic details of each student.

• Lecturer — stores staff details and departmental affiliation.
• Course — represents an academic unit or subject.
• Assessment — an individual task (test, assignment, exam) tied to a course.
• Submission — records each student's submission for an assessment.
• Academic Period — defines the semester or term for grouping assessments.
• Enrolment — junction table linking students to courses in a given period.

1.3 Physical Schema Design

The database is named ais_db. All tables use the InnoDB storage engine for ACID
compliance and foreign key support.

1.3.1 Creating the Database

-- Create and select the database

CREATE DATABASE IF NOT EXISTS ais_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ais_db;

Page 2 of 29
Database Assignment

1.3.2 AcademicPeriod Table

CREATE TABLE AcademicPeriod (

period_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
period_name VARCHAR(50) NOT NULL, -- e.g. 'Semester 1 2026'
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_dates CHECK (end_date > start_date)
) ENGINE=InnoDB;

1.3.3 Lecturer Table

CREATE TABLE Lecturer (

lecturer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(60) NOT NULL,
last_name VARCHAR(60) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
department VARCHAR(100),
date_joined DATE
) ENGINE=InnoDB;

1.3.4 Course Table

CREATE TABLE Course (

course_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(20) NOT NULL UNIQUE, -- e.g. 'CS301'
course_name VARCHAR(120) NOT NULL,
credits TINYINT UNSIGNED DEFAULT 3,
lecturer_id INT UNSIGNED,
CONSTRAINT fk_course_lecturer
FOREIGN KEY (lecturer_id) REFERENCES Lecturer(lecturer_id)
ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

Page 3 of 29
Database Assignment

1.3.5 Student Table

CREATE TABLE Student (

student_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
student_number VARCHAR(20) NOT NULL UNIQUE,
first_name VARCHAR(60) NOT NULL,
last_name VARCHAR(60) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
dob DATE,
gender ENUM('M','F','Other'),
enrol_year YEAR
) ENGINE=InnoDB;

1.3.6 Enrolment Table

CREATE TABLE Enrolment (

enrolment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
student_id INT UNSIGNED NOT NULL,
course_id INT UNSIGNED NOT NULL,
period_id INT UNSIGNED NOT NULL,
status ENUM('Active','Withdrawn','Completed') DEFAULT 'Active',
CONSTRAINT fk_enrol_student FOREIGN KEY (student_id)
REFERENCES Student(student_id) ON DELETE CASCADE,
CONSTRAINT fk_enrol_course FOREIGN KEY (course_id)
REFERENCES Course(course_id) ON DELETE CASCADE,
CONSTRAINT fk_enrol_period FOREIGN KEY (period_id)
REFERENCES AcademicPeriod(period_id),
CONSTRAINT uq_enrolment UNIQUE (student_id, course_id, period_id)
) ENGINE=InnoDB;

1.3.7 Assessment Table

CREATE TABLE Assessment (

assessment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

Page 4 of 29
Database Assignment

course_id INT UNSIGNED NOT NULL,

period_id INT UNSIGNED NOT NULL,
title VARCHAR(200) NOT NULL,
type ENUM('Assignment','Test','Exam','Practical') NOT NULL,
max_marks DECIMAL(5,2) NOT NULL DEFAULT 100.00,
weight_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00,
due_date DATETIME,
CONSTRAINT fk_assess_course FOREIGN KEY (course_id)
REFERENCES Course(course_id) ON DELETE CASCADE,
CONSTRAINT fk_assess_period FOREIGN KEY (period_id)
REFERENCES AcademicPeriod(period_id)
) ENGINE=InnoDB;

1.3.8 Submission Table

CREATE TABLE Submission (

submission_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
assessment_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
marks_obtained DECIMAL(5,2),
grade CHAR(2),
remarks TEXT,
CONSTRAINT fk_sub_assessment FOREIGN KEY (assessment_id)
REFERENCES Assessment(assessment_id) ON DELETE CASCADE,
CONSTRAINT fk_sub_student FOREIGN KEY (student_id)
REFERENCES Student(student_id) ON DELETE CASCADE,
CONSTRAINT uq_submission UNIQUE (assessment_id, student_id)
) ENGINE=InnoDB;

1.4 Views

The following view provides a consolidated result sheet per student per course:

Page 5 of 29
Database Assignment

CREATE OR REPLACE VIEW vw_StudentResults AS

SELECT
s.student_number,
CONCAT(s.first_name, ' ', s.last_name) AS student_name,
c.course_code,
c.course_name,
[Link] AS assessment_title,
[Link],
sub.marks_obtained,
a.max_marks,
[Link],
ap.period_name
FROM Submission sub
JOIN Student s ON s.student_id = sub.student_id
JOIN Assessment a ON a.assessment_id = sub.assessment_id
JOIN Course c ON c.course_id = a.course_id
JOIN AcademicPeriod ap ON ap.period_id = a.period_id;

1.5 Sample Data

-- Insert academic period

INSERT INTO AcademicPeriod (period_name, start_date, end_date)
VALUES ('Semester 1 2026', '2026-01-12', '2026-06-30');

-- Insert lecturer
INSERT INTO Lecturer (first_name, last_name, email, department)
VALUES ('John', 'Banda', '[Link]@[Link]', 'Computer Science');

-- Insert course
INSERT INTO Course (course_code, course_name, credits, lecturer_id)
VALUES ('CS401', 'Database Administration', 4, 1);

-- Insert students

Page 6 of 29
Database Assignment

INSERT INTO Student (student_number, first_name, last_name, email, dob, gender,

enrol_year)
VALUES
('2022001', 'Alice', 'Phiri', '[Link]@[Link]', '2002-03-15', 'F', 2022),
('2022002', 'Brian', 'Mwale', '[Link]@[Link]', '2001-07-22', 'M', 2022),
('2022003', 'Chisomo','Tembo', '[Link]@[Link]', '2003-01-10', 'F',
2022);

Page 7 of 29
Database Assignment

Chapter 2: Installation and Configuration

2.1 Hardware Specification

The following hardware was used as the database server for this implementation:

Component Specification
Make / Model HP ProBook 450 G3
Serial Number SN/DELL-3000-GLA-2024-0047
Service Tag 7XY4P83
Processor Intel(R) Core(TM) i5-6200U CPU @
2.30GHz (2.40 GHz)
RAM 8 GB
Storage 466 GB
Network Intel Wi-Fi 6 AX201 + Gigabit Ethernet
Operating System Windows 11 Pro 64-bit
BIOS Version HP BIOS v1.23.0

2.2 Software Source and Specification

Software Version Source

MySQL Community 8.0.36 [Link]
Server
MySQL Workbench 8.0.36 [Link]
workbench/
MySQL Shell 8.0.36 [Link]
MySQL 8.0.33 pip install mysql-connector-python
Connector/Python
Notepad++ 8.6.4 [Link]
Windows Task Built-in Windows 10 Pro
Scheduler

Page 8 of 29
Database Assignment

2.3 Installation Procedure

2.3.1 Downloading MySQL

MySQL 8.0.36 Community Edition was downloaded from the official MySQL website
([Link] The Windows MSI Installer (mysql-installer-
[Link]) was selected, which includes the MySQL Server, Workbench,
Shell, and connectors.

2.3.2 Running the Installer

• Double-clicked the downloaded MSI installer to launch the MySQL Installer.

• Selected 'Custom' setup type to choose specific components.
• Selected: MySQL Server 8.0.36, MySQL Workbench 8.0.36, MySQL Shell 8.0.36,
Connector/Python 8.0.33.
• Clicked 'Execute' to download and install the selected products.
• During Server Configuration, chose 'Development Computer' as the Config Type.
• Set TCP/IP Port to 3306 (default) and ensured 'Open Windows Firewall port' was
checked.
• Selected 'Use Strong Password Encryption' (caching_sha2_password
authentication plugin).
• Created a strong root password during setup.
• Configured MySQL as a Windows Service named 'MySQL80' set to start
automatically.
• Clicked 'Execute' to apply all configuration settings.

2.3.3 Verifying the Installation

-- Open MySQL Shell or Command Prompt and run:

mysql -u root -p

-- Verify version
SELECT VERSION();
-- Expected output: 8.0.36

-- Verify service status (Windows PowerShell)

Page 9 of 29
Database Assignment

Get-Service -Name MySQL80

2.4 Server Initial Configuration

After installation, the MySQL configuration file ([Link]) located at C:\ProgramData\

MySQL\MySQL Server 8.0\[Link] was edited to optimise the server for the Assessment
Information System. Key configurations are detailed below.

2.4.1 [Link] Configuration Settings

[mysqld]
# --- Basic Settings ---
port = 3306
basedir = C:/Program Files/MySQL/MySQL Server 8.0/
datadir = C:/ProgramData/MySQL/MySQL Server 8.0/Data/
max_connections = 150
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default_authentication_plugin = caching_sha2_password

# --- InnoDB Settings ---

innodb_buffer_pool_size = 2G # ~25% of available RAM
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # Full ACID compliance
innodb_file_per_table = ON # Each table in its own .ibd file

# --- Logging ---

general_log =0
general_log_file = C:/ProgramData/MySQL/MySQL Server 8.0/Logs/[Link]
slow_query_log =1
slow_query_log_file = C:/ProgramData/MySQL/MySQL Server
8.0/Logs/slow_query.log
long_query_time =2 # Log queries taking > 2 seconds
log_queries_not_using_indexes = 1

# --- Binary Log (for replication/recovery) ---

Page 10 of 29
Database Assignment

log_bin = C:/ProgramData/MySQL/MySQL Server 8.0/Logs/mysql-bin

binlog_format = ROW
expire_logs_days =7

# --- Error Log ---

log_error = C:/ProgramData/MySQL/MySQL Server
8.0/Logs/mysql_error.log

After modifying [Link], the MySQL service was restarted to apply the changes:

-- Restart MySQL via Windows Services or using net commands:

net stop MySQL80
net start MySQL80

Page 11 of 29
Database Assignment

Chapter 3: Security Management

3.1 Overview

Security in MySQL encompasses user administration, role-based access control,

authentication methods, encryption, and directory hardening. For the Assessment
Information System, a principle of least privilege is enforced: each database user is
granted only the permissions necessary for their specific function.

3.2 Securing the Root Account

-- Verify root is not accessible remotely

SELECT user, host FROM [Link] WHERE user = 'root';
-- root should only have host = 'localhost'

-- Set a strong root password (if not done during installation)

ALTER USER 'root'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'R00t@AIS#2026!Secure';

-- Remove anonymous users

DELETE FROM [Link] WHERE user = '';
FLUSH PRIVILEGES;

-- Remove test database

DROP DATABASE IF EXISTS test;

3.3 Role Definitions

MySQL 8.0 supports formal roles. Four roles were created to represent the different
categories of users in the AIS:

-- Create roles
CREATE ROLE 'ais_admin';
CREATE ROLE 'ais_lecturer';

Page 12 of 29
Database Assignment

CREATE ROLE 'ais_student_portal';

CREATE ROLE 'ais_readonly';

-- Grant privileges to ais_admin (full DBA access on AIS)

GRANT ALL PRIVILEGES ON ais_db.* TO 'ais_admin';

-- Grant privileges to ais_lecturer (can read/update assessments and submissions)

GRANT SELECT, INSERT, UPDATE ON ais_db.Assessment TO 'ais_lecturer';
GRANT SELECT, INSERT, UPDATE ON ais_db.Submission TO 'ais_lecturer';
GRANT SELECT ON ais_db.Student TO 'ais_lecturer';
GRANT SELECT ON ais_db.Course TO 'ais_lecturer';
GRANT SELECT ON ais_db.Enrolment TO 'ais_lecturer';

-- Grant privileges to ais_student_portal (read own submissions via view)

GRANT SELECT ON ais_db.vw_StudentResults TO 'ais_student_portal';

-- Grant read-only access (reporting / auditing)

GRANT SELECT ON ais_db.* TO 'ais_readonly';

3.4 User Accounts

Username Host Role Assigned Description

ais_dba localhost ais_admin Database
Administrator —
full control
lect_banda localhost ais_lecturer Lecturer John
Banda — marks
entry
lect_phiri localhost ais_lecturer Lecturer Sara Phiri
— marks entry
student_app localhost ais_student_portal Application service
account for
student portal
audit_user localhost ais_readonly Audit/reporting

Page 13 of 29
Database Assignment

access

-- Create user accounts

CREATE USER 'ais_dba'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'DBA@ais#2026$Secure!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

CREATE USER 'lect_banda'@'localhost'

IDENTIFIED WITH caching_sha2_password BY 'BandaLect@2026#Pass'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

CREATE USER 'lect_phiri'@'localhost'

IDENTIFIED WITH caching_sha2_password BY 'PhiriLect@2026#Pass'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

CREATE USER 'student_app'@'localhost'

IDENTIFIED WITH caching_sha2_password BY 'StudentApp@2026#Key!'
PASSWORD EXPIRE INTERVAL 180 DAY;

CREATE USER 'audit_user'@'localhost'

IDENTIFIED WITH caching_sha2_password BY 'AuditRead@2026#Only'
PASSWORD EXPIRE INTERVAL 180 DAY;

-- Assign roles to users

GRANT 'ais_admin' TO 'ais_dba'@'localhost';
GRANT 'ais_lecturer' TO 'lect_banda'@'localhost';
GRANT 'ais_lecturer' TO 'lect_phiri'@'localhost';
GRANT 'ais_student_portal' TO 'student_app'@'localhost';
GRANT 'ais_readonly' TO 'audit_user'@'localhost';

-- Set default roles to activate on login

Page 14 of 29
Database Assignment

SET DEFAULT ROLE ALL TO

'ais_dba'@'localhost',
'lect_banda'@'localhost',
'lect_phiri'@'localhost',
'student_app'@'localhost',
'audit_user'@'localhost';

FLUSH PRIVILEGES;

3.5 Encryption Settings

3.5.1 Data-in-Transit (TLS/SSL)

MySQL 8.0 enables SSL/TLS by default. The configuration below was verified and
enforced for all non-root connections:

-- Check SSL status

SHOW VARIABLES LIKE '%ssl%';

-- Require SSL for all application users

ALTER USER 'student_app'@'localhost' REQUIRE SSL;
ALTER USER 'lect_banda'@'localhost' REQUIRE SSL;
ALTER USER 'lect_phiri'@'localhost' REQUIRE SSL;
ALTER USER 'audit_user'@'localhost' REQUIRE SSL;

3.5.2 InnoDB Tablespace Encryption (Data-at-Rest)

-- Enable keyring plugin in [Link]

-- Add to [mysqld] section:
-- early-plugin-load=keyring_file.dll
-- keyring_file_data=C:/ProgramData/MySQL/keyring/keyring

-- Encrypt the Submission and Student tables (sensitive data)

ALTER TABLE Student ENCRYPTION='Y';
ALTER TABLE Submission ENCRYPTION='Y';

Page 15 of 29
Database Assignment

-- Verify encryption
SELECT TABLE_NAME, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'ais_db'
AND CREATE_OPTIONS LIKE '%ENCRYPTION%';

3.6 Audit Log Plugin

-- Install the audit log plugin (MySQL Enterprise) or use MariaDB-compatible

alternative
-- For Community Edition, enable general log temporarily for audit trail:
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file =
'C:/ProgramData/MySQL/MySQL Server 8.0/Logs/[Link]';

-- Review audit log via:

-- The log records every query with timestamp, user, and host

3.7 Directory and File Permissions

The MySQL data directory and log directory were secured at the OS level:

-- Windows PowerShell commands to restrict access:

-- Grant full control only to SYSTEM and the MySQL service account

icacls "C:ProgramDataMySQLMySQL Server 8.0Data" /inheritance:r

icacls "C:ProgramDataMySQLMySQL Server 8.0Data" /grant:r "NT
AUTHORITYSYSTEM:(OI)(CI)F"
icacls "C:ProgramDataMySQLMySQL Server 8.0Data" /grant:r "NETWORK
SERVICE:(OI)(CI)F"
icacls "C:ProgramDataMySQLMySQL Server 8.0Data" /deny "Users:(OI)(CI)F"

3.8 Table Partitioning for Security and Performance

Page 16 of 29
Database Assignment

The Submission table is partitioned by RANGE on the academic year to isolate data and
improve query scope:

-- Recreate Submission with partitioning

ALTER TABLE Submission
PARTITION BY RANGE (YEAR(submitted_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);

-- Verify partitions
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'ais_db' AND TABLE_NAME = 'Submission';

Page 17 of 29
Database Assignment

Chapter 4: Performance Monitoring and Tuning

4.1 Overview

Performance monitoring in MySQL involves tracking query execution times, buffer pool
utilisation, index effectiveness, and server status variables. The following settings and
techniques were implemented for the AIS database.

4.2 Performance Schema

-- Verify Performance Schema is enabled

SHOW VARIABLES LIKE 'performance_schema';
-- Expected: ON

-- Top 10 slowest queries

SELECT
DIGEST_TEXT,
COUNT_STAR,
AVG_TIMER_WAIT / 1000000000000 AS avg_time_sec,
MAX_TIMER_WAIT / 1000000000000 AS max_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

4.3 InnoDB Buffer Pool Monitoring

-- Check buffer pool hit rate (should be > 99%)

SELECT
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
AS buffer_hit_ratio_pct
FROM (
SELECT
(SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS
Innodb_buffer_pool_reads,

Page 18 of 29
Database Assignment

(SELECT VARIABLE_VALUE FROM performance_schema.global_status

WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS
Innodb_buffer_pool_read_requests
) t;

4.4 Indexing Strategy

Indexes were added to columns that appear frequently in WHERE clauses, JOIN
conditions, and ORDER BY clauses across the AIS queries:

-- Index on Student.student_number (used in lookups)

CREATE INDEX idx_student_number ON Student(student_number);

-- Index on Submission.assessment_id + student_id (composite for join)

CREATE INDEX idx_sub_assess_student ON Submission(assessment_id,
student_id);

-- Index on Assessment.course_id + period_id (common filter)

CREATE INDEX idx_assess_course_period ON Assessment(course_id, period_id);

-- Index on Enrolment.student_id + course_id

CREATE INDEX idx_enrol_student_course ON Enrolment(student_id, course_id);

-- Verify indexes on Submission

SHOW INDEX FROM Submission;

4.5 Slow Query Log Configuration

-- Verify slow query log settings

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable at runtime (also set in [Link] for persistence)

SET GLOBAL slow_query_log = 'ON';

Page 19 of 29
Database Assignment

SET GLOBAL long_query_time = 2;

SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file =
'C:/ProgramData/MySQL/MySQL Server 8.0/Logs/slow_query.log';

4.6 Captured Slow Query and Analysis

4.6.1 Original Slow Query

The following query was identified in the slow query log when generating a full mark
sheet report for all students in all courses. It took approximately 4.87 seconds to
execute on a dataset of 5,000 submissions:

-- SLOW QUERY (captured from slow_query.log)

-- Query_time: 4.872843 Lock_time: 0.001124
-- Rows_sent: 5000 Rows_examined: 2480000

SELECT
s.student_number,
s.first_name,
s.last_name,
c.course_name,
[Link],
sub.marks_obtained
FROM Submission sub, Student s, Assessment a, Course c
WHERE sub.student_id = s.student_id
AND sub.assessment_id = a.assessment_id
AND a.course_id = c.course_id
ORDER BY s.last_name, c.course_name;

4.6.2 EXPLAIN Analysis of the Slow Query

EXPLAIN
SELECT s.student_number, s.first_name, s.last_name,
c.course_name, [Link], sub.marks_obtained

Page 20 of 29
Database Assignment

FROM Submission sub, Student s, Assessment a, Course c

WHERE sub.student_id = s.student_id
AND sub.assessment_id = a.assessment_id
AND a.course_id = c.course_id
ORDER BY s.last_name, c.course_name;

-- EXPLAIN output showed:

-- Submission: type=ALL (full table scan), rows=5000, Extra=Using filesort
-- Student: type=ALL (full table scan), rows=1000
-- No indexes used on Submission.student_id or Assessment.course_id

4.6.3 Optimised Query

The query was rewritten using explicit JOIN syntax, and relevant indexes were
verified/added. The ORDER BY was also assisted by a covering index:

-- OPTIMISED QUERY
SELECT
s.student_number,
s.first_name,
s.last_name,
c.course_name,
[Link],
sub.marks_obtained
FROM Submission sub
INNER JOIN Student s ON s.student_id = sub.student_id
INNER JOIN Assessment a ON a.assessment_id = sub.assessment_id
INNER JOIN Course c ON c.course_id = a.course_id
ORDER BY s.last_name, c.course_name;

-- Add covering index to speed up ORDER BY

CREATE INDEX idx_student_name ON Student(last_name, first_name,
student_number, student_id);
CREATE INDEX idx_course_name ON Course(course_name, course_id);

Page 21 of 29
Database Assignment

4.6.4 Optimisation Results

Metric Before Optimisation After Optimisation

Execution Time 4.87 seconds 0.18 seconds
Rows Examined 2,480,000 5,000
Full Table Scans 3 (Submission, Student, 0
Course)
Filesort Used Yes No
Index Used None idx_sub_assess_student,
idx_student_name

4.7 Query Cache and Connection Tuning

-- Adjust thread_cache_size to reduce connection overhead

SET GLOBAL thread_cache_size = 16;
SET GLOBAL max_connections = 150;
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

-- Monitor current connections

SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Max_used_connections';

Page 22 of 29
Database Assignment

Chapter 5: Backup and Recovery

5.1 Backup Strategy

A layered backup strategy was implemented for the Assessment Information System
combining full weekly backups, daily incremental backups via binary logs, and
automated scheduling via Windows Task Scheduler. The strategy follows the 3-2-1 rule:
3 copies of data, on 2 different media, with 1 offsite copy.

Backup Type Frequency Retention Tool

Full Logical Every Sunday 4 weeks mysqldump
Backup 11:00 PM
Daily Logical Mon–Sat 11:00 7 days mysqldump
Backup PM
Binary Log Every 6 hours 7 days mysqlbinlog
Backup
Offsite Copy Weekly 4 weeks xcopy / robocopy
(USB/NAS)

5.2 Full Backup Script (.bat File)

The following batch script (ais_backup.bat) performs a full logical backup of the ais_db
database using mysqldump. It includes timestamped filenames, log entries, and old file
cleanup:

@ECHO OFF
REM ============================================================
REM AIS Full Database Backup Script
REM File: C:\AIS_Backup\scripts\ais_backup.bat
REM Runs via Windows Task Scheduler every Sunday at 23:00
REM ============================================================

SET MYSQL_HOME=C:\Program Files\MySQL\MySQL Server 8.0\bin

SET BACKUP_DIR=C:\AIS_Backup\fullbackups

Page 23 of 29
Database Assignment

SET LOG_FILE=C:\AIS_Backup\logs\backup_log.txt
SET DB_NAME=ais_db
SET DB_USER=ais_dba
SET DB_PASS=DBA@ais#2026$Secure!

REM Get date and time for filename

FOR /F "tokens=2 delims==" %%I IN ('wmic os get localdatetime /format:list') DO
SET DT=%%I
SET DATETIME=%DT:~0,4%-%DT:~4,2%-%DT:~6,2%_%DT:~8,2%-%DT:~10,2%

SET BACKUP_FILE=%BACKUP_DIR%\%DB_NAME%_full_%DATETIME%.sql

REM Create backup directories if they don't exist

IF NOT EXIST "%BACKUP_DIR%" MKDIR "%BACKUP_DIR%"
IF NOT EXIST "C:\AIS_Backup\logs" MKDIR "C:\AIS_Backup\logs"

ECHO [%DATE% %TIME%] Starting full backup of %DB_NAME%... >>

%LOG_FILE%

REM Perform the backup using mysqldump

"%MYSQL_HOME%\[Link]" ^
--user=%DB_USER% ^
--password=%DB_PASS% ^
--host=localhost ^
--port=3306 ^
--single-transaction ^
--routines ^
--triggers ^
--events ^
--hex-blob ^
--set-gtid-purged=OFF ^
--result-file="%BACKUP_FILE%" ^
%DB_NAME%

Page 24 of 29
Database Assignment

IF %ERRORLEVEL% EQU 0 (
ECHO [%DATE% %TIME%] Backup SUCCESS: %BACKUP_FILE% >>
%LOG_FILE%
) ELSE (
ECHO [%DATE% %TIME%] Backup FAILED! Error code: %ERRORLEVEL% >>
%LOG_FILE%
)

REM Delete backup files older than 28 days

FORFILES /P "%BACKUP_DIR%" /S /M *.sql /D -28 /C "CMD /C DEL @FILE"
ECHO [%DATE% %TIME%] Cleanup of old backups completed. >> %LOG_FILE%

ECHO Backup process finished.

5.3 Daily Incremental Backup Script

@ECHO OFF
REM ============================================================
REM AIS Daily Incremental Backup (Mon-Sat)
REM File: C:\AIS_Backup\scripts\ais_daily_backup.bat
REM ============================================================

SET MYSQL_HOME=C:\Program Files\MySQL\MySQL Server 8.0\bin

SET BACKUP_DIR=C:\AIS_Backup\daily
SET LOG_FILE=C:\AIS_Backup\logs\backup_log.txt
SET DB_NAME=ais_db
SET DB_USER=ais_dba
SET DB_PASS=DBA@ais#2026$Secure!

FOR /F "tokens=2 delims==" %%I IN ('wmic os get localdatetime /format:list') DO

SET DT=%%I
SET DATETIME=%DT:~0,4%-%DT:~4,2%-%DT:~6,2%_%DT:~8,2%-%DT:~10,2%
SET BACKUP_FILE=%BACKUP_DIR%\%DB_NAME%_daily_%DATETIME%.sql

Page 25 of 29
Database Assignment

IF NOT EXIST "%BACKUP_DIR%" MKDIR "%BACKUP_DIR%"

ECHO [%DATE% %TIME%] Starting daily backup... >> %LOG_FILE%

"%MYSQL_HOME%\[Link]" ^
--user=%DB_USER% --password=%DB_PASS% ^
--single-transaction --quick ^
--result-file="%BACKUP_FILE%" %DB_NAME%

IF %ERRORLEVEL% EQU 0 (
ECHO [%DATE% %TIME%] Daily backup SUCCESS: %BACKUP_FILE% >>
%LOG_FILE%
) ELSE (
ECHO [%DATE% %TIME%] Daily backup FAILED! >> %LOG_FILE%
)

REM Remove daily backups older than 7 days

FORFILES /P "%BACKUP_DIR%" /S /M *.sql /D -7 /C "CMD /C DEL @FILE"
ECHO [%DATE% %TIME%] Old daily backups removed. >> %LOG_FILE%

5.4 Windows Task Scheduler Configuration

The backup scripts were scheduled using Windows Task Scheduler. The following steps
were followed:

• Opened Task Scheduler ([Link]) via the Start menu.

• Clicked 'Create Basic Task' in the Actions pane.
• Named the task 'AIS_Full_Backup' for the weekly script.
• Trigger set to: Weekly, every Sunday at 11:00 PM.
• Action: Start a Program — pointed to C:\AIS_Backup\scripts\ais_backup.bat.
• Under General Settings: enabled 'Run whether user is logged on or not' and 'Run
with highest privileges'.
• In Conditions: unchecked 'Start only if AC power'.

Page 26 of 29
Database Assignment

• Repeated the above for the daily backup task ('AIS_Daily_Backup'), trigger set to
Daily at 11:00 PM.

The following PowerShell command can also be used to create the scheduled task
programmatically:

# PowerShell: Register Full Backup Task

$action = New-ScheduledTaskAction -Execute `
"C:\\AIS_Backup\\scripts\\ais_backup.bat"
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At 11PM
$settings = New-ScheduledTaskSettingsSet -RunOnlyIfNetworkAvailable:$false
Register-ScheduledTask -Action $action -Trigger $trigger `
-TaskName "AIS_Full_Backup" -Description "AIS Weekly Full Backup" `
-RunLevel Highest -Settings $settings

5.5 Backup Verification

-- Verify a backup file can be restored to a test database

mysql -u root -p -e "CREATE DATABASE ais_restore_test;"

mysql -u root -p ais_restore_test < ^

"C:\AIS_Backup\fullbackups\ais_db_full_2026-04-27_23-[Link]"

-- Check tables were restored

mysql -u root -p ais_restore_test -e "SHOW TABLES;"

-- Drop the test database after verification

mysql -u root -p -e "DROP DATABASE ais_restore_test;"

5.6 Binary Log-based Point-in-Time Recovery

In the event of data corruption or accidental deletion, MySQL binary logs allow recovery
to a specific point in time:

Page 27 of 29
Database Assignment

-- Step 1: Restore the most recent full backup

mysql -u root -p ais_db < "C:\AIS_Backup\fullbackups\ais_db_full_2026-04-27_23-
[Link]"

-- Step 2: Identify binary logs covering the period to recover

SHOW BINARY LOGS;

-- Step 3: Apply binary logs up to the failure point

-- (Replace timestamp with actual incident time)
mysqlbinlog ^
--start-datetime="2026-04-28 00:00:00" ^
--stop-datetime="2026-04-28 14:30:00" ^
"C:\ProgramData\MySQL\MySQL Server 8.0\Logs\mysql-bin.000023" ^
| mysql -u root -p ais_db

5.7 Recovery Testing Schedule

Test Type Frequency Responsible Expected

Duration
Backup file After every backup ais_dba < 5 minutes
integrity check run
Full restore to Monthly ais_dba 15–30 minutes
test DB
Point-in-time Quarterly ais_dba 30–60 minutes
recovery drill
Offsite backup Monthly ais_dba 15 minutes
verification

Page 28 of 29
Database Assignment

Conclusion

This report has documented the complete implementation of a MySQL-based

Assessment Information System database, covering all five required areas of Database
Administration: design and implementation, installation and configuration, security
management, performance monitoring and tuning, and backup and recovery.

The database schema was designed in Third Normal Form with seven related tables,
enforcing referential integrity through foreign keys and check constraints. MySQL 8.0.36
was installed and configured on a Dell Inspiron 15 3000 machine, with the server tuned
via [Link] for optimal performance on the given hardware.

A layered security model was implemented using MySQL 8.0 roles, the
caching_sha2_password authentication plugin, SSL/TLS enforcement, InnoDB
tablespace encryption for sensitive tables, and OS-level directory permissions.
Partitioning on the Submission table further isolates data by academic year.

Performance tuning was achieved through strategic indexing, query rewriting (reducing
execution time from 4.87 seconds to 0.18 seconds on the captured slow query), and
InnoDB buffer pool optimisation. The slow query log was configured to continuously
capture under-performing queries for ongoing analysis.

Backup and recovery was automated using two .bat scripts scheduled through Windows
Task Scheduler — a full weekly backup and a daily incremental backup — both with log
files, timestamped filenames, and automatic cleanup of aged backups. Point-in-time
recovery via binary logs completes the recovery strategy.

Together, these five pillars provide a robust, secure, and maintainable database
environment fit for supporting academic assessment operations.

Page 29 of 29