First Normal Form (1NF) :

 Violation :
o There are multiple data in a single attribute (repeating groups).
 Solution:
o Split all repeating groups into single data per attributes.
o Add a new column called StudentID to uniquely identify a tuple
with combination of CourseID.
CourseI StudentI
D CourseName D EnrolledStudent Grade Professor
C001 Biology 1 Nigar Kazimova E Farid Kazimov
C001 Biology 2 Nigar Hasanov B Farid Kazimov
Computer
C002 3 Yusif Hasanov A Zahra Kazimova
Science
Computer
C002 4 Leyla Quliyeva E Zahra Kazimova
Science
Computer
C002 5 Nigar Huseynova A Zahra Kazimova
Science
C003 Physics 6 Farid Kazimov C Nigar Rahimova
Rashad
C003 Physics 7 A Nigar Rahimova
Mammadov
C003 Physics 8 Nigar Rahimova A Nigar Rahimova
Elvin
C004 Psychology 9 Amina Kazimov D
Mammadov
Elvin
C004 Psychology 10 Farid Ibrahimova B
Mammadov
Elvin
C004 Psychology 11 Nigar Yusifova C
Mammadov
C005 Economics 12 Samir Yusifova B Elvin Ibrahimov
C005 Economics 10 Farid Ibrahimova D Elvin Ibrahimov
Amina
C005 Economics 13 D Elvin Ibrahimov
Ibrahimova
Amina
C006 Chemistry 14 Farid Yusifova A
Rahimova
Amina
C006 Chemistry 15 Elvin Rahimov A
Rahimova
Amina
C006 Chemistry 16 Elvin Aliyev C
Rahimova
Amina
C006 Chemistry 17 Zahra Quliyeva B
Rahimova
C007 Physics 18 Leyla Jafarova C Amina Quliyeva
C007 Physics 19 Zahra Hasanova E Amina Quliyeva
C008 Physics 20 Zahra Jafarova D Gulnar Quliyeva
C008 Physics 21 Amina Jafarova A Gulnar Quliyeva
C008 Physics 22 Amina Yusifova F Gulnar Quliyeva
C008 Physics 23 Leyla Aliyeva B Gulnar Quliyeva
C009 Mathematics 23 Leyla Aliyeva D Yusif Huseynova
Amina
C009 Mathematics 13 A Yusif Huseynova
Ibrahimova
C0010 Psychology 24 Amina Aliyeva B Yusif Hasanov
C0010 Psychology 5 Nigar Huseynova C Yusif Hasanov
C0010 Psychology 25 Zahra Rahimov B Yusif Hasanov
C0010 Psychology 26 Leyla Yusifova B Yusif Hasanov
 Second Normal Form (2NF):

 Violation:

o There is some data redundancy in several columns.

o There is also a presence of partial dependency on the primary key
(CourseID and StudentID), such as, CourseName and Professor
attributes are not fully dependent on PK, but only on CourseID.

 Solution:
o Creation of two new tables out of the 1NF table named
courses(CourseID, CourseName, Professor) and
enrollment(CourseID, StudentID, EnrolledStudent, Grade).

courses
CourseID CourseName Professor
C008 Physics Gulnar Quliyeva
C007 Physics Amina Quliyeva
C009 Mathematics Yusif Huseynova
C004 Psychology Elvin Mammadov
C001 Biology Farid Kazimov
C010 Psychology Yusif Hasanov
C002 Computer Science Zahra Kazimova
C006 Chemistry Amina Rahimova
C005 Economics Elvin Ibrahimov
C003 Physics Nigar Rahimova
enrollment
CourseID StudentID EnrolledStudent Grade
C008 22 Amina Yusifova F
C010 26 Leyla Yusifova B
C007 18 Leyla Jafarova C
C010 5 Nigar Huseynova C
C006 17 Zahra Quliyeva B
C006 16 Elvin Aliyev C
C010 25 Zahra Rahimov B
C010 24 Amina Aliyeva B
C002 5 Nigar Huseynova A
C005 12 Samir Yusifova B
C008 20 Zahra Jafarova D
C005 13 Amina Ibrahimova D
C009 23 Leyla Aliyeva D
C003 8 Nigar Rahimova A
C002 4 Leyla Quliyeva E
C004 10 Farid Ibrahimova B
C003 6 Farid Kazimov C
C008 23 Leyla Aliyeva B
C001 2 Nigar Hasanov B
C008 21 Amina Jafarova A
C006 15 Elvin Rahimov A
C002 3 Yusif Hasanov A
C009 13 Amina Ibrahimova A
C007 19 Zahra Hasanova E
C006 14 Farid Yusifova A
C005 10 Farid Ibrahimova D
C003 7 Rashad Mammadov A
C001 1 Nigar Kazimova E
C004 9 Amina Kazimov D
C004 11 Nigar Yusifova C
FK PK
 Third Normal Form (3NF):
 Violation:

o In the 2NF, there is a transitive data dependency in the courses

table; the Professor attribute is not a fact about CourseID (PK). In
addition, there will be an insertion and update anomaly when a
course is added without a professor yet, or when there is an
update to be made on Professor teaching several courses, which
can also be considered as data redundancy.
o Same issues can be seen in enrollment relation. A student can
exist, yet has been enrolled yet; in addition, a student will not be
able to have a grade even enrolled in a course from the beginning;
these issues fall into insertion, update anomalies, and redundancy.

 Solution:

The solution is to create new tables out of the 2NF tables, and we can
add new columns to certain newly created tables to build a relationship
table between those tables.

 courses3(CourseID, CourseName)
 professors3(ProfessorID, ProfessorName)
 teaching3(ProfessorID, CourseID)
 students3(StudentID, StudentName)
 enrollment(CourseID, StudentID)
 grade3(CourseID, StudentID, Grade)
courses3
CourseID CourseName
C008 Physics
C010 Psychology
C003 Physics
C002 Computer Science
C001 Biology
C005 Economics
C004 Psychology
C007 Physics
C009 Mathematics
C006 Chemistry

professors3
ProfessorID ProfessorName
1 Elvin Mammadov
2 Amina Rahimova
3 Elvin Ibrahimov
4 Zahra Kazimova
5 Farid Kazimov
6 Yusif Huseynova
7 Nigar Rahimova
8 Yusif Hasanov
9 Amina Quliyeva
10 Gulnar Quliyeva

teaching3
ProfessorID CourseID
1 C004
2 C006
3 C005
4 C002
5 C001
6 C009
7 C003
8 C010
9 C007
10 C008
students3
StudentID StudentName
8 Nigar Rahimova
13 Amina Ibrahimova
10 Farid Ibrahimova
20 Zahra Jafarova
12 Samir Yusifova
22 Amina Yusifova
2 Nigar Hasanov
15 Elvin Rahimov
18 Leyla Jafarova
4 Leyla Quliyeva
3 Yusif Hasanov
17 Zahra Quliyeva
7 Rashad Mammadov
21 Amina Jafarova
25 Zahra Rahimov
23 Leyla Aliyeva
19 Zahra Hasanova
16 Elvin Aliyev
6 Farid Kazimov
24 Amina Aliyeva
5 Nigar Huseynova
11 Nigar Yusifova
9 Amina Kazimov
26 Leyla Yusifova
14 Farid Yusifova
1 Nigar Kazimova
enrollment3
StudentID CourseID
22 C008
26 C010
18 C007
5 C010
17 C006
16 C006
25 C010
24 C010
5 C002
12 C005
20 C008
13 C005
23 C009
8 C003
4 C002
10 C004
6 C003
23 C008
2 C001
21 C008
15 C006
3 C002
13 C009
19 C007
14 C006
10 C005
7 C003
1 C001
9 C004
11 C004
grade3
StudentID CourseID Grade
22 C008 F
26 C010 B
18 C007 C
5 C010 C
17 C006 B
16 C006 C
25 C010 B
24 C010 B
5 C002 A
12 C005 B
20 C008 D
13 C005 D
23 C009 D
8 C003 A
4 C002 E
10 C004 B
6 C003 C
23 C008 B
2 C001 B
21 C008 A
15 C006 A
3 C002 A
13 C009 A
19 C007 E
14 C006 A
10 C005 D
7 C003 A
1 C001 E
9 C004 D
11 C004 C