How would I normalized this table?

One Comment

1. John says:

   April 17, 2010 at 7:19 pm

   1NF is:
   - Eliminate duplicative columns from the same table.
   - Create separate tables for each group of related data and identify each row with a unique column (the primary key).

   your table seems to contain 4 groups (and already contains a unique column for the primary key).

   so break that thing up into 4 tables with following columns:

   employee (empno, empna, tel)
   department (deptno, deptna, depthe)
   course (cno, access, cdate)
   instructor (ino, ina)

   2NF:
   -Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
   -Create relationships between these new tables and their predecessors through the use of foreign keys.

   that’s a bit tricky .. i guess a department head is still always an employee, so your department table could link to employees instead of having the Department Head name there. .. same for instructors. And if you want you could even say Courses are of a certain topic and are given by different instructors depending on the couse date (so you could make an additional table called Topics and link to that).

   employee (empno, empna, tel)
   department (deptno, deptna, FK to head_empno)
   instructor (ino, FK to inst_empno)
   Topic (tno, topicname)
   course (cno, FK to topic_tno, FK to inst_ino, cdate)

   3NF:
   - Already meet the requirements of both 1NF and 2NF
   - Remove columns that are not fully dependent upon the primary key.

   your question does not contain any more columns that we could get rid of. So no change for 3NF.