Relational Databases

Term

Definition

attribute, column, field

A single data element

row, tuple, record

A set of attributes

table

A set of rows

relation

A set of 2 or more tables that may exhibit two types of relational property.  One is normalization.  The other is referencing or linking.

data type

Attributes are assigned a data type.  Examples of data types are numeric (real), integer, character, date. 

constraint

Attributes are optionally assigned constraints.  The constraints are:

  1. Null/Not Null:  Can be null or not
  2. Check: Must be specific values
  3. Primary Key: Is designated as the primary key
  4. Unique: Must have a unique value
  5. Foreign Key... References: References a key in another table

domain

A pre-specified data-type and set of constraints.  Attributes can then be assigned to a domain, as a convenience.

key, candidate key

One or more attributes that uniquely identify the row

primary key

An arbitrarily selected key.

foreign key

An attribute or set of attributes in a table B that references a key in another table, A.  The data-types of the foreign key and its referenced attribute(s) must be the same.  This is one mechanism for establishing the referencing property of a relation.  The other mechanism is a join. 

nonkey

An attribute that is neither a key nor a key component

referential integrity

Two tables, A and B,  have referential integrity if every record in table B is linked by a key-foreign key relation to a record in table A.  Conversely, if a table has a foreign key then it participates in referential integrity.

join

A new table that is dynamically created, based upon (1) selection of attributes from the "joined" tables, and (2) linkage of attribute values between the "joined" tables.  Tables that are joined do not necessarily have referential integrity.

 

Example

normalization

A process whereby data redundancy is reduced and illogical results from joins are eliminated.

functional dependency

An attribute A is functionally dependent (fd) on an attribute B if there is an N to 1 mapping of the values of B onto those of A.  That is, A particular value of B will always have a particular value of A associated with it, though 2 different values of B can associate with the same value of A. Or, we can say that A has a 1 to N relation to B.  Symbolically, we say B->A, or B implies A or, if you give me B I will tell you what A is.  Example: Assume that  B= County and A =  Tax Rate and that a particular County has a certain Tax Rate for its real estate.  Then we can say that Tax Rate is fd on County.  That is, if you tell me the County I'll tell you the Tax Rate.  Also, two different Counties can have the same Tax Rate.

Normalization

Normal Form

Test for Violation of Normal Form (nNF)

Normalization (Remedy)

1NF

A Table should have no composite (non atomic) attributes, such as arrays, structures or nested relations.

Form another table and relation that breaks the composite attribute into atomic attributes.  The composite attribute becomes another table, possibly in combination with other coordinated composites and attributes of the original table.  A key of the original table is also placed in the new table, where it becomes a foreign key that references the original table.

2NF

If a key contains multiple attributes, then no nonkey is functionally dependent on any component attribute of the key.

Create a separate table, where the key component becomes a key of the new table and the nonkey becomes a nonkey of the new table

3NF

A nonkey may not be functionally dependent on another nonkey or set of nonkeys.  That is, {a1, a2,...} -> b, where the ai are the determining set of nonkeys and b is the determined nonkey.

Create a separate table, where {a1, a2,...}becomes the key  and b becomes the nonkey of the new table.

Example 1

 

 

Example 2

 

Examples of Problems with Less than 3NF