Downloaded 4,442 times
Uploaded byDamian T. Gordon
Databases: Normalisation
Normalisation is a process that structures data in a relational database to minimize duplication and redundancy while preserving information. It aims to ensure data is structured efficiently and consistently through multiple forms. The stages of normalization include first normal form (1NF), second normal form (2NF), third normal form (3NF), Boyce-Codd normal form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). Higher normal forms eliminate more types of dependencies to optimize the database structure.
More Related Content
Similar to Databases: Normalisation
More from Damian T. Gordon
Databases: Normalisation
- 1.
- 2. Normalisation Normalisation isa process by which data structures in a relational database are as efficient as possible, including the elimination of redundancy, the minimisation of the use of null values and the prevention of the loss of information.
- 3. Aims of NormalisationNormalisation ensures that the database is structured in the best possible way. To achieve control over data redundancy. There should be no unnecessary duplication of data in different tables. To ensure data consistency. Where duplication is necessary the data is the same. To ensure tables have a flexible structure. E.g. number of classes taken or books borrowed should not be limited. To allow data in different tables can be used in complex queries.
- 4. Duplication vs RedundantData Duplicated Data: When an attribute has two or more identical values Redundant Data: If you can delete data with a loss of information
- 5. Stages of NormalisationFirst Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4NF) Fifth Normal Form (5NF)
- 6.
- 7. First Normal FormA table is in its first normal form if it contains no repeating attributes or groups of attributes
- 8.
- 9. First Normal FormTo convert data for unnormalised form to 1NF, simply convert any repeated attributes into part of the candidate key STUDENT( Number , Name, Classes) STUDENT( Number , Name, Classes )
- 10. First Normal FormSTUDENT
- 11.
- 12. Second Normal FormA table is in the second normal form if it's in the first normal form AND no column that is not part of the primary key is dependant only a portion of the primary key
- 13. Second Normal FormThe concept of functional dependency in central to normalisation and, in particular, strongly related to 2NF.
- 14. Functional Dependency If‘X’ is a set of attributes within a relation, then we say ‘A’ (an attribute or set of attributes), is functionally dependant on X, if and only if, for every combination of X, there is only one corresponding value of A We write this as : X -> A
- 15.
- 16. Functional Dependency Itis clear that : RefNo -> Name, Address, Status or, most correctly, AccNo, RefNo -> Name, Address, Status
- 17.
- 18.
- 19. Table in SecondNormal Form
- 20. Third Normal FormA table is in the third normal form if it is the second normal form and there are no non-key columns dependant on other non-key columns that could not act as the primary key.
- 21. Table in SecondNormal Form
- 22. Table in ThirdNormal Form
- 23. Boyce-Codd Normal FormAll attributes in a relation should be dependant upon the key, the whole key and nothing but the key
- 24. Important Papers E.F.Codd‘ A Relational Model for Large Shared Data Banks’ CACM 13(6) June 1970 E.F. Codd ‘ Extending the Database Relational Model to Capture More Meaning’ ACM Transactions on Database Systems, 4(4), December 1979
- 25. Table in ThirdNormal Form
- 26. Redundancy in 3NFThe combination of ROOM, TIME is unique to each tuple, no room is used twice at the same time (thus it is in 3NF). But, we know there is a redundancy in that ROOM depends LECTURER, therefore, we split the table...
- 27.
- 28. Difference between BCNFand 3NF Most relations in 3NF are also in BCNF, the only time this may not be true is when there is more than one candidate key for a relation and at least one of is composite.
- 29. Fourth NormalForm Fourth normal form (or 4NF) requires that there be no non-trivial multivalued dependencies of attribute sets on something other than a superset of a candidate key. A table is said to be in 4NF if and only if it is in the BCNF and multivalued dependencies are functional dependencies. The 4NF removes unwanted data structures: multivalued dependencies.
- 30. Fifth NormalForm Fifth normal form (5NF and also PJ/NF) requires that there are no non-trivial join dependencies that do not follow from the key constraints. A table is said to be in the 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys.
- 31. Domain/Key Normal Form Domain/key normal form (or DKNF) requires that the database contains no constraints other than domain constraints and key constraints.