Relational Database Design Principles

Keys and Superkeys

superkey - a set of attributes that uniquely identifies any entity from among all possible entities in the entity class.

key - A superkey that has no proper subset of it is also a superkey.

Redundancy

Attributes can be used for identification and/or informational purposes.

Normal Forms

A normal form is a special set of forms, properties, or constraints a table scheme may possess, in order to achieve certian desired goals.

normalization - the process of changing a database design to produce table schemes in normal from.

First Normal Form

First Normal Form (1NF) occurs when all attributes in a table scheme are indivisible.

scalar or atomic attribute - an attribute that only allows indivisible values.

structured attribute - an attribute that allows a list of divisible items.

Good database design almost always requires that an attribute be atomic so that the table scheme is in first normal form.

Functional Dependencies

The attribute of left completely determines the attribute of the right.

{PubName, PubPhone} -> {PubID}

superkey - a set of attributes upon which all other attributes of the table scheme are functionally dependent.

trivial dependencies - If we know the values of A, B, C, then we know the value of A and B.

nontrivial - If is is not a trivial dependency.

Second Normal Form

Second Normal Form (2NF) occurs when all strictly informational attributes are attributes of the entities in the table scheme, and not of some other class of entities.

example: {City, Street, HouseNumber, HouseColor, CityPopulation}

CityPopulation is strictly informational and gives information about that city and not the house address.

{City} -> {CityPopulation}

Third Normal Form

Third Normal Form (3NF) occurs only if strictly informational attributes depend only upon superkeys.

Boyce-Codd Normal Form

Boyce-Codd Normal Form (BCNF) occurs if no attribute depends upon anything other than a superkey.

Table Scheme {City, StreetName, ZipCode}

{City, StreetName} -> {ZipCode}

and:

{Zipcode} -> {City}

Normalization

{ISBN, Title, Authors} becomes {ISBN, Title, AuID} and {AuID, AuName} which is 1NF.

{City, StreetName, ZipCode} becomes {ZipCode, City} and {ZipCode, StreetName} which is BCNF.

A design of a database may begin with E/R diagram. If some of the table schemes have redundanceis, it may be desirable to split them into smaller table schemes that satisfy a higher normal form.

Decomposition

lossless decomposition - decomposing a table scheme into small schemes does not cause any information to be lost.

dependency-preserving decomposition- decomposing that doesn't cause any dependencies to be lost.