Functional and Multivalued Dependencies in a table

Question

This is a homework assignment I was assigned in my Database book. Basically I am supposed to tell which of these are functional dependencies and which are multivalue dependencies, say if it is in BCNF or 4NF, and then come up with a better design (aka another table if needed) so that the table(s) are in both BCNF and 4NF. Here is what I know (or think I know at least) so far:

Functional Dependancy: X determines one and only one Y (X->Y)

Multivalue Dependancy: X determines multiple Y's (x->>Y)

Candidate Key: A unique column that can be used as a primary key

Primary Key: A unique candidate key (Selected the best candidate key)

PERSONALTRAINER_SESSION

[Columns]

Trainer, TrainerPhone, TrainerEmail, TrainerFee, ClientLastName, ClientFirstName, ClientPhone, ClientEmail, TrainingDate, TrainingTime

---

Now, I know I am supposed to narrow my focus when figuring out these dependencies to only the columns and data supplied by the tables instead of in general, but there doesn't seem to be any clear dependency relation to me. For example just take the column 'Trainer'. 'Trainer' could determine the email or something similar I suppose...but that wouldn't be a clear and concise connection would it?

Likewise in terms of candidate keys.....TrainerEmail, TrainerPhone, ClientEmail, and ClientPhone all seem like possible candidate keys to me. Both phone numbers and emails are unique in real life, but I would imagine that phone numbers might be unique only to a certain country while emails are unique worldwide. So which would be a good primary key for the table?

Maybe my thinking isn't correct but the homework said to list functional and multivalued dependencies first and then reconfigure the tables so I guess I'm missing something somewhere.

Any ideas on how to go about this the right way would be appreciated :D

========================================================================= EDIT: Ok so I've changed this table into 3.

TRAINER

TrainerID, Trainer, TrainerPhone, TrainerEmail, TrainerFee

CLIENT

ClientID, ClientLastName, ClientFirstName, ClientPhone, ClientEmail

TRAININGSESSION

TrainerID(foreign key), ClientID(foreign key), TrainingDate, TrainingTime

Would this make more sense? Perhaps it would make more sense to use a single composite key.

Answer 1

To pick up on a few ideas suggested already: In reality are email addresses unique? In reality are phone numbers unique? Most definitely yes they are. In reality facts are always uniquely identifiable from other facts and that goes equally for trainers, clients and anything else that you might want to record in the database.[*] That is why in the relational model we represent information as relations consisting of unique tuples with keys.

For homework exercises in normalization students are usually not expected to invent new attributes for keys or anything else. The point is generally to arrive at a normalized synthesis of the given attributes based on a set of dependencies.

The real question here is what types of facts (predicates) do you need to represent in your database and what business rules (dependencies) apply to them. Your question doesn't include that information - only guesses and assumptions are possible.

Given such an arbitrary exercise I would be tempted to say that the only prudent answer is a single relation with the full set of attributes as key. Leave it at that.

[* for the avoidance of doubt: I am not asserting that email and telephone numbers are the right keys to use for trainers or anything else. My point is simply that an email is intrinsically "unique" just like any other fact but without further context for the question there are no good grounds for saying whether email is a useful identifier that can or should be implemented as a key in your database]