Jump to a key chapter
Explaining Database Normalisation
Database normalisation is a systematic approach employed to organise data within a database, reducing data redundancy and preventing data anomalies. It achieves this by following a sequence of steps while structuring related data into tables.The concept of Database Normalisation was introduced in 1970 by Dr. Edgar F. Codd, an IBM researcher, as part of his Relational Model of Database Systems.
- Reducing data redundancy
- Improving data integrity
- Maintaining referential integrity
- Preventing anomalies such as insertion, deletion, and update
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
- Fifth Normal Form (5NF)
Database Normalisation Explained: Key Concepts
Database normalisation comprises several concepts that help in understanding the overall process. Some of the core ideas are:Functional Dependency: A relationship between attributes in a relation where the values of one attribute, or a set of attributes, uniquely determine the value of another attribute.
Transitive Dependency: A type of functional dependency where if a set of attributes A determines another set of attributes B, and B determines a set of attributes C, then A determines C.
For example, if A -> B (A determines the values of B) and B -> C (B determines the values of C), then A -> C (A determines the values of C).
- Normalization - the step-by-step process of applying functional dependency rules to the relational schema to eliminate insertion, deletion, and update anomalies.
- Decomposition - splitting a complex relation into simpler relations to remove issues such as data redundancy, incomplete keys or transitive dependencies.
- Synthesis - combining simpler relations obtained through decomposition into a consistent and normalized relational schema.
Decomposition and Synthesis in Database Normalisation
Decomposition and synthesis are essential processes in database normalisation. Decomposition involves breaking down a complex relation into simpler, more manageable relations. This process helps eliminate data redundancy and improve data integrity. On the other hand, synthesis is the process of reconstructing the relations after decomposition, ensuring that the reconstructed schema is consistent with the original schema and adheres to the rules of a specific normal form.For instance, consider a sales database with a single table containing columns for product information, customer information, and order transaction data. This table has numerous redundancies and possible anomalies. By applying decomposition, we can split the table into separate tables (e.g., Products, Customers, and Orders) to eliminate anomalies and achieve data integrity.
In some cases, lossless decomposition may be desired. Lossless decompositions ensure that the original relation can be reconstructed from the decomposed relations without losing any data.
Forms of Database Normalisation
In the process of database normalisation, there are three primary normal forms typically considered: First normal form (1NF), second normal form (2NF), and third normal form (3NF). By understanding and implementing these three forms, databases can be designed and structured optimally to reduce redundancies and prevent data anomalies.First, Second, and Third Normal Form Example
To better understand the differences and applications of these normal forms, consider the following example involving a relation with a primary key:CustomerID | CustomerName | ProductID | ProductName | OrderDate |
CustomerID | CustomerName |
ProductID | ProductName |
In our example, if there is a transitive dependency on 'ProductName' and 'ProductID', let's assume the product category is dependent on the product name. To achieve 3NF, we could create another table as follows:
ProductName | ProductCategory |
Higher Normal Forms
While 1NF, 2NF, and 3NF help optimise database design, there are higher normal forms that can be considered for further normalisation: 1. Boyce-Codd Normal Form (BCNF) 2. Fourth Normal Form (4NF) 3. Fifth Normal Form (5NF) These higher normal forms offer more robust normalisation by eliminating additional anomalies and dependencies not addressed by the first three normal forms.Boyce-Codd Normal Form (BCNF) and Fifth Normal Form (5NF)
BCNF and 5NF are advanced normal forms that address specific types of dependencies that may still exist after applying 1NF, 2NF, and 3NF. These normal forms provide a more rigid structure to the database, minimising the risks of data inconsistencies and redundancies. Boyce-Codd Normal Form (BCNF): A relation reaches BCNF when it is in 3NF and, for every functional dependency \(A \to B\), the determinant (A) is a candidate key for the relation.In our previous example, if we find additional dependencies not covered by 3NF, we could apply BCNF by decomposing the relation further to eliminate any remaining dependencies.
Database Normalisation vs Denormalisation
Database design often involves deciding between applying normalisation or denormalisation techniques. While normalisation seeks to eliminate redundancy and improve data integrity, denormalisation aims to improve performance at the cost of some redundancy. Choosing the right approach depends on the specific requirements and constraints of a database system.Comparison of Normalisation and Denormalisation Techniques
Normalisation and denormalisation provide two different approaches to designing a database, each with its strengths and weaknesses.Benefits and Drawbacks of Database Normalisation and Denormalisation
Key benefits and drawbacks of applying normalisation or denormalisation are listed below. Database Normalisation:- Eliminates data redundancy and improves data integrity.
- Keeps data consistent and avoids anomalies.
- Facilitates maintenance and updating of data.
- Potentially more complex queries, leading to slower performance.
- Introduces some redundancy to improve performance.
- Reduces the number of joins required in queries, which can lead to faster retrieval of data.
- May cause data inconsistency and complicate updates.
- Requires more storage space due to redundancy.
When to Use Normalisation or Denormalisation
Deciding when to use normalisation or denormalisation depends on the specific use case and requirements of a database system.Deciding Between Database Normalisation and Denormalisation in Real-World Scenarios
In real-world scenarios, the choice between normalisation and denormalisation may depend on factors such as performance, data consistency, and storage requirements. Some guidelines to help in making a decision include: Opt for normalisation when:- Data integrity and consistency are crucial.
- There are frequent updates to the data.
- Database schema is still evolving and requires regular changes.
- Storage space is a concern, and the elimination of redundancy is necessary to preserve space.
- Query performance and speed are vital, and complex joins are burdening the system.
- The focus is primarily on read-heavy operations, and updates are less frequent.
- Additional storage space is available to accommodate redundancy.
- Application or system-level solutions can maintain data consistency in spite of redundancy.
Advantages of Database Normalisation
One of the key advantages of database normalisation is the improvement in data consistency and integrity. By ensuring that related data is stored in separate tables and adhering to the set rules for each normal form, normalisation helps maintain the quality and accuracy of information in the database.Database Normalisation Example: Ensuring Data Quality and Accuracy
Consider an online retail store with a single table containing product, customer, and order information. Without normalisation, the same product and customer details are repeatedly stored with each new order, leading to data redundancy and potential inconsistencies in the database. By applying normalisation techniques, the retail store's database can be structured into separate tables, such as Products, Customers, and Orders, with each table storing unique data.- The Products table stores product details, ensuring that each product is stored only once, reducing redundancy and errors.
- The Customers table holds customer information, promoting consistent and accurate data.
- The Orders table contains order transactions, with references to the Products and Customers tables, eliminating the need for duplicate data.
Preventing Data Anomalies
Another advantage of database normalisation is the prevention of data anomalies, which are inconsistencies or errors that can occur when performing actions such as inserting, updating, or deleting data records. When a database is not properly normalised, anomalies can compromise the validity and integrity of the data.
How Database Normalisation Helps Avoid Redundancy and Anomaly Issues
Database normalisation can address various types of data anomalies, including:- Insertion Anomalies: Occur when adding a new record to a table results in the unnecessary duplication of data, or the record cannot be added due to missing information. Normalisation prevents this by decomposing tables and enforcing strict rules for inserting data.
- Update Anomalies: Arise when updating a record in a table requires multiple changes to the same data, or the update doesn't propagate to all related records. By isolating data in separate tables with references, normalisation ensures that updates to the data are performed consistently and accurately.
- Deletion Anomalies: Occur when deleting a record from the table leads to unintended loss of other related data. Normalisation prevents this by separating tables, so the deletion of a record in one table does not affect the data in another table.
Database Normalization - Key takeaways
Database Normalisation: A systematic approach to organising data, reducing redundancy, and preventing data anomalies in databases.
Normal Forms: Classification levels of database normalisation, including 1NF, 2NF, 3NF, BCNF, and 5NF.
Decomposition and Synthesis: Processes involved in breaking down complex relations and reconstructing them into consistent and normalized schemas.
Database Normalisation vs Denormalisation: choosing between applying normalisation for data integrity or denormalisation for improved performance.
Advantages of Database Normalisation: Improved data consistency and integrity, prevention of data anomalies, and ensuring data quality and accuracy.
Learn faster with the 16 flashcards about Database Normalisation
Sign up for free to gain access to all our flashcards.
Frequently Asked Questions about Database Normalisation
About StudySmarter
StudySmarter is a globally recognized educational technology company, offering a holistic learning platform designed for students of all ages and educational levels. Our platform provides learning support for a wide range of subjects, including STEM, Social Sciences, and Languages and also helps students to successfully master various tests and exams worldwide, such as GCSE, A Level, SAT, ACT, Abitur, and more. We offer an extensive library of learning materials, including interactive flashcards, comprehensive textbook solutions, and detailed explanations. The cutting-edge technology and tools we provide help students create their own learning materials. StudySmarter’s content is not only expert-verified but also regularly updated to ensure accuracy and relevance.
Learn more