How can I normalize this further?

1NF

Minimal FDs:

{student_id} -> name, start, program
{coure_code} -> course_name, faculty, credits, time
{student_id, coure_code} -> grade

I am on, what I think is the correct 2NF :

student (student_id, name, start, program)
course (coure_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)

I am trying to achieve 3NF and BCNF , I have identified what I believe are transitive dependencies , {credit} -> time , and maybe {program} -> start . Does this make any sense?

your 2NF looks to be pretty close to 3NF to me…
if credits -> time is a dependency, perhaps it is actually an abstraction out of the database - (If Time is a mathmatical operation on credits, remove time from the database and simply use the mathematical procedure to select “Half” as (credits < X )).

I dont see how Program and Start have a dependency. Can you elaborate?

though the syntax highlighter is flagging that a couple of your column names are reserved words, so keep an eye on that.

1 Like

My goes, is that {credits} -> determines time, however, I am probably completely wrong here, and yes, I asked because I felt this was more close to an 3NF.

Credits -> Time is a valid dependency, so you’d have to resolve it to achieve 3NF. 3NF says you’d break it away into a separate table; my statement is to obliviate the column entirely and simply store credits; the requestor of data should be aware of the relationship of credits to “time-ness”, and construct their queries respectively.

Again, Program and Start does not have an obvious dependency between the two in my head, so I don’t see them as transitively dependant. While it may be true that a given program might put a lower bound on start year, it cannot be inferred simply from Program what the value of Start should be for a given student. (Keep in mind that a student could change programs after starting, so that would break the dependency)

As far as BCNF, taking this table to 3NF should take you to BCNF as well, as all of your Functional Dependencies have been resolved as keys in the breakdown tables.

1 Like
student (student_id, name, start, program)
course (coure_code, course_name, faculty)
student_course(student_id, course_code, grade)
*(credits, time)

But then I am missing something? Or, please review this, thanks a lot. What about a dependency diagram?

@m_hutley, are you sure that {credits} -> time, see this table:

Define your ‘time’ column for me. What goes into it, based off of what?

Time = study pace, Quart, Half, Full

What defines Quart vs Half vs Full?

1 Like

You can study many courses at a part-time pace, for example half-time or quarter-time. This means you study fewer credits per week, resulting in less time devoted to studies.

so there is a link between the number of credits, and quart/half/full time. When you define the values and use another column to do it :stuck_out_tongue:

So i’m going to throw out some numbers. It’s been a while since I was in college, but for example:
Quart: Less than 8 credits a week.
Full: 16 or more credits a week
Half: Between full and quart.

So at that point, I don’t need the Time column anymore. Lets say I want to find all Full Time students.

SELECT name FROM students WHERE time = "Full"
But I could also write that query as
SELECT name FROM students WHERE credits >= 16

I could find Half time students as
SELECT name FROM students WHERE time = "Half"
But I could also write that query as
SELECT name FROM students WHERE credits BETWEEN 8 AND 15.99

so… why do i need the time column?

student (student_id, name, start, program)
course (coure_code, course_name, faculty)
student_course(student_id, course_code, grade)
*(credits, time)

I am thinking, that this isn’t correct, right?

These are my minimal functional dependencies:

{student_id} -> name, start, program
{coure_code} -> course_name, faculty, credits, time
{student_id, coure_code} -> grade
student (student_id, name, startyear, program, credits)
course (course_code, course_name, faculty)
student_course(student_id, course_code, grade)

Is how I would design this database.

What about time? Is it irrelevant? Did you see my minimal functional dependencies? These identified dependencies are correct.

Can’t be correct, it only allows one value for credits to be recorded for the student, ignoring the number of different courses taken, each with a different number of credits earned.
When I did courses or taught courses, each course would earn/ / be worth credits towards the required total number to be taken to complete the program.

needs a course_credits{course_code, credits}
with student (student_id, name, startyear, program)
I think…

Which then means you can sum a given student’s total credits earned.
Which implies a required_credits for the program taken

1 Like

Depends. Is credits tracking the total number of credits earned, or the current number of credits being taken?

A student’s current credits being taken is a singular value.
A total is a singular value also, but can be a total of a history of student_courses.

Credit for single course.

Mostly it depends on if you’re deriving the value or supplying it.

If it’s credits for a single course, it belongs in the student_course table.