Second normal form (2NF) is reached after first normal form when every attribute depends on the complete composite primary key. An attribute that depends on only part of that key signals a separate entity, not a column to retain in the current relation. section 4.1.3
Do not treat normalization as a mechanical split-the-table exercise. Check the business meaning and rules behind each attribute: a plausible-looking design can still place facts beside a key that does not fully determine them. section 4.1.3
Separating order facts from related facts
A sales design can retain order-specific facts under Sales Order Number while moving salesperson, sales area, customer, and contact facts into their own entities and linking them with foreign keys. This keeps each fact with the key that determines it. section 4.1.3
Use this quick 2NF check:
- Confirm the relation already meets 1NF requirements for atomic, uniquely named attributes and removed repeating groups. section 4.1.3
- For each non-key attribute, ask whether every component of the composite primary key is needed to determine it. section 4.1.3
- Move attributes with a partial dependency into a new entity, then compare models side by side if needed. 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 separated Sales, Sales Person, Sales Areas, Customer, and Customer Contact entities to see foreign keys replace unrelated attributes on the Sales relation. section 4.1.3
