How can I normalize this further?

Databases
1

1NF

1142Ɨ178 7.59 KB

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?

2

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
3

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.

4

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
5 
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?

6

@m_hutley, are you sure that {credits} ā†’ time, see this table:

1142Ɨ178 7.59 KB

7

Define your ā€˜timeā€™ column for me. What goes into it, based off of what?

8

Time = study pace, Quart, Half, Full

9

What defines Quart vs Half vs Full?

1 Like
10

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.

11

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?

12 
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?

13

These are my minimal functional dependencies:

{student_id} -> name, start, program
{coure_code} -> course_name, faculty, credits, time
{student_id, coure_code} -> grade
14 
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.

15

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

16

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
17

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.

18

Credit for single course.

19

Mostly it depends on if youā€™re deriving the value or supplying it.

20

If itā€™s credits for a single course, it belongs in the student_course table.