2

I need to design database which would keep track of the following attributes:

stdnum       // student number
postcode     // postal code
phone_number // student phone number
city         // student address: city

Also listed are functional dependencies:

stdnum -> postcode
stdnum -> phone_number
postcode -> city
phone_number -> city

I need to find a lossless-join, dependency preserving, 3rd normal form decomposition.

I have tried different decompositions but there was no one that obeys all requirements (they are: lossless-join, dependency preserving, 3rd normal form).
E.g. if I leave the original relation without changes (table would have all 4 attributes) I would get lossless-join and dependency preserving but not 3NF, only 2NF.

The decomposition(stdnum, postcode, phone_number) JOIN (postcode, city) JOIN (phone_number, city) is in 3NF and dependency preserving, but not lossless-join.

philipxy
  • 14,867
  • 6
  • 39
  • 83
executeinstaller
  • 686
  • 6
  • 12

4 Answers4

1

As explained in this slides, there's always a dependency preserving, lossless join 3NF. The described algorithm for computing it is implemented in this prolog script (explanation and source).

Such a decomposition always exists, and in this case it's the one you approached:

(stdnum, postcode, phone_number) JOIN
(postcode, city) JOIN
(phone_number, city)

You can run the Tableau Algorithm to check that it actually is lossless join.

TuteC
  • 4,342
  • 30
  • 40
1

Your breakdown of the original relation presumes these dependencies point to the same CITY.

postcode -> city
phone_number -> city

In real life that is not always the case. For instance, in my own locality there are addresses which have a phone number with a LONDON area code but which lie in a KINGSTON, SURREY postcode. And then there are mobile phones, which don't map to any geographical location.

So I would resolve your problem by changing the data model.

"Attributes are abstraction. You don't need to understand meaning of atributes. All information about them is in the functional dependencies listed in the task."

Thinking with concrete examples is a better way to understand the flaws in our abstractions. In this case perhaps you really have five attributes not four. Or perhaps there is functional dependency postcode -> city is valid but phone_number -> city is bogus. Or perhaps you need to model the fact that a student can have more than one phone number e.g. digs landline (shared), mobile (personnel).

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    Thank for reply, but the specific of my task is that I can`t determine dependencies, they are already defined in the task. You can use symbols like A,B,C,D instead of given attributes. Attributes are abstraction. You don't need to understand meaning of atributes. All information about them is in the functional dependencies listed in the task. – executeinstaller Nov 03 '11 at 14:19
1

Perhaps the purpose of the assignment is to make you discover the negative answer to your question "Is there any solution for my problem?".

Having your database as a single 4-attribute thing necessarily means there can be only one D (city) for each A (stud). Decomposing in the usual way for the B->D and C->D FDs, necessarily introduces the possibility of having two distinct D's associated with each A.

Dealing with that requires the introduction of a database constraint into the design, but database constraints are outside the scope of normalization theory proper.

And not decomposing necessarily means that you won't get 3NF.

Hence : perhaps the purpose of the assignment is to make you discover that normalization isn't a holy grail of database design. I think you were already on that track.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • "Decomposing in the usual way for the B->D and C->D FDs, necessarily introduces the possibility of having two distinct D's associated with each A." In other words, normalisation reveals an error ih the original data model. I think that's a validation of the normalisation exercise, not a strike against it. – APC Nov 07 '11 at 13:36
  • Huh ? I don't think it does. If you do think it does, it seems up to you to reveal what the error is, precisely. What is wrong with the concept of "if you know the zipcode, then you know the city", and what is wrong with the concept of "if you know the phone number, then you know the city", and what is wrong with those two concepts applying simultaneously ? ... – Erwin Smout Nov 08 '11 at 23:15
  • ... (I mean, of course, from a _theoretical_ perspective, e.g. if phone companies organized their phone number allocations such that the first n digits of any phone number really are a determinant factor that allows one to determine the city from this.) (I mean : the consideration that this does not happen to be the case in practice, today, that consideration does not count.) – Erwin Smout Nov 08 '11 at 23:17
0

In dependency theory, a join dependency is a constraint on the set of legal relations over a database scheme. A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T. If one of the tables in the join has all the attributes of the table T, the join dependency is called trivial.

The join dependency plays an important role in the Fifth normal form, also known as project-join normal form, because it can be proven that if you decompose a scheme R in tables R_1 to R_n, the decomposition will be a lossless-join decomposition if you restrict the legal relations on R to a join dependency on R called *(R_1,R_2,...R_n).

Another way to describe a join dependency is to say that the set of relationships in the join dependency is independent of each other.

Unlike in the case of functional dependencies, there is no sound and complete axiomatization for join dependencies, though axiomatization exist for more expressive dependency languages such as full typed dependencies. However, implication of join dependencies is decidable.

remudada
  • 3,751
  • 2
  • 33
  • 60
Chand RAthi
  • 1