Entity Relationship Diagram (ERD) Normalization: Achieving 3rd Normal Form

Preparatory Instructions:
Please be sure to read Chapter 6 – Data Modeling (pp 187 – 213, Systems Analysis & Design text).
Entity Relationship Diagrams (known as ERDs) depict the data that will be used and created in a system and provides business requirements details such as the type and amount of each data element as well as the relationships between data entities.
This week we will practice creating ERDs. You will find that ERDs are very commonly used and can be a critical tool used during requirements analysis and design phases.
Objectives:
This week’s assignment is to apply each Normalization rule to the Inventory Order model to reach the 3rd Normal Form.
The system development team at the Wilcon Company is working on developing a new customer order entry system. In the process of designing the new system, the team has identified the following data entity attributes:
Inventory Order
Order Number (identifier)
Order Date
Customer Name
Street Address
City
State
Zip
Customer Type
Initials
District Number
Region Number
1 to 22 occurrences of:
Item Name
Quantity Ordered
Item Unit
Quantity Shipped
Item Out
Quantity Received
Example:
Entity Relationship Diagram for Music CD Information
Deliverables:
Apply each normalization rule in order (1 to 3) to reach the 3rd Normal form.
Apply first normal form rules and provide result as 1st normal form. Provide a brief description of what was applied to reach 1st normal form as well as the model in 1st normal form.
Apply second normal form rules and provide result as 2nd normal form. Provide a brief description of what was applied to reach 2nd normal form as well as the model in 2nd normal form.
Apply third normal form and provide result as 3rd normal form. Provide a brief description of what was applied to reach 3rd normal form as well as the model in 3rd normal form.
Submit your Entity Relationship Models (1st thru 3rd normal form) as Visio, Word or image files to Canvas

Struggling with where to start this assignment? Follow this guide to tackle your assignment easily!

Dear Students,

Embarking on the task of normalizing the Inventory Order model to the Third Normal Form (3NF) and creating corresponding Entity Relationship Diagrams (ERDs) can seem daunting. However, by following a structured approach, you can systematically achieve the desired outcomes. Below is a step-by-step guide to assist you through this process.

Step 1: Understand the Inventory Order Model

Begin by familiarizing yourself with the provided data entity attributes for the Inventory Order:

• Order Number (identifier)
• Order Date
• Customer Name
• Street Address
• City
• State
• Zip
• Customer Type
• Initials
• District Number
• Region Number
• Items (1 to 22 occurrences of):
  • Item Name
  • Quantity Ordered
  • Item Unit
  • Quantity Shipped
  • Item Out
  • Quantity Received

Step 2: Apply First Normal Form (1NF)

Objective: Ensure that the table has only atomic (indivisible) values and each entry in a column is of the same data type.

Actions:

• Eliminate Repeating Groups: Since an order can contain multiple items (1 to 22 occurrences), separate these items into individual rows. This process involves creating a new table to handle the multiple items per order.

Resulting 1NF Structure:

• Inventory Order Table:
  • Order Number (Primary Key)
  • Order Date
  • Customer Name
  • Street Address
  • City
  • State
  • Zip
  • Customer Type
  • Initials
  • District Number
  • Region Number
• Order Items Table:
  • Order Number (Foreign Key)
  • Item Name
  • Quantity Ordered
  • Item Unit
  • Quantity Shipped
  • Item Out
  • Quantity Received

Description: By creating the “Order Items” table, we have removed repeating groups, ensuring that each field contains only atomic values.

Step 3: Apply Second Normal Form (2NF)

Objective: Remove partial dependencies; ensure that non-key attributes are fully dependent on the entire primary key.

Actions:

• Identify Composite Keys: In the “Order Items” table, the combination of “Order Number” and “Item Name” can serve as a composite primary key.
• Eliminate Partial Dependencies: Ensure that all non-key attributes in the “Order Items” table depend on both “Order Number” and “Item Name.”

Resulting 2NF Structure:

• Inventory Order Table:
  • Order Number (Primary Key)
  • Order Date
  • Customer Name
  • Street Address
  • City
  • State
  • Zip
  • Customer Type
  • Initials
  • District Number
  • Region Number
• Order Items Table:
  • Order Number (Foreign Key, part of Composite Key)
  • Item Name (Foreign Key, part of Composite Key)
  • Quantity Ordered
  • Item Unit
  • Quantity Shipped
  • Item Out
  • Quantity Received

Description: By ensuring that non-key attributes in the “Order Items” table are fully dependent on the composite primary key, we have achieved 2NF.

Step 4: Apply Third Normal Form (3NF)

Objective: Remove transitive dependencies; ensure that non-key attributes are not dependent on other non-key attributes.

Actions:

• Identify Transitive Dependencies: In the “Inventory Order” table, attributes like “Street Address,” “City,” “State,” and “Zip” are dependent on “Customer Name,” not directly on “Order Number.”
• Create Separate Tables: Establish a “Customer” table to store customer-related information and link it to the “Inventory Order” table using a foreign key.

Resulting 3NF Structure:

• Customer Table:
  • Customer ID (Primary Key)
  • Customer Name
  • Street Address
  • City
  • State
  • Zip
  • Customer Type
  • Initials
  • District Number
  • Region Number
• Inventory Order Table:
  • Order Number (Primary Key)
  • Order Date
  • Customer ID (Foreign Key)
• Order Items Table:
  • Order Number (Foreign Key, part of Composite Key)
  • Item Name (Foreign Key, part of Composite Key)
  • Quantity Ordered
  • Item Unit
  • Quantity Shipped
  • Item Out
  • Quantity Received

Description: By creating a separate “Customer” table and linking it to the “Inventory Order” table via “Customer ID,” we have removed transitive dependencies, achieving 3NF.

Step 5: Create Entity Relationship Diagrams (ERDs)

Objective: Visually represent the relationships between the entities in your normalized database.

Actions:

• Use Diagramming Tools: Utilize tools like Microsoft Visio, Lucidchart, or any ERD software to create your diagrams.
• Define Entities and Relationships: Clearly depict the “Customer,” “Inventory Order,” and “Order Items” tables, illustrating primary keys, foreign keys, and the relationships between them.

Deliverables: Submit your ERDs for 1NF, 2NF, and 3NF as Visio, Word, or image files through Canvas.

By following this structured approach, you will effectively normalize the Inventory Order model to the Third Normal Form and create comprehensive ERDs that accurately represent the system’s data relationships.

Best regards,

[Your Instructor’s Name]

ORDER A SIMILAR PAPER NOW