Tables Normalized To 3NF

ER Diagram: Many-to-Many Relationships

Changes that Occurred

Since there was a many to many relationships between student and courses, another table: course_student has been introduced to break the many to many relationships into two one to many relationships.  

Table Normalization to 3NF

The following is a list of Tables Normalized to 3NF. Primary Keys are Underlined While Foreign Keys Are Italicized:

Student (StudentId, FirstName, LastName, Phone)

Department (DepartmentName, Location)

Instructor (InstructorId, FirstName, LastName, Phone, DepartmentName)

Course (CourseId, Duration, CourseName, DepartmentName, InstructorId)

Course_Student (CourseId, StudentId)

Refined ERD:

 

Refined tables:

Based on the refined ER above, the following are the refined tables normalized to 3NF:

Student (StudentId, FirstName, LastName, Phone)

PRIMARY KEY(StudentId)

Department (DepartmentName, Location)

PRIMARY KEY(DepartmentName)

Instructor (InstructorId, FirstName, LastName, Phone, DepartmentName)

PRIMARY KEY(InstructorId)

FOREIGN KEY (DepartmentName) REFERENCES Department (DepartmentName)

Course (CourseId, Duration, CourseName, DepartmentName, InstructorId)

PRIMARY KEY(CourseId)

FOREIGN KEY (InstructorId) REFERENCES Instructor (InstructorId)

FOREIGN KEY (DepartmentName) REFERENCES Department (DepartmentName)

Course_Student (CourseId, StudentId)

PRIMARY KEY (CourseId, StudentId)

FOREIGN KEY (CourseId) REFERENCES Course (CourseId)

FOREIGN KEY (StudentId) REFERENCES Student (StudentId) 

/*CREATING DATABASE TABLES*/

–Student

CREATE TABLE Student(

StudentId INT PRIMARY KEY,

FirstName VARCHAR(30),

LastName VARCHAR(30),

Phone CHAR(10));

— Department

CREATE TABLE Department(

DepartmentName VARCHAR(30) PRIMARY KEY,

Location VARCHAR(30));

— Instructor

CREATE TABLE Instructor(

InstructorId INT PRIMARY KEY,

FirstName VARCHAR(30),

LastName VARCHAR(30),

Phone CHAR(10),

DepartmentName VARCHAR(30),

CONSTRAINT Dept_FK_1 FOREIGN KEY(DepartmentName) REFERENCES Department(DepartmentName));

— Course

CREATE TABLE Course(

CourseId INT PRIMARY KEY,

Duration VARCHAR(10),

CourseName VARCHAR(30),

DepartmentName VARCHAR(30),

InstructorId INT,

CONSTRAINT Inst_FK FOREIGN KEY(InstructorId) REFERENCES Instructor(InstructorId),

CONSTRAINT Dept_FK_2 FOREIGN KEY(DepartmentName) REFERENCES Department(DepartmentName));

–Course_Student

CREATE TABLE Course_Student(

CourseId INT,

StudentId INT,

CONSTRAINT CS_PK PRIMARY KEY (CourseId, StudentId),

CONSTRAINT Course_FK FOREIGN KEY(CourseId) REFERENCES Course(CourseId),

CONSTRAINT Stud_FK FOREIGN KEY(StudentId) REFERENCES Student(StudentId));

/*Data Population*/

— Student

INSERT INTO STUDENT VALUES

(11111110,’John’,’Perry’,’9867536773′),

(11111111,’Mary’,’Jay’,’9767536773′);

–Department

INSERT INTO DEPARTMENT VALUES

(‘Informatics’,’New Hampshire’),

(‘Biological Sciences’,’News Bridge’);

— Instructor

INSERT INTO Instructor VALUES

(11111111,’Caleb’,’Smith’,’0967339834′,’Informatics’),

(11111112,’Paul’,’Peterson’,’8996875432′,’Biological Sciences’);

–Course

INSERT INTO Course VALUES

(2000,’3HRS’,’Computer Science’,’Informatics’,11111111);

— Course_Student

INSERT INTO Course_Student VALUES

(2000,11111110); 

/*View*/

–Student_Courses_View

CREATE VIEW Student_Courses_View AS

SELECT Student.StudentId, Student.FirstName, Student.LastName, Course.CourseName FROM course_student

INNER JOIN Student ON course_student.StudentId = Student.StudentId

INNER JOIN Course ON course_student.CourseId = Course.CourseId;

SELECT * FROM Student_Courses_View;

 

— Instructor_Courses

CREATE VIEW Instructor_Courses_View AS

SELECT Instructor.InstructorId, Instructor.FirstName, Instructor.LastName,Course.CourseName FROM Instructor

INNER JOIN Course ON Instructor.InstructorId = Course.InstructorID;

SELECT * FROM Instructor_Courses_View;

 

/*Triggers*/

— Student_Audit_Table

CREATE TABLE Student_Audit_Test  

(   

Id int IDENTITY,   

Audit_Action VARCHAR(100)   

);   

–Student_Insert_Trigger

CREATE TRIGGER Student_Insert_Trigger   

ON STUDENT  

FOR INSERT  

AS  

BEGIN  

  Declare @Id int  

  SELECT @Id = StudentId from inserted  

  INSERT INTO Student_Audit_Test  

  VALUES (‘New student with Id = ‘ + CAST(@Id AS VARCHAR(30)) + ‘ is added at ‘ + CAST(Getdate() AS VARCHAR(30)))  

END; 

–Student_Delete_Trigger

CREATE TRIGGER Student_Delete_Trigger   

ON STUDENT  

FOR DELETE  

AS  

BEGIN  

  Declare @Id int  

  SELECT @Id = StudentId from deleted  

  INSERT INTO Student_Audit_Test  

  VALUES (‘Student with Id = ‘ + CAST(@Id AS VARCHAR(30)) + ‘ is deleted at ‘ + CAST(Getdate() AS VARCHAR(30)))  

END; 

— Student_Update_Trigger

CREATE TRIGGER Student_Update_Trigger   

ON STUDENT  

FOR UPDATE  

AS  

BEGIN  

  Declare @Id int  

  SELECT @Id = StudentId from deleted  

  INSERT INTO Student_Audit_Test  

  VALUES (‘Student with Id = ‘ + CAST(@Id AS VARCHAR(30)) + ‘ is updated at ‘ + CAST(Getdate() AS VARCHAR(30)))  

END;

/*Queries*/

— Query 1

SELECT StudentId, FirstName, Lastname FROM Student WHERE StudentID = 11111110;

 

— Query 2

SELECT InstructorId, FirstName, Lastname FROM Instructor WHERE InstructorId = 11111111;

 

— Query 3

SELECT CourseId, Duration, CourseName FROM Course WHERE CourseName = ‘Computer Science’;   

 

— Query 4

SELECT DepartmentName, Location FROM Department WHERE Location = ‘New Hampshire’;

 

— Query 5

SELECT StudentId FROM Student

WHERE StudentId NOT IN (SELECT StudentId FROM Course_Student);

 

— Query 6

SELECT DepartmentName FROM Department;

 

— Query 7

SELECT StudentId FROM Student

WHERE StudentId IN (SELECT StudentId FROM Course_Student);

 

— Query 8

SELECT InstructorId FROM Instructor

WHERE InstructorId IN (SELECT InstructorId FROM Course);

 

— Query 9

SELECT * FROM Student WHERE LastName = ‘Perry’;

 

— Query 10

SELECT * FROM Instructor WHERE LastName = ‘Peterson’;