Re: relational databases

"wombat" <wendy.cox2_at_ntlworld.com> wrote in message news:f12b00a0.0302130631.3ed7e545_at_posting.google.com... [snip]

> In our assigment we have been given a snippet of sample data and told
> to normalise it to BCNF.

!!!!

 But how can you normalise data without knowing the constraints on that data?

> The sample data is as follows:
>
> ProductID ProdName ProdType PartNo PartCost AsNo AsName ToolID
> 101 Sink Bthroom 123 25.78 4342 Olive AB53
> 101 Sink Bthroom 156 26.45 6543 Brown ER34
> 105 Bath Bthroom 156 26.45 2321 Green TR34
> 105 Bath Bthroom 178 101.23 6543 Brown PO23
> 108 Stool General 123 25.78 8767 Smith QW12
> 108 Stool General 164 32.00 OI98 Brown YT45

What your assigner is asking is to *assume* various constraints (including functional dependencies) based upon things as nebulous as column names and a single relation value.

At the very least I would prefix any answer to the question with a statement such as

"Assuming that the following constraints hold in general (as well as in the given snippet):

ProductID -> ProdName
ProductID -> ProdType
{ProductID, PartNo} is Unique
...

and that no other constraints hold (unlike, obviously, in the given snippet)

then a BCNF normalisation is:

...
"

> When I come to the first normalisation stage of removing repeating
> groups, I end up with three relations:
>
> PRODUCT (ProductID, ProductName) ProductID = primary key
> PRODUCTTYPE (ProductID, ProductType) ProductID, ProductType = primary
> key
> PART (ProductID, PartNo, PartCost, AsNo, AsName, ToolID)
> ProductID,PartNo = primary key

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu Feb 13 2003 - 11:23:09 CST