So, I cannot normalize this further?
E.g. faculty are under course as a two courses in a program can belong to few faculties
Consider the primary reason for Normalization which is reducing data duplication. Look at your table(s). Do you see duplicate data? Then you likely need another table for that data keyed back to where it was duplicated. The second purpose is data integrity. Think, if I changed the value of column X will I have to update the data in multiple rows? i.e Column faculty value âTechâ changes to âAdvanced Techâ. Will you have to make more than one change?
In the quoted design @m_hutley posted, student should not have the program and credits column and probably not the start year. What if the student is part of more than one program? You now have to duplicate the student data. It is also possible that a student could have a different start year for each program they are in.
The course should have faculty_id and not the actual faculty data. A question to be asked is, can there ever be more than one faculty per course? If so, you have more re-design to do. More normalization, the faculty would not be in the course table at all. You would have another table that ties the courses to faculty, table course_faculty with course_id | faculty_id. You are now infinitely scalable to assign an unlimited number of of faculty to any given course.
In a nutshell,
- Move duplicate data to another table and tie it back by the id key.
- Ask yourself, if I changed this data value, will I have to change it in numerous rows.
If you do that you will end up with a well normalized database that will be infinitely scalable.
1 Like
Thanks for clearing. I was arriving to the same conclusion. One question, how would this be modeled using dependency diagram? Because 3NF prohibits transitive dependencies.
Take the approach I mentioned and then post back what you ended up with. Also provide an SQL dump of it so I/we donât have to recreate the Schema to show you changes. Images of data just donât import well.
Pictures are for hanging on the wall. Post an SQL dump of your latest attempt.
Also, can a student ever have more than one course? If so, can those courses have a different start year? Can a course ever have more than one faculty?
I donât have SQL dumps, I havenât implemented this. I was doing it on paper, that my Functional Dependency Diagram, I am trying to break to 3NF. https://www.youtube.com/watch?v=fyg0gEXh8U4
Can a course ever have more than one faculty?
Yes.
Also, can a student ever have more than one course?
Yes.
I would suggest you actually create the database and table structure.
By the way, what is this âpaperâ thing you speak of?
I am doing everything on paper first, because it is easier.
This is the sample of the data:
I am not going to hand recreate your image. You need to make it easy for us us to help you.
By the way, the video you posted has issues. It is not complete. Since he has disabled comments no one can tell him and viewers whats wrong with it.
I understand.
Functional dependencies:
{student_id} -> name, start, program
{coure_code} -> course_name, faculty, credits, time
{student_id, coure_code} -> grade
It seems you are getting caught up in the âLetter of the Lawâ which is making this difficult for you.
Do the two things I previously mentioned on an actual database and then post the SQL dump and we can take it from there.
Also, answer a third question for your self, Can Column X value ever have more than one of these? i.e Can a student ever have more than one course, can a course ever have more than one teacher, etc, etcâŚ
If the same course is given in different years, how can you tell them apart?
If a student takes a course, fails, and takes it again the next year (assuming thatâs allowed), how will you record their grade?
What if the course changes its name, but the course code stays the same?
2 Likes
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.