FD and databases

Question

I'm trying to find all the functional dependencies that hold in this table:

The way I understand it is: If faculty determines office_hours then since Sun has 4 appearances in faculty, each one has to map to the same value for office_hours. But since they map to 3 different values for office_hours, FD faculty ---> office_hours doesn't hold in this table. Correct?

I get that the following FDs hold in the table. How can I check whether this is right?

[faculty]---> {room,phone,}

{phone}---->{faculty,room,}

{faculty,room}--->{phone}

(faculty,phone,room]--->{course}

Answer 1

^{Transferring multiple comments into an answer, with some amendments, additions, etc.}

This whole answer assumes that it is legitimate to infer functional dependencies based solely on the values visible in the table. If there is other information to modify the rules, the answer might have to change, but the question would have to change too.

You're right, faculty does not determine office hours. Equally clearly, quarter does not determine office hours; Fall 2013 has two different values for office hours associated with it. OTOH, Faculty + quarter does determine office hours; so too does phone + quarter, and room + quarter, but it seems likely that everyone involved considers faculty + quarter to be the relevant determinant.

The empty set, Φ, determines section number since the section number is the same for all rows.

The triad faculty, room, phone could have any one column determining the other two on the basis of the sample data; on the whole, though, it would be normal to assume that faculty is the most important member of these three attributes, and the room and phone are determined by faculty. (Note that I'm referring to the projection of the sample data over just these three attributes.)

You haven't specified what determines course (if anything does) — nor have you mentioned it yet.

Ahhh!! Yeah, I see the problem with quarter and office hours — thanks. But as for courses, I don't see what determines course, unless its just section number? I was looking at quarter ⟶ course, but Fall 2013 has 4 different courses.

If anything, course determines faculty on the basis of the data, and therefore it determines any and all of the faculty, phone, room triumvirate. Given course CS520, you know that the faculty is C Sun, the room E&T A317 and the phone x6697, don't you? It's not the only course that gives that, but a given course gives the same values for faculty, phone, room each time.

Yeah, you're right. I was very confused with how this works, but its becoming a little clearer. Thanks for the help. Now I gotta figure out how to get the key from these FDs. I also updated my answer with what I changed them to. Also, I was thinking: wouldn't faculty, room, phone also give the office hours?

No (faculty, room, phone don't control office hours. C Sun has different office hours in different quarters.

Ah! now I'm confused. So just to make sure, this (faculty,phone,room) ⟶ {course} is correct? I don't see much of a difference with (faculty,phone,room) ⟶ {officehours}; the same office hours match the same faculty, phone, and room. Also, why does quarters come into play? If we were just comparing (faculty,phone,room) ⟶ {course}.

No. Each faculty teaches two courses, so faculty alone does not determine course. In fact, I don't think anything determines course.

From the given data, it appears to me that the FDs are:

• faculty ⟶ phone, room
• room ⟶ faculty, phone

• phone ⟶ faculty, room

• course ⟶ faculty, room, phone

• faculty, quarter ⟶ office_hours

• Φ ⟶ section

(where Φ is the empty set).

Answer 2

If you were only told that the sample data is some possible value of a table, then you only know some cases where there is not an FD. Ie where a subtuple value for some columns appears with more than one value of another column. You cannot deduce that there is an FD when a given possible determinant appears with only one value.

You may have been told to propose what sensible business rules you think should hold. That would lead to certain FDs holding or not holding besides the ones above.

Or you may have been told that the data was chosen so that all non-FD cases are shown. Then you know some FDs and can derive others using FD derivation rules.

Or you may have been told that whenever a subtuple value for some columns appears with only one value of another column then that indicates a FD. There again you know some FDs and can derive others using FD derivation rules.

So you need to find out what the example table has been set up to communicate.