Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2012
    Posts
    11

    Unanswered: 2nd and 3rd Normal form??

    Hi, I hope someone can help me finish my school project? there is a one-to-many relationship between MortgageBank and the following tables, Address, LoanInformation, and Employer Tables. There is also one-to-one relationship between MortgageBank and CreditHistory tables. My problem is with the MortgageBank table. If I create a composite key with MortgageBankID and SocialSecurity, will that solve my problem??


    Thanks
    Attached Thumbnails Attached Thumbnails BankY.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is a mortgagebank? the attributes make it look like a person

    and can a mortgagebank really have multiple employers?

    also, what is your problem? you never said

    further, where do 2nd and 3rd normal forms come into your question?

    finally, i want to give you a suggestion to facilitate data modelling -- start ~without~ using artifical "id" primary keys, find natural keys for your entities

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Rudy: Careful, they, the new world client server architects, may call you a heretic

    When's the assignment due?

    And why don't you ask the teacher's assistant?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    There are many, many problems with what you have given us, and for us to get you back on-track, we'd basically be doing the assignment for you.

    Rudy pointed-out a few issues--and I see more.

    So, as Brett suggested, talk to your teacher/assistant first.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jan 2012
    Posts
    11
    Thanks guys, you have been very helpful. With your help and my teachers hints I have the following data model. This project is due in a week.
    Attached Thumbnails Attached Thumbnails BankZ.jpg  

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK,let's start simply

    You should have a PERSON table

    Only thos thing specific to a PERSON should be in it

    Then you should have a Customer table

    Only those things specific to a person being a customer should be in it

    Thing like a loan or balance should be in a separate table because they relate to an account, that a person or persons may own or have some relationship to

    What school do yo go to

    And if you said you talked to the teacher or assistant, you are a) Lying or b) they are incompetent
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He has a customers table, equivalent to a persons table.
    Though I'll agree it does not appear well normalized.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Hold on a second Brett . . .

    I see enormous progress from the first diagram to this second pass, so let's try and encourage him.

    The Fed will not likely authorize his bank, based upon this database diagram, but for a school assignment, it's getting there.

    As Rudy recommended earlier, I would not start out every table with an ID field, which I presume is going to be some machine-generated number (in SQL we call it an "identity"). Look for some naturally unique value to make your primary key; like in the Account table, you have an AccountNumber field that is surely unique--there's no need to create a second unique field on the table. In the Customer table you might want to add some government identifier number (like a Social Security Number), as most banks require a number like that to be one of their customers and take out a loan, or open an account. And that could be the natural primary key of the Customer table

    Your arrows are a bit screwed-up. For instance, the arrow between the Transaction table and the Account table should be reversed--"each" Account can have "many" transactions and the arrow is always anchored on the "each" side and the arrowhead points to the "many" side.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser View Post
    And if you said you talked to the teacher or assistant, you are a) Lying or b) they are incompetent
    this is, again, needlessly confrontational, and in this case flat out rude

    maybe you should up your meds a bit, brett
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by PracticalProgram View Post
    As Rudy recommended earlier, I would not start out every table with an ID field, which I presume is going to be some machine-generated number (in SQL we call it an "identity").
    Disagree. I include a surrogate key with all my tables (as Rudy knows already), and I'm not the only DBA who does this. So, if the poster wants those columns in there then there do exist valid reasons for retaining them.
    Note, surrogate keys are not a substitute for having a defined and enforced natural key as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    so what's c). then?

    And what happened to we don't answer homework questions?

    In any case i seemed like this is a data modeling question

    Which I thought that oppie might want to understand normalization

    I apologize for being rude...I will keep it in check

    pot 1 --- M Kettle
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman View Post
    He has a customers table, equivalent to a persons table.

    Ahhhh...no

    Can a person be a customer and an employee?

    Phones should be in their own table....

    As well as addresses

    What's the assignment here?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by blindman View Post
    Disagree. I include a surrogate key with all my tables (as Rudy knows already), and I'm not the only DBA who does this. So, if the poster wants those columns in there then there do exist valid reasons for retaining them.
    Note, surrogate keys are not a substitute for having a defined and enforced natural key as well.
    I believe there is general disagreement in this area, and I can live with that.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Note, surrogate keys are not a substitute for having a defined and enforced natural key as well.
    indeedy-deedy-do

    which why in the logical modelling stage -- which is where he's at, doing an ER diagram -- it is important to do the natural keys

    later, in the physical modelling stage -- when you do the schema/tables diagram -- please do feel free to add surrogate keys

    we don't disagree, blindman, i just don't think it's wise -- especially for people who are new to logical modelling -- to jump immediately to surrogate keys, because that increases the likelihood that the natural keys will be forgotten

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

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh Good Rudy, just say it

    I've heard Waaaaaaaaaaaaayyyy too many people..IN THE B'dness..tell me they want a Unique row..THAT'S why they add an IDENTITY Column

    And the really SMART ones say they need to use a GUID

    For the love of God and in all the name of things that are holy

    STOP THE MADNESS

    What if OHIO Changed it's state abbreviation..holy cow, we can use a stateId

    The entire real problem is, that it's a new thing and you don't want to lose what was

    For example, just because California is going out of Business, there was still a time that it was called California (CA)

    OK..Need to pass out and watch Craig
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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