Search code examples
databasedatabase-normalization

Normalisation from 1NF to 3NF

If I have the following table which is currently in 1NF:

enter image description here

I am required to decompose this table into 3NF.

I would then have (correct me if I am wrong):

Customer(Customer ID-PK, Customer Name)
Branch(Branch ID-PK, Branch Name, BSB)
Account(Account Type-PK, Account Name)

What would I have for Account Details? I'm thinking something like:

Account Details (Account Number, Customer ID, Branch ID, Account Type, Account Balance)

I'm stuck on what to make the primary key of this main table as there are multiple customers on a single account number.

Would I be needing to use a composite primary key with Account Number and Branch ID?

What other/better options do I have here?

Solution

  • I'm assuming that the organization being modelled is a bank.

    Given the sample data, it appears that the Branch ID, Branch Name and BSB are all associated with a specific branch, and each of the columns is a candidate key (could be 'the' primary key).

    One of the 3NF tables should be:

    Branch:  Branch ID, Branch Name, BSB — Branch ID nominated as PK

    Only two bits of data are recorded for each customer: the customer name and their customer ID. Since names can repeat, we'll assume the the customer ID is the candidate key.

    One of the 3NF tables should be:

    Customer: Customer ID, Customer Name — Customer ID nominated as PK

    Given that the first two data rows and the last all identify a/c 9047 1234 but the account type information is different, the a/c number is not unique across all branches in the bank. Based on the data shown, the combination of account number and Branch ID should be unique (and hence the PK). However, a given account can be shared by more than one customer – witness the first two rows of data.

    Consequently, one of the 3NF tables should be:

    Account: Account Number, Branch ID, Account Type, Account Balance, Account Type, Account Name
— PK(Account Number, Branch ID)

    And another should track the customer(s) associated with a given account number. Again, the table shown is 'all key', with the Account Number and Branch ID forming one foreign key and the Customer ID being another.

    Account_Customers: Customer ID, Account Number, Branch ID

    So, I think you need 4 tables. The primary key columns are marked with an asterisk.

    • Branch: Branch ID*, Branch Name, BSB
    • Customer: Customer ID*, Customer Name
    • Account: Account Number*, Branch ID*, Account Type, Account Balance, Account Type, Account Name
    • Account_Customers: Customer ID*, Account Number*, Branch ID*