CODEWITHSUNDEEP
dataframedatabase-designrdbmsdatabase-normalizationfunctional-dependencies
Eileen
Eileen

Reputation: 21

transformation into BCNF with only a relation and no data

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

Upvotes: 0

Views: 198

Answers (1)

Renzo
Renzo

Reputation: 27424

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.

Upvotes: 1

Related Questions