Make Into First Normal Form

Question

Given the following relation:

school =(teacherID, list of kids)

I need to convert this to First Normal Form. My thought was that I have one table for teacherID then have a kids table with each kid have a kidID. Then I could connect each kidID to their correct teacherID. Would that fix the 1NF problem?

Answer 1

It would, but it would also put it into 2nd normal form, which may or may not be desired for your purposes.

If, for whatever reason, you solely wanted it in 1st normal form you'd simply want it so that there's no cells which have more than one item of data. In this case turning the list of kids into a column, and having each kid have the teacher ID would be sufficient.

Answer 2

To satisfy the 1NF, you only need to have atomic data fields, so that the information of a column cannot be split into two.

For example, if you store the information of a teacher (teacherid, name) this would not be first normal form, because you can split name into first and last name.

A list of kids in one field is not atomic so have to split that into multiple records. So you need to have one entry for each kid, like (teacherID, kid1ID), (teacherID, kid2ID),...

Answer 3

If the unnormalized version is:

Teacher ID	Kids
T1234	Alice,Bob,Carol

Then the 1NF version is:

Teacher ID	Kids
T1234	Alice
T1234	Bob
T1234	Carol