Purchase Solution

Improving Table Designs Using Normalization.

Not what you're looking for?

Ask Custom Question

Consider a typical sales invoice that would include the following information. Design a single table to hold all of the information required to store an invoice including this information. Next, apply normalization to reduce this table to third normal form. Prepare a Word document showing the original and final tables. Include a short summary (2-3 paragraphs) of the problems with the original single table design and how normalization improved the design.

Orders:
Order_id
Order_date
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
Item_id
Item_description
Item_qty
Item_price
Item_total_price
Order_total_price

Purchase this Solution
Solution Preview

Let us start with a single table with all columns in it. Let's remember that, in all tables candidate keys (also known as Primary Keys) are in BOLD

Let's call the below table OrderInfo table:

Table OrderInfo:

Order_id
Order_date
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
Item_id
Item_description
Item_qty
Item_price
Item_total_price
Order_total_price

The following FDs (functional dependencies) hold on the above table:

Order_Id -> Customer_id, Order_date, Order_total_price
Item_Id, Order_Id -> Item_Qty, Item_total_price, Item_description, Item_Price
Customer_id -> Customer_Name, Customer_Address, Customer_City, Customer_State
Item_Id -> Item_description, Item_price

Primary key of the above table is a composite candidate key Order_id and Item_id

Clearly, this table is in 1 NF, but it is not in 2NF, so obviously not in 3 NF.

The table is not in 2 NF because we have FDs with part of candidate key Order_id and Item_id and also Customer_id

So, we have to separate the fields according to the FDs and create more tables.

Hence, at first we take the FD

Customer_id -> Customer_Name, Customer_Address, Customer_City, Customer_State

Then, we need to put the below fields in Customer table

Customer_id
Customer_name
Customer_address
Customer_city
Customer_state

In a Customer ...

Purchase this Solution
Free BrainMass Quizzes
SWOT

This quiz will test your understanding of the SWOT analysis, including terms, concepts, uses, advantages, and process.

Organizational Behavior (OB)

The organizational behavior (OB) quiz will help you better understand organizational behavior through the lens of managers including workforce diversity.

Understanding the Accounting Equation

These 10 questions help a new student of accounting to understand the basic premise of accounting and how it is applied to the business world.

Paradigms and Frameworks of Management Research

This quiz evaluates your understanding of the paradigm-based and epistimological frameworks of research. It is intended for advanced students.

Organizational Leadership Quiz

This quiz prepares a person to do well when it comes to studying organizational leadership in their studies.

View More Free Quizzes
Related BrainMass Solutions

  • redundancy in a database

    While this is the expected behavior for flat file database designs and spreadsheets, it defeats the purpose of relational database designs.

  • MS Access and ERD Normalization

    Still there is scope for improving this data model and depends the functionality & requirements of this system. The expert examines MS access and ERD normalization.

  • Second Normal Form versus Third Normal Form

    Normalization is important to 'clean up' data in most database designs due to undesirable relations. This is because of data needs to be inserted or removed that the related data can get impacted by these activities.

  • PHP and MySQL Benefits of relational databases

    While this is the expected behavior for flat file database designs and spreadsheets, it defeats the purpose of relational database designs.

  • Transforming normal forms in database systems

    Normalization is important to 'clean up' data in most database designs due to undesirable relations. This is because data needs to be inserted or removed so that the related data can get impacted by these activities.

  • Database Normalization

    Table 4: Order table Order_Id Customer_id Order_date Order_total_price This solution clearly explains Database Normalization concepts using an example.

  • An explanation of database normalization with and example

    283462 What is normalization quiz 1. What is normalization? Why is it important? 2. What is functional dependency? 3. Why is this table NOT in First Normal Form (1NF) normalization? What can you do to put it in 1NF?

  • Discussing the Concept of Normalization

    There are three main normal forms, each with increasing levels of normalization: - First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birth date field.

  • Normalisation of Database Design

    Modify the database you created in Module 3: drop the tables that you have redesigned for normalization, and keep those that are intact. Create new tables as required in your new design, using SQL in DBMS.

View More Related Solutions