22.214.171.124 define the connections between tables in database(1-3NF)
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.
Zero form. Is it normalised form?
No! Field "SchoolSubject" stores not atomic data.
Is it normalised 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):
- It should only have unambiguous (atomic) attributes / fields.
- The values stored in the field must have one characteristic.
- All attributes in the table must have unique names.
- And the order in which the data is stored does not matter.
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.
- Records should not depend on anything other than a table's primary key (a compound key, if necessary).
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 primary key
Key features of normalisation:
- Referential Integrity - if a value appears in a foreign key in one table, it must also appear in the primary key in another table.
- Normalised Entities - a set of entities that contain no redundant data.
- Normalisation - a technique used to produce a set of normalised entities.
Benefits of normalisation
- 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 which can instantly be cascaded across any related records.
More about Normalisation https://www.studytonight.com
Normalisation on wikibooks
Relationship example on BBC.co.uk
1. Explain the purpose of normalisation database.
Test Relational databases