Database Terms

Front 1

Functional dependency:

Back 1

1. Attribute B has a functional dependency on attribute A if, for each value of attribute A, there is exactly one value of attribute B.

2. An attribute may be functionally dependent either on a single attribute or on a combination of attributes.

Front 2

Trivial functional dependency:

Back 2

An attribute depends on a superset of itself.

Front 3

Full functional dependency:

Back 3

An attribute is fully functionally dependent on a set of attributes X if.

1. Functionally dependent on X, and

2. Is not functionally dependent on any proper subset of X.

E.G {Employee Address} has a functional dependency on {Employee ID, Skill}, but not a full functional dependency, for it is also dependent on {Employee ID}.

Front 4

Transitive dependency:

Back 4

A transitive dependency is an indirect functional dependency, one in which X→Z only by virtue of X→Y and Y→Z.

Front 5

Multivalued dependency:

Back 5

A multivalued dependency is a constraint according to which the presence of certain rows in a table implies the presence of certain other rows.

Front 6

Join dependency:

Back 6

A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T.

Front 7

Superkey:

Back 7

A superkey is an attribute or set of attributes that uniquely identifies rows within a table; in other words, two distinct rows are always guaranteed to have distinct superkeys. {Employee ID, Employee Address, Skill} would be a superkey for the "Employees' Skills" table; {Employee ID, Skill} would also be a superkey.

Front 8

Candidate key:

Back 8

A candidate key is a minimal superkey, that is, a superkey for which we can say that no proper subset of it is also a superkey. {Employee Id, Skill} would be a candidate key for the
"Employees' Skills" table.

Front 9

Non-prime attribute:

Back 9

A non-prime attribute is an attribute that does not occur in any candidate key. Employee Address would be a non-prime attribute in the "Employees' Skills" table.

Front 10

Primary key:

Back 10

Most DBMSs require a table to be defined as having a single unique key, rather than a number of possible unique keys. A primary key is a candidate key which the database designer has designated for this purpose.

Front 11

First Normal Form (1NF)

Back 11

A relation (table) must not have any duplicate records. In other words, it must have at least one candidate key.

Every column must be atomic, i.e. single-valued with respect to its datatype. In other words, a column may represent exactly one member from its domain.

Front 12

Second Normal Form (2NF)

Back 12

The table (relation) must be in 1NF.

Functional dependencies of non-prime attributes on candidate keys are full functional dependencies. If a non-prime attribute of a table is functionally dependent on only a part (subset) of a candidate key, this table is not in 2NF.

Front 13

Third Normal Form (3NF)

Back 13

No non-prime attribute is transitively dependent on a candidate key. If a non-prime attribute is only indirectly dependent (transitively dependent) on a candidate key, this table is not in 3NF.

Alternative way to determine if a table is in 3NF:

For any functional dependency X → A, at least one of the following conditions holds:

X contains A

X is a superkey

A is a prime attribute

Front 14

Boyce-Codd Normal Form (BCNF)

Back 14

A table is in Boyce-Codd normal form (BCNF) if and only if, for every one of its non-trivial functional dependencies X → Y, X is a superkey. i.e., X is either a candidate key or a superset thereof.

2NF prohibits partial functional dependencies of non-prime attributes on candidate keys.

3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys.

BCNF does not permit any functional dependency in which the determinant set of attributes is not a candidate key (or superset of a candidate key).

Front 15

Fourth Normal Form (4NF)

Back 15

A table is in fourth normal form (4NF) if and only if, for every one of its non-trivial multivalued dependencies X -->> Y, X is a superkey, i.e., X is either a candidate key or a superset thereof.

4NF ensures that independent multivalued facts are correctly and efficiently represented in a database design.

A table with a multivalued dependency often causes redundancy. 4NF removes this redundancy.

Front 16

Fifth Normal Form (5NF)

Back 16

A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys.

5NF is designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships.