Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Redundancy and Inconsistence in Relational DB

  1. Oct 11, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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?
     

    Attached Files:

  2. jcsd
  3. Oct 11, 2015 #2

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    What do you think of numberoffloors ? Does it depend on the primary key ?
     
  4. Oct 11, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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?
     

    Attached Files:

  5. Oct 12, 2015 #4

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    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 ...:smile:

    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.
     
    Last edited: Oct 13, 2015
  6. Oct 13, 2015 #5

    chiro

    User Avatar
    Science Advisor

    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)?
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Redundancy and Inconsistence in Relational DB
Loading...