Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?

by

We write S -> T and say that a set of columns S functionally determines another set T. S -> T in a table value when each subrow for the first always appears with the same subrow for the second. S -> T in a table variable/schema when S -> T in every value that can arise for it per the constraints.

We say S is the determinant set and T is the determined set. We call S -> T a FD (functional dependency). When S is a superset of T we say it is a trivial FD. When S is {A} we say A -> T & when T is {A} we say S -> A.

A superkey is a set of columns that uniquely identifies rows. That is so when it functionally determines every attribute. A proper subset or superset of a set is one that is not equal to it. A CK (candidate key) is a superkey that contains no proper superkey. We can pick a CK as PK (primary key). A column is prime when it is in some CK.

That’s enough to understand the answer that you link to:

The difference between 2NF and 3NF is this. Suppose that some relation
satisfies a non-trivial functional dependency of the form A->B, where
B is a nonprime attribute.

2NF is violated if A is not a superkey but is a proper subset of a
candidate key

3NF is violated if A is not a superkey

The quote says a NF “is violated if” such an FD exists. Also it’s only violated if such an FD exists.

A FD S -> T is partial when a proper subset of S also functionally determines T; otherwise it is full. Note that this does not involve CKs. A table is in 2NF when every non-prime column is fully functionally dependent on every CK.

S -> T is transitive when there is an X where S -> X and X -> T and not X -> S and not X = T. Note that this does not involve CKs. A table is in 3NF when every non-prime column is non-transitively dependent on every CK.

(Note the alternate definitions of 2NF & 3NF different from the quote.)

More Related Contents:

  1. Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
  2. Is storing a delimited list in a database column really that bad?
  3. Normalization in database management system
  4. What is the minimal proof that a database relation is not in BCNF?
  5. What are database normal forms and can you give examples? [closed]
  6. Normalization: What does “repeating groups” mean?
  7. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  8. have address columns in each table or an address table that is referenced by the other tables?
  9. Minimum no of tables that exists after decomposing relation R into 1NF?
  10. What’s the better database design: more tables or more columns?
  11. What exactly does database normalization do?
  12. How to perform compound queries with logical OR in Cloud Firestore?
  13. Database development mistakes made by application developers [closed]
  14. Copying PostgreSQL database to another server
  15. Surrogate vs. natural/business keys [closed]
  16. Do you use source control for your database items? [closed]
  17. database vs. flat files
  18. Can we update primary key values of a table?
  19. What are the design criteria for primary keys?
  20. Oracle (ORA-02270) : no matching unique or primary key for this column-list error
  21. How do I output the results of a HiveQL query to CSV?
  22. Normalization in plain English
  23. Import SQL dump into PostgreSQL database
  24. Can I do transactions and locks in CouchDB?
  25. Elasticsearch query to return all records
  26. wikidata get all properties with labels and values of an item
  27. Are Cloud Firestore queries still case sensitive?
  28. Populating Spinner From SQLite Database Android
  29. CAP theorem – Availability and Partition Tolerance
  30. DB design for microservice architecture [closed]

Leave a Comment