A database is a collection of data stored in an organised manner.
**Atomicity **– all changes to data are performed as if they are a single
operation. That is, all the changes are performed, or none of them are.
**Consistency **– Data is in a consistent state when a transaction starts and
when it ends.
**Isolation **– The intermediate state of a transaction is invisible to other
transactions. As a result, transactions that are run concurrently appear to be
serialized.
**Durability **– After a transaction successfully completes, changes to data
persist and are not undone, even in the event of a system failure.
| Key Type | Definition |
|---|---|
| Candidate Key | An attribute or a combination of attributes that can uniquely identify each record. |
| Primary Key | A candidate key that is most suited to become the main key. |
| Secondary Key | A candidate key that is not chosen as the primary key. |
| Composite Key | A combination of two or more attributes that can uniquely identify each record. |
| Foreign Key | An attribute in one table that references the primary key in another table. |
Data redundancy is where the same data is being stored more than once.
Data inconsistency is where different versions of the same data exist at the same time
- Data redundancy will cause issues when inserting, updating and
deleting data from the database. - Causes Data Anomalies, where not all of the required changes in the redundant data
- Insertion Anomalies:
- a new student cannot be created unless a class has been created. Else it will result in data inconsistency
- Updata Anomalies
- say class is modified, all data related to class should be altered or else it will lead to data inconsistency
- Deletion Anomalies
- Insertion Anomalies:
Data dependency:
- Functional dependency is a direct relationship where one attribute uniquely identifies another attribute in the same table.
- Transitive dependency is an indirect relationship formed by two functional dependencies
Normalisation is the process of organising the tables in a database to reduce data redundancy and inconsistency.
Unnormalized Form (UNF)
2.
First Normal Form (1NF)
Conditions:
All attributes should hold only atomic values (each record has to be unique).
3.
Second Normal Form (2NF)
Conditions:
- In 1NF
All non-key attributes should be fully dependent on the entire primary key.
4.
Third Normal Form (3NF)
Conditions:
- In 2NF
- No transitive dependencies.