Redundancy and Inconsistence in Relational DB

[WWGD]

Hi All,
I am given this relational table

ROOM (RoomID, BuildingName, FloorNumber, NumberOfFloors).
The underline and bold mean that RoomID is the primary key.

And I am asked to show an example of how redundancies may result in inconsistencies.

This is a questions someone asked, hoping for my help, but I am stuck.

So I am not even sure of what to do. I am looking for an example, a "model" where one or more
entries are repeated which leads to inconsistencies. I am assuming for now that just one repetition
will do, so I set up a table with a repeated entry (please see attached file). I chose rooms in buildings
with the same number of rooms and same floor number.
Any ideas?

 

[BvU]

What do you think of numberoffloors ? Does it depend on the primary key ?

 

[WWGD]

Ah, thanks, I am not just clear on what we need to do. So we may have two identical records. Then we mistakenly enter different PK entries, which we can do, since, , as you pointed out, PK does not determine numberoffloors (I know this is ambiguous, what I mean is different rooms may be in different buildings with the same number of floors), and this gives us an inconsistency. So, if we mistakenly enter "Number of Floors" with a different value in the same copy, we have no way of automatically telling this is wrong?

 

[BvU]

Hi WW,

Sorry, I didn't bother to look at the word file before replying to post #1: the table definition was enough to reply as I did.

My reply was triggered by the fact that #floors appears in every record for a given building, so in the normalization process it should turn out to be an attribute for the building, not for the room.
In the design of the table, it's more a redundancy issue than an inconsistency issue.
In a design redundancy is to be avoided, because inconsistency can arise easily when you add a record like (8, 1, 3, 4) (*)

For the word file in post #1, I would say adding record 2 will already be disallowed because the primary key must be unique. Same for the second word file in post #3.

(*) Which is what you pointed at in the second word file in post #3, but somewhat obfuscated because of the identical PK.

And yes, sometimes we do leave redundancies in and make sure inconsistencies are prevented by triggering checking procedures (which slows down the process, but less than splitting off a separate table with its own overhead). But in the learning process we don't ...

Redundancies are a sure recipe for future trouble: in your example, adding a floor to a building forces having to go through all records and fix them.

 

[chiro]

Do you reference multiple copies of the same record and or field?

Redundancy means that you have more than the minimum required information to represent something. Do you store the same field twice or more and do you store more than the number of references you should (and is the choice of primary key structure optimal in that regard as well)?