Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2014
    Posts
    3

    (Very simple) BCNF design trouble

    Hello, I am creating a simple database for class and I am having trouble with the normalization concept. I get the need to reduce redundancy but I'm not sure how.

    Story:
    A programming language has 1:n inventors and manufacturers. A programming language offers 1:n job types. Each job type has a level of difficulty from 0 (the easiest) to 10 (the most difficult). Each job type has a yearly and monthly average salary. The monthly average salary is the yearly average salary / 12.

    Data available:
    A few programming langauges
    Job types
    Invetors for the programming langauges (multivariabled)
    Manufacturers of the programming languages (multivariabled)
    salary examples(multivariabled)
    a preset lvl of difficulty for the job. (multivariabled)


    Concept I currently have:
    Table for Programming Language
    Table for Manufacturer
    Table for Inventors
    Table for Jobs

    The problem I dont seem to get is how to show that the programming languages have multiple inventors and multiple manufacturers without having redundant data values.
    Right now I have a autonumber as a primary key for the language and manufacturer, etc.
    Then another autonumber for the second one causing two lines with the same language shown and sometimes the same inventor or manufacturer.


    Any advice would be helpful. (I hope I explained clearly)

    *using sql server management studio 2012*

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What you need is an additional table to support the "many" kinds of joins. A programming_languages_inventors table can link a row from your programming_languages to one from your inventors. This allows a given programming language to have multiple inventors, and an inventor to contribute to many programming languages.

    As a side note, you could also keep the sequence or weight for each inventor in this relationship table to indicate what order the inventors should be listed. There is almost always a primary inventor and contributors for a programming language, and this can be a matter of considerable importance to them that the sequence is listed correctly.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2014
    Posts
    3
    So let me see if I have this correct.

    An extra table that would look something like this in design?

    (primary key)Programming_Language_InventorID
    (foreign key)Programming_LanguageID
    (foreign key)Inventor

    And this in row information?
    (ID representation used)

    1 | Java | James Gosling
    2 | Java | Sun Microsystems

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Yes, you have grasped the basic idea.

    You could dispense with the programming_language_inventorID unless it buys you something that isn't readily apparent. I would personally keep it since my application infrastructure demands a column like that for other reasons, but it isn't needed from a relational perspective.

    I would also add a sequence or date column to allow the order of the inventors to be stored as part of the table that shows the relationship between the programming_language and the inventor. Because the table actually tracks the relationships between the programming_languages and the inventors, that table is the appropriate place to track related information like the sequence too.

    From a RA (Relational Algebra) perspective, the NK (Natural Key) is the combination of the programming_languages and inventors keys. I always include an SK (Surrogate Key), like your Programming_Languages_Inventors_ID because other parts of my infrastructure depend on it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2014
    Posts
    3
    Thank you Mr. Phelan, you have been more helpful than you would think.



    // Glad I found this site, I think I'll stick around.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm glad that I was able to help!

    For issues like this you might want to "poke the bear" and get another DBForums user named Celko involved. He's the (in)famous Joe Celko, who can tell you more horror stories about databases (of all kinds, not just relational) than anyone else I know on this planet! I'm struggling to knock off the worst of his rough edges and make Joe more palatable to database newcomers, but that is definitely a work in progress.

    In terms of being able to tell you the "right" way to do things, he certainly knows his stuff... Joe is sometimes rather caustic in his delivery, but his content is the best I've ever seen.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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