> 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.
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.