Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Need help understanding decomposition and anomalies

    I'm having trouble understanding the concept of decomposition.

    So far, I think decomposition means: decompose one relation into 2 or more relations to eliminate anomalies and redundant information. Is that right?

    I understand the definition of insert, delete and update anomalies. I just dont understand how they go away with decomposition.

    One of my assignment quesions is:
    The following relation holds details about cars ;

    CAR_DETAILS(Registration_No , Make , Model, Tow_Load,Engine_Size,Colour )

    And has the following functional dependencies;
    Make , Model -> Engine_Size
    Registration_No -> Make, Colour, Model
    Engine_Size -> Tow_Load

    The relation is decomposed into ;
    CAR_DETAILS1(Registration_No , Colour, Make , Engine_Size* ,Model)
    CAR_DETAILS2(Engine_Size, Tow_Load)

    Prove that the decomposition is incomplete and complete the decomposition.

    (Please dont flame me about doing my own homework, I'm not after an answer to the question. I really just want to understand the concept and to clear my defintion of decomposition and anomamlies.)

    My understanding is that the decomposition is incomplete because the decomposed relations can contain redundant info and have update, insert and deletion anomalies. Am I right in thinking that?

    Redundant info for CAR_DETAILS1 can be multiple rows with the same attribute value for Colour, Make, Engine_Size and Model.
    (If I'm right about this, then I dont quite understand how you could ever have two rows with the same value for an attribute and for them not to be considered redundant information, OR is it the combination of a certain non-key attribute plus the primary key that makes data non-redundant?)(Hope that made sense)

    The update anomalie is: say for example it turns out that engine size for a particular make and model was entered wrong, then I would have to update it for all occurences of that make and model for that engine size(Is that a correct example of update anomalie?)
    I'm reading SQL Demystified (great book) and the defintion there says
    "Update Anomaly: An update anomaly refers to a situation wherein an update of a single data value requires multiple rows to be updates."
    So going by my understanding of that definition CAR_DETAILS1 has update anomalies.
    (But if thats the case then wouldnt an update anomaly apply apply to every attribute of every tuple? OR again is it the combination of a non-key attribute plus the primary key that determines whether or not the update anomaly applies?)

    I hope I was able to articulate the difficulty I'm having, I think my understanding of the concepts is way off. So if yous can help me clear them up I'd be really grateful.

    I'm not after help completing the decomposition, just want to understand the concepts.

    Thanks heaps
    Last edited by JKeegan; 11-13-11 at 00:46.

  2. #2
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by JKeegan View Post
    My understanding is that the decomposition is incomplete because the decomposed relations can contain redundant info and have update, insert and deletion anomalies. Am I right in thinking that?
    The decomposition is incomplete because you have information about multiple relationships contained in one table. If one relationship changes while the other remains the same, this creates the insert/update/delete problems you describe.

    Redundant information shouldn't be confused with information that could be, or is, similar in multiple rows. Consider the following sample data:
    Code:
     registration # | color |  make |  model  | engine size
    ----------------+-------+-------+---------+-------------
     1              | red   | mazda | protege | 4 cylinder
     2              | blue  | mazda | protege | 4 cylinder
     3              | red   | mazda | protege | 4 cylinder
     4              | green | mazda | miata   | 6 cylinder
    The fact that there are 2 red mazda proteges does not mean that there is redundant/duplicate information in the table. Since they have different registration numbers, this indicates that these are two distinct cars that happen to have the same characteristics. If something changes about one car, that doesn't mean something changes about the other. For example, let's say we decide to paint car #3 blue, now we have:
    Code:
     registration # | color |  make |  model  | engine size
    ----------------+-------+-------+---------+-------------
     1              | red   | mazda | protege | 4 cylinder
     2              | blue  | mazda | protege | 4 cylinder
     3              | blue  | mazda | protege | 4 cylinder
     4              | green | mazda | miata   | 6 cylinder
    However, now let's say car #3 was entered incorrectly. It's actually a miata, not a protege. This results in:
    Code:
     registration # | color |  make |  model  | engine size
    ----------------+-------+-------+---------+-------------
     1              | red   | mazda | protege | 4 cylinder
     2              | blue  | mazda | protege | 4 cylinder
     3              | blue  | mazda | miata   | 4 cylinder
     4              | green | mazda | miata   | 6 cylinder
    But the miata isn't a 4-cylinder car, it's a 6-cylinder car. The relationship between the registration number and type of car has changed (car #3 is now a miata), but the relationship between the type of car and engine size has remained the same (proteges have 4-cylinder engines, miatas have 6-cylinder engines). Since the table isn't normalized, we have an update anomaly. In order to fix this, we would need to make a second update to correct the engine size:
    Code:
     registration # | color |  make |  model  | engine size
    ----------------+-------+-------+---------+-------------
     1              | red   | mazda | protege | 4 cylinder
     2              | blue  | mazda | protege | 4 cylinder
     3              | blue  | mazda | miata   | 6 cylinder
     4              | green | mazda | miata   | 6 cylinder
    See how this update is different from the previous updates? We did NOT update the engine size because we changed out the engine in the car, but because we changed other information in the row (specifically, the type of car). In a properly normalized database, we would not need to make this second update, because we know that all miatas have a 6-cylinder engine.

    Now consider that the miata actually has a 4-cylinder engine. In our current table, we would need to make this change in all rows with a miata. In a properly normalized database, we would only need to make this change once. Consider what is changing here: we are not updating information about people's car registration -- nobody's vehicles have actually changed -- instead we are updating information about the car type itself.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    Thanks for the very detailed reply futurity, really appreciate it.
    Thankyou

Tags for this Thread

Posting Permissions

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