Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hello I am attempting to identify a transitive dependency in the following diagram. I think that possibly the determinate of "Subtotal" is "Quantity" and "Price Charged" but I'm not sure if "subtotal" is also dependant on the "product_code" which would make it a partial dependency right?

enter image description here

This is how I've worked out the 1NF.

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
INVOICE (INVOICE_NUMBER → INVOICE_DATE)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME, QUANTITY, PRICE_CHARGED)
TRANSITIVE DEPENDENCY
SUBTOTAL (QUANTITY, PRICE_CHARGED → SUBTOTAL)

I'm fairly confident about the other dependencies but am not sure the transitive one is correct?

edit: MODIFIED DETAIL

1NF (CUTOMER_NUMBER, INVOICE_NUMBER, PRODUCT_CODE → CUSTOMER_NAME, INVOICE_DATE, LINE_NUMBER, COMMON_NAME, SCIENTIFIC_NAME, CATEGORY, QUANTITY, PRICE_CHARGED)
PARTIAL DEPENDENCIES:
CUSTOMER (CUSTOMER_NUMBER → CUSTOMER_NAME)
PRODUCT (PRODUCT_CODE → COMMON_NAME, SCIENTIFIC_NAME)
INVOICE (INVOICE_NUMBER, LINE_NUMBER → QUANTITY, PRICE_CHARGED, INVOICE_DATE, PRODUCT_CODE)
TRANSITIVE DEPENDENCY
-   ?

Is this looking better, is there no transitive dependency?

share|improve this question
    
I don't see how QUANTITY and PRICE_CHARGED depend on the product_code. Do customers always order the same quantity of a product? –  ypercube Mar 24 at 11:13
add comment

1 Answer 1

up vote 1 down vote accepted

Can you get a different answer for subtotal for any specific pair of quantity and price_charged? No, of course not. Arithmetic is arithmetic.

Quantity and price charged depend on invoice_number and line_number, not on product_code at all. In fact, product_code also depends on invoice_number and line_number.

EDIT:

Partial Dependencies are dependencies on part of a multi-part key. Transitive Dependencies are things that depend on non-key attributes. Step one is to look at your initial relation and determine what the key of the whole thing is, or if there are multiple candidate keys, what they are.

Here are the pieces that I'd guess make up your key, based on the limited sample data and some common sense:

  • Invoice Number
  • Line Number

Your table looks to me like an invoice detail table to me. This means that if you know what invoice and which line on the invoice, you know which distinct record you're looking at. If that's the case, what do each of the non-key columns depend on? Let's look at each in turn:

  • CUTOMER_NUMBER: depends on just the INVOICE_NUMBER, therefore PARTIAL.
  • PRODUCT_CODE: depends on the whole key, therefore FULL
  • CUSTOMER_NAME: depends on CUSTOMER_NUMBER (non-key), therefore TRANSITIVE
  • INVOICE_DATE: depends on just the INVOICE_NUMBER, therefore PARTIAL.
  • COMMON_NAME: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.
  • SCIENTIFIC_NAME and CATEGORY (ditto)
  • QUANTITY: depends on the whole key, therefore FULL.
  • PRICE_CHARGED: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.

As noted previously, SUBTOTAL is calculated based on QUANTITY and PRICE_CHARGED so it depends on the full key as well.

share|improve this answer
    
How exactly would product_code depend on invoice_number and line_number? –  crm Mar 24 at 11:59
    
Product code is a foreign key to the PRODUCT table in your invoice detail. In the product table, product code is the determinant, in the invoice item table, it is just a dependent attribute. –  Joel Brown Mar 24 at 14:37
    
Thanks @Joel I edited my question with an updated detail of tables, does it look correct? Does no transitive dependency exist? Sorry really struggling to get my head around all this. Its part of a CS module I'm studying at the moment. –  crm Mar 25 at 11:57
    
@crm - Yes. Looks good now. –  Joel Brown Mar 30 at 14:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.