ADVANCE DATABASE NORMALIZATION Flashcards
is a database design technique that organizes tables in a manner that reduces redundancy and dependency of
data.
NORMALIZATION
- The purpose of Normalization is to eliminate ____
___ and ensure data is stored logically.
redundant (useless) data
The inventor of the relational model ____ proposed the
theory of normalization
Edgar Frank Codd
is the process of organizing data to
minimize redundancy in the design
of a relational database
management system (RDBMS)
Database
Normalization or Normalization
This rule is all about disallowing multivalued attributes
Eliminate repeating
data
Goals of Normalization
- ELIMINATE REDUNDANT
DATA - ELIMINATE INSERT, DELETE,
AND UPDATE ANOMALIES
Three main rules for normalizing data
Eliminate repeating
data
Eliminate partial
dependencies
Eliminate transitive
dependencies
This refers to situations where the primary key for a table is a composite
primary key, which means ‘a key composed of multiple columns’.
Eliminate partial
dependencies
This refers to situations where in a column in the table refers to a non-key
column instead of referring to the primary key of a table.
Eliminate transitive
dependencies
Problems that can occur in poorly
planned, unnormalized databases
where all the data is stored in one
table (a flat- file database).
Anomaly
Types of Anomalies:
Insert Delete Update
Suppose a new faculty joins the University, and the Database Administrator inserts the faculty data into the above table. But he is not able to insert because Sid is a primary key, and can’t be NULL. So this type
of anomaly is known as an
insertion anomaly
When the Database Administrator wants to delete the
student details of Sid=2 from the above table, then it will
delete the faculty and course information too which
cannot be recovered further.
Delete Anomaly
DETELE ANOMALY in SQL:
DELETE FROM ___ WHERE __:
When the Database Administrator wants to change
the salary of faculty F1 from 30000 to 40000 in above
table University, then the database will update salary
in more than one row due to data redundancy
Update Anomaly
Update Anomaly in SQL
UPDATE __WHERE ____;
evolution of
Normalization
theories
1st Normal Form
2nd Normal Form
3rd Normal Form
Boyce Codd NF
4th Normal Form
5th Normal Form
6th Normal Form
states that the domain of an attribute must include only atomic
values and that the value of any attribute in a row must be a single
value from the domain of that attribute
First Normal Forms
pertains to the built-in atomic data types
Atomic Values
ACID stands for
(atomicity, consistency, isolation, and durability)
Rules of 1NF
Each attribute name must be unique
Each attribute value must be single
Each row must be unique
is unique, not null, and unchanged; can be either an attribute or combined attributes
primary key
The table should be in the First Normal Form.
There should be no Partial Dependency.
Second Normal Form (2NF)
The attributes
which are used to form a candidate
key are called
Prime attributes
The attributes
which do not form a candidate key
are called
Non-Prime attributes
is a
concept that specifies the
relationship between two
sets of attributes where one
attribute determines the
value of another attribute.
Functional
Dependency
is a specific type of
dependency that happens in tables with
a composite primary key
Partial dependency
If a non-prime attribute can be
determined by the part of the candidate
key in a relation, it is known as a
partial
dependency.
a primary key
made up of two or more columns
composite primary key
- it should be in the second normal form
- it should not have transitive dependency
- all transitive dependencies are removed to place in another table
Third Normal Form (3NF)
a functional dependency is said to be transitive if it is directly formed by two functional dependencies (True or False)
FALSE - INDIRECTLY
is said to be
transitive if it is indirectly formed by two
functional dependencies.
functional dependency
