Below is a complete example using the MySQL CLI. It includes:
- Starting MySQL
- Creating the database
- Creating tables
- Inserting sample data
- Performing all the required operations
- Configuring replication (Master → Replica) in a client-server distributed architecture
Part A: University Course Enrollment Database
Step 1: Start MySQL
Windows
mysql -u root -p
Enter your password.
Linux
sudo systemctl start mysql
mysql -u root -p
Step 2: Create Database
CREATE DATABASE UniversityDB;
USE UniversityDB;
Step 3: Create Tables
Students
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Department VARCHAR(50)
);
Lecturers
CREATE TABLE LecturerDetails (
LecturerID INT PRIMARY KEY,
LecturerName VARCHAR(100),
Department VARCHAR(50)
);
Courses
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Department VARCHAR(50),
LecturerID INT,
FOREIGN KEY (LecturerID)
REFERENCES LecturerDetails(LecturerID)
);
Enrollments
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Marks DECIMAL(5,2),
Grade CHAR(2),
EnrollmentStatus VARCHAR(20),
FOREIGN KEY (StudentID)
REFERENCES Students(StudentID),
FOREIGN KEY (CourseID)
REFERENCES Courses(CourseID)
);
Step 4: Insert Sample Data
Students
INSERT INTO Students VALUES
(101,'Alice','Computer Science'),
(102,'Bob','Information Technology'),
(103,'Carol','Computer Science'),
(104,'David','Business');
Lecturers
INSERT INTO LecturerDetails VALUES
(1,'Dr Smith','Computer Science'),
(2,'Dr James','Information Technology'),
(3,'Dr Grace','Business');
Courses
INSERT INTO Courses VALUES
(201,'Database Systems','Computer Science',1),
(202,'Networking','Information Technology',2),
(203,'Accounting','Business',3);
Enrollments
INSERT INTO Enrollments VALUES
(1,101,201,'Semester 1',85,'A','Active'),
(2,102,202,'Semester 1',70,'B','Active'),
(3,103,201,'Semester 2',92,'A','Active'),
(4,104,203,'Semester 2',65,'C','Active'),
(5,101,202,'Semester 2',78,'B','Active');
Operations
1. Calculate Average Marks and Grade per Student
SELECT
s.StudentID,
s.StudentName,
AVG(e.Marks) AS AverageMarks,
CASE
WHEN AVG(e.Marks)>=80 THEN 'A'
WHEN AVG(e.Marks)>=70 THEN 'B'
WHEN AVG(e.Marks)>=60 THEN 'C'
WHEN AVG(e.Marks)>=50 THEN 'D'
ELSE 'F'
END AS OverallGrade
FROM Students s
JOIN Enrollments e
ON s.StudentID=e.StudentID
GROUP BY s.StudentID,s.StudentName;
2. Filter Enrollments by Department
SELECT
e.EnrollmentID,
s.StudentName,
c.CourseName,
c.Department
FROM Enrollments e
JOIN Students s
ON e.StudentID=s.StudentID
JOIN Courses c
ON e.CourseID=c.CourseID
WHERE c.Department='Computer Science';
Filter by Semester
SELECT *
FROM Enrollments
WHERE Semester='Semester 2';
3. Join with LecturerDetails to Generate Lecturer Performance Report
SELECT
l.LecturerName,
c.CourseName,
COUNT(e.StudentID) AS TotalStudents,
AVG(e.Marks) AS AverageMarks
FROM LecturerDetails l
JOIN Courses c
ON l.LecturerID=c.LecturerID
JOIN Enrollments e
ON c.CourseID=e.CourseID
GROUP BY
l.LecturerName,
c.CourseName;
4. Aggregate Enrollment Numbers per Course
SELECT
c.CourseName,
COUNT(e.StudentID) AS TotalEnrollments
FROM Courses c
LEFT JOIN Enrollments e
ON c.CourseID=e.CourseID
GROUP BY c.CourseName;
Aggregate per Department
SELECT
c.Department,
COUNT(e.StudentID) AS TotalEnrollments
FROM Courses c
JOIN Enrollments e
ON c.CourseID=e.CourseID
GROUP BY c.Department;
5. Update Grades After Exams
Example
UPDATE Enrollments
SET Grade='A'
WHERE EnrollmentID=2;
Update Enrollment Status
UPDATE Enrollments
SET EnrollmentStatus='Completed'
WHERE Semester='Semester 2';
View Updated Records
SELECT *
FROM Enrollments;
Part B: Configure Distributed Database Replication (Client-Server)
Suppose:
Master Server
192.168.1.10
Replica Server
192.168.1.20
On Master Server
Edit MySQL configuration
Linux
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add
server-id=1
log_bin=mysql-bin
binlog_do_db=UniversityDB
Restart
sudo systemctl restart mysql
Create Replication User
CREATE USER 'replica'@'%' IDENTIFIED BY 'password123';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
Check Master Status
SHOW MASTER STATUS;
Example
File: mysql-bin.000001
Position: 157
Save these values.
On Replica Server
Edit configuration
server-id=2
relay-log=relay-bin
Restart
sudo systemctl restart mysql
Configure Replica
CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='replica',
MASTER_PASSWORD='password123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=157;
Start Replication
START SLAVE;
(For MySQL 8.0.22 and later, the equivalent command is START REPLICA;.)
Check Status
SHOW SLAVE STATUS\G
(Or SHOW REPLICA STATUS\G on newer versions.)
Important fields should indicate successful replication:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Test Replication
On the Master Server:
USE UniversityDB;
INSERT INTO Students
VALUES(105,'Emily','Computer Science');
On the Replica Server:
SELECT * FROM Students;
Output should include:
105 Emily Computer Science
This confirms that the central UniversityDB database is successfully replicated from the master server to the remote replica in a client-server architecture.
