databasedatabase-normalizationfunctional-dependencies3nfcandidate-key# Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?

I've viewed this answer at Difference between 2nd normalization phase / normal form and 3rd normalization phase / normal form but I don't understand the terms used (non-trivial functional dependency, superkey).

Maybe 2nd normal form to relates to composite keys whereas 3rd normal form relates to primary keys.

2nd normal form: There's a composite key and all fields in the table must relate to both of the composite key fields. If something doesn't relate then it should be refactored into another table.

3rd normal form: Everything has to be dependent on the primary key. I guess that in 3rd normal form there's only 1 key rather than in 2nd normal form where there can be composite keys.

Solution

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.)

- How to add 1 hour to currrent_timestamp in mysql which is the default value?
- FOR EACH STATEMENT trigger example in PostgreSQL
- row-level trigger vs statement-level trigger
- How to get start and end dates of current month
- I tried using postgresql on laravel but got could not find driver (SQL: select * from "users" where "id" = 4 limit 1)
- The type or namespace name 'SQLConnection' could not be found
- Clickhouse SummingMergeTree + a large number of ORDER BY fields
- Are Databases and Functional Programming at odds?
- Problem javax.persistence.EntityNotFoundException
- How can i update multiple databases at the same time in SQL Server?
- How to return the sum of one column but only if it matches another
- PostgreSQL: How to create two instances in same window machine?
- How do I deal with concurrent changes in a web application?
- How to select unique records by SQL
- Issue encountered while saving data to database in ASP.NET Core
- How to DROP a database with '`' at the beginning as adding single backstick cannot be solved
- H2 in-memory database. Table not found
- 'Idle in transaction' connections history in postgresql
- SQLite3 read issue with multiple simultaneous access to db
- Timescaledb design for existing tables / dedicated table(s)
- Why is my InDesign Javascript not fully working?
- How to skip certain database tables with mysqldump?
- org.h2.jdbc.JdbcSQLSyntaxErrorException after H2 version upgrade
- How to output the next date in SQL?
- How to kill Postgresql Sessions at "Idle In Transaction (aborted)" state automatically?
- What exactly does database normalization do?
- How to clear cache for database settings in Laravel 8?
- Backup/Restore a dockerized PostgreSQL database
- How to use upsert on the many to many relationship using sequelize?
- PostgreSQL 9.6 nested INSERT/RETURN statements have unacceptably poor CTE performance