Database Normalization Practice Assignment

Objective:

To understand and apply the concepts of database normalization in SQL Server. This exercise

involves designing a database, identifying anomalies, and normalizing it up to the third normal form

$(3$NF$).$

Background:

You are given a scenario where a bookstore wants to manage its inventory and sales records in a

database. The initial design of the database is in an unnormalized form $($UNF$).$

Scenario:

A bookstore maintains a single table called 'BookSales' with the following columns:

'SalelD' $($unique identifier for each sale$)$

'BookTitle'

'Author'

'Category' $($e$.$g$.,$ Fiction, Non$-$fiction, Educational$)$

'PublishDate'

'CustomerlD'

'CustomerName'

'SaleDate'

'QuantitySold'

'PricePerUnit'

Tasks:

Analyze the Unnormalized Form $($UNF$)$:

Identify and list the problems associated with redundancy and data integrity in the 'BookSales'

table.

First Normal Form $(1$NF$)$:

Create a new table structure that conforms to $1NF.$

Explain how this structure resolves the issues identified in the UNF.

Second Normal Form $(2$NF$)$:

Further normalize your $1NF$ tables to $2NF.$

Describe the process and how it addresses the problems of partial dependency.

Third Normal Form $(3$NF$)$:

Normalize your $2$NF tables to $3$NF$.$

Explain how this normalization eliminates transitive dependencies.

SQL Implementation:

Write SQL statements to create the normalized tables.

Include SQL statements for inserting sample data into your tables.

Reflection:

Discuss the advantages and potential disadvantages of normalizing the 'BookSales' database to

$3$NF$.$

Submission Guidelines:

Document each step of normalization, including the reasoning behind your decisions.

Provide SQL code for table creation and data insertion.

Include a section reflecting on the normalization process and its implications for the bookstore's

database management.

Resources:

You may use SQL Server Management Studio $($SSMS$)$ or any SQL Server environment for implementing and testing

your sql code