Normalize to 4NF?

Question

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?

Answer 1

-- Subject TITLE taught in year YEAR, session SESSION,
-- is worth CREDIT credits.
--
subject {TITLE, SESSION, YEAR, CREDIT}

Given the predicate, and the data sample, the obvious FD is TITLE -> CREDIT, so it is possible to decompose subject to:

r1 {TITLE, CREDIT}
r2 {TITLE, SESSION, YEAR}

When contemplating NFs, it is important to focus on predicates and constraints -- logic. Sample data is helpful, but rarely tells the whole story. A table is in a specific NF for any set of valid rows, empty set included.

Based on the data sample, it looks possible to decompose {TITLE, SESSION, YEAR} into {TITLE, SESSION} and {TITLE, YEAR}, as in your attempt. More formally, it looks as if the join dependency

* { {TITLE, SESSION}, {TITLE, YEAR} } holds.

Let's see if I can verbalize this:

If a (subject) title was taught in session, and a title was taught in a year, then that title was taught in that session in that year.

Does this make sense? Is it logical?

If this makes sense then your initial solution {TITLE, SESSION}, {TITLE, YEAR}, {TITLE, CREDIT} is correct. However, what will happen in 2020, 2021? The rule states that if title C21 is taught in year 2021, it must be in sessions 2,3,4. Is this correct?

School assignments are hard, you often have to guess what the teacher was thinking. Sometimes I wonder if they were thinking at all. Guessing constraints from a small data sample is an extremely bad idea.

---

Here is my suggestion, the way I see it:

1. That join dependency does not hold.
2. FD TITLE -> CREDIT holds.

-- Subject TITLE is worth CREDIT credits.
--
subject {TITLE, CREDIT}
     PK {TITLE}


-- Subject TITLE was taught in year YEAR, session SESSION.
--
subject_ys {TITLE, SESSION, YEAR}
        PK {TITLE, SESSION, YEAR}

Both of these are now in 6NF and therefore in: 5,4,BCNF,3,... That is if and only if I understand predicates and constraints properly. Take a look at this example.

Answer 2

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.