//////////////////////////// common.php //////////////////////////// <?= e($title) ?> | ICT3612 Assessment 3
UNISA · ICT3612 · 2026

Source code used for this task

PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]); initialise_database($pdo); return $pdo; } function initialise_database(PDO $pdo) { $statements = [ "CREATE TABLE IF NOT EXISTS Modules ( ModuleCode VARCHAR(10) PRIMARY KEY, ModuleName VARCHAR(120) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS Lecturers ( LecturerID INT AUTO_INCREMENT PRIMARY KEY, LecturerName VARCHAR(120) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS Roles ( RoleID INT AUTO_INCREMENT PRIMARY KEY, RoleName VARCHAR(80) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS ModuleLecturerRole ( ModuleCode VARCHAR(10) NOT NULL, LecturerID INT NOT NULL, RoleID INT NOT NULL, Year SMALLINT NOT NULL, PRIMARY KEY (ModuleCode, LecturerID, RoleID, Year), CONSTRAINT fk_mlr_module FOREIGN KEY (ModuleCode) REFERENCES Modules(ModuleCode) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_mlr_lecturer FOREIGN KEY (LecturerID) REFERENCES Lecturers(LecturerID) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_mlr_role FOREIGN KEY (RoleID) REFERENCES Roles(RoleID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS organisations ( organisation_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(140) NOT NULL, registration_number VARCHAR(60) NOT NULL UNIQUE, contact_email VARCHAR(160) NOT NULL, phone VARCHAR(40) NOT NULL, address VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS users ( user_id INT AUTO_INCREMENT PRIMARY KEY, organisation_id INT NULL, username VARCHAR(60) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','organisation') NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_user_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(organisation_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS trainings ( training_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(140) NOT NULL, description VARCHAR(255) NOT NULL, training_date DATE NOT NULL, venue VARCHAR(140) NOT NULL, cost DECIMAL(10,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, organisation_id INT NOT NULL, employee_number VARCHAR(40) NOT NULL, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, email VARCHAR(160) NOT NULL, UNIQUE KEY uq_employee_number_org (organisation_id, employee_number), CONSTRAINT fk_employee_organisation FOREIGN KEY (organisation_id) REFERENCES organisations(organisation_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "CREATE TABLE IF NOT EXISTS registrations ( registration_id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, training_id INT NOT NULL, status ENUM('Registered','Completed','Cancelled') NOT NULL DEFAULT 'Registered', registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_employee_training (employee_id, training_id), CONSTRAINT fk_registration_employee FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE, CONSTRAINT fk_registration_training FOREIGN KEY (training_id) REFERENCES trainings(training_id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", ]; foreach ($statements as $statement) { $pdo->exec($statement); } seed_task2($pdo); seed_task4($pdo); } function seed_task2(PDO $pdo) { if ((int) $pdo->query('SELECT COUNT(*) FROM Modules')->fetchColumn() === 0) { $pdo->exec("INSERT INTO Modules (ModuleCode, ModuleName) VALUES ('ICT2613', 'Internet Programming'), ('ICT3611', 'Advanced Databases'), ('ICT3612', 'Advanced Internet Programming'), ('ICT3715', 'Information Security'), ('ICT3722', 'Human-Computer Interaction')"); } if ((int) $pdo->query('SELECT COUNT(*) FROM Lecturers')->fetchColumn() === 0) { $pdo->exec("INSERT INTO Lecturers (LecturerName) VALUES ('Dr Amina Jacobs'), ('Prof Kabelo Mokoena'), ('Dr Naledi Khumalo'), ('Ms Lerato Molefe'), ('Prof Sipho Dlamini'), ('Ms Thandi Ndlovu')"); } if ((int) $pdo->query('SELECT COUNT(*) FROM Roles')->fetchColumn() === 0) { $pdo->exec("INSERT INTO Roles (RoleName) VALUES ('Primary lecturer'), ('Secondary lecturer'), ('Internal moderator'), ('External moderator'), ('Critical reader')"); } if ((int) $pdo->query('SELECT COUNT(*) FROM ModuleLecturerRole')->fetchColumn() === 0) { $pdo->exec("INSERT INTO ModuleLecturerRole (ModuleCode, LecturerID, RoleID, Year) VALUES ('ICT2613', 1, 1, 2026), ('ICT3611', 2, 1, 2026), ('ICT3612', 3, 1, 2026), ('ICT3715', 4, 2, 2026), ('ICT3722', 5, 3, 2026), ('ICT3612', 2, 4, 2026)"); } } function seed_task4(PDO $pdo) { if ((int) $pdo->query('SELECT COUNT(*) FROM organisations')->fetchColumn() === 0) { $pdo->exec("INSERT INTO organisations (name, registration_number, contact_email, phone, address) VALUES ('Ubuntu Digital Solutions', '2020/145821/07', 'learning@ubuntudigital.co.za', '011 555 0182', '45 Rivonia Road, Sandton'), ('Cape Horizon Logistics', '2018/278411/07', 'hr@capehorizon.co.za', '021 555 0134', '12 Dock Road, Cape Town'), ('Mahlangu Health Group', '2019/304567/07', 'people@mahlanguhealth.co.za', '012 555 0199', '88 Francis Baard Street, Pretoria')"); } if ((int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn() === 0) { $insert = $pdo->prepare( 'INSERT INTO users (organisation_id, username, password_hash, role) VALUES (?, ?, ?, ?)' ); $insert->execute([null, 'admin21194440', password_hash('Admin@2026', PASSWORD_DEFAULT), 'admin']); $insert->execute([1, 'ubuntu_org', password_hash('Ubuntu@2026', PASSWORD_DEFAULT), 'organisation']); $insert->execute([2, 'cape_org', password_hash('Cape@2026', PASSWORD_DEFAULT), 'organisation']); $insert->execute([3, 'mahlangu_org', password_hash('Health@2026', PASSWORD_DEFAULT), 'organisation']); } if ((int) $pdo->query('SELECT COUNT(*) FROM trainings')->fetchColumn() === 0) { $pdo->exec("INSERT INTO trainings (title, description, training_date, venue, cost) VALUES ('Cybersecurity Essentials', 'Practical cyber hygiene, phishing prevention and incident reporting.', '2026-08-18', 'Johannesburg Learning Hub', 2850.00), ('Data Analytics with Power BI', 'From data preparation to interactive business dashboards.', '2026-09-03', 'Cape Town Learning Hub', 3400.00), ('People Leadership', 'Coaching, feedback and performance conversations for new managers.', '2026-09-16', 'Virtual classroom', 2200.00), ('Project Management Foundations', 'Planning, risk, quality and stakeholder communication.', '2026-10-07', 'Pretoria Learning Hub', 3150.00)"); } if ((int) $pdo->query('SELECT COUNT(*) FROM employees')->fetchColumn() === 0) { $pdo->exec("INSERT INTO employees (organisation_id, employee_number, first_name, last_name, email) VALUES (1, 'UDS-104', 'Ayanda', 'Maseko', 'ayanda.maseko@ubuntudigital.co.za'), (1, 'UDS-117', 'Jason', 'Pillay', 'jason.pillay@ubuntudigital.co.za'), (2, 'CHL-088', 'Zanele', 'Radebe', 'zanele.radebe@capehorizon.co.za'), (2, 'CHL-093', 'Megan', 'Adams', 'megan.adams@capehorizon.co.za'), (3, 'MHG-221', 'Thabo', 'Mahlangu', 'thabo.mahlangu@mahlanguhealth.co.za'), (3, 'MHG-236', 'Fatima', 'Ismail', 'fatima.ismail@mahlanguhealth.co.za')"); } if ((int) $pdo->query('SELECT COUNT(*) FROM registrations')->fetchColumn() === 0) { $pdo->exec("INSERT INTO registrations (employee_id, training_id, status) VALUES (1, 1, 'Registered'), (2, 2, 'Registered'), (3, 3, 'Registered'), (4, 1, 'Completed'), (5, 4, 'Registered'), (6, 3, 'Registered')"); } } //////////////////////////// task2.php //////////////////////////// prepare('INSERT INTO Modules (ModuleCode, ModuleName) VALUES (?, ?)'); $statement->execute([$code, $name]); $message = "Module {$code} was added successfully."; } if ($action === 'update_assignment') { $original = explode('|', $_POST['assignment'] ?? ''); if (count($original) !== 4) { throw new InvalidArgumentException('Choose a module lecturer role row to modify.'); } $newRole = filter_input(INPUT_POST, 'new_role_id', FILTER_VALIDATE_INT); $newYear = filter_input(INPUT_POST, 'new_year', FILTER_VALIDATE_INT); if (!$newRole || !$newYear || $newYear < 2020 || $newYear > 2100) { throw new InvalidArgumentException('Choose a role and enter a valid year.'); } $statement = $pdo->prepare( 'UPDATE ModuleLecturerRole SET RoleID = ?, Year = ? WHERE ModuleCode = ? AND LecturerID = ? AND RoleID = ? AND Year = ?' ); $statement->execute([ $newRole, $newYear, $original[0], (int) $original[1], (int) $original[2], (int) $original[3], ]); if ($statement->rowCount() === 0) { throw new RuntimeException('No row changed. Choose a different role or year.'); } $message = 'The module lecturer role row was updated successfully.'; } if ($action === 'delete_lecturer') { $lecturerId = filter_input(INPUT_POST, 'lecturer_id', FILTER_VALIDATE_INT); if (!$lecturerId) { throw new InvalidArgumentException('Choose a lecturer to delete.'); } $pdo->beginTransaction(); try { $related = $pdo->prepare('DELETE FROM ModuleLecturerRole WHERE LecturerID = ?'); $related->execute([$lecturerId]); $removedAssignments = $related->rowCount(); $statement = $pdo->prepare('DELETE FROM Lecturers WHERE LecturerID = ?'); $statement->execute([$lecturerId]); if ($statement->rowCount() !== 1) { throw new RuntimeException('The lecturer was not found.'); } $pdo->commit(); $message = "The lecturer and {$removedAssignments} related assignment(s) were deleted in one transaction."; } catch (Throwable $error) { $pdo->rollBack(); throw $error; } } } } catch (Throwable $error) { $message = $error->getMessage(); $messageType = 'error'; } $queries = [ 'order' => [ 'sql' => 'SELECT ModuleCode, ModuleName FROM Modules ORDER BY ModuleName ASC', 'caption' => 'Modules ordered alphabetically', ], 'like' => [ 'sql' => "SELECT LecturerID, LecturerName FROM Lecturers WHERE LecturerName LIKE 'Dr %'", 'caption' => 'Lecturers whose names begin with Dr', ], 'join' => [ 'sql' => 'SELECT m.ModuleCode, m.ModuleName, mlr.LecturerID, mlr.Year FROM Modules m INNER JOIN ModuleLecturerRole mlr ON m.ModuleCode = mlr.ModuleCode', 'caption' => 'Two-table inner join of modules and assignments', ], 'where' => [ 'sql' => "SELECT ModuleCode, LecturerID, RoleID, Year FROM ModuleLecturerRole WHERE Year = 2026 AND RoleID = 1", 'caption' => 'Primary lecturer assignments in 2026', ], 'max' => [ 'sql' => 'SELECT MAX(Year) AS LatestYear FROM ModuleLecturerRole', 'caption' => 'Latest year represented in the assignments', ], ]; $selectedQuery = $_GET['query'] ?? 'order'; if (!array_key_exists($selectedQuery, $queries)) { $selectedQuery = 'order'; } function task2_table($title, array $rows) { ?>

No rows to display.

Task 2

Modules, lecturers and roles

The four tables below are read directly from MySQL. All create, update and delete actions use prepared statements and database foreign keys to uphold referential integrity.

query('SELECT ModuleCode, ModuleName FROM Modules ORDER BY ModuleCode')->fetchAll()); task2_table('Lecturers', $pdo->query('SELECT LecturerID, LecturerName FROM Lecturers ORDER BY LecturerID')->fetchAll()); task2_table('Roles', $pdo->query('SELECT RoleID, RoleName FROM Roles ORDER BY RoleID')->fetchAll()); task2_table( 'ModuleLecturerRole', $pdo->query( 'SELECT mlr.ModuleCode, mlr.LecturerID, l.LecturerName, mlr.RoleID, r.RoleName, mlr.Year FROM ModuleLecturerRole mlr INNER JOIN Lecturers l ON l.LecturerID = mlr.LecturerID INNER JOIN Roles r ON r.RoleID = mlr.RoleID ORDER BY mlr.ModuleCode, mlr.Year' )->fetchAll() ); $roles = $pdo->query('SELECT RoleID, RoleName FROM Roles ORDER BY RoleName')->fetchAll(); $assignments = $pdo->query( 'SELECT mlr.*, l.LecturerName, r.RoleName FROM ModuleLecturerRole mlr JOIN Lecturers l ON l.LecturerID = mlr.LecturerID JOIN Roles r ON r.RoleID = mlr.RoleID ORDER BY mlr.ModuleCode' )->fetchAll(); $lecturersForDeletion = $pdo->query( 'SELECT LecturerID, LecturerName FROM Lecturers ORDER BY LecturerName' )->fetchAll(); ?>

Add a module

Modify ModuleLecturerRole

Delete a lecturer

The lecturer and any related ModuleLecturerRole rows are deleted in one transaction, so no orphaned foreign keys remain.

Five SELECT queries

Choose one query

$query): ?>
query($queries[$selectedQuery]['sql'])->fetchAll()); ?>

Database connection required

Enter the live Eduquest MySQL details in config.php, then reload this page. The schema and seed data are created automatically.