-1

Ok so i basically created a students table that has the following attributes :- Id(PK),firstname,lastname,age,nationality,email,phonenumber. Now, i am still very very much confused about the 2nd and 3rd normalization form they are quite hard to understand and differentiate. However, the most i understood was that in 3rd normalization form, each attribute shouldn't depend upon each other. I had a quick look at my students table and thought it matches the 3rd normalization form. However i am not sure. What do u think..

Rahul
  • 76,197
  • 13
  • 71
  • 125
Buffy Anne-summers
  • 29
  • 6
  • If two students have the same nationality, then it is not in 3rd normal form. – Gordon Linoff Dec 03 '16 at 20:22
  • 1
    @GordonLinoff can you explain why you say that? I would think nationality is simply a dependent attribute of a student. If two students with the same nationality breaks 3NF, then why wouldn't two students with the same age break 3NF? – Bill Karwin Dec 03 '16 at 20:25
  • @BillKarwin has a point. So am i right? Is my table matching the 3rd normalization form? – Buffy Anne-summers Dec 03 '16 at 20:31
  • 1
    Normal forms through BCNF depend solely on functional dependencies. What are the functional dependencies? – Mike Sherrill 'Cat Recall' Dec 03 '16 at 20:36
  • @MikeSherrill'CatRecall' you mean the relations i created with other tables? Well this table doesn't have any foreign key.. It is linked with other tables tho – Buffy Anne-summers Dec 03 '16 at 20:52
  • Please learn about functional dependencies, candidate keys and normal forms, then edit your question to show us what you can figure out about your case, because from you question and comments you have no understanding of what normalization is. Having *any* kind of *uninformed* look is pointless. – philipxy Dec 04 '16 at 09:55
  • Possible duplicate of [Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?](http://stackoverflow.com/questions/27474203/normal-forms-2nd-vs-3rd-is-the-difference-just-composite-keys-non-trivial-d) – philipxy Dec 04 '16 at 10:25
  • @BillKarwin . . . Clearly, my comment can be read in a way other than as I intended. This should be clearer: Repeating values of nationality in the `students` table would be an example of non-normalized data. – Gordon Linoff Dec 04 '16 at 18:59
  • @GordonLinoff, oh, is this about creating a `nationality` table and making references to it by an integer `nationality_id`? Because you should know that has nothing to do with normalization. – Bill Karwin Dec 04 '16 at 19:02
  • @GordonLinoff That isn't any clearer. If you mean the non-relational non-SQL property of "having a repeating group", it seems inapplicable when we're given a "table" with a "PK". If you mean nationality as a list, set, table etc then we enter the realm of whether we limit the definition of "normalized" to tables with certain column types, which is othogonal to higher normal forms. [What is atomicity in dbms](http://stackoverflow.com/a/24038895/3404097) Otherwise, maybe you are forgetting that we are told that id is a PK? (Although the OP doesn't seem to know enough to know whether that's so.) – philipxy Dec 04 '16 at 21:00

2 Answers2

0

In 2NF the table is in 1NF and attributes are dependent on the complete primary key. In 3NF, 2NF must be followed and then there cannot be any transitive dependency. By this, we mean that if A->B and B->C then A->C where A is part of the primary key. Here C is dependent on A because of B. So this is not allowed in 3 NF. In your case, your table is in 3NF form because all attributes are characteristics of a student. So, all attributes are dependent on the primary key Id(PK) and no attribute is transitively dependent on Id(PK).

basicknowledge
  • 93
  • 1
  • 9
  • Those are not good definitions. Eg 2NF requires *no* *partial* dependency of *non-prime* attributes on any *candidate key*. So assuming there's *only one* candidate key, being dependent on the complete PK is inadequate (it happens even when there is partial dependency) and also it should be non-prime attributes. @BuffyAnne-summers: You need to memorize *correct* definitions from a college/university text and in turn the definitions of the terms they use. – philipxy Dec 04 '16 at 10:09
  • @sukanyanath The convention for writing dependencies is an arrow pointing from the independent variable to the dependent variable, i.e. A->B->C, which I read as A determines B which determines C, or C is dependent on B which is dependent on A. – reaanb Dec 05 '16 at 06:52
  • @philipxy yes, they are probably not good definitions but I was trying to make it easy to understand. Thank you for pointing out the difference. Buffy Anne-summers, glad you found it useful! I do hope that you will look at proper definitions. – basicknowledge Dec 05 '16 at 12:43
  • For FDs, A->C transitively when A->B and B->C and not (C->A). And for 3NF every non-prime attribute must be non-transitively dependent on every CK, ie there must be no transitive dependence of a non-prime attribute on a CK. See my answer in my "possible duplicate" comment. – philipxy Dec 05 '16 at 13:20
0

Functional dependencies pertain to relationships between columns within the same table (not relationships between one table and another table). The id can be used to look up a row in your table, therefore the set of other columns in that table have a functional dependency on id. In other words, if you know the value of id, you can use it to find the corresponding values in the other columns.

In your students table, each of the attribute columns is functionally dependent on the id column, and none of them are functionally dependent on each other.

For example, if I know a student's email, that doesn't automatically give me enough information to know their phone number (or to rule out a possible phone number). And similarly for all other combinations of columns.

So all the attribute columns have an FD on the primary key, and nothing but the primary key.

That's the definition of 3NF.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • A "definition" like that is not very helpful, even if you limit it to when there is only one candidate key. Eg every attribute is always trivially dependent on itself, but that doesn't violate 3NF. – philipxy Dec 04 '16 at 10:01
  • @philipxy, well I hope it will help the OP understand nevertheless. Not intended to be a formal definition. – Bill Karwin Dec 04 '16 at 19:00