Search code examples
mysqlnormalizationdatabase-normalizationbcnf

How to understand whether a schema is in BCNF

I understood the concept of BCNF very well so someone gives me a relationship in this format like

R = {P, Q, S}
F = {PQ->S, PS->Q, QS->P}

I can easily say that the relation is in BCNF since all the keys on left hand side are also super keys. But If I get a real world questions like the below image: enter image description here

then how will I determine whether this schema is in BCNF? How will I create functional dependencies from the schema and then found out about super keys? could anyone give me some pointers to solve such questions?

If you convert the schema into :

Code -> City, State
Flightnum -> .....
model -> ...
cid -> ....
Cid, flightnum, date -> ...

then obviously this is not in BCNF but the answer is that the following schema is BCNF, and I am not able understood how they derived the equation for it.

Solution

  • Informally, a schema is in BCNF if all of its relations are in BCNF.

    How will I create functional dependencies from the schema . . .

    Without any other context, I'd assume that underlined attributes in the image are attributes in the primary key.

    So I'd read that image as saying

    code->city, state
cid->name, city
etc.

    Those are your functional dependencies.