Determining Functional and Transitive dependencies from Relational Model (with no sample data)

Question

I have a relational model which I've converted from an EER diagram... I've been asked to find the functional and transitive dependencies but how does one do that when there's no sample data to start from?

My relational model is as follows:

Product(productID, name, manufacturer, description, categoryID, supplierID, qtyDescription, unitPrice, status, availableQty, reOrderLevel, maxDiscount)
Primary Key productID
Foreign Key categoryID references Category(categoryID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Category(categoryID, name, description)
Primary Key categoryID
Alternate Key name

Quotation(quoteID, quoteDate, empID, quoteValidity, supplierID, description, productID, qty, unitPrice)
Primary Key quoteID, productID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Supplier(supplierID, name, address, phoneNo, faxNo, contactPerson)
Primary Key supplierID

SupplierOrder(supplierOrdID, orderDate, quoteID, description, productID, qty, unitPrice, orderTotal, status, orderReceiveDate, orderPaymentDate, PaymentRefNo)
Primary Key supplierOrdID
Alternate Key PaymentRefNo
Foreign Key quoteID references Quotation(quoteID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Employee(empID, name, gender, positionID, phoneNo, homeAddress, homePhone, dateOfBirth)
Primary Key empID
Foreign Key positionID references Position(positionID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Position(positionID, title, hourlyRate)
Primary Key positionID

Payslip(PayslipID, empID, startOfPayPeriod, endOfPayPeriod, noOfDaysWorked, hourlyRate, totalPay)
Primary Key PayslipID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE


CustomerOrder(customerOrdID, empID, customerID, orderDate, productID, qty, unitPrice, discountGiven, overDueFees, cancellationFees, totalAmtDue, billingDate, dueDate, status, description)
Primary Key customerOrdID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key customerID references Customer(customerID)
    ON UPDATE CASCADE, ON DELETE CASCADE 
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Customer(customerID, companyName, address, phoneNo, faxNo, emailAddress, contactPerson, gender, 
maxAvailableCredit)
Primary Key customerID

CustomerPayment(paymentRefNo, customerOrderID, paymentDate, amtPaid)
Primary Key paymentRefNo
Foreign Key customerOrderID references CustomerOrder(customerOrdID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Any assistance would be greatly appreciated... I just need an understanding of whether it is possible, and if so, how? A few examples from the model would be great, and I can determine the rest through application of the theory behind the examples.

Answer 1

You are being asked to engage in reverse engineering. Reverse engineering is often done in the case of legacy systems, in order to find out what goals the original designers might have been pursuing. It's really an iffy proposition, nevertheless. You best handle is the foreign key constraints. These reveal the FDs, presumably. Also, you can presume that every non key value is dependent on the primary key of the table it is in.

In regular (forward) engineering, you don't discover the FDs by studying the design. You study the subject matter, and uncover the FDs there. This can be time consuming, and people driven by a tight schedule may accuse you of "analysis paralysis". Only once you've discovered the FDs, among other subject matter features, and you've decided whether or not to pursue a normalized design, do you proceed to compose the tables and the constraints.

Good luck. You're going to need it.