Training Management Database System: Training Providing Info
Training Management Database System A training providing institute is offering training courses to their clients
Develop a comprehensive database system for a training institute that offers various courses to clients. The system should facilitate registration, tracking, and management of courses, teachers, clients, and trainees. The project involves analyzing data requirements, designing an Entity-Relationship (ER) model, creating normalized schemas, implementing them with SQL, populating tables, and executing specific queries.
Paper For Above instruction
Introduction
The design and implementation of a robust training management database is essential for streamlining operations, enhancing data accuracy, and facilitating comprehensive reporting. The database should accurately capture the relationships between teachers, courses, clients, and trainees, ensuring data integrity and supporting operational queries efficiently. This paper outlines the process from conceptual design to final implementation, following best practices in relational database modeling and normalization.
Entity Identification
Based on the provided system requirements, the primary entities involved are:
- Teacher: Contains details about instructors who teach courses.
- Course: Represents the training courses offered by the institute.
- Client: The organizations or individuals who enroll trainees in courses.
- Trainee: The individuals undertaking training under a client.
Each entity has specific attributes and a unique identifier (primary key), critical for establishing relationships and ensuring data uniqueness.
Entity Attributes and Keys
| Entity Type | Attributes | Key |
|---|---|---|
| Teacher | teacher_id, name, date_of_birth, address (house_number, street_name, city, state), phone_numbers | teacher_id |
| Course | course_id, course_name, start_date, end_date, course_language, fee | course_id |
| Client | client_id, client_name, address, contact_person, contact_person_name | client_id |
| Trainee | trainee_id, first_name, last_name, phone_number, and foreign key: client_id | trainee_id |
Relationship Types and Cardinalities
Reflecting the system's Dynamics:
- Teaches: Connects Teacher and Course entities. A teacher can teach many courses, and a course can be taught by many teachers. Many-to-many (M:N).
- Offers: Connects Course and Client to indicate which courses are offered to which clients. Typically one course can be offered to many clients; each client can enroll in many courses. M:N.
- Enrolls: Connects Trainee and Course. Trainees may enroll in multiple courses, and each course can have multiple trainees. M:N.
- Trains: Connects Teacher and Course to indicate which teacher instructs which course instances. Depending on requirements, this could be modeled as a direct relationship or via a bridging entity.
Additional attributes such as number of courses taught by a teacher can be stored and managed via related attributes or via auxiliary tables.
ER Diagram
The ER diagram visually models entities and their relationships with cardinalities, including associative entities where necessary for managing many-to-many relationships.
Database Schemas Before Normalization
| Schema Name | Schema Attributes | Key |
|---|---|---|
| Teacher | teacher_id, name, date_of_birth, address, phone_numbers | teacher_id |
| Course | course_id, course_name, start_date, end_date, course_language, fee | course_id |
| Client | client_id, client_name, address, contact_person, contact_person_name | client_id |
| Trainee | trainee_id, first_name, last_name, phone_number, client_id | trainee_id |
| Teachings | teacher_id, course_id, number_of_courses_taught | teacher_id, course_id |
| Offers | course_id, client_id | course_id, client_id |
| Enrollments | trainee_id, course_id, fee_paid | trainee_id, course_id |
Schemas After Normalization up to 4NF
Normalization involves decomposing schemas to eliminate redundancies and dependencies, ensuring data integrity. The normalized schemas are designed to meet 4NF criteria, maintaining atomicity and independence of data:
- Personal details separated into Teacher and Trainee with contact information stored in related tables.
- Relationships modeled through associative tables with appropriate foreign keys.
- Address and phone numbers are handled via separate tables if multiple entries exist.
Example for normalized tables:
| Schema Name | Attributes | Key |
|---|---|---|
| Teacher | teacher_id, name, date_of_birth | teacher_id |
| Teacher_Address | address_id, house_number, street_name, city, state, teacher_id | address_id |
| Teacher_Phone | phone_id, phone_number, teacher_id | phone_id |
| Course | course_id, course_name, start_date, end_date, course_language, fee | course_id |
| Client | client_id, client_name, contact_person, contact_person_name | client_id |
| Client_Address | address_id, address, client_id | address_id |
| Trainee | trainee_id, first_name, last_name, phone_number, client_id | trainee_id |
SQL Table Creation Scripts
CREATE TABLE Teacher (
teacher_id INT PRIMARY KEY,
name VARCHAR(100),
date_of_birth DATE
);
CREATE TABLE Teacher_Address (
address_id INT PRIMARY KEY AUTO_INCREMENT,
house_number VARCHAR(10),
street_name VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);
CREATE TABLE Teacher_Phone (
phone_id INT PRIMARY KEY AUTO_INCREMENT,
phone_number VARCHAR(15),
teacher_id INT,
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);
CREATE TABLE Course (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
start_date DATE,
end_date DATE,
course_language VARCHAR(10),
fee DECIMAL(10, 2)
);
CREATE TABLE Client (
client_id INT PRIMARY KEY,
client_name VARCHAR(100),
contact_person VARCHAR(50),
contact_person_name VARCHAR(100)
);
CREATE TABLE Client_Address (
address_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(255),
client_id INT,
FOREIGN KEY (client_id) REFERENCES Client(client_id)
);
CREATE TABLE Trainee (
trainee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone_number VARCHAR(15),
client_id INT,
FOREIGN KEY (client_id) REFERENCES Client(client_id)
);
Populating Tables with Data
The following are example insert statements for each table, actioned with at least five rows.
INSERT INTO Teacher VALUES (1, 'John Doe', '1980-05-15');
INSERT INTO Teacher VALUES (2, 'Jane Smith', '1975-08-22');
INSERT INTO Teacher VALUES (3, 'Ahmed Ali', '1985-12-10');
INSERT INTO Teacher VALUES (4, 'Sara Ahmed', '1990-03-05');
INSERT INTO Teacher VALUES (5, 'Michael Brown', '1978-11-30');
INSERT INTO Teacher_Address (house_number, street_name, city, state, teacher_id) VALUES
('123', 'Main St', 'Riyadh', 'Riyadh', 1),
('456', 'First Ave', 'Jeddah', 'Makkah', 2),
('789', 'Second St', 'Dammam', 'Eastern', 3),
('321', 'Third Ave', 'Abha', 'Asir', 4),
('654', 'Fourth St', 'Khobar', 'Eastern', 5);
INSERT INTO Teacher_Phone (phone_number, teacher_id) VALUES
('0551234567', 1),
('0559876543', 1),
('0557654321', 2),
('0552468135', 3),
('0551357924', 4);
INSERT INTO Course VALUES (101, 'MS Word', '2023-07-01', '2023-07-10', 'English', 500.00);
INSERT INTO Course VALUES (102, 'MS Excel', '2023-07-05', '2023-07-15', 'English', 600.00);
INSERT INTO Course VALUES (103, 'Data Analysis', '2023-08-01', '2023-08-20', 'Arabic', 700.00);
INSERT INTO Course VALUES (104, 'Cyber Security', '2023-09-01', '2023-09-30', 'English', 800.00);
INSERT INTO Course VALUES (105, 'Networking Basics', '2023-07-20', '2023-07-30', 'Arabic', 450.00);
INSERT INTO Client VALUES (201, 'Saudi Electronic University', 'IT Department', 'Ali Al-Harbi');
INSERT INTO Client VALUES (202, 'King Fahd University', 'Training Dept.', 'Sara Al-Harith');
INSERT INTO Client VALUES (203, 'Al Rajhi Bank', 'Training Division', 'Hassan Al-Ahmari');
INSERT INTO Client VALUES (204, 'National Commercial Bank', 'HR Dept.', 'Noura AlQahtani');
INSERT INTO Client VALUES (205, 'GCC Telecom', 'Training Team', 'Faisal Al-Ghamdi');
INSERT INTO Trainee VALUES (301, 'Faisal', 'Al-Ghamdi', '0555678901', 201);
INSERT INTO Trainee VALUES (302, 'Laila', 'Al-Harbi', '0556789012', 202);
INSERT INTO Trainee VALUES (303, 'Khaled', 'Al-Otaibi', '0557890123', 203);
INSERT INTO Trainee VALUES (304, 'Mona', 'Al-Faisal', '0558901234', 204);
INSERT INTO Trainee VALUES (305, 'Yousef', 'Abdullah', '0559012345', 205);
Sample SQL Queries
- List of teachers who taught “MS Word” at least 2 times:
SELECT t.name, COUNT(*) AS times_taught
FROM Teacher AS t
JOIN Teaching AS te ON t.teacher_id = te.teacher_id
JOIN Course AS c ON te.course_id = c.course_id
WHERE c.course_name = 'MS Word'
GROUP BY t.teacher_id, t.name
HAVING COUNT(*) >= 2;
- List all course ids and names given since 1/1/2018:
SELECT course_id, course_name
FROM Course
WHERE start_date >= '2018-01-01';
- List all trainees who have taken “MS Word” and work for “Saudi Electronic University”:
SELECT t.first_name, t.last_name, c.client_name
FROM Trainee AS t
JOIN Client AS c ON t.client_id = c.client_id
JOIN Enrollment AS e ON t.trainee_id = e.trainee_id
JOIN Course AS c2 ON e.course_id = c2.course_id
WHERE c2.course_name = 'MS Word' AND c.client_name = 'Saudi Electronic University';
- Find all clients who have taken “MS Excel” course:
SELECT DISTINCT c.client_name
FROM Client AS c
JOIN Trainee AS t ON c.client_id = t.client_id
JOIN Enrollment AS e ON t.trainee_id = e.trainee_id
JOIN Course AS c2 ON e.course_id = c2.course_id
WHERE c2.course_name = 'MS Excel';
- Find all clients who have taken “MS Excel” and “MS Word”:
SELECT c.client_name
FROM Client AS c
JOIN Trainee AS t ON c.client_id = t.client_id
JOIN Enrollment AS e ON t.trainee_id = e.trainee_id
JOIN Course AS c2 ON e.course_id = c2.course_id
WHERE c2.course_name IN ('MS Excel', 'MS Word')
GROUP BY c.client_name
HAVING COUNT(DISTINCT c2.course_name) = 2;
Conclusion
This comprehensive design captures all functional requirements, enforces data integrity through normalization, and provides a structured approach for managing a training institute’s data. Using ER modeling, normalization to 4NF, and SQL implementation ensures the database is both efficient and easy to extend, supporting the organization’s operational and analytical needs.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Communications of the ACM, 13(6), 377–387.