CODEWITHSUNDEEP
databasedatabase-normalizationfunctional-dependencies3nfbcnf
Tim Barclay
Tim Barclay

Reputation: 855

Identifying Boyce Codd Normal Form

Attempt at 3NF from question Identifying Functional Dependencies (starting from a schema in chapter Normalization in Connolly & Begg's Database Systems):

Client {clientNo(PK), clientName}
Owner {ownerNo(PK), ownerName}
Property {propertyNo (PK), propertyAddress, rent}
ClientRental {clientNo(PK), propertyNo(PK), rentStart, rentFinish, ownerNo(FK)}

Each property has only one owner and clients can rent those properties. Rent is fixed for each property.

How do I show whether these are also in BCNF?

What is wrong with my argument that ClientRental is not in BCNF because PropertyNo->ownerNo so PropertyNo is a determinant in a functional dependency but isn't a superkey?

Upvotes: 0

Views: 894

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

The short, informal way to express the difference is that, in BCNF, every "arrow" for every functional dependency is an "arrow" out of a candidate key. For a relation that's in 3NF, but isn't in BCNF, there will be at least one "arrow" out of something besides a candidate key.

Wikipedia entry for 3NF table not meeting BCNF

A common misconception is that you can normalize to 2NF and no higher, then to 3NF and no higher, then to BCNF and no higher. In fact, fixing a partial key dependency in order to reach 2NF often leaves you with all relations in 5NF. That is, you went from one relation in 2NF to multiple relations in 5NF without stopping at BCNF in between.

Upvotes: 0

Related Questions