STEP 1: Logical Database Design (ERD)

We'll identify the main entities, their attributes, and relationships, making sure it follows 3rd Normal Form (3NF).

Entities & Key Attributes

Customer

• CustomerID (PK)
• Name
• ShippingAddress
• Channel (Retail/Industrial)
• AssignedDistributionCenterID (FK)

CustomerEmail

• EmailID (PK)
• CustomerID (FK)
• Email

OrderHeader

• OrderNumber (PK)
• CustomerID (FK)
• CustomerShippingAddress
• DistributionCenterID (FK)
• OrderDate
• TotalBillingAmount

OrderLine

• OrderLineID (PK)
• OrderNumber (FK)
• ProductID (FK)
• ProductDescription
• Quantity
• LineItemPrice

DistributionCenter

• DistributionCenterID (PK)
• Name
• Address

NationalAccount

• AccountID (PK)
• Name
• SalesTeamID (FK)

SalesTeam

• SalesTeamID (PK)
• Name

CustomerNationalAccount

• CustomerID (PK, FK)
• AccountID (FK)

Product

• ProductID (PK)
• Description
• UnitPrice
• ProductFamilyID (FK)

ProductFamily

• ProductFamilyID (PK)
• Description
• ProductLineID (FK)

ProductLine

• ProductLineID (PK)
• Description

ComponentPart

• PartID (PK)
• Description
• StandardCost
• ComponentPlantID (FK)

ProductComponent (BOM - Bill of Materials)

• ProductID (PK, FK)
• PartID (PK, FK)

Plant

• PlantID (PK)
• Name
• Type (Assembly/Component)
• Address

ProductFamilyAssemblyPlant

• ProductFamilyID (PK, FK)
• PlantID (PK, FK)

STEP 2: PLEASE CREATE Visio-Style ERD CreatioN and provide me with a PDF or PICTURE OF THE DIAGRAM