Question

Identify every possible primary key, candidate key, and foreign key for the following relations. Separate each key using a semicolon to avoid confusion.

Assumptions: MIScompany has branches located in several states within the United States. A customer can be an individual or organization. driverId is the driving license number, ssno is the social security number and upc is the universal product code. Any equipment is rented and returned at the same branch. A customer can be a manufacturer and vice versa. (Minus 1 point for each wrong answer)

            MIScompany (name, address, phone, email, FedTaxId, StaTaxId)

           

Primary key:

Candidate key:

Foreign key: none

branch (branchId, name, address, phone, email, FedTaxId, StaTaxId)

Primary key:

Candidate key:

Foreign key:

employee (empId, driverId, ssno, name, branchId)

Primary key:

Candidate key:

Foreign key:

customer (custId, name, address, driverId, ssno, FedTaxId, StaTaxId)

Primary key:   

Candidate key:

Foreign key:

equipment (equipId, type, upc, purchaseDate, year, manufacturId, cost, rentFee, branchId)

Primary key:

Candidate key:

Foreign key:

manufacturer (manufacturId, name, FedTaxId, StaTaxId, phone, email)

Primary key:

Candidate key:

Foreign key:

rental (rentalId, equipId, custId, rentDate&time, returnDate&time, empId, branchId)

Primary key:

Candidate key:

Foreign key: equipId;

Answer 1

MIScompany (name, address, phone, email, FedTaxId, StaTaxId)

Primary key:Email

Candidate key:Name and address,email

Foreign key: none

branch (branchId, name, address, phone, email, FedTaxId, StaTaxId)

Primary key:branchid

Candidate key:name and phone

Foreign key:FedTaxId, StaTaxId

employee (empId, driverId, ssno, name, branchId)

Primary key:empid

Candidate key:driverid,empid

Foreign key:ssno,branchid

customer (custId, name, address, driverId, ssno, FedTaxId, StaTaxId)

Primary key: custid

Candidate key:driverid,StaTaxid

Foreign key:FedTaxId, StaTaxId

equipment (equipId, type, upc, purchaseDate, year, manufacturId, cost, rentFee, branchId)

Primary key: Equipid

Candidate key:Equipid,manufacturid

Foreign key:type,branchid

manufacturer (manufacturId, name, FedTaxId, StaTaxId, phone, email)

Primary key:manufactuId

Candidate key:email,phone,StaTaxId

Foreign key:FedTaxId,StaTaxId

rental (rentalId, equipId, custId, rentDate&time, returnDate&time, empId, branchId)

Primary key:empId

Candidate key:rentalId, equipId, custId,empId

Foreign key: equipId;