Given the following table:
Title Session Year Credit
C21 2 2018 6
C21 3 2018 6
C21 4 2018 6
C21 2 2019 6
C21 3 2019 6
C21 4 2019 6
D22 2 2018 4
D22 3 2018 4
D22 4 2018 4
D21 3 2019 4
D21 4 2019 4
With the relational schema : SUBJECT(title, session, year, credit)
Is it possible to normalize the above to 4NF?
The criteria to normalize to 4NF is that it need to meet BCNF and multiple value of B exists for a single value of A given A->B. I am having trouble understanding and applying the bold to the table.
My attempt:
The table have repeated attributes for session, year and credit. Based on the bold statement (and BCNF met), I will normalize it to 4NF
Title Session
C21 2
C21 3
C21 4
D22 2
D22 3
D22 4
Title Year
C21 2018
C21 2019
D22 2018
D22 2019
Title Credit
C21 6
D22 4
I am conflicting on the 3 decomposed table though because the 3 attributes (session, year, credit) do not look like independent relation to title.
Can anyone advise?
Whenever you want to normalize data relationships, the first thing you do is list all of the relationships.
A subject has a title and credit hours.
A subject has multiple sessions.
A subject is taught in multiple years.
A session is in a single year.
So, let's start with a Subject table.
Subject
-------
Subject ID
Subject Title
Subject Credit Hours
Subject ID is a blind autoincrementing integer primary key.
Looks good so far.
Now, let's create a Session table.
Session
-------
Session ID
Session Number
Session Year
Subject ID (FK)
Session ID is another blind autoincrementing integer primary key.
We can break the Session table down to remove the repeating session numbers by moving the Subject ID foreign key to a junction table.
Session
-------
Session ID
Session Number
Session Year
SubjectSession
--------------
SubjectSession ID
Subject ID
Session ID
Looks good so far. Let's fill the tables
Subject ID | Subject Title | Subject Credit Hours
-----------------------------------------------------
1 | C21 | 6
2 | D21 | 4
3 | D22 | 4
Session ID | Session Number | Session Tear
----------------------------------------------
1 | 2 | 2018
2 | 3 | 2018
3 | 4 | 2018
4 | 2 | 2019
5 | 3 | 2019
6 | 4 | 2019
SubjectSessionID | SybjectID | Session ID
---------------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 1 | 4
5 | 1 | 5
6 | 1 | 6
7 | 3 | 1
8 | 3 | 2
9 | 3 | 3
10 | 2 | 5
11 | 2 | 6
I don't think that the year needs to be broken out. It's part of what defines a session, even though it repeats.
I don't know if this is 4NF, but this is as normalized as I would make it.