Normalization the table to 3NF

Question

I have trouble in understanding how do I represent the foreign key after I normalize the table to 3NF.

Lets consider the table this way:

student(studentid,studentname,courseid,coursename,dateofexam,marks,grade)

I can able to figure out the FD as follows:

studentid -> studentname
courseid -> coursename
{student,courseid} -> dateofexam,marks
marks -> grade

Now I'm trying to create a new table for FD that has non-ket attributes like this:

marks grade
studentid studentname

Now how do I relate the foreign key for these tables with my old table student? I'm confused on doing this.

Thanks in advance.

Answer 1

Your FD actually shows you how:

studentid -> studentname
courseid -> coursename
{student,courseid} -> dateofexam,marks
marks -> grade

Each row would be a separate table. The keys that exists in other tables would be the fk. In this case:

{student,courseid} -> dateofexam,marks

that would be studentid, courseid and marks(which probably must be an id too)

It would be something like this

Student( studentid, studentname)

Course( courseid, coursename)

Exams( examid, fk_studentid, fk_courseid, dateofexam, fk_markid)

Marks( markid, grades)