Skip to content

Week 10 - SQLite3 IV

Objective
This assignment aims to deepen your understanding of database design principles, with a specific focus on normalization. You will learn how to reduce data redundancy and improve data integrity by normalizing a database schema to at least the 3rd Normal Form (3NF).

Background
A well-designed database not only stores data efficiently but also ensures its integrity and reduces redundancy. Normalization is a systematic approach used in database design to achieve these goals. This assignment provides practical experience in transforming a poorly designed database schema into a well-structured and normalized one.

Tasks
  1. Analysis of the Provided Schema:
    • Examine the provided database schema, which is intentionally designed with flaws and redundancies, the database schema is available in GeorgiaView.
    • Identify issues related to data redundancy, anomalies in data insertion, deletion, and update.
  2. Normalization Process:
    • Normalize the database schema to the 1st Normal Form (1NF), resolving issues like repeating groups and ensuring each field contains atomic values.
    • Further normalize the schema to the 2nd Normal Form (2NF), eliminating partial dependencies on any candidate key.
    • Finally, achieve the 3rd Normal Form (3NF) by removing transitive dependencies.
  3. Schema Redesign:
    • Based on the normalization process, redesign the database schema.
    • Create new tables and adjust relationships as needed to reflect the normalized design.
    • Ensure that the new design addresses the identified issues and improves data integrity and efficiency.
  4. Documentation and Reflection:
    • Document each step of your normalization process, explaining the rationale behind your decisions.
    • Reflect on how normalization improves database design and the challenges encountered during the process.

Deliverables
  • A report detailing each step of the normalization process, including the transition from the original schema to 1NF, 2NF, and 3NF.
  • The redesigned database schema, including diagrams and descriptions of the new table structures and relationships.
  • A reflection on the importance of normalization in database design and the practical challenges faced during the assignment.

Evaluation Criteria
  • Accuracy and thoroughness in the normalization process.
  • Clarity and efficiency in the redesigned database schema.
  • Depth of understanding as reflected in the documentation and reflection on the normalization process.
Upon completing this assignment, you will have a practical understanding of database normalization principles and their application in designing efficient, robust, and scalable databases. This knowledge is crucial for any database professional, ensuring the creation of databases that are not only functionally effective but also maintainable and adaptable to changing data needs.
 
  •