ISYS224 Database Design And Implementation

Normalization takes place in through steps;

• 1NF- checking for any repeating group
• 2NF- Checking for any partial dependencies
• 3NF-checking for any transitive dependencies and

Given the table;

Normalization can be done by following the three steps of normalization to normalize the table to 3NF.

• 1NF- The table above is already in 1NF since there are no repeating groups thus this step is skipped.
• 2NF- Since the table shown above is already in 1NF, normalization to 2NF can be done. The following partial dependenciy can be seen from the table;
  • AccountNumber, BSB à accountBalance, customerID, branchID, accountType

Thus eliminating the partial dependency will leave all resulting relations in 2NF.

• 3NF- This step involves identifying any transitive dependencies and eliminating them as carrying out 2NF. The following transitive dependencies can be identified from the table

• Account(accountNumber,BSB,accountBalance,customerID,branchID,accountType)
• Customer(CustomerID,customerName)
• Branch(branchID branchName)

Elimination of the transitive dependencies results to decomposition of the table to form new tables. This is the last step of normalisation thus the final tables achieved as a result of normalization through 1nF, 2NF and 3NF are;

• Customer(CustomerID,customerName)

Primary key- customerID

• Account_type( AccountType,accountName)

Primary key - AccountType

• Branch(branchID branchName)

Primary key - branchID

• Account(accountNumber,BSB,accountBalance,customerID,branchID,accountType)

Primary key – (accountNumber,BSB)

Foreign key -  customerID refereces customer (customerID)

Foreign key- branchID references branch (branchID)

Foreign key – accountType references account_type (accountType)

At 3NF the entities are ready to be implemented as tables in a database using MySQL

Task 4: DML

SET FOREIGN_KEY_CHECKS=0;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

TRUNCATE TABLE `account`;

INSERT INTO `account` VALUES('56756567', '56765757', 'CHECK', '343');

INSERT INTO `account` VALUES('65757', '756875', 'SAV', '567');

INSERT INTO `account` VALUES('8778768', '8678678', 'IRA', '678');

TRUNCATE TABLE `accountownership`;

INSERT INTO `accountownership` VALUES('56756567', 'C1');

INSERT INTO `accountownership` VALUES('56756567', 'C2');

INSERT INTO `accountownership` VALUES('65757', 'C4');

INSERT INTO `accountownership` VALUES('8778768', 'C2');

INSERT INTO `accountownership` VALUES('8778768', 'C3');

TRUNCATE TABLE `accounttype`;

INSERT INTO `accounttype` VALUES('CHECK', 'checking  acc', 'checking acc', '6', '34');

INSERT INTO `accounttype` VALUES('IRA', 'Retirement acc', 'retirement acc', '6', '342');

INSERT INTO `accounttype` VALUES('SAV', 'Savings acc', 'target savings account', '4', '432');

TRUNCATE TABLE `atm`;

INSERT INTO `atm` VALUES('A0001', 'rhode island', '08:00-16:00', '343423');

INSERT INTO `atm` VALUES('A0002', 'melbourne', '08:00-16:00', '4234134');

INSERT INTO `atm` VALUES('A0003', 'Victoria', '08:00-16:00', '23412423');

TRUNCATE TABLE `branch`;

INSERT INTO `branch` VALUES('BR1', 'victoria', 'victoria drive', '+ 324 32432432');

INSERT INTO `branch` VALUES('BR2', 'Queens', 'Queens dr', '+324 23423423 324');

INSERT INTO `branch` VALUES('BR3', 'Melbourne', 'MElbourne dr', '+324 3242345234 34');

TRUNCATE TABLE `customer`;

INSERT INTO `customer` VALUES('C1', 'Peter Griffin', 'Victoria', '+23 2342324', '[email protected]', '2018-05-08');

INSERT INTO `customer` VALUES('C2', 'Lois Griffin', 'Melboune dr', '+3 32232 323', '[email protected]', '2018-07-02');

INSERT INTO `customer` VALUES('C3', 'Brian Griffin', 'queens dr', '+3 232323', '[email protected]', '2018-07-03');

INSERT INTO `customer` VALUES('C4', 'Stewie Griffin', 'Melbourne dr', '+3 3242 324234', '[email protected]', '2018-07-03');

INSERT INTO `customer` VALUES('C5', 'Cleveland Brown', 'Queens dr', '+32 2323', '[email protected]', '2018-09-04');

TRUNCATE TABLE `debitcard`;

INSERT INTO `debitcard` VALUES('2342234234', '09/2015', 523, 3453, '65757');

INSERT INTO `debitcard` VALUES('4343223323', '08/2054', 353, 5463, '8778768');

INSERT INTO `debitcard` VALUES('5675875455', '12/2065', 545, 4646, '56756567');

TRUNCATE TABLE `department`;

INSERT INTO `department` VALUES('DHR', 'HR department', 'BR1');

INSERT INTO `department` VALUES('DIT', 'IT depart', 'BR2');

INSERT INTO `department` VALUES('DSL', 'Sales depart', 'BR3');

TRUNCATE TABLE `employee`;

INSERT INTO `employee` VALUES('EMP001', 'Homer simpson', 'Melbourne', '+3 23232 3232', 'HR', '43324', 'DHR');

INSERT INTO `employee` VALUES('EMP002', 'Marge Simpon', 'Victoria', '+2 23 23232', 'IT', '42323', 'DIT');

INSERT INTO `employee` VALUES('EMPLOYEE3', 'Bart Simpson', 'Queens', '+23 3232323', 'Sales', '431232423', 'DSL');

TRUNCATE TABLE `loan`;

INSERT INTO `loan` VALUES('L/1', 'FIX', '12', '24342', '234', '56756567');

INSERT INTO `loan` VALUES('L/2', 'PER', '12', '45534', '434232', '8778768');

INSERT INTO `loan` VALUES('L/3', 'RED', '21', '2312', '321', '65757');

TRUNCATE TABLE `loanrepayment`;

INSERT INTO `loanrepayment` VALUES('R/01', 'L/1', '223', '2018-09-07 09:29:32', 'EMP001');

INSERT INTO `loanrepayment` VALUES('R/02', 'L/2', '234', '2018-09-07 09:29:40', 'EMP002');

INSERT INTO `loanrepayment` VALUES('R/03', 'L/3', '544', '2018-09-07 09:30:04', 'EMPLOYEE3');

TRUNCATE TABLE `loantype`;

INSERT INTO `loantype` VALUES('FIX', 'fixed', 'fixed loan');

INSERT INTO `loantype` VALUES('PER', 'personal', 'personal loan');

INSERT INTO `loantype` VALUES('RED', 'reducing ', 'reducing loan');

TRUNCATE TABLE `manager`;

INSERT INTO `manager` VALUES('DHR', 'EMP001', '2018-05-01', '2018-09-29');

INSERT INTO `manager` VALUES('DIT', 'EMP002', '2018-04-08', '2018-09-21');

INSERT INTO `manager` VALUES('DSL', 'EMPLOYEE3', '2018-04-08', '2018-09-21');

TRUNCATE TABLE `withdrawal`;

INSERT INTO `withdrawal` VALUES('W-001', 'A0001', '2342234234', '2018-09-07 09:31:42', '234');

INSERT INTO `withdrawal` VALUES('W-002', 'A0002', '5675875455', '2018-09-07 09:31:54', '432');

INSERT INTO `withdrawal` VALUES('W-003', 'A0003', '4343223323', '2018-09-07 09:32:04', '343');

Task 5: DML

SELECT COUNT(a.accountNO) as "total count of joint accounts" FROM account a WHERE a.accountNO  IN (SELECT ao.accountNO FROM accountownership ao GROUP BY ao.accountNO HAVING COUNT(ao.accountNO) > 1);

SELECT   a.*  FROM account a WHERE a.accountno IN (SELECT  l.accountNO FROM loan l INNER JOIN loantype lt ON l.loanTypeID = lt.loanTypeID WHERE lt.loanname = 'personal');

SELECT d.name, b.branchname, e.employeename FROM department d INNER JOIN branch b ON b.branchID = d.branchID INNER JOIN manager m ON m.departmentID = d.departmentID INNER JOIN employee e ON e.employeeID = m.employeeID;

SELECT d.name, b.branchname, e.employeename FROM department d INNER JOIN  branch b ON b.branchID = d.branchID  INNER JOIN  manager dm ON dm.departmentID = d.departmentID INNER JOIN employee e ON e.employeeID = dm.employeeID;

Assumptions, if any:

No assumptions were made as the all requirements were clear.