Trying to normalize to BCNF

Question

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, InstrumentType, 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?

Answer 1

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.