Database, how to determine functional dependencies and if is in BCNF?

Question

I am currently working on this question to identify which normal form it is and so I have to list out the functional dependencies. I worked out the solution but still have questions about it.

In a final-year-project selection process, students are to select one research topic for his/her project. Students are allowed to select the same research topic. For each research topics, supervisors are assigned to supervise it. A supervisor may be supervising up to two different research topics and each research topic may be assigned to different supervisors. For each of the research topic a supervisor supervises, a consultation day is allocated for the student to meet and discuss with the supervisor.

This information of final-year-project selection are stored in the following relational table: FINALYEARPROJECT(supervisor, researchTopic, consultationDay, student)

These is the functional dependencies I listed:

student → researchTopic, consultationDay, supervisor

• student is the candidate key.

supervisor, researchTopic, student → consultationDay

Question 1: From student I can find all the attributes. But then with the (supervisor, researchTopic, student), I also can find consultationDay. However those are just superkey and not candidate key. So should it be a dependency?

Question 2: Assume my dependencies were correct, I can deduced this relational table to be in BCNF. However in my lecture notes,

The definition of Boyce-Codd Normal Form (BCNF)states that a relation is in BCNF if and only if every determinant is a candidate key.

This is very different from what I found on the net (eg. wiki):

A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold:

X → Y is a trivial functional dependency (Y ⊆ X)
X is a superkey for schema R

So now, according to my lect notes, with the dependencies found, the table will not be in BCNF as (supervisor, researchTopic, student) is not a candidate key, it is just a superkey. However if is according to wiki's, then this table will be in BCNF as all the determinants are superkey.So is this table in BCNF?

Answer 1

Both the definitions of BCNF that you cite do not mention which set of Functional Dependencies is used when checking for satisfaction of the normal form, but this is important.

You know that, given a set of Functional Dependencies, for instance that found by reasoning over a problem, there are many equivalent sets, or more precisely there are many sets that are a coverage of it; for instance, a minimal or canonical cover of a set of FDs is a cover with no redundant dependencies nor superfluous attributes, and with a single attribute on the right part of each dependency.

So, actually, it is easy to prove that a definition that mentions superkeys, like the wiki definition, for instance, is equivalent to a definition that mentions candidate keys, like those of your lecture notes, when the functional dependencies considered are those of a minimal cover. In fact, in a minimal cover trivial dependencies are not present, as well as no strict superkeys (i.e. a superkey formed by a candidate key plus a non-empty set of attributes) can be present as left part of any dependency, for the definition of minimal cover.

So, when checking for a normal form, it is always a good idea to first find a minimal cover of the given dependencies.

For what concerns the functional dependencies of your example, given the specification of the problem, it is not clear to me if a student selects a reasearch topic and then can go to any supervisor for that research topic to discuss it, or instead is assigned also to a specific supervisor. Of courses the dependencies are different in the two cases.

Answer 2

students are to select one research topic

student -> research-topic

Because each student has only one research topic, and these are the only two attributes in this relation, we know student is unique, and thus a candidate key.

Students are allowed to select the same research topic.

That tells us research topic is not unique in the same relation, and cannot be a candidate key.

For each research topics, supervisors are assigned to supervise it.

research-topic -> supervisor

A supervisor may be supervising up to two different research topics

So supervisor is not unique in this relation, and cannot be a candidate key.

each research topic may be assigned to different supervisors.

OK, revise (1st time)

research-topic, supervisor -> {}

Each researcher many have more than one topic, and each topic more than one researcher.

For each of the research topic a supervisor supervises, a consultation day is allocated for the student

2nd revision:

research-topic, supervisor, student -> consultation-day

This is a bit messy, perhaps intentionally so to create a problem to solve. Since each student has only 1 research topic, For each of the research topic is a red herring. We can equally well say:

3rd revision:

research-topic, supervisor -> {}

and

supervisor, student -> consultation-day

It's unnecessary to put topic in the key of the 3rd relation because when the student meets with the supervisor, it will be on the student's only topic. If a student could have more than one topic, we'd have to add that to the relation, to know what's on the agenda on consultation-day.

to meet and discuss with the supervisor.

Call these three relations student, supervisor, and consultation. I leave it to you to write a join to produce {student, topic, supervisor, day}, and show that the natural join of student with supervisor produces only 1 row.

All I have done is express the stated requirements as dependencies. Every dependency is minimally captured. That, in essence, is BCNF.

Your student table is not BCNF. Nowhere is it stated that students choose or are assigned a supervisor.