A superkey is a set of attributes that uniquely identifies every row in a relation. A candidate key is a minimal superkey: removing any attribute would stop it from uniquely identifying rows. section 4.1.3
When selecting keys during normalization, check whether one attribute is enough before building a composite key:
- Sales Order Number uniquely identifies Sales records, so it can serve as a single-attribute candidate key. section 4.1.3
- Purchase Order Number uniquely identifies Purchasing records and is a superkey by itself. section 4.1.3
- Product Name alone fails when supplier parts share names with customer products; Product Name plus Product Type Name uniquely identifies a product. section 4.1.3
Avoid the product-name mistake
Do not assume a familiar label is unique. If products can share a name, use the additional attribute needed to distinguish records, then carry that attribute into related sales and purchasing data. section 4.1.3
Sales
Sales Order Date
PK Sales Order Number
Sales Person First Name
Sales Person Last Name
Sales Person Email
Sales Area Name
Sales Area Manager First Name
Sales Area Manager Last Name
Customer Company Name
Customer Contact First Name
Customer Contact Last Name
Customer Contact Email
FK Invoice Address Street
FK Invoice Address Zip Code
FK Delivery Address Street
FK Delivery Address Zip Code
Sales Order Delivery Due Date
Sales Order Delivery Actual Date
Currier Used for Delivery
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
Supplier Name
Supplier Contact First Name
Supplier Contact Last Name
Supplier Contact Email
FK Supplier Address Street
FK Supplier Address Zip Code
Purchase Order Date
PK Purchase Order Number
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
Product Next Manufacture Date
Product Next Manufacture Quantity
Back Order Manufacturing ID
PK Product Type Name
Product Type Description
Product Category Name
Product Category Description
Product Subcategory Name
Product Subcategory Description
Inspect the normalized design to see composite primary and foreign keys for Sales Order Details, including Product Name and Product Type Name. section 4.1.3
