How do you determine functional dependencies and a primary key?

Question

In my Oracle Database Programming course, the first part of our final lab assessment requires that we:

• Identify the Primary Key of the table as it is currently shown
• Find all functional dependencies of the table which we are given to work with.
• Draw the dependency diagram for the table(s)

The table is in 1NF to begin with.

From the research I have done, it seems like I need to essentially combine every possible FD, which would not only consume a very large amount of time, but seems bizarre considering he wants us to then map these relationships in the dependency diagram. This would basically cause everything to link to everything - and this is why I believe I do not understand functional dependencies.

I understand that a functional dependency constitutes that in R, X->Y, where Y is not produced by anything other X, and should enable you to determine every other value in the table through this dependency.

I also understand that 'X' and 'Y' can consist of more than one attribute. This is what I don't understand, because if I map my attributes into algebraic variables (which seems to be the way to do it?), I have the letters A-J; the number of dependencies I would come up with seems astounding, and I don't want to waste time doing something the wrong way.

Does this mean I need to provide all fully functional dependencies, partial dependencies, and transitive dependencies?

My table consists of 10 columns in its current state of 1NF - thus, A-J would be my attribute identifiers in the process. I have found that R(AD) constitutes a formidable Primary Key, but I'm not sure if I need to derive the PK from laying out all of the FD's, or if I can choose a PK and find my FD's from this point. If that's the case - do I still lay out every FD, given that my PK will really determine the mapping of the relationships within the model?

https://www.dropbox.com/s/3vwo1axe7a1i20s/final%20lab%20instructions.pdf?dl=0

Answer 1

You must know what the table and rules mean first. Apparently, the table means something like:

/*
student with id [si] has name [sn] and address [sa] and major [sm]
    and takes course [ci] with title [ct]
        from instructor with id [ii] and name [in] and office [io]
        with grade [scg]
*/
t(si,sn,sa,sm,ci,ct,ii,in,io,scg)

The table (aka relation) holds rows that make a criterion (aka predicate) true. That criterion can be expressed as the AND of a bunch of separate criteria and decomposing it into them is what design and normalization is about. A table like this from ANDing together everthing you could say about your application is called its "universal relation".

Now using what that criterion means and what the rules mean you have to figure out for every subset of columns when a given value for it always appears with one value for another column, ie what FDs hold. Note that if a subset determines a column then all supersets of that column determine that column. You can also rule out FDs when you know that a given subset can appear with more than one value of a column. Learn about the "transitive closure" and "minimal cover" of a set of FDs to express all FDs concisely. You must show that you have accounted for every possible determinant, ie for every subset of columns.

Every time you decompose you have to decide whether you want a given component only ever to hold the projection of rows of the original on its columns or whether it could hold other rows as well. Eg can a professor exist without teaching a course? If so it means that your original criterion/table is not actually sufficient to say all the things that you want to about your application and you need an extra table that is not a projection of it but has the same columns. This is often described as part of normalization but it isn't. It is noticing that your design was wrong while you are normalizing.

You may have to hypothesize because you don't know what situations can arise. We normally assume X_ids are 1:1 with Xs; is that so? Can a student have more than one name? If not then {si} -> sn. Even if they do, is the table going to only record one? If so, the criterion becomes "...and has name [sn] that has been chosen to be recorded..." and all your queries that involve student names are going to involve those that "have been chosen to be recorded" and the above FD holds.

• Only one class is taught for each course ID.
• Students may take up to 4 courses.
• Each course may have a maximum of 25 students.
• Each course is taught by only one Instructor.
• Each student may have only one major.

What's a class? Are there subrows of values that are 1:1 with classes, so that what goes for classes goes for those subrows?

Does ci determine ct? Does ii determine in? From the rules, a given ci only appears with one ii. FD. From the rules, a given si only appears with a single sm. FD. From the rules, si does not determine ci and course does not determine si.

Unanswered questions must be resolved between you and your client (professor).

You can only determine candidate keys (one of which you can call the primary key) on FDs.

(Your assignment uses E-R so you need to find criteria/predicates about entities, then find corresponding ones about column sets that identify entities, which get tables.)