BTMA 331 02 - relational schema

Relational Schema

what is a relation

a named, two-dimensional table of data.

• A table consists of rows (records) and columns (attribute or field)

relational database

represents data in the form of tables

components of a relational model

data structure, data manipulation, data integrity

data structure

table and keys

data manipulation

Powerful SQL operations for retrieving and modifying data

data integrity

Mechanisms for implementing business rules that maintain integrity of manipulated

data

EER

enhanced entity relationship - more detailed + comprehensive version of ERD

requirements for a table to qualify as a relation

— It must have a unique name.

— Every attribute value must be atomic (not multivalued, not composite).

— Every row must be unique (can’t have two rows with exactly the same values for all their fields).

— Attributes (columns) in tables must have unique names.

— The order of the columns must be irrelevant.

— The order of the rows must be irrelevant.

entity integrity rule

No primary key attribute (or component of a primary key attribute) may be null

in entity integrity constraints, all primary key fields must be

NOT NULL and Unique to each other

referential integrity constraints

a rule that maintains consistency among the rows of two related tables

in referential integrity constraint, if there is a foreign key in one relation, then

either each foreign key value must match a primary key value in another relation or the foreign key value must be null.

approaches to ensure the referential integrity

restrict, cascade, set-to-null

restrict

don’t allow delete of “parent” side if related rows exist in “dependent” side

cascade

automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted

set to null

set the foreign key in the dependent side to null if deleting from the parent side

functional dependency

constraint between two attributes in which the value of one attribute is determined by the value of another attribute

functional dependency example: The functional dependency of B on A is represented by an arrow: A → B… A functional dependency is not a mathematical dependency: B ____ be computed from A. Rather, if you know the value of A, there can be ___ ___ value for B.”

cannot, only one

candidate key

A unique identifier. One of the candidate keys will become the primary key

• ex: there is both credit card number and SS# in a table...in this case both are candidate keys.

Each non-key field is functionally dependent on every candidate key

normalization

process of decomposing relations with anomalies to produce smaller, well-structured relations.

normalization is primarily a tool to

 to validate and improve a logical

design so that it satisfies certain constraints that

avoid unnecessary duplication of data

relations in first normal from (1NF)

contain no multivalued attributes or repeating groups

relations in second normal form (2NF)

are in first normal form and contains no partial functional dependencies

• 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key

• Every non-key attribute must be defined by the entire key, not by only

  part of the key

relations in third normal form (3NF)

is in second normal form and has no transitive dependencies. A transitive dependency… is a functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.”

• 2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)

• the primary key is a determinant for another attribute, which in turn is a determinant for a third