-1

I'm trying to get a better understanding of normalisation so I can use best practices going forward. I've found a question in an old book and I'm a little confused by it. Essentially I'm given this table with the following data:

Name    Sport    Sport Centre
Jim     Tennis   A1
Jim     Golf     A2
Dan     Tennis   A1
Dan     Golf     A3
Ben     Golf     A2

So we're assuming that each sport centre can ONLY host one sport. What I want is to convert this to BCNF. My process (from what I've learned so far) is as follows:

1, I identified all of the functional dependencies here:

Sport Centre->Sport
(Name, Sport Centre)->Sport

2, I identified all candidate keys:

(Name, Sport Centre)

But this is where I get stuck. I thought to be in BCNF that the table must have more than 1 candidate key and I can only see one. I'm unsure how to get this to BCNF. What I have done is the following splitting up of the table:

Name    Sport Centre
Jim     A1
Jim     A2
Dan     A1
Dan     A3
Ben     A2

Sport Centre    Sport
A1              Tennis
A2              Golf
A3              Golf

But I also understand that to be in 3NF (before BCNF) every attribute must be dependant on the full primary key, yet my splitting up breaks this rule.

How do I normalize properly here?

philipxy
  • 14,867
  • 6
  • 39
  • 83
JamMan9
  • 706
  • 2
  • 9
  • 22
  • Hi. What (good) textbook are you following? Where are you stuck following its definitions & procedures? If you aren't following one, why ask us? You are just asking us to write one. Please read & act on [ask], the downvote arrow mouseover text & hits googling 'stackexchange homework'. PS What exactly is your question? (See my edit.) And what does "BCNF - Is it possible here?" in your title mean? – philipxy Apr 25 '18 at 04:25

2 Answers2

2

1, I identified all of the functional dependencies here:

You have not identified all the FDs (functional dependencies) that hold. First: FDs are between sets of attributes. Although it happens that if we restrict ourselves to FDs from a set of attributes to a set holding a single attribute then we can infer what other FDs hold. So we can restrict what we mean by "all", but you should know what you are saying. Next: You have identified some FDs that hold. But all the ones implied by them via Armstrong's axioms also hold. This always means some trivial FDs, eg {Sport Centre} -> Sport Centre & {} -> {}. Although it happens that we can infer the trivial FDs just from knowing the attributes. So again we can restrict what we mean by "all", but you should know what you are saying. It happens that you have identified all the non-trivial FDs with one attribute on the RHS. But you have not justified that the ones you found hold or that you have found all the ones that hold.

You need to learn algorithms & relevant definitions for generating a description of the set of all FDs that hold. Including Armstrong's axioms, the notion of a FD transitive closure & the notion of a FD canonical cover to concisely characterize a closure.

2, I identified all candidate keys:

Assuming that { {Sport Centre} -> Sport } is a canonical cover, the only CK is {Name, Sport Centre}.

You need to learn algorithms & relevant definitions for finding all CKs.

I thought to be in BCNF that the table must have more than 1 candidate key

That's wrong. You seem to be trying to recall something like "3NF & not BCNF implies more than 1 CK" or "3NF & 1 CK implies BCNF", which are true. But these don't give that BCNF implies more than 1 CK, or equivalently, that 1 CK implies not BCNF.

You need to learn a definition of BCNF & other relevant definitions.

I'm unsure how to get this to BCNF.

We can always decompose to a BCNF design. Most definitions of BCNF say it is when there are no FDs of a certain form. It happens that we can get to BCNF by repeatedly losslessly decomposing to eliminate a problem FD. However, that might needlessly not "preserve" FDs. So we typically decompose with preservation to 3NF/EKNF first, which can always preserve FDs. Although then going to BCNF might fail to preserve a FD even though there was a FD-preserving decomposition directly from the original.

You need to learn algorithms & relevant definitions for decomposing to a given NF. Including the notions of lossless decomposition & FD preservation.

But I also understand that to be in 3NF (before BCNF) that every attribute must be dependant on the full primary key and my splitting up breaks this rule.

To normalize to a given NF it is not necessary to go through lower NFs. In general that can eliminate good final NF designs from arising.

Also "to be in 3NF [...] every attribute must be dependent on the full primary key" is not correct. You need to memorize definitions--necessary & sufficient conditions. And PKs (primary keys) do not matter to normalization, CKs do. Although we can investigate the special case of just one CK, which we could then refer to as the PK. Also "my splitting up breaks this rule" doesn't make sense. A necessary condition for a table to be in some NF is not a rule about how to decompose to it or any other NF.

You need to find a (good) academic textbook and learn its normalization definitions & algorithms. (Dozens of textbooks are free online, also slides & courses.) When you are stuck following it, reference & quote it, show your work following it, and explain about how you are stuck.

philipxy
  • 14,867
  • 6
  • 39
  • 83
-1

I think I might have answered my own question, but I won't mark it unless an expert on the community can confirm.

So my splitting up is valid, I have incorrectly identified the candidate keys.

There are 2 candidate keys which are:

(Name,Sport Centre)

(Sport Centre, Sport)

If this is correct, then me splitting the tables up is BCNF and valid. I think this is correct.

JamMan9
  • 706
  • 2
  • 9
  • 22
  • Hi. "So my splitting up is valid, I have incorrectly identified the candidate keys." is not clear. Please use enough words & sentences to say what you mean. Clearly {Sport Centre, Sport} is not a CK of the original, because a CK determines every attribute but {Sport Centre, Sport} does not determine Name. – philipxy Apr 25 '18 at 04:22