Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Bcnf 2

  1. #1
    Join Date
    Feb 2004
    Posts
    78

    Bcnf 2

    here is another example that i have tried to normalise to BCNF- does n e 1 know if this is correct.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Bcnf 2

    What do the colours mean in this diagram? Something looks "fishy" to me, but as I'm not sure what the primary keys are I may be incorrect. If red indicates a primary key, then you have 2 relations with the same primary key and Client No appears as a non-key attribute 2 relations with different keys!

    BTW, it would help if you named each relation so that we could refer to them by name. R1, R2, R3 would do.

  3. #3
    Join Date
    Feb 2004
    Posts
    78
    red means primary key, blue means foriegn key and pink means both primary and foriegn key. i have changed it a litle is this better?
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Looks the same as before to me.

  5. #5
    Join Date
    Feb 2004
    Posts
    78
    sorry i forgot to save -it here it is
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I'm not clear about the relationship between Client and Office: can many Clients have same Office? (Yes, according to your tables).

    Can the same Contract No be used by many Clients? (again, yes according to your tables).

    Trouble is, I don't know what the real FDs are anyway!

  7. #7
    Join Date
    Feb 2004
    Posts
    78
    clients contracts are based at a office. so many clients can be based at the same office. and only one client can have 1 contact number but many job id's . where do i go from here then?

  8. #8
    Join Date
    Feb 2004
    Posts
    78
    IGNORE THE ABOVE

    clients contracts are based at a office. so many clients can be based at the same office. and only one client can have many contract numbers and many job id's . where do you think i should go from here then?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Your relationships between Client and Office may be correct then, but your model allows 2 clients to have the same Contract No.

    My suspicion would be that if you know the Contract No, you know the Client too. i.e. Contract->Client. Your model doesn't say that.

  10. #10
    Join Date
    Feb 2004
    Posts
    78
    does that mean i need another table with just contractno and clientno in it?

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Why not just add ClientNo to R1 as a foreign key?

  12. #12
    Join Date
    Feb 2004
    Posts
    78
    is this correct then for 3NF and BCNF?
    Attached Files Attached Files

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I don't know. What does R3 mean now? Each Office can have only 1 contract?

    The trouble is (I think) you are reacting to each feedback comment from me in a piecemeal way, reposting your modified solution here within seconds almost. You need to slow down and review your full set of relations against the original relations and FDs, and verify them.

    A primary key means: every other attribute in this relation is functionally dependent on this key. So it is easy to compare FDs and relations to see if they agree:

    Relation (key, otheratt1, otheratt2) means:

    key -> otheratt1, otherat2

  14. #14
    Join Date
    Feb 2004
    Posts
    78
    r3 is needed to show which clients are based at which office. i have now changed that to this. i have also changed relation 2. i got a little confused there as i though this was the offices details but it's not it should be the clients details. it looks alot better now and i think it's in 3NF and BCNF. please can you check this for me. p.s i am very greatful for your help- i do feel i am learning
    Attached Files Attached Files

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    R3 isn't quite right: it shows OfficeNo as the PK, but has 2 rows with same value! Did you mean both columns to be in the PK?

Posting Permissions

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