C170 - Data Management Applications

First Normal Form -- when all non-key columns depend on the primary key - a table with a primary key is in first normal form - a table with no duplicate rows is in first normal form Second Normal Form -- when all non-key columns depend on the whole primary key - cannot depend on part of a composite primary key - a table with a simple primary key is automatically in second normal form A table with a simple primary key must be in what normal form? -- second normal form or higher Third Normal Form -- when all non-key columns depend on the key, the whole key, and nothing but the key Violation of Third Normal Form -- a non-key column depends on a column that is not unique Boyce-Codd Normal Form -- whenever column A depends on column B, then B is unique - same as Third Normal Form, except "non-key" is removed A table has: - columns A, B, C, D - candidate key (A, C, D) - primary key (A, C, D) - dependency D → B (in addition to dependencies on candidate keys) What is the normal form of the table? -- First Normal Form - the table has one candidate key, which is also a primary key - any table with a primary key is in FIRST nf - the non-key column B does not depend on the whole primary key...so not in SECOND normal form A table has: - columns A, B, C, D - candidate keys (C, D) and A - primary key (C, D) - dependencies on (C, D) and A only What is the normal form of the table? -- Boyce-Codd - The table is in Boyce-Codd normal form since all dependencies are on candidate keys. - Therefore, all dependencies are on unique columns. A table has: - columns A, B, C, D - candidate key (A, B, D) - primary key (A, B, D) - dependency C → D (in addition to dependencies on candidate keys) What is the normal form of the table? -- THIRD - The table is in third normal form since non-key columns depend only on unique columns. - D is not a non-key column. - The table is not in Boyce-Codd normal form since D depends on the non-unique column C. - Primary keys must be minimal, so no column in a composite primary key can be unique. - Therefore, C is not unique. A table has: - columns A, B, C, D - candidate keys D and (A, C) - primary key (A, C) - dependency B → C (in addition to dependencies on candidate keys) What is the normal form of the table? -- THIRD