You will design and implement a course management system for an IT training center.

The system consists of 8 main tables: Instructor, Course, Student, Enrollment, Schedule,
Feedback, Category, and Certificate.

The project requirements include: database design, sample data insertion, writing queries,
creating views, stored procedures, triggers, and indexes.

1.Database Design and ERD [5 điểm]

1.1. Table Instructor

Column Data type Constraints

InstructorId INT Primary key (identity(1,1))

FullName NVARCHAR(100) NOT NULL

Email VARCHAR(100) NOT NULL

Phone VARCHAR(20) NOT NULL

BirthDate DATE NOT NULL

1.2. Table Course

Column Data type Constraints

CourseId INT Primary key (identity(1,1))

Title NVARCHAR(100) NOT NULL

Description NVARCHAR(255)

DurationInWeeks INT NOT NULL

InstructorId INT FOREIGN KEY → Instructor(InstructorId)

CategoryId INT FOREIGN KEY → Category(CategoryId)

StartDate DATE NOT NULL

EndDate DATE NOT NULL

1.3. Table Student
Column Data type Constraints

StudentId INT Primary key (identity(1,1))

FullName NVARCHAR(100) NOT NULL

Gender TINYINT NOT NULL (0: Male, 1: Female)

BirthDate DATE NOT NULL

Email VARCHAR(100) NOT NULL

Phone VARCHAR(20) NOT NULL

1.4. Table Enrollment

Column Data type Constraints

EnrollmentId INT Primary key (identity(1,1))

StudentId INT FOREIGN KEY → Student(StudentId)

CourseId INT FOREIGN KEY → Course(CourseId)

EnrollDate DATE DEFAULT GETDATE()

FinalScore FLOAT DEFAULT NULL

1.5. Table Schedule

Column Data type Constraints

ScheduleId INT Primary key (identity(1,1))

CourseId INT FOREIGN KEY → Course(CourseId)

DayOfWeek INT NOT NULL (1: Monday, ..., 7:

Sunday)

TimeSlotStart TIME NOT NULL

TimeSlotEnd TIME NOT NULL

1.6. Table Feedback

 Column Data type Constraints

FeedbackId INT Primary key (identity(1,1))

EnrollmentId INT FOREIGN KEY → Enrollment(EnrollmentId)

Rating INT NOT NULL (1–10)

Comment NVARCHAR(255) NULL

1.7. Table Category

Column Data type Constraints

CategoryId INT Primary key (identity(1,1))

CategoryName NVARCHAR(100) NOT NULL

Description NVARCHAR(255) NULL

1.8. Table Certificate

Column Data type Constraints

StudentId INT FOREIGN KEY → Student(StudentId)

CourseId INT FOREIGN KEY → Course(CourseId)

IssueDate DATE NOT NULL

CertificateCode VARCHAR( NOT NULL UNIQUE

50)

Composite Primary Key: (StudentId, SubjectId)

2. Data Insertion Requirements [2 marks]

-​ Insert 5 records for each table.
3. Use SQL SELECT, UPDATE, and DELETE queries to meet the following requirements:
[5 marks]

1.​ Retrieve the list of Students, sorted ascending by FullName, displaying the following
columns:​
StudentId, FullName, Email, Phone, Gender, BirthDate, Age.
 2.​ Retrieve the list of Instructors, displaying:​
InstructorId, FullName, Email, Phone, BirthDate, Age, CourseCount​
(CourseCount is the number of courses each instructor is assigned to.)
3.​ Retrieve all Courses where StartDate is in the year 2024.​
Display: CourseId, Title, StartDate, EndDate, DurationInWeeks.
4.​ Retrieve Course details including Instructor and Category names.​
Display: CourseId, Title, InstructorName, CategoryName, StartDate.
5.​ Retrieve all Enrollments, displaying:​
EnrollmentId, Student FullName, Course Title, EnrollDate, FinalScore.
6.​ Update the FinalScore in the Enrollment table:​
Set FinalScore = NULL for students who already have a Certificate for the same Course.
7.​ Delete Feedback records where the related Enrollment has FinalScore < 5.
8.​ Retrieve the list of Courses that have more than 3 students enrolled.​
Display: CourseId, Title, StudentCount.
9.​ Retrieve the list of Students who have NOT been issued any Certificate.​
Display: StudentId, FullName.
10.​Retrieve the average rating for each Course based on Feedback.​
Display: CourseId, Title, AverageRating​
(Only include courses with AverageRating ≥ 7).

4. View Creation Requirements [2 marks]

Create the following SQL views:

1.​ View: v_StudentPerformance​

Retrieve: StudentId, FullName, AverageScore, Classification​
Classification logic:
○​ AverageScore < 5 → 'Weak'
○​ 5 ≤ AverageScore < 7 → 'Average'
○​ 7 ≤ AverageScore < 8.5 → 'Good'
○​ ≥ 8.5 → 'Excellent'
2.​ View: v_CourseRating​
Retrieve: CourseId, Title, AverageRating​
Only include courses that have received feedback.
3.​ View: v_CertificateIssued​
Retrieve: StudentId, FullName, CourseId, Title, IssueDate​
Show all students who have been issued certificates.
4.​ View: v_EnrollmentDetails​
Retrieve: EnrollmentId, StudentName, CourseTitle, EnrollDate, FinalScore​
Join Student and Course tables for human-readable output.
5. Stored Procedure Requirements [3 marks]

Create the following stored procedures:

1.​ Procedure: addNewStudent​

Inputs: FullName, Gender, BirthDate, Email, Phone​
→ Insert new student into the Student table.
2.​ Procedure: getAvailableInstructors​
Inputs: DayOfWeek, TimeSlotStart, TimeSlotEnd​
→ Return instructors not teaching during that time.
3.​ Procedure: getStudentCertificates​
Input: StudentId​
→ Return all certificates issued to that student.
4.​ Procedure: getTopRatedCourses​
Input: MinimumRating​
→ Return courses with average feedback rating ≥ input value.

6. Trigger Requirements [2 marks]

Create the following triggers:

1.​ Trigger: tr_CheckStudentAge​

Before insert into Student, ensure age ≥ 16.​
If not, block and return: "Student is too young to register."
2.​ Trigger: tr_IssueCertificate​
After Enrollment.FinalScore ≥ 8 is updated, and no certificate exists,​
→ insert into Certificate with GETDATE() and generated code.
3.​ Trigger: tr_PreventDuplicateEnrollment​
Before insert into Enrollment, block duplicates on same StudentId and CourseId.
4.​ Trigger: tr_LogFeedbackInsert​
After insert into Feedback, log entry into Audit table.

7. Index Creation Requirement [1 mark]

Create the following indexes:

1.​ Create an index on Student(BirthDate) to improve age-based queries.

2.​ Create a composite index on Enrollment(StudentId, CourseId) to optimize joins and
lookups.