Primary Key

Topics: Foreign key, Candidate key, Data modeling Pages: 3 (438 words) Published: June 23, 2014
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 keys.

3....
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • What Is Super Key in Dbms Essay
  • Project Management and Key Stakeholders Involved Essay
  • Hardwork Is the Key to Success Essay
  • Sarah s Key Essay
  • Reflection and Key Points Essay
  • The Small Key by Paz Latorena Essay
  • Key Skills of Management Essay
  • Sarah's Key Novel vs Film Essay

Become a StudyMode Member

Sign Up - It's Free