Data Model, Normalisation, DDL and DML for Spock Bank 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)
);