[Examination] Hệ thống quản lý sinh viên - SQL Server
Dưới đây là bản văn bản thuần (plain text) đã được định dạng sạch sẽ, giữ nguyên nội dung tiếng Anh và loại bỏ thang điểm để bạn dễ dàng sao chép:
1. Database Creation
Create a database named ‘StudentManagementSystem’
Make use of the database
2. Table Creation
Class – table to store class information
Column: ClassId | Datatype: INT | Constraint: NOT NULL
Column: ClassCode | Datatype: NVARCHAR(50)
Student – table to store student information
Column: StudentId | Datatype: INT | Constraint: NOT NULL
Column: StudentName | Datatype: NVARCHAR(50)
Column: BirthDate | Datatype: DATETIME
Column: ClassId | Datatype: INT
Subject – table to store subject information
Column: SubjectId | Datatype: INT | Constraint: NOT NULL
Column: SubjectName | Datatype: NVARCHAR(100)
Column: SessionCount | Datatype: Int | Description: Number of sessions
Result – table to store study result of each student.
Column: StudentId | Datatype: INT | Constraint: NOT NULL
Column: SubjectId | Datatype: INT | Constraint: NOT NULL
Column: Mark | Datatype: INT
3. Index Creation and Table Alternation
Create NONCLUSTERED INDEX: Name: NCI_Student_StudentName | Table: Student | Column: StudentName
Alter table Result, change data type of the column Mark to FLOAT
4. Constraints (added to Tables)
PRIMARY KEY
PK_Class: ClassId (Table: Class)
PK_Student: StudentId (Table: Student)
PK_Subject: SubjectId (Table: Subject)
PK_Result: StudentId, SubjectId (Table: Result)
FOREIGN KEY
FK_Student_Class: Student(ClassId) references Class(ClassId)
FK_Result_Student: Result(StudentId) references Student(StudentId)
FK_Result_Subject: Result(SubjectId) references Subject(SubjectId)
CHECK
CK_Subject_SessionCount: Table: Subject | Column: SessionCount | Condition: SessionCount > 0
5. Inserting Data
Class: (1, 'C1106KV'), (2, 'C1108GV'), (3, 'C1108IV'), (4, 'C1108HV'), (5, 'C1109GV')
Student: 1, PhạmTuấnAnh, 1993-08-05, 1 2, PhanVănHuy, 1992-06-10, 1 3, NguyễnHoàng Minh, 1992-09-07, 2 4, TrầnTuấnTú, 1993-10-10, 2 5, ĐỗAnhTài, 1992-06-06, 3
Subject: 1, C Programming, 22 2, Web Design, 18 3, Database Management, 23
Result: (1,1,8), (1,2,7), (2,3,5), (3,2,6), (4,3,9), (5,2,8)
6. Query Operations
Search students whose BirthDate is between from ‘1992-10-10’ and ‘1993-10-10’. Display info of StudentId, StudentName, DateOfBirth.
Count students in each class with information of ClassId, ClassCode, TotalStudent.
Sum up mark of all subjects for each student, then display only students whose sum of mark is greater than 10. Display info of: StudentId, StudentName, TotalMark.
7. Views
Create a view named view_StudentSubjectMark which should show result of each student by each subject, with information of: StudentId, StudentName, SubjectName, Mark.
Show top 3 records from that view, which ordered by Mark of student (Descending).
8. Procedures
Create a procedure named up_IncreaseMark, with input parameter @SubjectId (INT). This procedure increases 1 point for every Mark of every Student by that Subject.
Execute the procedure with SubjectId=2.
9. Trigger (INSERT)
Create a trigger named TG_Result_Insert, which would be execute when user inserts a new record into Result. This trigger will check if Mark < 0, then display an error message: “Cannot insert mark less than zero.”. Otherwise, allows user to insert record normally.
Please execute the trigger using testing data (StudentId, SubjectId, Mark)=(1,3,-2)
10. Trigger (UPDATE)
Create a trigger for UPDATE event named [TG_Subject_Update]. When the update event on [SubjectName] column of table [Subject] occurs, it will be failed and print message “You don’t update this column”.
