1

I'm studying normalization and was wondering if this table could be considered to be normalized to 2NF?

FishTable

philipxy
  • 14,867
  • 6
  • 39
  • 83
DrSlugger
  • 29
  • 3
  • Hi. Please read & act on [ask], hits googling 'stackexchange homework' & the downvote arrow mouseover text. This shows no effort & no understanding of the topic of normalization to higher NFs, in which FDs are fundamental yet you don't mention them. Give a reference to the definitions & algorithms that you are to use & show what happens when you apply them & ask a specific question when stuck. [Note 1NF has no single meaning.](https://stackoverflow.com/a/40640962/3404097) This is just asking us to rewrite your textbook & do your homework. – philipxy Sep 12 '18 at 00:29
  • This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. – philipxy Sep 12 '18 at 00:31
  • @philipxy "...FDs are fundamental yet you don't mention them. " True, but 99% of the normalization questions on SO do not provide FDs. The temptation to rely on the semantics is too great for most people. – nicomp Sep 12 '18 at 13:59

1 Answers1

0

Yes, it is:

☑ 1NF: A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

Since the names of the lakes/creeks, though containing the fishes' names, are not dividable, they are atomic in themselves.

In other words: the first words of the lake/creek names alone are not sufficient to identify the lakes/creeks properly and so are the second words.

☑ 2NF: [...] a relation is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.

There is no proper subset of the PK attribute Fish , since it is just one (apart from the empty set {}, see comments). Best Lake is non-prime since it doesn't belong to the PK and it doesn't depend on a subset (because there is no proper one apart from the empty one) but on the whole PK.

Gerold Broser
  • 14,080
  • 5
  • 48
  • 107
  • The empty set {} is a proper subset of every other set & determines an attribute when the attribute can only have 1 value in a relation. So your reasoning that there is no partial dependency on {fish} is wrong. Moreover the question does not give enough information for a solution, so you are making assumptions that you are not mentioning, so you should state them, except instead you should comment for clarification. The question is unclear, too broad & a duplicate so it should be closed & it is not useful & shows no research effort so it should be downvoted & as such it should not be answered. – philipxy Sep 12 '18 at 00:41
  • @philipxy Yes, the empty set is a proper subset of every other set. Here "_Subset_" refers to a set of attributes that are candidate keys. In practice, how can a not existing key (Is it even a key then?) be a candidate key? And this is long before any values are assigned to attributes, so I can't comprehend what "_when the attribute can only have 1 value in a relation_" has to do with it. – Gerold Broser Sep 12 '18 at 01:42
  • Apply the definition of FD. If subrows on set of attributes {} all have the same value of Y then {}->Y. Ie if Y is constrained to hold only 1 value then {}->Y holds. Eg: Suppose a table is constrained to have at most one row. This is if & only if {} is its (only) CK; {} determines every attribute. Eg: Suppose a table is constrained to have attribute X be 7. Then {}->X. If {} isn't the CK then this violates 2NF. Read my answers re 2NF. PS [The definition of partial FD does not use the notion of CK. The definition of 2NF uses partial FDs & CKs.](https://stackoverflow.com/a/25827210/3404097) – philipxy Sep 12 '18 at 01:51
  • [functional dependencies involving the empty set](https://stackoverflow.com/a/16272415/3404097) – philipxy Sep 12 '18 at 02:05
  • @philipxy I understand this. Is this what's called "_theory vs. practice_"? And should I tell my next customer who likes to have a simple table like the one in the question that all this is much too sophisticated and he'd better forget about it? ;) – Gerold Broser Sep 12 '18 at 02:26
  • Tables/relations have attributes/columns & tuples/rows. Typically only some values for a table variable can arise. We say the table is constrained & we give constraints--conditions that the table variable's value alway satisfies. (An FD is a constraint.) A table could be constrained to have one row or to have at most one row. Eg a table of current parameter names & values. In those two cases {} determines every attribute & {} is a CK (the only one). Apply the definitions of FK & CK. (Last comment I chose ambiguous phrasing "constrained to have at most one row".) – philipxy Sep 12 '18 at 02:30
  • @philipxy Yes, I know. This is not my first table I normalized, and implemented (Though after skimming your answer you linked in your first comment to the question I'm not sure which of the normalizations you mention there I really did.). However, it always worked, the customers were happy. – Gerold Broser Sep 12 '18 at 02:39
  • You believed someone when they said something wrong. If they gave a justification then you didn't find the mistake(s). (Even textbooks get this wrong. Same reason.) Learn definitions & apply them. My last comment gives an example of {} as CK/determinant & the {}->Sex link gives a non-2NF table with {} as determinant--that you wrongly call 2NF. "He who loves practice without theory is like the sailor who boards ship without a rudder and compass and never knows where he may cast."--Leonardo da Vinci. {} is the 0 of sets. How is it not practical? PS See my answers re 5NF & normalization. – philipxy Sep 12 '18 at 02:53
  • "_In theory there is no difference between theory and practice, while in practice there is._" – Benjamin Brewster – Gerold Broser Sep 12 '18 at 07:56
  • Unfortunately the edit is also unsound. Some correct reasoning for 2NF here is: There is no proper subset of a CK that determines a non-CK attribute--because [by unstated assumption] the only CK is PK {Fish}, so the only non-CK attribute is Best Lake & the only proper subset of a CK is {}, but {} doesn't determine Best Lake because it appears with more than one Best Lake value. – philipxy Sep 12 '18 at 10:07