Page 1 of 5 123 ... LastLast
Results 1 to 15 of 72
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Clairification on 3NF

    Can someone please tell me if I have a transitive dependancy here. I would like to have one table with mfgr, model number and serial number as non-key fields with a surrogate key.

    I think that I possibly have a transitive dependancy between mfgr and serial number and most certainly between mfgr and model number but I am not positive.


    Thanks.

    Frank

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pretty hard to tell just from the names, but yeah, i would say you're right

    try this: given a specific value of the PK, this determines the values of all non-key attributes, right?

    so for a specific value of the PK, if i were to change the value in one of the non-key attributes, would the data still be the same in the other non-key attributes? if i changed the mfgr, would the model number still be valid?

    by comparison, consider a table for employees, with empno as the PK, and let's take a specific value of the PK, the one corresponding to you

    if i changed the phone number for you, would that require a change to your address? a change to your hatsize? no, because all these non-key attributes are independently dependent on the value of the PK, namely you
    Last edited by r937; 08-25-07 at 05:16.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    pretty hard to tell just from the names, but yeah, i would say you're right

    try this: given a specific value of the PK, this determines the values of all non-key attributes, right?

    so for a specific value of the PK, if i were to change the value in one of the non-key attributes, would the data still be the same in the other non-key attributes? if i changed the mfgr, would the model number still be valid?
    Hi Rudy. Thanks for the reply.

    The PK will be an auto-incrementing surrogate. So if a value was changed in one of the non-key fields, it would affect the the other fields as well. Unless I am misunderstanding your question. Here would be some sample data.

    Code:
    mfgr        model    serial     PK
    motorola   X563    12dsd     1
    motorola   r2dd     jo445     2
    I have a pic I am going to post. brb.
    Last edited by Frunkie; 08-25-07 at 05:32.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's use an example

    for PK=1, if i changed the serial number to XP428-834, it is still model=X563?

    answer: prolly not

    it looks like model is dependent on serial, and mfgr on model

    i.e. it's not 3NF
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    That is what I figured. It is not in 3NF. Ok.. So here is a jpg of the changes I have made so far..

    We are talking about the company_Equipment tables. I have taken serial_Number out and I am debating whether to put mfgr in its own table.
    Attached Thumbnails Attached Thumbnails 11.jpg  

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so a piece of company equipment cannot be entered into your database unless it is already loaned out to a specific employee? what happens when he returns it? you won't allow company_equipment.employee_number_seq to be null, so what do you put there if the piece of equipment is currently not loaned out?

    and what's the purpose of naming your columns like company_equipment_status? what's wrong with just status? you particularly enjoy typing extra needless characters?

    and how come a single piece of company equipment can have multiple serial numbers?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    so a piece of company equipment cannot be entered into your database unless it is already loaned out to a specific employee? what happens when he returns it? you won't allow company_equipment.employee_number_seq to be null, so what do you put there if the piece of equipment is currently not loaned out?

    and what's the purpose of naming your columns like company_equipment_status? what's wrong with just status? you particularly enjoy typing extra needless characters?

    and how come a single piece of company equipment can have multiple serial numbers?
    That is correct. The only time that a pice of equipment would be entered into the database is if it has been loaned out.

    When he returns it, the date is entered into the date_In field. I know that these tables have some issues. I am trying to work them out.. I am all ears if you have a better way to model this.

    LOL.. I hate typing! Ok, here is where being a nubie comes into play. I type the prefixes such as customer_Status because when I am joining the tables back together and I am viewing the results, the tables that have the same names such as "status" are null. I am thinking that may be because the names are the same. Can I use an alias to fix that?

    No, I don't see that a single piece of equipment can have multiple serial numbers. Actually, your correct.. What do you suggest. I am really out of answers.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so if a piece of equipment has been loaned out, and then comes back in, it's not still loaned out, is it? so what do you do if it has to be loaned out again?

    using column aliases in your queries for specific reporting purposes sounds like a much more sensible approach than bastardizing the column names in the table

    as for the serial number, i don't know your equipment but i would guess that the serial number is actually a candidate key for the equipment

    can two pieces of equipment have the same serial number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    so if a piece of equipment has been loaned out, and then comes back in, it's not still loaned out, is it? so what do you do if it has to be loaned out again?

    using column aliases in your queries for specific reporting purposes sounds like a much more sensible approach than bastardizing the column names in the table

    as for the serial number, i don't know your equipment but i would guess that the serial number is actually a candidate key for the equipment

    can two pieces of equipment have the same serial number?
    Well, if the piece of equipment comes back, it would be reflected in the date_In field then if it were loaned back out, the equipment would be re-entered into the db under a different person. Is there a better way to do that? Ywo pieces of equipment would not have the same serial number.

    Ok.. I hate bastardizing anything.. I have to go back through the database and rename all of those columns.

    The serial number is the candidate key but the reason I gave it a surrogate key was because the user may not know what the serial number is. Does that make sense?

    EDIT: Nevermind. I understand what you are saying about the serial number.. Ok, I need to dump the surrogate..

    I have a question regarding the company_Vehicle tables. The vehicle can be loaned out during a shift and I need to tie that table to the employee. Vehicles are equipment as well so shouldn't that be somehow tied to the equipment table?
    Last edited by Frunkie; 08-25-07 at 06:20.

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    What I did to circumvent a single piece of equipment from having a second serial number attached to it was to drop the serial number table and add serial_Number to the equipment table as its PK.

    I am seeing that now, with this change, it will be impossible to add a piece of equipment that *does not* have a serial number. What would be the best way around this?

    Thanks,

    Frank

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you dump the surrogate and make serial number the PK, then you can never have a piece of equipment without a serial number

    you could give the serial number a unique constraint

    but then you'd only ever be able to loan it out once


    ain't data modelling fun?

    do you see now why so many programmers just throw their hands up and forget about primary keys and foreign keys?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What on earth leads you to suspect that device serial numbers are unique? IP Addresses are supposed to be unique, and they are unique on any given VLAN, but do you have any concept of how many devices have the address of 192.168.1.100 in the world? VIN numbers are supposed to be unique too, but I happen to own a vehicle that shares its VIN with at least one other vehicle registered in the United States (this is ugly, do NOT try it at home).

    The only "unique" that I will ever depend on again is when I can define something to be unique, such as a GUID or (under duress) a sequence number with a unique constraint.

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    What on earth leads you to suspect that device serial numbers are unique?
    who, me??

    " ... on earth?" sure, your point may have merit

    but actually, within a given app, where we are dealing with pieces of equipment owned by a given company, it's not unreasonable to have unique serial numbers

    in fact, i've worked for more than one company where they inventoried every single piece of equipment in the company with "asset tags" which had unique serial numbers (once as part of a Y2K initiative)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    such as a GUID or (under duress) a sequence number with a unique constraint.
    In SQL Server the GUID is only guaranteed unique if generated by two machines on the same network. I hope you don't have any rogue servers knocking around on different netwroks on remote sites

    I would agree with Rudy that you need to measure this against your domain. I have (in my much more limited experience naturally) experienced far more problems from absent unique constraints than present ones.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Statistically speaking there are 2 other people who share the same DNA as you!
    George
    Home | Blog

Posting Permissions

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