Normalization

							                       Normalization / ER-Model
Screen 1
Normalization

       0NF

                customer(customer_number, password, onlne_registration_flag)

       1NF/2NF/3NF

                Already in these forms.



ER-Model

CUSTOMER



Screen 2
Normalization

       0NF

                customer(customer_number, first, last, last_login_date, date_of_birth,
                (account_number, account_type_code, account_type_description, bsb, balance,
                credit_limit, overdraft_limit))

       1NF

                customer(customer_number, first, last, last_login_date, date_of_birth)

                customer_account(customer_number, account_number, account_type_code,
                account_type_description, bsb, balance, credit_limit, overdraft_limit)

       2NF

                customer(customer_number, first, last, last_login_date, date_of_birth)

                customer_account(customer_number, account_number)

                account(account_number, account_type_code, account_type_description, bsb,
                balance, credit_limit, overdraft_limit)

                customer(customer_number nothing added, we already have all fields.

       3NF
                customer(customer_number, first, last, last_login_date, date_of_birth)

                customer_account(customer_number, account_number)

                account(account_number, account_type_code, bsb, balance, credit_limit,
                overdraft_limit)

                account_type(account_type_code, account_type_description)



ER-Model

CUSTOMER                                          ACCOUNT_TYPE

 |                                                      |

----   1…1                                             ----    1…1

 |                                                      |

 0     0…*                                              0      0…*

 ^                             1…*       1…1            ^

CUSTOMER_ACCOUNT               -- | ------ | --     ACCOUNT




Screen 3
Normalization

       0NF

                account(account_number, bsb, account_type_code, account_type_description,
balance, credit_limit, overdraft_limit, (transaction_date, transaction_type_code,
transaction_type_description, account_transaction_description, transaction_amount))

       1NF

                account(account_number, bsb, account_type_code, account_type_description,
balance, credit_limit, overdraft_limit)

               account_transaction(account_number, transaction_date, transaction_type_code,
transaction_type_description, account_transaction_description, transaction_amount)

       2NF

                account(as above)
               account_transaction(account_number, transaction_date, transaction_type_code,
transaction_type_description, account_transaction_description, transaction_amount)

       3NF

                account(account_number, account_type_code)

                account_type(account_type_code, account_type_description)

               account_transaction(account_number, transaction_date, transaction_type_code,
account_transaction_description, transaction_amount)

                transaction_type(transaction_type_code, transaction_type_description)



ER-Model

TRANSACTION_TYPE                                  ACCOUNT_TYPE

 |                                                      |

----   1…1                                             ----   1…1

 |                                                      |

 0     0…*                                              0     0…*

 ^                             0…*       1…1            ^

ACCOUNT_TRANSACTION            -- 0 ------ | --     ACCOUNT



Screen 4
Normalization

       0NF

               account(account_number, bsb, account_type_code, account_type_description,
(transaction_date, amount, transaction_type_code, transaction_type_description, receipt_number))

       1NF

                account(account_number, bsb, account_type_code, account_type_description)

               account_transaction(transaction_date, amount, transaction_type_code,
transaction_type_description, receipt_number)



       2NF
                As for 1NF

       3NF

                account(account_number, bsb, account_type_code)

                account_type(account_type_code, account_type_description)

              account_transaction(transaction_date, amount, transaction_type_code,
receipt_number)

                transaction_type(transaction_type_code, transaction_type_description)



ER-Model

TRANSACTION_TYPE                                  ACCOUNT_TYPE

 |                                                      |

----   1…1                                             ----   1…1

 |                                                      |

 0     0…*                                              0     0…*

 ^                             0…*       1…1            ^

ACCOUNT_TRANSACTION            -- 0 ------ | --     ACCOUNT



Screen 5
Normalization

       0NF

               account(account_number, bsb, account_type_code, account_type_description,
(transaction_date, biller_code, biller_organization, customer_reference, receipt_number, amount))

       1NF

                account(account_number, bsb, account_type_code, account_type_description)

                 account_transaction(account_number, transaction_date, biller_code,
biller_organization, customer_reference, receipt_number, amount)

       2NF

                Same as 1NF
         3NF

                account(account_number, account_type_code, bsb)

                account_type(account_type_code, account_type_description)

              account_transaction(account_number, transaction_date, biller_code, amount,
receipt_number, customer_reference, transaction_type_code)

                biller(biller_code, biller_orgranization)

                transaction_type(transaction_type_code, transaction_type_description)



ER-Model

TRANSACTION_TYPE

 |

----     1…1

 |

 0       0…*

 ^                               0…*       1…1

ACCOUNT_TRANSACTION              -- 0 ------ | --     ACCOUNT

 V

 0       0…*

 |

 0       0…1

 |

BILLER



Screen 6
Normalization

         0NF

                account(account_number, bsb, balance, account_type_code,
                account_type_description, credit_limit, overdraft_limit, interest_rate, date_created,
                (customer_number, first, last, street, suburb, postcode, date_of_birth,
                email_address, contact_number))

        1NF

                account(account_number, bsb, balance, account_type_code,
                account_type_description, credit_limit, overdraft_limit, interest_rate, date_created)

                customer_account(account_number, customer_number, first, last, street, suburb,
                postcode, date_of_birth, email_address, contact_number)

        2NF

                account(as for 2NF)

                customer_account(customer_number, account_number)

                customer(customer_number, first, last, street, suburb, postcode, state,
date_of_birth, email_address, contact_number)

                account(account_number)

        3NF

                 account(account_number, bsb, balance, account_type_code, credit_limit,
overdraft_limit, date_created)

                account_type(account_type_code, account_type_description, interest_rate)

                customer_account(customer_number, account_number)

               customer(customer_number, first, last, street, suburb, postcode, date_of_birth,
email_address, contact_number)



ER-Model

CUSTOMER                                           ACCOUNT_TYPE

 |                                                       |

----    1…1                                             ----    1…1

 |                                                       |

 0      0…*                                              0      0…*

 ^                              1…*       1…1            ^

CUSTOMER_ACCOUNT                -- | ------ | --     ACCOUNT
Cumulative ER-Model

TRANSACTION_TYPE                                     ACCOUNT_TYPE                 CUSTOMER

 |                                                          |                           |

----     1…1                                         1…1   ----                1…1     ----

 |                                                          |                           |

 0       0…*                                         0…*    0                  0…*      0

 ^                                0…*       1…1             ^     1…1    1…*            ^

ACCOUNT_TRANSACTION               -- 0 ------ | --     ACCOUNT -- | ------ | -- CUSTOMER_ACCOUNT

 V

 0       0…*

 |

 0       0…1

 |

BILLER



Cumulative Normalization

account(account_number, bsb, balance, account_type_code, credit_limit, overdraft_limit,
date_created)

account_type(account_type_code, account_type_description, interest_rate)

account_transaction(account_number, transaction_date, biller_code,
account_transaction_description , amount, receipt_number, customer_reference,
transaction_type_code)

customer_account(customer_number, account_number)

customer(customer_number, first, last, street, suburb, postcode, date_of_birth, email_address,
contact_number, password, onlne_registration_flag)

biller(biller_code, biller_orgranization)

transaction_type(transaction_type_code, transaction_type_description)

customer(customer_number, first, last, last_login_date, date_of_birth)