Primary Key

AutoParts Warehouse
1.

Draw an ERD for AutoParts Warehouse indicating the types of relationships between the entities.

2.
Owns
CUSTOMER

1

VEHICLE
M

M

ORDERS

N
N
PARTS

INVENTORIES

1
STORE

M

REQUEST

M
TRANSFER

2. For each of the entities identified, design tables and identify the possible candidate keys, the primary key, a probable foreign key, and potential secondary keys.
a. PARTS (PART_NUMBER, STORE_NUMBER, PART_DESCRIPTION, QTY_ON_HAND,
COST, RETAIL_PRICE)
Candidate keys: PART_NUMBER is the only candidate key. No other field will identify parts uniquely.
Primary key: PART_NUMBER is the primary key.
Foreign key: STORE_NUMBER is a foreign key from the STORE table.
Secondary keys: There are no other potential secondary keys.
b. CUSTOMER (CUSTOMER-ID, CUSTOMER_NAME, CUSTOMER_PHONE_NUMBER,
CUSTOMER_EMAIL)
Candidate keys: CUSTOMER-ID, CUSTOMER_PHONE_NUMBER, and
CUSTOMER_EMAIL are the candidate keys. The CUSTOMER_NAME field will not identify customers uniquely because two customers may have the same name.
Primary key: CUSTOMER-ID is the primary key.
Foreign key: There are no foreign keys.
Secondary keys: CUSTOMER_NAME, CUSTOMER_PHONE_NUMBER, and
CUSTOMER_EMAIL are potential secondary fields.
c. PARTS_ORDER_DETAIL (INVOICE_ID, PART_NUMBER, DATE, SUBTOTAL,
SALES_TAXES, TOTAL, PAYMENT_METHOD)
Candidate keys: The candidate keys are INVOICE_ID and PART_NUMBER.
Primary key: Combination of the INVOICE_ID and the PART_NUMBER is the primary key. Foreign key: PART_NUMBER is the primary key of the PARTS table.
Secondary keys: PART_NUMBER, and DATE could be used as a secondary key.
d. STORE (STORE_NUMBER, ADDRESS, PHONE_NUMBER)
Candidate keys: The candidate keys are STORE_NUMBER and PHONE_NUMBER.
Primary key: The STORE_NUMBER is the primary key, while it is unique, it would not be practical to use the phone number.
Foreign key: There are no foreign keys.
Secondary keys: There are no potential secondary