Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

DATABASE NORMALIZATION
Normalization: process of efficiently organizing data in the
DB
...


of

data,

↓
relationships

and

Goal: - Eliminate redundant data in a DB
...

Guidelines for ensuring that DBs are normalized → normal
forms: 1NF, 2NF, 3NF, BCNF
...

Note: meet practical business requirements
...

Reason for normalization: to prevent possible corruption of
DB stemming from update anomalies (insertion, deletion,
modification)
...

Constraint between attributes
...
If A and B are attributes of a
relation R, B is functionally dependent on A (den
...

B is functionally
A

dependent on A

B

Determinant: attribute or set of attributes on the left hand
side of the arrow
...
All of the attributes that are not part of the
primary key (non-primary key attributes) should be
functionally dependent on the key
...

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
...

UNF → 1NF: remove repeating groups:
Entering appropriate data in the empty columns of rows
...
Identifying a primary
key for each of the new relations
...

A → B is partially dependent if there is some attribute that
can be removed from A and the dependency still holds
...

Staff_No, SName → Branch_No

partial

Staff_No → Branch_No

full

Second normal form (2NF): A relation that is in 1NF and
every non-primary key attribute is fully functionally
dependent on the primary key
...
A relation with a single
attribute primary key is in at least 2NF
...


Transitive dependency: A condition where A, B and C are
attributes of a relation such that if A → B and B → C, then
C is transitively dependent on A via B (provided that A is
not functionally dependent on B or C)
...

2NF → 3NF: remove transitive dependencies: the
transitively dependent attributes are removed from the
relation by placing them in a new relation along with a copy
of their determinant
...
This result in a
nonloss (lossless) decomposition, which is reversible using
the natural join operation (primary key / foreign key
mechanism)
...

Notes:
BCNF is a stronger form of 3NF
BCNF => 3NF
3NF ≠> BCNF

Violation of BCNF happen under specific conditions:
• A relation contains two (or more) composite candidate keys,
• which overlap and share at least one attribute in common
...




---