Relational Model

Relational Data Model

Represent data in the form of tables A named, two-dimensional table is called a relation. Each relation consists of named columns and an arbitrary number of unnamed rows. A named column is called an attribute. Each row of a relation corresponds to a record that contains data

Required

must have a value

Optional

may not have a value

Simple (or atomic)

cannot be broken down into smaller components

Composite

has meaningful component parts

Single-valued

attribute which has only one value

Multivalued Attributes

attribute that may take on more than one value

Stored

value is provided by user

Derived

value is computed or derived from another attribute/s

Identifier

an attribute or combination of attribute whose value distinguishes instances

of an entity type. Must not change in value. Must not be null. Must be unique

Primary Key

an attribute or a combination of attributes that uniquely identifies each row in a

relation

Composite key

a primary key that consists of more than one attribute

Foreign Key

– attribute used to establish the relationship between two tables. A key in a

table/relation always point to the primary key of another table/relation

Integrity Constraints

Rules limiting acceptable values and actions, to facilitate maintaining

the accuracy and integrity of data

Domain Constraints

is a set of values that can be assigned to an attribute ; a

domain definition consists of domain name, meaning, data type, size and allowable

values for the domain values

Entity Integrity

ensures that every relation has a valid primary key

Referential Integrity

rule that maintains consistency among the rows of two relations.

The rule states that if there is a foreign key, either each foreign key value must match a

primary key value or the foreign key value must be null.

Database Normalization

Optimizing table structures. Removing duplicate data entries. Process of efficiently organizing data in the DB. A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise. A formal method that identifies relations based on their primary key and the functional

dependencies among their attributes.

Insertion Anomaly

occurs when certain attributes cannot be inserted into

the database without the presence of other attributes

Update Anomaly

exists when one or more instances of duplicated data is

updated, but not all

Deletion Anomaly

exists when certain attributes are lost because of

the deletion of other attributes

Functional dependency

Describes the relationship between attributes in a relation.

Determinant

attribute or set of attributes on the left hand side of the arrow.

Unnormalized form (UNF):

A table that contains one or more repeating groups.

Repeating group

an attribute or group of attributes within a table that occurs with multiple

values for a single occurrence of the nominated key attributes of that table.

First normal form (1NF):

A relation in which the intersection of each row and column contains one

and only one value, meaning there are no repeating groups (Removing repeating groups).

Second normal form (1NF -> 2NF):

A relation that is in 1NF and with no partial dependencies. Remove partial functional dependencies.

partial functionally dependent attributes

thios attribute are removed from the relation by

placing them in a new relation along with a copy of their determinant in 2NF.

Partial functional dependency

is when a nonkey attribute is functionally dependent on part (but

not all) of the primary key

Full functional dependency:

when a nonkey attribute is functionally dependent on the primary key.

Third normal form (3NF)

A relation in which no non-primary key

attribute is transitively dependent on the primary key. Remove transitive dependencies.