By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Transitive dependencies

P: n/a
Hi there,

I'm having some trouble identifying transitive dependencies in the student
table below

StudId Name CourseCode CourseDesc Lecturer Grade Office
'S1234', 'Jack', 'C224', 'Database', 'Codd', 'D', 381
'S1234', 'Jack', 'C225', 'Algorithms', 'Djikstra', 'P', 380
'S2345', 'Jill', 'C224', 'Database', 'Codd', 'HD', 381
'S2345', 'Jill', 'C226', 'Architecture', 'Ritchie', 'HD', 390
'S4567', 'Jack', 'C226', 'Architecture', 'Ritchie', 'D', 390
'S4567', 'Jack', 'C224', 'Database', 'Codd', 'F', 381
'S9872', 'Howard', 'Cpol', 'Politics', 'Marx', 'F', 380

I've first tried to find the functional dependencies (they may or may not be
correct)

* StudId -> Name
* StudId, CourseCode -> Name, CourseDesc, Lecturer, Grade, Office
* CourseCode -> CourseDesc, Lecturer, Office
* CourseDesc -> CourseCode, Lecturer, Office
* Lecturer -> CourseCode, CourseDesc, Office

I then thought that StudId, CourseCode -> Office, CourseDesc, Lecturer
might be a transitive dependency within student. Is it?





Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Before identifying transitive dependencies, you need to clearly identify the
primary key. The primary key is an attribute (or attributes!) that uniquely
identifies every record in the relation. Once you've done that, the
transitive dependency should be apparent. Hint: Dr. Codd might well teach
more that one Database course, but he will only have one office.
"David" <au*******@hushmail.com> wrote in message
news:3f**********************@news.optusnet.com.au ...
Hi there,

I'm having some trouble identifying transitive dependencies in the student
table below

StudId Name CourseCode CourseDesc Lecturer Grade Office
'S1234', 'Jack', 'C224', 'Database', 'Codd', 'D', 381
'S1234', 'Jack', 'C225', 'Algorithms', 'Djikstra', 'P', 380
'S2345', 'Jill', 'C224', 'Database', 'Codd', 'HD', 381
'S2345', 'Jill', 'C226', 'Architecture', 'Ritchie', 'HD', 390
'S4567', 'Jack', 'C226', 'Architecture', 'Ritchie', 'D', 390
'S4567', 'Jack', 'C224', 'Database', 'Codd', 'F', 381
'S9872', 'Howard', 'Cpol', 'Politics', 'Marx', 'F', 380

I've first tried to find the functional dependencies (they may or may not be correct)

* StudId -> Name
* StudId, CourseCode -> Name, CourseDesc, Lecturer, Grade, Office
* CourseCode -> CourseDesc, Lecturer, Office
* CourseDesc -> CourseCode, Lecturer, Office
* Lecturer -> CourseCode, CourseDesc, Office

I then thought that StudId, CourseCode -> Office, CourseDesc, Lecturer
might be a transitive dependency within student. Is it?





Nov 12 '05 #2

P: n/a

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Qs********************@newssvr28.news.prodigy .com...
Before identifying transitive dependencies, you need to clearly identify the primary key. The primary key is an attribute (or attributes!) that uniquely identifies every record in the relation. Once you've done that, the
transitive dependency should be apparent. Hint: Dr. Codd might well teach more that one Database course, but he will only have one office.

Well, StudId, CourseCode uniquely identifies every row - I think this is
probably the primary key.

I should have explained myself better: the reason I thought that StudId,
CourseCode -> Office, CourseDesc, Lecturer was a transitive dependency was
because CourseCode and Lecturer both depend on CourseDesc, and CourseDesc in
turn depends on StudId, CourseCode. Similarly, Office depends on Lecturer
and Lecturer depends on StudId, CourseCode.

Have I understood the concepts correctly? I'd like to put the student table
in *both* 2NF and 3NF.
Nov 12 '05 #3

P: n/a
A transitive dependency is a situation where an attribute depends on another
attribute that is not part of the primary key. So, if (StudID, CourseCode)
is the PK, examine each of the other attributes to see if any are dependant
on an attribute that is not part of the PK.

"David" <au*******@hushmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Qs********************@newssvr28.news.prodigy .com...
Before identifying transitive dependencies, you need to clearly identify the
primary key. The primary key is an attribute (or attributes!) that

uniquely
identifies every record in the relation. Once you've done that, the
transitive dependency should be apparent. Hint: Dr. Codd might well

teach
more that one Database course, but he will only have one office.

Well, StudId, CourseCode uniquely identifies every row - I think this is
probably the primary key.

I should have explained myself better: the reason I thought that StudId,
CourseCode -> Office, CourseDesc, Lecturer was a transitive dependency was
because CourseCode and Lecturer both depend on CourseDesc, and CourseDesc

in turn depends on StudId, CourseCode. Similarly, Office depends on Lecturer
and Lecturer depends on StudId, CourseCode.

Have I understood the concepts correctly? I'd like to put the student table in *both* 2NF and 3NF.

Nov 12 '05 #4

P: n/a
rkc

"David" <au*******@hushmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Qs********************@newssvr28.news.prodigy .com...
Before identifying transitive dependencies, you need to clearly identify the
primary key. The primary key is an attribute (or attributes!) that

uniquely
identifies every record in the relation. Once you've done that, the
transitive dependency should be apparent. Hint: Dr. Codd might well

teach
more that one Database course, but he will only have one office.

Well, StudId, CourseCode uniquely identifies every row - I think this is
probably the primary key.

I should have explained myself better: the reason I thought that StudId,
CourseCode -> Office, CourseDesc, Lecturer was a transitive dependency was
because CourseCode and Lecturer both depend on CourseDesc, and CourseDesc

in turn depends on StudId, CourseCode. Similarly, Office depends on Lecturer
and Lecturer depends on StudId, CourseCode.

Have I understood the concepts correctly? I'd like to put the student table in *both* 2NF and 3NF.


You're confusing a partial dependency for a transitive dependency.
A partial dependency is when an attribute is functionally dependent on only
part of the primary key.

In your base relation CourseDesc is only dependent on CourseID, Lecturer
is only dependent on CourseID and Name is only dependent on StuID.
All are partial dependencies.

Office is dependent on Lecturer, Lecturer is dependent on CourseID, so
Office is transitively dependent on CourseID.



Nov 12 '05 #5

P: n/a

"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message
news:YL*******************@twister.nyroc.rr.com...

You're confusing a partial dependency for a transitive dependency.
A partial dependency is when an attribute is functionally dependent on only part of the primary key.

In your base relation CourseDesc is only dependent on CourseID, Lecturer
is only dependent on CourseID and Name is only dependent on StuID.
All are partial dependencies.

Office is dependent on Lecturer, Lecturer is dependent on CourseID, so
Office is transitively dependent on CourseID.

But isn't CourseID (CourseCode) functionally dependent on Lecturer? The
definition that I'm looking at says that:

If A -> B and B->C then C is transitively dependent on A via B *provided
that* A is not functionally dependent on B or C.

In the example you gave, A (CourseID) is *not* dependent on C (Office) but A
*is* dependent on B (Lecturer) - for each value of Lecturer there's exactly
one value of CourseID.
Nov 12 '05 #6

P: n/a
rkc

"David" <au*******@hushmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...

"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message
news:YL*******************@twister.nyroc.rr.com...

You're confusing a partial dependency for a transitive dependency.
A partial dependency is when an attribute is functionally dependent on only
part of the primary key.

In your base relation CourseDesc is only dependent on CourseID, Lecturer
is only dependent on CourseID and Name is only dependent on StuID.
All are partial dependencies.

Office is dependent on Lecturer, Lecturer is dependent on CourseID, so
Office is transitively dependent on CourseID.

But isn't CourseID (CourseCode) functionally dependent on Lecturer? The
definition that I'm looking at says that:

If A -> B and B->C then C is transitively dependent on A via B *provided
that* A is not functionally dependent on B or C.

In the example you gave, A (CourseID) is *not* dependent on C (Office) but

A *is* dependent on B (Lecturer) - for each value of Lecturer there's exactly one value of CourseID.


You're a step behind worrying about transitive dependencies. Get your
relation
into 2NF and take another look.
Nov 12 '05 #7

P: n/a
"David" <au*******@hushmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...

"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in message
news:YL*******************@twister.nyroc.rr.com...

You're confusing a partial dependency for a transitive dependency.
A partial dependency is when an attribute is functionally dependent on only
part of the primary key.

In your base relation CourseDesc is only dependent on CourseID, Lecturer
is only dependent on CourseID and Name is only dependent on StuID.
All are partial dependencies.

Office is dependent on Lecturer, Lecturer is dependent on CourseID, so
Office is transitively dependent on CourseID.

But isn't CourseID (CourseCode) functionally dependent on Lecturer? The
definition that I'm looking at says that:

If A -> B and B->C then C is transitively dependent on A via B *provided
that* A is not functionally dependent on B or C.

In the example you gave, A (CourseID) is *not* dependent on C (Office) but

A *is* dependent on B (Lecturer) - for each value of Lecturer there's exactly one value of CourseID.


The example rkc gave you was perfect.
A(CourseID) -> B(Lecturer) and B(Lecturer) -> C(Office)
C is dependent on B and B is dependent on A
So C is transitively dependent on A

Also, as rkc stated, as did I in my first post, the transitive dependency
has no significance until the relation is in 2NF. Do that by defining the
primary key. THEN examine the transitive dependencies.

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.