-2

Let some table stores information about subjects that are read by professors. The business rule is that one professor can read only one subject but the same subject can be read by many professor. Teacher field is unique. Id is a surrogate key.

The table structure and data example are as below.

id  teacher subject
--------------------
1    A       Math
2    B       Computer science
3    C       Math

Id here is a surrogate key.

The question is if the second condition of 3 NF definition holds for this table.

The 3NF definition I consider is

Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

The relation R (table) is in second normal form (2NF).

Every non-prime attribute of R is non-transitively dependent on every key of R.

PavelX
  • 9
  • 1
  • 3
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/207883/discussion-on-question-by-pavelx-third-normal-form-3nf-explanation). – Samuel Liew Feb 15 '20 at 13:07
  • @SamuelLiew (Thanks for moving that pile of comments.) Could you please also move the comments on the answer to chat? (I appreciate the comments being kept.) – philipxy Feb 15 '20 at 18:02

1 Answers1

-1

For the table given the only FDs that don't hold are X->{id}, X->{teacher} & X->{id, teacher} where X is {} or {subject}. Hence from the superkey we can conclude that CKs are {id} & {teacher}. Non-CK attribute is {sibject}.

FDs analysis shows that every non-prime attribute ({subject}) is non-transitevely dependent on every key (CK) beacause

{id}->{subject}, {teacher}->{subject}.

So the second condition of the definition holds.

PavelX
  • 9
  • 1
  • 3
  • Unfortunately Watt is a very poor textbook & it 3NF definition is wrong & the others are poor. Also: Why do you think those FDs hold? Why do you think those are the CKs? (How can you think you know the CKs if you don't know the FDs? Do you understand that when some FDs hold, others must, & there are always trivial FDs?) Your question is still too broad so you should be trying to pin it down to a specific question. You still don't justify anything. PS Dozens of published academic information modeling & DB design textbooks are online free in pdf. stanford.edu has a free online course. – philipxy Feb 13 '20 at 20:36
  • To show what FDs hold & don't hold given a definiton & brute force you must show for every pair of subsets of attributes whether the associated FD holds. There are shortcuts. Also it involves interpreting the rows in terms of the business. [Identifying Functional Dependencies II](https://stackoverflow.com/a/49475039/3404097) You really need to find a decent published textbook. What is the (Russian) one from your class? All these are SO faqs also, although usually the answers are very poor, which you can see by comparing them to a some textbooks. My answers are (mostly) OK. (I have to go now.) – philipxy Feb 13 '20 at 20:43
  • thank you, philipxy. I have no exact textbook recommended in my class since the database normalization topic was skipped and we started straight from SQL, and PL/SQL then. The question arose during the attempt to learn this topic by open sources. The specific question may be now formulated as below. The table stores information about subjects that are read by professors in that exact way. The business rule is that one professor can read only one subject, but the same subject can be read by many professor. Id here is a surrogate key. The question is if this table is in 3 NF. – PavelX Feb 14 '20 at 05:34
  • I see now that the answer is "yes" and as I understand your objection are not concerning this fact, they are about justification. I understand that it is not strict because,e.g., I didn't show the analysys of every FD existing. But my main purpose was to get the idea how this table meets 3NF condition because I thought that id-> teacher and teacher-> subject result in non-prime attribute transitive dependency id->subject. Now I see that it was wrong and why it was wrong. Thank you for helping me to be carefully with terminology and definitions and to get the answer desired. – PavelX Feb 14 '20 at 05:39
  • At this point your question is something like, you have this schema & some business rules & data & want to find all FDs that hold & you found a method (textbook or my link) & you did certain steps & you are stuck & googled with site:stackoverflow.com & here's what you know about being stuck, so, how to be unstuck? Please edit your post to ask such a question. The problem with not saying what definitions & algorithms you using & how you followed them is you are asking for that textbook section to be rewritten & a bespoke tutorial... to get stuck in.... Nb the current posts merit downvotes.... – philipxy Feb 14 '20 at 05:48
  • Assuming your latest business rules plus per your early question & comments that {teacher} is unique, the CKs are {id} & {teacher} & the only FDs that don't hold are X->{id}, X->{teacher} & X->{id, teacher} where X is {} or {subject}. PS When you literally don't know what algorithm to follow you need to find out & ask re where & how you are stuck following it or ask re where & how you are stuck finding an algorithm. And we can't say where you went wrong when you don't give algorithm & steps. PS Transitive FDs are complicated & many, use Zaniolo's 3NF definition if you have a choice. – philipxy Feb 14 '20 at 06:24
  • philipxy, at this point I realize my initial question came from treating terminology carelessly. Precisely I thought that if id is a primary key then {teacher}->{subject} is a FD between non-prime attributes because, in other words I thought that "non-prime" means "not a primary key". Because of the same reason I also considered that A->B & B->C means obviously transitive FD A->C anyway, even if A is PK and B is CK. But at that point I could not understand where was a mistake in my reasoning. – PavelX Feb 14 '20 at 09:32
  • You comments that my definitions are poor and wrong did show me the place where to find the answer. That was my my real problem. I will edit my question to clarify this. If you consider that this question merits downvotes I will not argue but I was stucked and this discussion eventually helps me for sure. – PavelX Feb 14 '20 at 09:32
  • "Since" hides non-obvious reasoning steps. PS Suggest that whatever question you think this is answering, you should edit the question post to ask it. Right now the question isn't clear & contains wrong reasoning. (According to the question's business rules, {teacher} is not a CK & neither {teacher}->{subject} nor {teacher}->{id} hold. But do you even need to mention the business rules?) – philipxy Feb 15 '20 at 08:08