+ Reply to Thread
Results 1 to 6 of 6

What exactly is a Secondary key?

  1. What exactly is a Secondary key?

    greetings


    I must first point out that Iím not sure if the terminology used in my
    questions is correct and just to make sure that my second question
    will still be understood, regardless of my terminology being
    potentially wrong, I will first explain what I actually meant when
    using certain terms ( like secondary and supplementary keys ).


    1) According to my non-English book, a secondary key doesnít uniquely
    identify an object and as such it has one-to-many relationships with
    primary key ( if it has one-to-one relationship, then we call it
    supplementary key )

    Is the terminology correct?



    2) Say we have an entity class called ITEM:

    ITEM ( ITEM#, NAME, TYPE-OF-WOOD, PRICE )


    a) Now my book claims that TYPE-OF-WOOD is secondary key and as such
    it has one-to-many relationship with a primary key ITEM#, since it can
    be connected to more than one instance of ITEM# attribute.

    But if that is the case, then why couldnít NAME attribute or PRICE
    attribute also be considered secondary keys, since more than one
    instance of ITEM# attribute could have the same NAME or PRICE
    values?



    b) Since NAME and PRICE arenít considered secondary keys, what
    properties do make an attribute a secondary key ?



    thank you

  2. Re: What exactly is a Secondary key?

    A key uniquely identifies a row. Being primary or secondary (or whatever
    else you want to call it), it doesn't matter.

    When data modeling you may have one or more candidate keys, and each
    will uniquely identify a row (this is why they are called keys). From
    those candidate keys you decide on a primary key and the other keys are
    alternate keys (and you use unique constraints for those).

    In your example seems TYPE-OF-WOOD could be a foreign key, if it
    references the key of another table that stores types of wood. But
    foreign key is a different term and should not be described as
    "secondary" key.

    --
    Plamen Ratchev
    http://www.SQLStudio.com

  3. Re: What exactly is a Secondary key?

    As Plamen points out, a key uniquely identifies a row. What they're
    referring to in your book as a "secondary key" looks like a "foreign key" if
    you were to normalize the tables like Plamen recommends:

    ITEM Table
    ============
    Item_Number
    Name
    Type_Of_Wood_ID
    Price

    TYPE_OF_WOOD Table
    ===================
    Type_Of_Wood_ID
    Description

    In this case the Type_Of_Wood_ID relates TYPE_OF_WOOD to ITEM in a 1:0..Many
    relationship. That's what it sounds like they're describing, although the
    description doesn't exactly ring 100% true.

    Normally you'll run into these types of keys: primary keys, candidate (or
    "alternate") keys, foreign keys. Your keys can also be divided into two
    main groups: natural (or "business") keys and surrogate keys.

    --

    ========
    Michael Coles
    "Pro T-SQL 2008 Programmer's Guide"
    http://www.amazon.com/T-SQL-2008-Pro.../dp/143021001X


    wrote in message
    news:f87556da-c13e-468d-99cf-4b91411a5cfc@34g2000hsh.googlegroups.com...
    greetings


    I must first point out that Iím not sure if the terminology used in my
    questions is correct and just to make sure that my second question
    will still be understood, regardless of my terminology being
    potentially wrong, I will first explain what I actually meant when
    using certain terms ( like secondary and supplementary keys ).


    1) According to my non-English book, a secondary key doesnít uniquely
    identify an object and as such it has one-to-many relationships with
    primary key ( if it has one-to-one relationship, then we call it
    supplementary key )

    Is the terminology correct?



    2) Say we have an entity class called ITEM:

    ITEM ( ITEM#, NAME, TYPE-OF-WOOD, PRICE )


    a) Now my book claims that TYPE-OF-WOOD is secondary key and as such
    it has one-to-many relationship with a primary key ITEM#, since it can
    be connected to more than one instance of ITEM# attribute.

    But if that is the case, then why couldnít NAME attribute or PRICE
    attribute also be considered secondary keys, since more than one
    instance of ITEM# attribute could have the same NAME or PRICE
    values?



    b) Since NAME and PRICE arenít considered secondary keys, what
    properties do make an attribute a secondary key ?



    thank you



  4. Re: What exactly is a Secondary key?

    greetings

    I must point out that when the author was describing secondary keys,
    he hadnít had any particular database model in mind.


    > What they're referring to in your book as a "secondary key" looks like a
    > "foreign key"


    1) I understand what the foreign key is, but I donít think the author
    had foreign keys in mind when describing secondary keys. In lay manís
    terms, one could argue, that foreign key is a visitor, its home being
    another table. While thatís not what the author was implying by
    secondary key, since he did also mention that if secondary key
    uniquely identified a primary key, then it would also become a
    candidate for primary key.

    2) Anyways, unless we are talking about foreign keys, I assume there
    doesnít exist any other term ( with the word ďkeyĒ in it ) for
    attributes that donít have unique values?

    thank you for helping me out


  5. Re: What exactly is a Secondary key?

    A key in the relational model is an attribute or set of attributes that
    uniquely identifes a tuple. A "secondary key", as the author described it
    has no meaning in the relational model. I don't see any useful meaning for
    it (based on the definition provided) in other models I'm familiar with,
    like OO databases, XML databases, etc. Based on the author's definition,
    every attribute or set of attributes that's not a PK or a candidate key is
    automatically a "secondary key".

    Attributes, or sets of attributes, that don't uniquely identify tuples in
    the relational model are simply attributes.

    --

    ========
    Michael Coles
    "Pro T-SQL 2008 Programmer's Guide"
    http://www.amazon.com/T-SQL-2008-Pro.../dp/143021001X


    wrote in message
    news:e8cf8a57-9385-48fc-b712-f9c4189f2048@w39g2000prb.googlegroups.com...
    greetings

    I must point out that when the author was describing secondary keys,
    he hadnít had any particular database model in mind.


    > What they're referring to in your book as a "secondary key" looks like a
    > "foreign key"


    1) I understand what the foreign key is, but I donít think the author
    had foreign keys in mind when describing secondary keys. In lay manís
    terms, one could argue, that foreign key is a visitor, its home being
    another table. While thatís not what the author was implying by
    secondary key, since he did also mention that if secondary key
    uniquely identified a primary key, then it would also become a
    candidate for primary key.

    2) Anyways, unless we are talking about foreign keys, I assume there
    doesnít exist any other term ( with the word ďkeyĒ in it ) for
    attributes that donít have unique values?

    thank you for helping me out



  6. Re: What exactly is a Secondary key?

    >> I must first point out that Iím not sure if the terminology used in
    my questions is correct and just to make sure that my second question
    will still be understood, regardless of my terminology being potentially
    wrong, I will first explain what I actually meant when using certain
    terms ( like secondary and supplementary keys ).<<

    Have you seen the Monty Python sketch about the malicious English phrase
    book?

    >> 1) According to my non-English book, a secondary key doesnít uniquely

    identify an object and as such it has one-to-many relationships with
    primary key (if it has one-to-one relationship, then we call
    itsupplementary key)

    Is the terminology correct? <<

    NO! NO! NO! Dr. Codd first defined a PRIMARY KEY on a table as one of
    the CANDIDATE KEYS that we decide to "make special" for whatever reason
    (actually, it was because sequential file systems had to be sorted and
    needed one key for merging tapes). He realized his mistake and said
    that all keys are keys because the uniquely identify a row is a table.

    2) Say we have an entity class called ITEM:

    ITEM ( ITEM#, NAME, TYPE-OF-WOOD, PRICE ); <<

    Even for a skeleton table, this stinks-- learn the ISO-11179 rules for
    data element names. Declare a key. And never use special characters
    like # in a name!

    >> a) Now my book claims that TYPE-OF-WOOD is secondary key and as such

    it has one-to-many relationship with a primary key ITEM#, since it can
    be connected to more than one instance of ITEM# attribute. <<

    It should have been "wood_type" and types are not keys be definition;
    they are attributes. This is total crap!!!

    >> b) Since NAME and PRICE arenít considered secondary keys, what

    properties do make an attribute a secondary key? <<

    Magic. There is no such thing as a secondary key in RDBMS.


    --CELKO--
    Please post DDL in a human-readable format and not a machine-generated
    one. This way people do not have to guess what the keys, constraints,
    DRI, datatypes, etc. in your schema are. Sample data is also a good
    idea, along with clear specifications.


    *** Sent via Developersdex http://www.developersdex.com ***

+ Reply to Thread