Scheduled maintenance: Saturday, March 6 from 3–4 PM PST
Upgrade to remove ads
Only $2.99/month

Week 4 - Normalisation

Terms in this set (25)

normalisation
a process that assigns attributes to entities so that the data redundancies are reduced or eliminated

normalisation is the process of evaluating and correcting table structures to minimise data redundancies, thereby reducing the likelihood of data anomalies

normalisation is a systematic series of steps for progressively refining the data model
the normalisation process involves
assigning attributes to tables, based on the concepts of determination and functional dependency
normalisation forms
normalisation works through a series of stages called normal forms

first normal form (1NF)
second normal form (2NF)
third normal form (3NF)
and more...

From a structural point of view, 2NF is better than 1NF, and 3NF is better than 2NF

there is also a fourth normal form (4NF) used sometimes

generally the higher the normal form, the more relational join operations are needed to produce a specific output and more resources are required to respond to end-user queries
denormalisation
the process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed

although performance is increased, denormalisation potentially leads to data anomalies
prime attribute
a key attribute

an attribute that is part of a key or is the whole key
nonprime attribute
an attribute that is not part of a key

also known as nonkey attribute
key attributes
the attributes that form a primary key
when is normalisation used
database designers commonly use normalisation in two situations

after the initial database design is complete (eg. Crow's Foot ERD) normalisation can be used to improve the structure

normalisation can also be used to improve existing data structures (eg. spreadsheets, older database structure and flat files)

generally, normalisation is applied to validate and further refine a database model
unnormalised data
raw data in its original state

might contain redundant data, multivalued data and/or data anomalies not found on normalised data relations
normal forms basic characteristics
functional dependence in normalisation
in A -> B
attribute B is fully functionally dependent on attribute A if each value of A determines one and only one value of B

Example
PROJ_NUM -> PROJ_NAME

in this example,
PROJ_NUM is known as the determinant attribute
PROJ_NAME is known as the dependent attribute
partial dependency
a condition in which an attribute is dependent on only a portion (subset) of the primary key and/or candidate keys

a partial dependency exists when there is functional dependence in which the determinant is only part of the primary key or candidate keys

Note:
candidate keys are other primary keys that were not picked to be the main primary key

Example
if (A, B) -> C, D where (A, B) is the primary key then we can say that the functional dependence B -> C is a partial dependency because only part of the primary key (B) is needed to determine the value of C
transitive dependency
a condition in which an attribute is dependent on another attribute that is not part of the primary key

Example
a transitive dependency exists when there are functional dependencies such that,
if X -> Y and Y -> Z and X is the primary key then the dependency X -> Z is a transitive dependency because X determines Z via Y

they occur only when a functional dependence exists among nonprime (non-key) attributes
repeating groups
set of logically related fields or values that occur multiple times in one record

a characteristic describing a group of multiple entries of the same type or multiple types for a single key attribute occurrence

Example
a car can have multiple colours for its top, interior, bottom, trim and so on
conversion to unnormalised form (UNF)
Step 1: List all attributes from the table/form into one relation

Step 2: identify and '( )' the repeating groups
set of logically related fields or values that occur multiple times in one record
hint: usually a sub-form inside a form with multiple entries
place parenthesis around the identified repeating groups
conversion to first normal form (1NF)
Step 1: identify a unique identifier for the repeating group and a PK for the main relation

Step 2: Eliminate the repeating group along with the PK of the main relation
the PK of the new relation resulting from the removal of the repeating groups will normally have a composite primary key (PK made up of multiple attributes) made up of the PK of the main relation and the unique identifier chosen in step 1
Note: (this is not always the case, so check whether the unique identifier from the repeating group can act as a PK, then the PK of the main relation will act as FKs in the new relation)

Step 3: Identify and list all partial dependencies
check the primary key and any candidate keys to see whether a subset (portion) of the PK or CK can uniquely identify other attributes

Example
APPOINTMENT (dentist_no, dentist_name, patient_no, patient_name, app_datetime, surgeryroom_no)

has 3 candidate keys which are:
(dentist_no, app_datetime)
(patient_no, app_datetime)
(surgeryroom_no, app_datetime)

so the partial dependencies for the APPOINTMENT relation would be:
dentist_no -> dentist_name
patient_no -> patient_name
a table is in 1NF when
there are no repeating groups

all of the key attributes are defined

all attributes are dependent on the primary key

all partial dependencies are identified and listed
conversion to second normal form (2NF)
Note:
Conversion to 2NF occurs only when the 1NF has a composite primary key (PK composed of multiple attributes). If the 1NF has a single-attribute primary key, then the table is automatically in 2NF

Step 1: Eliminate partial dependencies
for every partial dependency, write a copy of its determinant as a primary key for a new relation and remove the dependants from the original table into the new relation

Note:
primary key of the new relation must also remain in the original relation because they will also act as foreign keys for the relationship between the original and new relation

Step 2: Identify and list all transitive dependencies
check to see whether any nonkey attribute uniquely identifies other nonkey attributes

Example
(2NF form) STUDENT (stu_no, stu_name, stu_address, stu_course, stu_mode, lec_no, lec_name)

has the transitive dependency:
lec_no -> lec_name
a table is in 2NF when
it is in 1NF

has no partial dependencies

all transitive dependencies are identified and listed
conversion to third normal form (3NF)
Step 1: Eliminate the transitive dependencies
for every transitive dependency, write a copy of its determinant as a primary key for a new relation and remove the dependants from the original relation into the new relation

Note:
primary key of the new relation must also remain in the original relation because they will also act as foreign keys for the relationship between the original and new relation

Step 2: Identify and list all full functional dependencies
i.e. list all the dependent relations
a table is in 3NF when
it is in 2NF

has no transitive dependencies

all full functional dependencies are identified and listed
improving the design of the database structure
the following concepts can be applied to further improve the structure of the database:

Evaluate primary key assignments

Evaluate naming conventions

Refine attribute atomicity

Identify/add new attributes

Identify/add new relationships

Refine primary keys as required for data granularity

Maintain historical accuracy

Evaluate using derived attributes
atomic attribute
an attribute that cannot be further subdivided to produce meaningful components

Example
a persons last name attribute cannot be meaningfully subdivided
data granularity
data granularity refers to the level of detail represented by the values storied in a table's row

the data stored at its lowest level of granularity is said to be atomic data
surrogate key
a surrogate key is a system-defined attribute generally created and managed via the DBMS

it is usually numeric and its value is automatically incremented for each new row
THIS SET IS OFTEN IN FOLDERS WITH...