11.4.1.5 define the connections between tables in a database(1-3NF)
Normalization database
Database normalization is the process of organizing a database to reduce redundancy and improve data integrity.
The primary goal: to structure data in a way that prevents anomalies during data insertion, update, or deletion.
The table database is not normalized if
it has data redundancy;
it consumes additional memory space;
it makes it difficult to process and update the database without losing data.
What Is Database Normalization?
Store only the minimal amount of information ('atomic' data).
'Atomic' is the word used to describe a data item that cannot be broken down any further.
Remove redundancies.
Remove anomalies.
Restructure data.
A flat file is a type of database or file that stores data in a simple, plain text format, often without any complex structures like tables or relationships using only the one table.
Zero form. Is it a normalized form?
No! Field "SchoolSubject" stores not atomic data.
Is it normalized form?
No! Three fields "SchoolSubject1", "SchoolSubject2", "SchoolSubject3" has data of the same characteristic.
A few rules for database normalization.
Each rule is called a "normal form" (NF).
First Normal Form (1NF):
all data must be atomic, i.e. each data element cannot be broken down further;
all attributes in the table must have unique names and consist of different characteristics;
a table doesn't contain duplicate records;
a table has a primary key.
Normalized form:
Second Normal Form(2NF):
It should be in the First Normal form.
Create separate tables for sets of values that apply to multiple records.
Relate the tables with a foreign key.
Non-key attributes must depend on every part of the primary key (it has Transitive Dependency)
Third Normal Form (3NF):
It should be in the Second Normal form.
Eliminate fields that do not depend on the primary key (it doesn't have Transitive Dependency).
Each non-primary key attribute must be dependent only on a primary key
Key features of normalization:
Referential Integrity - if a value appears in a foreign key in one table, it must also appear in the primary key in another table.
Normalized Entities - a set of entities that contain no redundant data.
Normalization - a technique used to produce a set of normalized entities.
Benefits of normalization
The database does not have redundant data, it is smaller in size so less money needs to be spent on storage
Because there is less data to search through, it is much faster to run a query on the data
Because there is no data duplication there is better data integrity and less risk of mistakes.
Because there is no data duplication there is less chance of storing two or more different copies of the data
Once change can be made it can instantly be cascaded across any related records.
