Solve the database question

Question

University consists of several departments. Each department offers several

courses. Students enroll in a particular course taught by a lecturer who is from

the related department and each lecturer tutors a group of students of his

department. No student enrolls any course outside of his department. 

(deptNo,dept_name, location, code, title, credit, studentID, student_name, email,

lecturer_ID, designation)

Do Normalization upto

3NF.

Answer 1

A relation is in 3NF if:

1. It is in 2NF.
2. No transitive dependencies exist (i.e., non-key attributes should not depend on other non-key attributes).

1. Department Table

Department(deptNo, dept_name, location)

Primary Key: deptNo

2. Course Table

Course(code, title, credit, deptNo)

Primary Key: code

Foreign Key: deptNo → Department(deptNo)

3. Student Table

Student(studentID, student_name, email, deptNo)

Primary Key: studentID

Foreign Key: deptNo → Department(deptNo)

4. Lecturer Table

Lecturer(lecturer_ID, designation, deptNo)

Primary Key: lecturer_ID

Foreign Key: deptNo → Department(deptNo)

5. Enrollment Table

Enrollment(studentID, code, lecturer_ID)

Primary Key: (studentID, code)

Foreign Key: studentID → Student(studentID)

Foreign Key: code → Course(code)

Foreign Key: lecturer_ID → Lecturer(lecturer_ID)