Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    4

    Question Wierd Looking Functional Dependency in a Normalization Problem!!

    Today, I've encountered a wierd looking functional dependency in a question on normalization in a university exam, and I'm now urgently seeking answer to that. Anybody wanna try it?

    The question was exactly as follows:

    "Given a relation, R(A,B,C) and a set of functional dependencies {Ø --> A, B --> C}. Is the relation in 3NF? Why, or why not? If not, can it be decomposed to 3NF? Is the relation in BCNF? Why, or why not? If not, can it be decomposed to BCNF?"

    The problem is that I've already tried looking for the answer in different books, but I could never find a situation where there is a functional dependancy that says NULL --> SomeAttribute, and I don't know how to deal with it.

    It'll be great if you guys can help me with it. I so need to know the answer. Also, if you know some books, where I can find critical problems like this with a good explanation of their solutions, please, refer them here.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Ø represents the empty set. It does not mean NULL. "Ø --> A" therefore means attribute A has only one possible value in any relation schema.

    So knowing that Ø and B are the determinants think about what key(s) would be possible here. Can Ø and B both be superkeys and what properties do 2NF and 3NF require of superkeys?

    Quote Originally Posted by db_baby
    Also, if you know some books, where I can find critical problems like this with a good explanation of their solutions, please, refer them here.
    The Alice Book

  3. #3
    Join Date
    Jan 2009
    Posts
    4
    Thank you so much for the reponse. I'm already in process of acquiring the book, but it will take some time.

    Meanwhile, I'm still confused in the sense that how an attribute set that is Empty, can be superkey, or even be part of a functional dependancy.

    Could you give me an example of a relation schema maybe (let it be hypothetical even) that illustrates such situation? I tried thinking in different ways, but it's still beyond my comprehension.

    Thanks again, I truly appreciate the help.
    Last edited by db_baby; 01-25-09 at 03:22.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Suppose you want to store the current interest rate and inflation rate in a relvar called CurrentRates:

    CurrentRates {BankBaseRate, Inflation}

    You only require one applicable base rate and inflation rate at any point in time so you'd want the CurrentRates relvar to contain exactly one tuple. That means the following sets of attributes:

    {BankBaseRate, Inflation}
    {BankBaseRate}
    {Inflation}

    don't make sense as candidate keys because they would still permit more than one tuple and therefore more than one value for those attributes at any one time. The only possible candidate key for CurrentRates is the empty set of attributes, (sometimes also written as {}).

    The FDs being satisfied by CurrentRates include:

    -> {BankBaseRate, Inflation}
    -> {BankBaseRate}
    -> {Inflation}

    Which is what we want. -> X means X has a single value in the entire database at any one time.

    Notice that the FDs satisfied by CurrentRates are all trivial because is a subset of every set, including {BankBaseRate}, {Inflation}, {BankBaseRate, Inflation} or any other set of attributes that might be a determinant. CurrentRates has no non-trivial FDs at all! By definition therefore CurrentRates satisfies 3NF.

    An interesting side note is that SQL does not have any concise syntax for specifying as a key. One popular workaround is to add a redundant column and some constraints that limit the table to just one row.

    CREATE TABLE CurrentRates
    (BankBaseRate NUMERIC(5,2) NOT NULL,
    Inflation FLOAT NOT NULL,
    foo CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY CHECK (foo = 'x'));

    In this example foo is a superkey and is the primary key. This shows that SQL's "PRIMARY KEY" constraint is really a misleadingly named superkey constraint. NOT NULL UNIQUE would do just as well instead of "PRIMARY KEY".

    I use the same technique for any rarely changed constants or "configuration" values within the database where I don't need to preserve the history of values over time.

  5. #5
    Join Date
    Jan 2009
    Posts
    4
    Awesome explanation!!! ....Thank you so much, it's all clear now.
    Last edited by db_baby; 01-25-09 at 14:09.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by dportas
    Notice that the FDs satisfied by CurrentRates are all trivial because is a subset of every set, including {BankBaseRate}, {Inflation}, {BankBaseRate, Inflation} or any other set of attributes that might be a determinant. CurrentRates has no non-trivial FDs at all!
    Oops, that's wrong. {Inflation} -> {BankBaseRate} is a non-trivial FD for example. What I meant was that all the determinants (trivial or otherwise) are superkeys and therefore CurrentRates is in 3NF and BCNF as well.

  7. #7
    Join Date
    Jan 2009
    Posts
    4
    Thank you for the correction.

    Btw, I got the book you recommended: "Foundations of Databases" (the Alice book). But, I was a bit disappointed to see so little discussion about normalization.

    Do you know any more books (like the Alice book, more on the advanced side) that discusses FD's and NF's in fuller details (and, preferably, with many examples)? Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •