Skip to content

Week 10 - SQLite3 IV

Date: March 11 – 17

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. Build the Database:
    • Implement the redesigned schema by creating a database. Include all necessary tables, relationships, constraints, and any other elements that are part of your design.
    • Populate the database with sample data that is sufficient to demonstrate the functionality of your queries in the next step.
  5. Complex Queries:
    • Develop four complex SQL queries that demonstrate your ability to interact with the database effectively. These queries should include a variety of operations such as joins, subqueries, aggregates, and any other advanced SQL features relevant to your database structure.
    • Each query should serve a hypothetical but realistic business or analytical need. Briefly describe the purpose of each query and what it aims to achieve.
  6. 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.
  • Submit the new assign4db_lastname.db file and assignment4_lastname.sql file.
  • 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.
 
  •