It's an almost verbatim example of getting to 1NF, the first and most basic normalization. The value (department name) is repeating and should be extracted and given its own ID.
1NF bans relation-valued attributes, not repetition of attribute value across tuples in a relation. Mainstream SQL databases don't support relation-valued attributes, so any table you make in a relational database is 1NF.
You can push back on this a little - for instance maybe you consider an array-valued attribute to be enough like a relation to argue array-valued attributes violate 1NF. But if you do that you must also explain what makes arrays different from strings, since strings are pretty similar to arrays of characters and can be treated the same way in most respects (for instance characters in a string can be addressed by index or split into substrings).
> The value (department name) is repeating and should be extracted
Then the id would be repeating. Furthermore, the department name would make a fine primary or alternate key for the new relation you're proposing.
Also, that's not what 1NF is. 1NF means there should be no table-valued attributes. And neither is any column list-valued nor does any subset of columns form a subtable.
The other normal forms talk about functional dependencies and there aren't any.
The only possible violation of 1NF could be not splitting the name in given name and family name. Other than that, the table is normalized.
Yes, but it's an ID, not a value. No problems there.
> Furthermore, the department name would make a fine primary or alternate key for the new relation you're proposing.
They're called "natural keys" and there's a lot of problems with them not actually mapping to identity. Like for one example, if a department's name is changed, it isn't just a change to the database column, you have to update all associated code as well - which is why you should have an ID and use that in the code anyway.
> Also, that's not what 1NF is. 1NF means there should be no table-valued attributes. And neither is any column list-valued nor does any subset of columns form a subtable.
If there's only one such column and you switch perspective to the inner table, the transformation is the same. That's why I said "almost" - it's not exactly the same, there are additional conditions, but if you hit them it's the same thing and the result is extracting the duplicate values into their own table, linking them with an ID instead.
In normalization theory and relational algebra an ID is just a value. DBMSs make no difference between this column and any other primary key column(s) and they make no difference between PK indexes over strings or numbers or any other supported data type.
You're just cargo culting here instead of applying database theory or actually looking at implementations.
Even if there were a separate Departments table, who is to say that the Department Name is not its primary key? The Department name certainly is A key.
