SQL Assignment: Implementing ER Diagram in SQL with Queries

This SQL assignment solution demonstrates the implementation of a given ER-diagram in SQL code. It includes the SQL code for creating tables such as Course, Field, InvolvedStudent, Lecture, LectureFeedback, Module, Organisation, Placement, PlacementApplication, PlacementLog, PlacementStudent, Project, ProjectLog, Staff, Student and VisitingLecture. The assignment also presents the implementation of five SQL queries to retrieve specific data from the database. Furthermore, it includes the implementation of two triggers on the Course table, one for insert operations and one for delete operations. The document provides figures and SQL code for each table and query, offering a comprehensive solution for the assignment. Desklib provides access to this and many other solved assignments for students.

SQL ASSIGNMENT

Table of Contents
Part 2.....................................................................................................................................................2
Implementation of desired ER-diagram in SQL code.........................................................................2
Implementation of desired Queries...................................................................................................7
Implementation of triggers................................................................................................................9
References...........................................................................................................................................11
List of figures
Figure 1: Course.....................................................................................................................................2
Figure 2: Field........................................................................................................................................2
Figure 3: InvolvedStudent......................................................................................................................2
Figure 4: Lecture....................................................................................................................................3
Figure 5: LectureFeedback table............................................................................................................3
Figure 6: Module...................................................................................................................................3
Figure 7: Organisation...........................................................................................................................4
Figure 8: placement...............................................................................................................................4
Figure 9: PalcementApplication.............................................................................................................4
Figure 10: PlacementLog.......................................................................................................................4
Figure 11: PalcementStudent................................................................................................................5
Figure 12: Project..................................................................................................................................5
Figure 13: ProjectLog.............................................................................................................................5
Figure 14: Staff......................................................................................................................................5
Figure 15: Student.................................................................................................................................6
Figure 16: vistingLecture.......................................................................................................................6
Figure 17: query 1..................................................................................................................................7
Figure 18: Query 2.................................................................................................................................7
Figure 19: Query 3.................................................................................................................................8
Figure 20: Query 4.................................................................................................................................8
Figure 21: Query 5.................................................................................................................................8
Figure 22: Trigger 1................................................................................................................................9
Figure 23: Trigger 2..............................................................................................................................10
1

Part 2
Implementation of desired ER-diagram in SQL code
Course table
Figure 1: Course
Field table
Figure 2: Field
InvolvedStudent table
Figure 3: InvolvedStudent
2

Lecture table
Figure 4: Lecture
LectureFeedback table
Figure 5: LectureFeedback table
Module table
Figure 6: Module
3

Organisation table
Figure 7: Organisation
Placement table
Figure 8: placement
PalcementApplication table
Figure 9: PalcementApplication
PlacementLog table
Figure 10: PlacementLog
4

PlacementStudent table
Figure 11: PalcementStudent
Project table
Figure 12: Project
ProjectLog
Figure 13: ProjectLog
Staff table
Figure 14: Staff
5

Student table
Figure 15: Student
Visiting Lecture
Figure 16: vistingLecture
6

Implementation of desired Queries
Query 1
SELECT dbo.Student.StudentName, dbo.Student.[Level], dbo.PlacementStudent.StartDate,
dbo.PlacementStudent.EndDate, dbo.Placement.PlacementMgrStaffId, dbo.Placement.Organisation,
dbo.Placement.TelNo, dbo.Placement.Email
FROM dbo.Placement INNER JOIN
dbo.PlacementStudent ON dbo.Placement.PlacementID =
dbo.PlacementStudent.PlacementID INNER JOIN
dbo.Student ON dbo.PlacementStudent.StudentID = dbo.Student.StudentID
Figure 17: query 1
Query 2
SELECT dbo.module.ModuleName, dbo.module.Notes, dbo.Project.ProjectTitle,
dbo.Project.ProjectOutcomes, dbo.Staff.StaffRole, dbo.Staff.SatffName
FROM dbo.Project INNER JOIN
dbo.module ON dbo.Project.ModuleID = dbo.module.ModuleID INNER JOIN
dbo.Staff ON dbo.Project.ProjectMgrStaffId = dbo.Staff.SatffID
Figure 18: Query 2
Query 3
SELECT dbo.Course.CourseTitle, dbo.Course.CourseID, dbo.Student.StudentName, dbo.Student.
[Level]
FROM dbo.Course INNER JOIN
dbo.Student ON dbo.Course.CourseID = dbo.Student.CourseID
7

Figure 19: Query 3
Query 4
SELECT dbo.LectureFeedback.Comments, dbo.LectureFeedback.Rating,
dbo.PlacementApplication.Outcome, dbo.PlacementApplication.Notes,
dbo.PlacementApplication.ApplicationDate, dbo.PlacementApplication.NextSteps
FROM dbo.LectureFeedback INNER JOIN
dbo.PlacementApplication ON dbo.LectureFeedback.StudentID =
dbo.PlacementApplication.StudentID
Figure 20: Query 4
Query 5
SELECT dbo.Staff.SatffName, dbo.Staff.StaffRole, dbo.Staff.Dept, dbo.Field.FieldName,
dbo.VisitingLecturer.LecturerName, dbo.VisitingLecturer.OrgId
FROM dbo.Field INNER JOIN
dbo.VisitingLecturer ON dbo.Field.FieldID = dbo.VisitingLecturer.FieldID INNER JOIN
dbo.Staff ON dbo.VisitingLecturer.ContactStaffId = dbo.Staff.SatffID
Figure 21: Query 5
8

Implementation of triggers
Trigger 1
CREATE TRIGGER tr_Course_Forinserted
ON Course
FOR INSERT
AS
BEGIN
SELECT * FROM inserted
END
Figure 22: Trigger 1
9

Trigger 2
CREATE TRIGGER tr_Course_Fordelete
ON Course
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
END
Figure 23: Trigger 2
10

References
 Barker, S., & Rosenthal, A. (2002). Flexible security policies in SQL. In Database and
Application Security XV (pp. 167-180). Springer, Boston, MA.
 Kroenke, D. M., Auer, D. J., Vandenberg, S. L., & Yoder, R. C. (2010). Database concepts.
Prentice Hall.
11