Data Model, Normalisation, DDL and DML for Spock Bank Database

This article explains the data model, normalisation, DDL and DML for Spock Bank Database. It covers the process of normalisation and the steps involved in creating the database. It also includes sample DDL and DML statements for creating and manipulating the database.

Task 1: Data Model
Data model elaborates the logical design of a database, it entails constraints, relationships, primary
keys of attribute, foreign keys, cardinality and datatypes. Below is the data model of Spock Bank
proposed database.
Task 2
Normalisation
Normalisation refers to the technique of arranging data in a database to evade data insertion,
updation, and redundancy and deletion anomalies.
This table is in un-normalized form. A database entity qualifies t be in normal form if it fulfils 3NF
requirements. Normalisation process follows the below steps.

i. Data source is selected and the piece of data is put in un-normal form. For instance, our
data is selected and put in a table which is not normal.
ii. The selected data is then transformed into 1NF by the rule of elimination of repeated
attributes.
iii. The 1NF is at that point converted to 2NF by law of elimination of every non-key column
that are depended on part of the table.
iv. The 2NF is afterwards transformed into 3NF by rule of removal of every new field that
has transitive dependency.
Normalisation of Spock Bank database
I will be using relational dependency diagram to normalise this database. Our above table can
represented as below
First Normal Form
By rule nummber two(ii) above, our table has repeated row values and this has to be eliminated.
This is achieved by dependency shown below.
Second normal form
Second normal form normalisation of requires that there should be no non-key column that is
depended on part of Primary key.
By keen observation of above tables, ACTYPE field in Account Table, it may depend on another table
types. This has to be eliminated as below

We will have a new table named Account table
Third normal form
3NF requires that there should be no field that has transitive dependency. Our tables above no field
that has transitive dependency. Hence, our table is in 3NF.
Task 3 DDL
Creating db
CREATE DATABASE SpockBank;

Create tables
CREATE TABLE Withdrawal(
WitID char(10) NOT NULL UNIQUE,
ATMNo char(10),
CardNo char(100),
DateOfWithdrawal date,
Phone_Number char(12),
TimeOfWithdrawal time
);
CREATE TABLE AccountType(
AccountTypeID char(10) NOT NULL UNIQUE,
CurrentBal dec(6,2),
AccountType char(100)
);

CREATE TABLE LoanType(
LTID char(10) NOT NULL UNIQUE,
Interest dec(2,2),
Amount dec(6,2),
);
CREATE TABLE Department(
DepID char(10) NOT NULL UNIQUE,
Name char(100),
OffLocation char(100),
ContactNo char(12)
);
CREATE TABLE Employee(

EmpID char(10) NOT NULL UNIQUE,
Name char(100),
PersonalAddress char(100),
PhoneContact char(12),
Position char(100),
AnnualSal char(100)
PRIMARY KEY (EmpID)
);
CREATE TABLE Manager(
ManID char(10) NOT NULL UNIQUE,
Descrption char(100),
Qualification char(100),
EmpID char(10),
PRIMARY KEY (ManID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
CREATE TABLE LoanRepayment(
LoanID char(10) NOT NULL UNIQUE,
Descrption char(100),
Amount dec(6,2),
DateOfRepayment date,
TimeOfRepay time,
EmpID char(10),
PRIMARY KEY (LoanID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

CREATE TABLE Account(
ACNo char(10) NOT NULL UNIQUE,
BSB char(10),
Type char(100),
Name char(100),
Descrption char(100),
InterestRate dec(2,2),
YearServiceFee dec(6,2)
PRIMARY KEY (ACNo)
);
CREATE TABLE Customer(
CusID char(10) NOT NULL UNIQUE,
ACNo char(10),
Name char(100),
EmailAddress char(100),
ContactNo char(100),
JoinDate date,
PRIMARY KEY (CusID),
FOREIGN KEY (ACNo) REFERENCES Account(ACNo)
);

CREATE TABLE Debit(
DBCID char(10) NOT NULL UNIQUE,
CusID char(10),
ExpDate date,
CVVNo char(100),
PINNo char(100),
PRIMARY KEY (CusID),
FOREIGN KEY (CusID) REFERENCES Customer(CusID)
);
CREATE TABLE ATM(
ATMNo char(10) NOT NULL UNIQUE,
Location char(100),
OPHours char(100),
MaxCashCapacity dec(6,2),
WitID char(10),
PRIMARY KEY (ATMNo),
FOREIGN KEY (WitID) REFERENCES Withdrawal(WitID)
);

REATE TABLE Branh(
BrID char(10) NOT NULL UNIQUE,
Name char(100),
Phone char(12),
EmailAddress char(100),
EmpID char(10),
ATMNo char(10),
PRIMARY KEY (EmpID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
FOREIGN KEY (ATMNo) REFERENCES ATM(ATMNo)
);
cREATE TABLE Loan(
LoanID char(10) NOT NULL UNIQUE,
Name char(100),
LoanDescription char(100),
LTID char(10),
PRIMARY KEY (LoanID),
FOREIGN KEY (LTID) REFERENCES LoanType(LTID)
);

Task 4
DML
VALUES ('WI001', 'AT001','CA001',CURRENT_TIMESTAMP, '[phone]',CURRENT_TIMESTAMP)
INSERT INTO Withdrawal VALUES('WI002', 'AT002','CA002',CURRENT_TIMESTAMP,
'[phone]',CURRENT_TIMESTAMP);
INSERT INTO Withdrawal VALUES('WI003', 'AT003','CA003',CURRENT_TIMESTAMP,
'[phone]',CURRENT_TIMESTAMP);
INSERT INTO Withdrawal VALUES('WI004', 'AT004','CA004',CURRENT_TIMESTAMP,
'[phone]',CURRENT_TIMESTAMP);
INSERT INTO AccountType VALUES('ACT002', '2000','SAVINGS');
INSERT INTO AccountType VALUES('ACT003', '3000','SAVINGS');
INSERT INTO AccountType VALUES('ACT004', '4000','LOAN');
INSERT INTO AccountType VALUES('ACT005', '5000','CURRENT');

INSERT INTO LoanType VALUES('LTID002', NULL,NULL);
INSERT INTO LoanType VALUES('LTID003', NULL,NULL);
INSERT INTO LoanType VALUES('LTID004', NULL,NULL);
INSERT INTO Department VALUES('DT001', 'Accounts','Mel','[phone]');
INSERT INTO Department VALUES('DT002', 'Marketing','Brisbane','[phone]');
INSERT INTO Department VALUES('DT003', 'Delivery','Mel','[phone]');
INSERT INTO Department VALUES('DT004', 'Loans','Mel','[phone]');
INSERT INTO Employee VALUES('EM002', 'Vincel
Pogba','VincelPogbaspockbank.com','[phone]','Manager','780');
INSERT INTO Employee VALUES('EM003', 'Godsen
Townce','[e-mail]','[phone]','Accountant', '563');
INSERT INTO Employee VALUES('EM004', 'Lynn
Pop','[e-mail]','[phone]','Accountant','6400');

INSERT INTO manager VALUES('MD002', 'HEAD OF CUSTOMER SERVICE','DEGREE IN
COMUNNICATIONS','EM002');
INSERT INTO manager VALUES('MD003', 'HEAD OF CREDITS','DEGREE IN CREDIT
CONTROL','EM002');
Task 5
a.
b. select * from Loan
where name='PERSONAL';

SELECT Name
FROM [Employee] JOIN Manager
ON [Employee].EmpID = manager.EmpID;

SELECT Name,AnnualSal
FROM [Employee] JOIN Manager
ON [Employee].EmpID = manager.EmpID;