0

I have to transform this following relation into BCNF. But the exercise did not indicate what are the primary determinant. Nothing is underline. I know the different steps but I have some difficulties to write the functional dependencies because I only have the relation and no data ... Could you please help me or guide me how can I start

Transform the following relation into BCNF. Make and state the appropriate assumptions about functional dependencies and show the steps in the transformation

INVOICE (Number,CustomerName,CustomerNumber,CustomerAdress,ItemNumber,ItemPrice,ItemQuantity,SalespersonNumber,SalespersonName,Subtotal,Tax,TotalDue)

My question is how to write functional dependencies if we only have the relation above

I tried that but I am not sure, I just do that by guessing

Number ---> (CustomerName,CustomerNumber,CustomerAdress)

CustomerNumber--->(CustomerName,CustomerAdress)

ItemNumber---> (Number,ItemPrice)

(Number,ItemNumber)--->(ItemQuantity,SalespersonNumber,SalespersonName,Subtotal,Tax,TotalDue)

Thank you

philipxy
  • 14,867
  • 6
  • 39
  • 83
Eileen
  • 21
  • 3
  • Does this answer your question? [Finding functional dependency](https://stackoverflow.com/questions/26796963/finding-functional-dependency) – philipxy Feb 12 '21 at 03:34
  • 1
    Right now you are just asking for us to rewrite a textbook with a bespoke tutorial & do your (home)work & you have shown no research on answering. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following a textbook/reference with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. Quote the definitions, theorems, algorithms & heuristics you are relying on. All the steps are also SO faqs. Google with & without 'site:stackoverflow.com'. – philipxy Feb 12 '21 at 03:35
  • 2
    The Number in your relationship is the Invoice Number, not a Customer Number. A Customer Number is implied. This is the main reason why you should never have a database column called Number or ID. It's too confusing., – Gilbert Le Blanc Feb 12 '21 at 09:17
  • Relational variable (table) is in a specific NF for any set of valid tuples (rows), *empty set* included. The NF is determined on logical design level, way before SQL `CREATE TABLE` is executed. Stick to logic, see Renzo's answer. – Damir Sudarevic Feb 12 '21 at 15:28
  • I am not asking to do my homework. I post the entire problem and said that my problem is to write the functional dependencies and I wrote them !! My question was to know if it is possible to write those dependencies even if we do not have data to analyze – Eileen Feb 13 '21 at 12:49

1 Answers1

1

Usually exercises of this kind can be solved if one has an idea of the meaning of the different attributes, since functional dependencies concern the meaning of the data.

We can just guess that, in your case, all the attribute with a prefix like “Customer”, “Item” and “Salesperson” represent attributes of different corresponding entities, while the other attributes are related to an Invoice, which concerns the selling of a (single?) product to a (single?) Customer, from a (single?) Salesperson. Of course, this is just a guess, and could be terribly wrong.

Under these hypotheses, and also under the hypothesis that an attribute with the suffix Number identifies uniquely a certain entity, we could define the following functional dependencies as a cover of the FDs of the relation:

CustomerNumber -> CustomerName, CustomerAddress
ItemNumber -> ItemPrice, ItemQuantity
SalespersonNumber -> SalespersonName
Number -> CustomerNumber, ItemNumber, SalespersonNumber, Subtotal, Tax, TotalDue

If this is correct, then a decomposition in BCNF of the relation could be the following:

Customers (CustomerNumber, CustomerName, CustomerAddress)
Items (ItemNumber, ItemPrice, ItemQuantity)
Salespersons (SalespersonNumber, SalespersonName)
InvoiceData (Number, CustomerNumber, ItemNumber, SalespersonNumber, Subtotal, Tax, TotalDue)

But of course, I repeat, this is just a guess based on (a supposed) naming conventions.

For instance, breaking the above supposed convention, another possibility is that ItemQuantity is not an attribute of Item, but is an attribute of Invoice.

So, in this case, a cover of the FD should be the following:

CustomerNumber -> CustomerName, CustomerAddress
ItemNumber -> ItemPrice
SalespersonNumber -> SalespersonName
Number -> CustomerNumber, ItemNumber, ItemQuantity, SalespersonNumber, Subtotal, Tax, TotalDue

If this is correct, then a decomposition in BCNF of the relation could be the following:

Customers (CustomerNumber, CustomerName, CustomerAddress)
Items (ItemNumber, ItemPrice)
Salespersons (SalespersonNumber, SalespersonName)
InvoiceData (Number, CustomerNumber, ItemNumber, ItemQuantity, SalespersonNumber, Subtotal, Tax, TotalDue)

Which is the correct answer? Well, nobody can tell, unless the meaning of the data is known for sure.

Renzo
  • 26,848
  • 5
  • 49
  • 61