Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Help to normalliase to 3NF

    Hi All

    I am quite new to database design please go easy with me. I am looking for some advice as the best way to structure some data to 3NF.

    I am correct trying to develop a project for my course at Uni. The project is based around a smartcard epurse. I am creating an application that when a user purchases a product though a merchants web site after the transaction is authorized the account is debited by the bank which also remotely debit the balance on javaccard of the client.

    I want to design a database that can be used to store data using sun one 5 enterprise java beans with a pointbase database server. As I am using Sunone IDE that actual implemenation isn't that bad due EJB wizards integrated into the IDE.

    I would just like to get the opinions of someone who has a bit my experience than myself which shouldn't be too hard due to the fact I am new to databases.

    Here is the table and fields I have selected so, any suggestions would really be welcomed. The main problem I have is how to make sure that do not replicate the data unnecessary.






    CardHolder
    unq_key
    first_name
    surname
    post_code
    city
    county
    telephone


    Account
    unq_id
    accountNo
    sort_code
    first_name
    surname
    card_issued
    date_created
    overdraft_amount
    balance
    card_blocked
    card_unblocked



    Card
    card_id
    certicate
    cardholder_firstname
    cardholder_surname
    accountNo
    date_expired
    date_valid

    Statement
    statement_id
    accountNo
    date_purchased
    amount
    location
    current_balance

    Merchants
    mer_id
    accountNo
    merchant_name
    certifcate
    terminal_id

    Transaction
    transaction_id
    card_id
    Card_accountNo
    merchants_accountNo
    merchants_id
    Terminal_id
    cardholder_name
    authorisation_code
    delcined




    Any advice especially with normalising the data would be very much apreaciated.

    Sean

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    First look at transaction. You have alot of redundant data in there.

    If you know the cardID and the MerchantID you could find much of the information in the Card and Member tables.

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710

    Exclamation

    Let's backtrack completely. First you have to identify the entities and the relationships.

    Take the names of the entities and go:

    Relationship One:
    A cardholder has (zero, one, many) Accounts
    An Account has (zero, one, many) cardholders

    Go through all the entities and identify all the relationships and the cardinality as above.

    Post all the relationships here and I will tell you the next step. There will be three steps.
    Last edited by certus; 01-09-04 at 00:30.

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    Originally posted by certus
    Let's backtrack completely. First you have to identify the entities and the relationships.

    Take the names of the entities and go:

    Relationship One:
    A cardholder has (zero, one, many) Accounts
    An Account has (zero, one, many) cardholders

    Go through all the entities and identify all the relationships and the cardinality as above.

    Post all the relationships here and I will tell you the next step. There will be three steps.
    Ok let me take the time to thank you certus for taking the time to offer assitance it very much appreactied so thanks a lot.

    here what i have come up, so far.

    see attachment for first go at ER Diagram. I did try and make CardHolder primary key join transaction table but the tool Case studio 2.1 wouldn't let me so i presume it wasn't valid.


    Here are the table now

    CardHolder[
    Account
    Card
    Statement
    Merchants
    Transaction
    card_id : PK
    accountNo: FK
    first_name
    surname
    post_code
    city
    county
    telephone
    }

    Account{
    Card_id :PK
    accountNo: FK
    sort_code
    first_name
    surname
    card_issued
    date_created
    overdraft_amount
    balance
    card_blocked
    card_unblocked
    }


    Card {
    card_id: FK
    accountNo: FK
    certicate
    firstname
    surname
    date_expired
    date_valid
    status

    }


    Starement{
    transaction_id: PK
    accountNo :FK
    statement_id
    date_purchased
    amount
    location
    current_balance
    }


    Merchants{
    merchants_id: PK
    accountNo
    merchant_name
    certificate
    terminal_id

    }

    Transaction{
    accountNo: FK
    card_id: FK
    merchants_id: FK
    Transaction_id
    Terminal_id
    authorisation_code
    delcined
    amount
    date
    time
    date_valid
    expires

    }

    And the relation are as follows

    Relationship One:
    A cardholder has (one, many) Accounts
    A Accounts has (Manu, one) cardholder {Not sure whether should be 1:1}



    Relationship Two:
    Card has (many , one) Cardholder
    Cardholder has (one many) Card

    Relationship Three:
    Account has a (one many) Statement
    Statement has a (one many) Account

    Relationship Four:
    Statement has a (one many) Transaction
    Transaction has a (many one) Statement


    Relationship Five:
    Cardholder has a (one many) Transaction
    Transaction has a (many one) Cardholder {I couldn't see to make it work}


    Transaction Six
    Transaction has a (Many one) Merchants
    Merchants has a (one Many) Transaction


    Please can make use reply as easy to understand as possible.

    Many Thanks to all

    Sean
    Attached Thumbnails Attached Thumbnails export.jpg  

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Alright, you have a diagram, good.

    First, you have to get your relationships between the entities right.
    I'm also going to simplify the language a bit to focus on the relationships and not the detailed cardinality.

    Relationship One:
    A cardholder has many Accounts
    An Account has many cardholders
    - sounds like a (M:N) likely one cardholder to many accounts.

    Relationship Two:
    Card has one Cardholder
    Cardholder has many Cards
    - one cardholder to many cards

    Relationship Three:
    Account has many Statements
    Statement has one Account
    - one account to many statements

    Relationship Four:
    Statement has many Transactions
    Transaction has one Statement
    - one statement to many transactions

    Relationship Five:
    Cardholder has many Transactions
    Transaction has one Cardholder
    - one cardholder to many transactions

    Relationship Six:
    Transaction has one Merchant
    Merchants has many Transactions
    - one merchant to many transactions

    Your are attempting to do too much at once with your diagram. First just draw the entity boxes without any keys or attributes inside and connect them with the relationships as are listed above.
    Post that diagram. It will be your entity relationship diagram.

  6. #6
    Join Date
    Jan 2004
    Posts
    30
    Originally posted by certus
    Alright, you have a diagram, good.

    First, you have to get your relationships between the entities right.
    I'm also going to simplify the language a bit to focus on the relationships and not the detailed cardinality.

    Relationship One:
    A cardholder has many Accounts
    An Account has many cardholders
    - sounds like a (M:N) likely one cardholder to many accounts.

    Relationship Two:
    Card has one Cardholder
    Cardholder has many Cards
    - one cardholder to many cards

    Relationship Three:
    Account has many Statements
    Statement has one Account
    - one account to many statements

    Relationship Four:
    Statement has many Transactions
    Transaction has one Statement
    - one statement to many transactions

    Relationship Five:
    Cardholder has many Transactions
    Transaction has one Cardholder
    - one cardholder to many transactions

    Relationship Six:
    Transaction has one Merchant
    Merchants has many Transactions
    - one merchant to many transactions

    Your are attempting to do too much at once with your diagram. First just draw the entity boxes without any keys or attributes inside and connect them with the relationships as are listed above.
    Post that diagram. It will be your entity relationship diagram.
    Ok here we go, i hope this is ok.
    See attachment.

    Thanks again for your help,

    Sean
    Attached Thumbnails Attached Thumbnails er-dia.jpg  

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Great. Now put in the primary key attributes for each table followed by the foreign key attributes.

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Make sure you know what is your natural key for each table first. This will be important in preventing duplicate records.

  9. #9
    Join Date
    Jan 2004
    Posts
    30
    Originally posted by certus
    Make sure you know what is your natural key for each table first. This will be important in preventing duplicate records.
    I have just had ago this, only thing as i am Case studio it gets putting 2 instances of postcode in Transaction.

    Blue = FK
    Red = PK


    What do ya think, i am hoping that this is nearly there.



    Thanks
    Sean
    Attached Thumbnails Attached Thumbnails export3.jpg  

  10. #10
    Join Date
    Jan 2004
    Posts
    30
    [QUOTE]Originally posted by Sean_W
    I have just had ago this, only thing as i am Case studio it gets putting 2 instances of postcode in Transaction.

    Blue = FK
    Red = PK


    What do ya think, i am hoping that this is nearly there.



    Thanks
    Sean
    [/QUOTE


    Hmmm i, not sure about postcode for a PK for Cardholder what do you reckon ?

    Sean

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Post code does seem sort of strange. Determine what else can be used as a natural primary key. Possibly FirstName, LastName and Postal Code. Even that is iffy. That would be your natural key and you could use a surrogate key of CardHolderID.

    You are defining your foreign keys as Primary Foreign Keys (PFK). It is necessary in Account to have the CardHolder as a primary foreign key? Is is necessary for Card to have CardHolder as a primary foreign key? Why not just foreign keys?

    That would clear up the collision in Transaction of post_code.

    Also, what makes a Statement unique? Account and Location? What makes a Transaction unique? Statement, Date, Time and Cardholder?

    Get your natural keys and follow with surrogate keys and the rest is gravy.
    Last edited by certus; 01-09-04 at 17:28.

  12. #12
    Join Date
    Jan 2004
    Posts
    30
    Right here we go,Im not sure whether the tool is making harder than it needs be. Transaction seem to be overloaded with PFK, not sure whether this would be effective.







    Thanks
    Sean
    Attached Thumbnails Attached Thumbnails export4.jpg  

  13. #13
    Join Date
    Jan 2004
    Posts
    30
    damm just noticed cardholder_id is duplicated in transaction.

    back to the drawing board



    Sean

  14. #14
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Sean,

    Figure out how to turn your PFKs into FKs in Account, Statement and Card. FKs do not carry over into the next relationship like PFKs.

    When you get that worked out you can add your non-key attributes to your entities. And we will take a final look at it. You are just trying to achieve a normalized model, right?

    Wish we could sit down at a table with the diagram an a couple of pens.

  15. #15
    Join Date
    Jan 2004
    Posts
    30
    Same here ,my goal is just a efficient database design without data redundancy, ideally 3NF would be great.

    Let me try that out again


    Cheers

    Sean

Posting Permissions

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