Assume the following table is complete, and will never change: a) Find 5 functio

Question

Assume the following table is complete, and will never change:

a) Find 5 functional dependencies.

b) Find all candidate keys.

(you can use the single letter headings rather than the full attribute names)

Part B - 33 points

Below is a relational schema for the Northwind database (a sample company supplied with Microsoft Access).

Notes:
Primary keys are in bold.
EMPLOYEES.ReportsTo is the employee number of the employees' supervisor.
ORDERS has the header for each order, whereas ORDER_DETAILS has the line items for the products ordered.
PRODUCTS.UnitsOnOrder refers to the product being ordered from a supplier for restocking, and not to a customer having placed an order for it.
ORDERS.ShipVia is the shipper for this order

Identify all foreign keys in the above schema - state any assumptions you make. Note that the foreign key name may not match the spelling of the corresponding primary key.

Part C - 33 points

A company called FastCabs provides a taxi service to clients.
The table shown below displays some details of client bookings for taxis.

Assume the table is complete, and will never change.
Assume that a taxi driver is assigned to a single taxi, but a taxi can be assigned to one or more drivers.

a) Identify the functional dependencies that exist between the columns of the table.

b) Identify the primary key.

c) Find any candidate keys (if present) for the table.

A B C D PILOT FLIGHT DATE DEPARTS Cushing 83 9-Aug 10:15a Cushing 116 10-Aug 1:25p Clark 281 8-Aug 5:50a Clark 301 12-Aug 6:35p Clark 83 11-Aug 10:15a Chin 83 13-Aug 10:15a Chin 116 12-Aug 1:25p Copely 281 9-Aug 5:50a Copely 281 13-Aug 5:50a Copely 412 15-Aug 1:25p

Explanation / Answer

PART A:
a) Functional Dependencies:
1. FLIGHT -> PILOT
2. FLIGHT -> DEPARTS
3. FLIGHT -> DATE, PILOT,DEPARTS
4. DATE -> FLIGHT, DEPARTS
5. DATE -> PILOT, FLIGHT

b) Candidate keys:
Here, the candidate key will be combination of FLIGHT and DATE.
Hence the candidate keys are:
83   9-Aug  
116   10-Aug  
281   8-Aug  
301   12-Aug  
83   11-Aug  
83   13-Aug  
116   12-Aug  
281   9-Aug  
281   13-Aug  
412   15-Aug

PART B:
Forien keys: bracket represents primary key from another table
In table EMPLOYEES --> ReportsTo (EmployeeID)
In table PRODUCTS --> SUPPLIER (SupplierID), UnitsOnOrder (OrderID)
In table ORDERS --> Customer (CustomerID), Employee (EmployeeID),
In tab;e ORDER_DETAILS --> Product (ProductID)

PART C:
a) Functional Dependencies:
1. JobID -> DriverName,TaxiID,ClientID,ClientName,JobPickUpAddress
2. DriverID ->DriverName
3. ClientID -> CLientName
4. TaxiID -> DriverID

b) primary key: JobID
c) Candidate Keys:
1. JobID
2. DriverID, ClientID, JobDate

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.