SQL EXAM: STUDENT MANAGEMENT SYSTEM

You are required to design and implement a database system to manage a student management
application. The system consists of the following six tables: Teacher, ClassRoom, Student,
Subject, Marks, and TeacherClass. Follow the requirements below in detail.

1. Database Design & ERD [5 marks]

Purpose of Each Table

●​ Teacher: Stores teacher information, including name, phone, email, and date of birth.
●​ ClassRoom: Stores details of classrooms, including the number of students, start date, and
end date.
●​ Student: Stores student details, including name, phone, email, gender, and associated
classroom.
●​ Subject: Stores subject information.
●​ Marks: Stores students' scores for each subject.
●​ TeacherClass: Tracks which teacher teaches which class.

1.1. Table: Teacher

Column Data Type Constraints

Id INT Primary key (auto-increment)

Name NVARCHAR(100) Not NULL

Phone VARCHAR(50) Not NULL

Email VARCHAR(100) Not NULL

BirthDay DATE Not NULL

1.2. Table: ClassRoom

Column Data Type Constraints

Id INT Primary key (auto-increment)

Name NVARCHAR(100) Not NULL

TotalStudent INT Default = 0

StartDate DATE Not NULL

EndDate DATE Not NULL

1.3. Table: TeacherClass
 Column Data Type Constraints

TeacherId INT Not NULL, foreign key references Teacher(Id)

ClassId INT Not NULL, foreign key references ClassRoom(Id)

StartDate DATE Not NULL, default = current date

EndDate DATE Not NULL

TimeSlotStart INT Not NULL, default = 8

TimeSlotEnd INT Not NULL

Composite Primary Key: (TeacherId, ClassId)

1.4. Table: Student

Column Data Type Constraints

Id INT Primary key (auto-increment)

Name NVARCHAR(150) Not NULL

Email VARCHAR(150) Not NULL

Phone VARCHAR(50) Not NULL

Address NVARCHAR(255)

Gender TINYINT Not NULL

BirthDay DATE Not NULL

ClassId INT Not NULL, foreign key references ClassRoom(Id)

1.5. Table: Subject

Column Data Type Constraints

Id INT Primary key (auto-increment)

Name NVARCHAR(100) Not NULL

1.6. Table: Marks

Column Data Type Constraints

StudentId INT Not NULL, foreign key references Student(Id)

SubjectId INT Not NULL, foreign key references Subject(Id)

 Score INT Not NULL

Composite Primary Key: (StudentId, SubjectId)

2. Data Insertion Requirements [2 marks]

Use SQL INSERT statements to add sample data. Order of insertion:

1.​ Teacher
2.​ ClassRoom
3.​ Student
4.​ Subject
5.​ Marks

Sample data:

-- 1) Insert sample Teacher data (5 records)

('Alice Johnson', '123456789', 'alice@example.com', '1985-05-10'),

('Bob Smith', '987654321', 'bob@example.com', '1980-08-20'),

('Charlie Brown', '456123789', 'charlie@example.com', '1978-02-15'),

('Diana Ross', '741852963', 'diana@example.com', '1990-06-25'),

('Evan Taylor', '369258147', 'evan@example.com', '1995-09-12');

-- 2) Insert sample ClassRoom data (5 records)

('Math101', 0, '2023-01-10', '2023-06-10'),

('Science102', 0, '2023-02-15', '2023-07-15'),

('History103', 0, '2023-03-20', '2023-08-20'),

('English104', 0, '2023-04-25', '2023-09-25'),

('Physics105', 0, '2023-05-30', '2023-10-30');

-- 3) Insert sample Student data (15 records)

('John Doe', 'john.doe@example.com', '1122334455', '123 Main St', 0, '2005-05-15', 1),

('Jane Doe', 'jane.doe@example.com', '2233445566', '456 Oak Ave', 1, '2004-08-22', 2),

('Sam Wilson', 'sam.wilson@example.com', '3344556677', '789 Pine Rd', 0, '2006-03-30', 3),

('Sara White', 'sara.white@example.com', '4455667788', '147 Elm St', 1, '2005-10-05', 4),

('Mark Black', 'mark.black@example.com', '5566778899', '258 Maple Dr', 0, '2007-02-14', 5);

-- 4) Insert sample Subject data (3 records)

('Mathematics'),

('Science'),

('History');

-- 5) Insert sample Marks data (20 records)

(1, 1, 9), (1, 2, 8), (1, 3, 7),

(2, 1, 6), (2, 2, 5), (2, 3, 8),

(3, 1, 7), (3, 2, 9), (3, 3, 6),

(4, 1, 5), (4, 2, 6), (4, 3, 7),

(5, 1, 8), (5, 2, 7), (5, 3, 9);

3. Data Query Requirements [5 marks]

Use SQL SELECT, DELETE, and UPDATE queries to meet the following requirements:

1.​ Retrieve the list of Students sorted ascending by Name, displaying: Id, Name, Email,
Phone, Address, Gender, BirthDay, Age.
2.​ Retrieve the list of Teachers including: Id, Name, Phone, Email, BirthDay, Age,
TotalClass (TotalClass is the number of classes each teacher is teaching).
3.​ Retrieve ClassRoom details: Id, Name, TotalStudent, StartDate, EndDate where StartDate
is in the year 2020.
4.​ Update the TotalStudent column in the ClassRoom table to match the total number of
students in each class.
5.​ Delete ClassRooms that started before 2020 and have TotalStudent = 0.

4. View Creation Requirements [2 marks]

1.​ View: v_getStudentInfo

○​ Retrieve Student details: Id, Name, Email, Phone, Address, Gender, BirthDay,
ClassName, MarksAvg
 ○​ MarksAvg classification:
■​ 0 < MarksAvg <= 5 → Weak
■​ 5 < MarksAvg < 7.5 → Average
■​ 7.5 <= MarksAvg <= 8 → Good
■​ MarksAvg > 8 → Excellent
2.​ View: v_getStudentMax
○​ Retrieve the list of Students with MarksAvg ≥ 7.5.

5. Stored Procedure Requirements [3 marks]

1.​ Stored Procedure: addStudentInfo

○​ Inserts a new Student (excluding the auto-increment Id).
2.​ Stored Procedure: getTeacherByTimeSlot
○​ Retrieves the list of Teachers available at a given TimeSlotStart and TimeSlotEnd
on a specific date.
○​ Columns: Id, Name, Phone, Email, TimeSlotStart, TimeSlotEnd

6. Trigger Requirements [2 marks]

1.​ Trigger: tr_Check_Student_age

○​ When inserting a Student, ensure that the student's age is at least 13.
○​ If not, prevent insertion and return an error message:​
“This student is too young to enroll in this program. Please enter another student.”
2.​ Trigger: tr_Update_TotalStudent
○​ After inserting a Student, update the TotalStudent column in the ClassRoom table.

7. Index Creation Requirement [1 mark]

Create an Index on Student(BirthDay) to optimize queries filtering by student age.