Database normalization

Databases
1

does this table fit for the 2NF? if not? why and how to corrrect it.


studentnumber   name     age      course   credit   

1               tom            21      math       3

2               jim             20          english    4
....
2

This tute on database normalisation should help you decide if it does.

3

no, it doesn’t

i’m going to speculate that there is no restriction that says each student can take only one course

thus if tom were to take english, then the rows would look like this –

studentnumber   name   age   course   credit   
     1          tom    21    math       3
     2          tom    21    english    2
     2          jim    20    english    4

now the primary key of this table has to be studentnumber plus course

and thus we can see that name and age are dependent only on studentnumber, not on the entire primary key, so this table fails 2NF

to fix it, you need a separate table for students, in which to record the age and name, with primary key studentnumber

then this table, with the course credits, will have only three columns – studentnumber and course as primary key, and credit as the data attribute

4

how could you know the primary key of that table has to be studentnumber plus course? thank you.

studentnumber name age
1 tom 21
2 jim 20

course credit studentnumber
math 3 1
english 2 2

the course id the pK. studentnumber is the foreign key. now, does the table fit for the 3NF?

5

“how could you know the primary key of that table has to be studentnumber plus course? thank you.”

to get unique values for the primary key

Because more than one student will take each course.

Remember, you don’t just look at the data SAMPLE you have, you must also consider the meaning of the data and the scenario where it will be used.

“the course id the pK. studentnumber is the foreign key. now, does the table fit for the 3NF?”
no, you need the joint key, no it is not 3nf

BECAUSE, you may also have a course table, remember. And now think of those non-mathematicians who take a course called maths - will it be the exact same course as that taken by the mathematicians? What’s the PK for the course table then? How should you now modify the table which currently has course, credit, and studentnumber? Even assuming there is only one course called maths, and all course names are unique, it needs modifying.

6

THINK
what is the determination for credit?