A transitive dependency is a warning that a nonprime attribute is determined by another nonprime attribute, rather than by the relation’s key. It violates third normal form (3NF), even when the relation already satisfies 2NF. section 4.1.3
The practical fix is to move the determining attribute and the attributes it identifies into their own relation. Keep its identifying attribute as that relation’s prime attribute, then retain it in the original relation as a foreign key. section 4.1.3
Sales person details
A sales order can store Sales Person Email as a foreign key rather than storing the salesperson’s first and last names. Because the email identifies the name combination, the person details belong in a Sales Person relation keyed by email. section 4.1.3
Sales
Sales Order Date
PK Sales Order Number
FK Sales Person Email
FK Sales Area Name
FK Customer Company Name
Sales Order Delivery Due Date
Sales Order Delivery Actual Date
Currier Used for Delivery
Sales Person
Sales Person First Name
Sales Person Last Name
PK Sales Person Email
Sales Areas
PK Sales Area Name
Sales Area Manager First Name
Sales Area Manager Last Name
Customer
PK Customer Company Name
FK Customer Contact Email
FK Invoice Address Street
FK Invoice Address Zip Code
FK Delivery Address Street
FK Delivery Address Zip Code
Customer Contact
Customer Contact First Name
Customer Contact Last Name
PK Customer Contact Email
Sales Order Details
PK FK Product Name
PK FK Product Type Name
Quantity
PK FK Sales Order Number
Address
PK Street
Area
City
PK Zip Code
Purchasing
FK Supplier Name
Purchase Order Date
PK Purchase Order Number
Suppliers
PK Supplier Name
FK Supplier Contact Email
FK Supplier Address Street
FK Supplier Address Zip Code
Supplier Contact
Supplier Contact First Name
Supplier Contact Last Name
PK Supplier Contact Email
Purchase Order Details
PK FK Product Name
PK FK Product Type Name
Quantity
PK FK Purchase Order Number
Products
PK Product Name
Product Stock Level
FK Back Order Manufacturing ID
PK FK Product Type Name
FK Product Subcategory Name
Product Types
PK Product Type Name
Product Type Description
Product SubCategories
FK Product Category Name
PK Product Subcategory Name
Product Subcategory Description
Product Categories
PK Product Category Name
Product Category Description
BackOrders
ProductNextManufactureDate
ProductNextManufactureQuantity
PK BackOrderManufacturingID
Inspect the normalized relations: Sales retains foreign keys for salesperson, sales area, and customer, while their dependent details move to separate relations. section 4.1.3
