-1

I'm learning MySQL with XAMPP and am trying a practice scenario. I want to achieve 3NF. I think I have got 1NF and 2NF down but not 3NF.

For example, in my Account table would I need to split out sort code and account number as can they not be dependent on each other? Also would my other tables conform to 3NF?

I think Account is not in 3NF because of account number and sortcode, these are both unique (so no two customers will have the same) also if I know the account number then I can find the sort code, and neither of them are primary keys so its a transitive dependency, unless I'm wrong?

Background/scenario:

Make a general banking system that can open accounts, customers. To open a bank account, a bank customer is required to provide their full name, date of birth, address, and telephone number. They are also required to deposit an initial sum of money called an opening balance. Once this is provided the bank will open a bank account and provide their new customers with a sort code and a unique account number. The bank will then manage the customer Transactions. The bank is also offering Loans to some of the customers and therefore receiving loan payments from customers on a particular date every month.

This is the design so far:

CREATE TABLE branch (
    branch_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    address_id INT UNSIGNED NOT NULL, 
    branch_name VARCHAR(30) NOT NULL,
    PRIMARY KEY (branch_id),
    UNIQUE (address_id, branch_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE customer (
    customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100)  NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth date NOT NULL,
    email VARCHAR(255),
    PRIMARY KEY (customer_id),
    UNIQUE (email)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE customer_account ( -- Junction table for many-many relation (join accounts)
    customer_id INT UNSIGNED,
    account_id INT UNSIGNED,
    PRIMARY KEY (customer_id, account_id) -- Composite key
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE account (
    account_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    account_type_id TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    opening_balance DECIMAL(10,2) NOT NULL DEFAULT 51,
    account_number VARCHAR(8) NOT NULL,
    sort_code VARCHAR(8) NOT NULL,
    create_date DATE DEFAULT now(),
    branch_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE(sort_code, account_number)    
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

CREATE TABLE account_types (
    account_type_id TEXT NOT NULL,
    description TEXT NOT NULL,
    PRIMARY KEY (account_type_id),
    UNIQUE(description)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE customer_address (
    customer_id INT UNSIGNED,
    address_id INT UNSIGNED,
    PRIMARY KEY (customer_id, address_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE address (
    address_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    postal_code VARCHAR(32) NOT NULL,
    state VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    PRIMARY KEY (address_id),
    UNIQUE(postal_code)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE transactions (
    transaction_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id INT UNSIGNED NOT NULL,
    transaction_type_id INT UNSIGNED NOT NULL,
    transaction_date DATETIME NOT NULL DEFAULT now(),
    isOutgoing BOOLEAN NOT NULL DEFAULT FALSE,
    isIncoming BOOLEAN NOT NULL DEFAULT FALSE,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    description TEXT DEFAULT 'None',
    PRIMARY KEY (transaction_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE transaction_types (
    transaction_type_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (transaction_type_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

CREATE TABLE loans (
    loan_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    monthly_rate DECIMAL(10,2) NOT NULL,
    duration_month INT NOT NULL,
    first_payment DATE NOT NULL,
    monthly_due_date DATE NOT NULL,
    PRIMARY KEY (loan_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE customer_phone (
    contact_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    contact_number VARCHAR(11) NOT NULL,
    country_code INT UNSIGNED NOT NULL,
    type ENUM('landphone','mobile','other') NOT NULL,
    PRIMARY KEY (contact_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
/* Add foreign keys and any neccessary constraints. */
/* KEY : fk_[referencing table name]_[referenced table name]_[referencing field name]. */

ALTER TABLE account ADD CONSTRAINT fk_account_branch_branch_id
FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
ON DELETE CASCADE ON UPDATE CASCADE; -- DOUBLE CHECK IF THIS IS CORRECT VALID OPTION TO USE (also for the ones below)

ALTER TABLE account ADD CONSTRAINT fk_account_account_types_account_type_id
FOREIGN KEY (account_type_id) REFERENCES account_types(account_type_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE branch ADD CONSTRAINT fk_branch_address_address_id
FOREIGN KEY (address_id) REFERENCES address(address_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_address ADD CONSTRAINT fk_customer_address_customer_customer_id
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_address ADD CONSTRAINT fk_customer_address_address_customer_id
FOREIGN KEY (address_id) REFERENCES address(address_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_account ADD CONSTRAINT fk_customer_account_customer_customer_id
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_account ADD CONSTRAINT fk_customer_account_account_account_id
FOREIGN KEY (account_id) REFERENCES account(account_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_phone ADD CONSTRAINT fk_customer_phone_customer_customer_id
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE transactions ADD CONSTRAINT fk_transactions_account_account_id
FOREIGN KEY (account_id) REFERENCES account(account_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE transactions ADD CONSTRAINT fk_transactions_transaction_types_transaction_type_id
FOREIGN KEY (transaction_type_id) REFERENCES transaction_types(transaction_type_id)
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE loans ADD CONSTRAINT fk_loans_account_account_id
FOREIGN KEY (account_id) REFERENCES account(account_id)
ON DELETE CASCADE ON UPDATE CASCADE;


/* DML (Data Manipulation) QUERIES */

/* Trigger on account table to prevent users entering a opening balance less than 51 */
DELIMITER $$
CREATE TRIGGER account_opening_balance_check BEFORE INSERT ON account
    FOR EACH ROW 
        BEGIN 
            IF NEW.opening_balance <= 50.00 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Your opening balance must be greater then 50.';
            END IF;
        END $$
DELIMITER ;

/* Trigger on transactions table to ensure a transaction isnt both incoming and outgoing */
DELIMITER $$
CREATE TRIGGER transactions_in_out_amount_check BEFORE INSERT ON transactions
    FOR EACH ROW
        BEGIN
            IF (NEW.isOutgoing = true and NEW.isIncoming = true) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Both "isOutgoing" and "isIncoming" columns cannot be set to "true"';
            END IF;
        END $$
DELIMITER ;


/* Populating the created tables with data */
INSERT INTO address (address_id, address_line1, address_line2, postal_code, state, city)
VALUES
(),
(),
(),
(),
(),
(),
(),
(),
(),
(),
(),
(),
()


INSERT INTO branch (branch_id, address_id, branch_name) 
VALUES 
(1, 1, "London main branch"),
(2, 2, "Manchester"),
(3, 3, "Lincoln");

INSERT INTO customers (customer_id, first_name, last_name, date_of_birth, email)
VALUES
(1, 'Orran', 'Targetter', '12/08/1979', 'otargetter0@ehow.com'),
(2, 'Danell', 'Braunroth', '11/25/1991', 'dbraunroth1@xrea.com'),
(3, 'Boris', 'Kesley', '11/22/1986', 'bkesley2@addtoany.com'),
(4, 'Antonin', 'Bladon', '03/26/1987', 'abladon3@jugem.jp'),
(5, 'Dorey', 'Nortcliffe', '06/30/1962', 'dnortcliffe4@about.me'),
(6, 'Karlan', 'Scedall', '07/11/1982', 'kscedall5@alibaba.com'),
(7, 'Brewster', 'Swett', '03/15/1987', 'bswett6@squidoo.com'),
(8, 'Jolynn', 'Twinn', '01/25/1981', 'jtwinn7@about.me'),
(9, 'Izabel', 'Boyson', '07/20/1995', 'iboyson8@dion.ne.jp'),
(10, 'Benedicto', 'Scane', '06/02/1959', 'bscane9@tripadvisor.com');

INSERT INTO account_types (account_type_id, description)
VALUES 
("CURRENTACC0", ""),
("SAVINGSACC0", ""),
("JOINTACC0", "")

I haven't added any data for the tables, as I don't want to start inserting data until I know I have 3NF.

New contributor
emilysharp is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
5
  • 2
    This post asks about achieving 3NF but contains nothing towards answering that. Right now you are just asking for us to rewrite a textbook with a bespoke tutorial & you show no research effort. Please see How to Ask, Help center & hits googling 'stackexchange homework'. Show the steps of your work following a textbook/reference with justification & ask 1 specific researched non-duplicate question re the 1st place you are stuck/unsure. Quote definitions, theorems, algorithms & heuristics you are relying on. All the steps are also faqs. Google with & without 'site:dba.stackexchange.com'.
    – philipxy
    Nov 26 at 8:29
  • But what you have allows two customers to have the same acct number. OR the same sortcode. Perhaps tiy want UNIQUE(account_number) and maybe INDEX(sort_code, account_number). Why not use account_number in place of account_id?
    – Rick James
    Nov 26 at 18:48
  • I think already put UNQIUE(account_number) also why would i need INDEX()? thanks
    – emilysharp
    Nov 26 at 19:07
  • 1
    Your design has UNIQUE(sort_code, account_number) which means the combination of sort_code and account_number is unique. Each one individually may not be unique. Is that not correct? Nov 26 at 21:52
  • @ypercubeᵀᴹ Oh I thought it meant both sort_code and account_number are unique by themselves I didn't know this, so do I need 2 separate UNQIUE() ? one for each? Thanks for spotting this! Is there anything else you think I could improve on or is the rest in 3NF as well? thanks again.
    – emilysharp
    Nov 26 at 23:06

Your Answer

emilysharp is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.