Database Normalisation

Database Normalisation Explained: Key Concepts

Database normalisation comprises several concepts that help in understanding the overall process. Some of the core ideas are:Other essential concepts include:

• 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.In summary, database normalisation is a vital process in the design and management of databases. It helps in reducing data redundancy, maintaining data integrity, and preventing anomalies, making it easier to store, query, and update data in the database. Understanding the key concepts and techniques involved in normalisation is crucial for anyone working with or designing a database system.

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:Suppose the relation has several dependencies and redundancies. To apply normalisation to this relation, we must follow these steps for each normal form: 1. First Normal Form (1NF): In this step, all attributes in the relation must be atomic, meaning they cannot be further decomposed into smaller pieces. In our example, all attributes are atomic, so the relation is already in 1NF. 2. Second Normal Form (2NF):To reach 2NF, the relation must first be in 1NF and also, all non-key attributes should be dependent on the whole primary key, not just part of it. So, in our example, if the primary key is a composite key of (CustomerID, ProductID), we could decompose the relation into:By doing so, all non-key attributes are now dependent on the whole primary key, and the relation is in 2NF. 3. Third Normal Form (3NF): To achieve 3NF, the relation must first be in 2NF and also, it should not contain any transitive dependencies. A transitive dependency exists if a non-key attribute depends on another non-key attribute. We must examine the relation to ensure no such dependencies are present. If any are found, further splitting may be required.After applying these three normal forms, the relation is now in 3NF, which eliminates most redundancies and anomalies.

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.Fifth Normal Form (5NF): A relation achieves 5NF if it is in 4NF and cannot be decomposed into any smaller relations without causing a loss of information or creating redundancy. 5NF addresses situations where multiple many-to-many relationships exist within a relation, which can lead to redundancies not identified by 1NF through 4NF. Applying these higher normal forms, along with 1NF, 2NF, and 3NF, can help create a well-structured and optimised database that avoids redundancies, reduces anomalies, and maintains data integrity.

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.

Database Denormalisation:

• 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.

Using these factors, database designers can evaluate the suitability of each technique for a given database system.

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.

Opt for denormalisation when:

• 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.

Ultimately, choosing between normalisation and denormalisation involves assessing the specific requirements and constraints of a database system to balance data integrity, performance, and storage space. In practice, a hybrid approach can be adopted, wherein certain tables or aspects of the database are normalised while others are denormalised, achieving a balance tailored to the needs of the application or system.

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.

The effective use of normalisation in the database design ensures that data integrity and consistency are maintained, enhancing data quality and accuracy throughout the system.

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.

By using database normalisation techniques, dependency and redundancy issues can be better managed, ensuring that the data is stored and operated upon efficiently. The prevention of data anomalies further contributes to the consistency and integrity of the database, improving the overall quality of information and ensuring the sustainability of the system.