Trying to normalize to BCNF

I am not sure if this is in BCNF or not, but the teacher told me that INSTRUMENT is in BCNF.. Is he messing with me? The teacher keeps messing up my mind on what is right and wrong and making me unsure. He keeps saying stuff that i've already thought of as clearly and I don't even get what he is saying.

INSTRUMENT(InstrumentID, Inst开发者_JS百科rumentType, Tune, Performer, Adr, Phone, Availability) NOTES(TitleNr,Tune, Composer, Copies, Title, Performer)

So is this normalized? in BCNF:

Instrument(InstrumentID, InstrumentType, Tune, Availability, PerformerID*)

NOTES(TitleNr, Title, Tune, Composer, Copies, PerformerID*)

PERFORMER(PerformerID, Name, Adr, Phone)

Tune is in both INSTRUMENT and NOTES. Can it be in both?

---

Each normal form requires the normal form preceding it.

1 NF - Your table only has atomic values i.e. no sets.

2 NF - Non-key attributes require every part of your key to be determined.

3 NF - Non-key attributes do not require anything but the key to be determined.

3.5 NF - If a non-key attribute can determine a key attribute, they must create a unique tuple.

My first concern is what TitleNr stands for. If it is a unique ID then it isn't 2 NF, since the non-key attributes don't require Title to be determined.

My second concern is that InstrumentID is not a proper candidate key if Tune is included. One instrument can play multiple Tunes, if Instrument.Tune represents what tunes have been played with that specific instrument. It would be better to split those attributes out into another table, otherwise the other non-key attributes will make it not be 2 NF.

If it merely represents what Tunes are available, that can already be determined by the PerformerID, which isn't part of Instrument's key. Then it is not 3 NF.

---

Normalization applies to individual tables; it's based on functional dependencies.

Functional dependencies are determined by data, not by column names. Your instructor didn't give you any data. Trying to determine functional dependencies by column names alone is risky business, even when the columns are named well. Your columns aren't named well.

An imaginative database designer could probably come up with a predicate and sample data for the first "Instruments" table that demonstrates BCNF.

Maybe your instructor doesn't understand this stuff either. Wouldn't be the first.

---

It's in 2NF, not BCNF.

INSTRUMENT(InstrumentID, InstrumentType, Tune, Performer, Adr, Phone, Availability)

You really need to know the functional dependencies, but taking a guess I'd say the adr and phone are attributes of the performer, not the instrument. If attributes don't describe the key (in its entirety) then it's not BCNF.

Assume each performer has at most one phone number and address. If so, you would have the functional dependency:

Performer -> phone, adr

That is, there could be multiple instruments associated with the same performer, but in each case their phone and address would be the same (hence recorded redundantly). I'll guess that the key for the Instrument relation is InstrumentId, so there's also a FD saying at most one performer is associated with each instrument;

InstrumentId -> Performer

That being the case, attributes phone and adr don't depend directly upon InstrumentId, but indirectly via Performer. Hence within this relation the FD Performer -> phone, adr is a transitive dependency. Any relation containing transitive dependencies by definition cannot be in anything higher than 2NF (second normal form). So it's not in 3NF, nor BCNF.

2NF doesn't allow partial dependencies. The good news is that since the key is only one attribute you don't have to worry about partial dependencies here: you can't have part of a single attribute.