Database Normalization and Queries

The provided assignment content consists of SQL queries and explanations of First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). The SQL queries are used to retrieve specific data from the database, such as selecting all customers opened in a particular branch or finding the total balance of all accounts. The 1NF, 2NF, and 3NF sections explain the concepts of atomic domains, functional dependency, and transitive dependency, respectively.

Fundamentals of Database System
1.
A.
The given entity relationship diagram we have four entities Customer, Branch, Transaction, and
Account. One Customer has many accounts, one branch has multiple accounts, and one
account can perform multiple transactions. A customer have attribute like unique customer
identification number (cid), customer name (cname). A branch has attributes like unique
branch code (bcode), branch name (bname). Account has attributes like unique account
number (ano) account type (atype), account balance
(balance), Transaction have unique transaction identification number (tid), transaction date
(tdate), transaction type (ttype), transaction amount (tamount).
B.
Create database OrientalBank;
Use OrientalBank;
Create table customer(
cid varchar(5) primary key,
cname varchar(20));
Create table Branch (
Bcode varchar(5) primary key,
BName varchar(20));
Create table Account (
Ano varchar(5) primary key,
Atype varchar(20),
Balance integer,
Bcode varchar(5)FOREIGN KEY REFERENCES Branch(Bcode),
cid varchar(5)FOREIGN KEY REFERENCES Customer(cid)
);
Create table Transection(
Tid varchar(5) primary key,
Ttype varchar(20),
Tamount integer,

Fundamentals of Database System
TDate Date,
Ano varchar(5)FOREIGN KEY REFERENCES Account(Ano)
);
C.
Insert into Customer(cid,cname) values
('c01','Robert'),
('c02','Mishell'),
('c03','Games'),
('c04','Ohio'),
('c05','Gambler'),
('c06','Tim'),
('c07','Polard'),
('c08','Bush'),
('c09','Russel'),
('c010','Peter'),
('c011','Carol');
Insert into Branch (Bcode,Bname) values
('b01','Melbourne city”'),
('b02','NSW'),
('b03','Oera House'),
('b04','Church Road'),

Fundamentals of Database System
('b05','Sydney'),
('b06','Walington'),
('b07','Baif Road'),
('b08','Poland'),
('b09','Cichago'),
('b010','South Wales');
Insert into Account(Ano,Atype,Balance,Bcode,cid) values
('a01','Saving', 123456,'b01','c01'),
('a02','Current', 356,'b01','c01'),
('a03','Current', 4789,'b02','c02'),
('a04','Saving', 555,'b03','c03'),
('a05','Saving', 10006,'b05','c04'),
('a06','Saving', 56787,'b03','c05'),
('a07','Current', 5555,'b06','c05'),
('a08','Saving', 234,'b08','c06'),
('a09','Current', 234,'b01','c01'),
('a010','Saving', 3689,'b09','c04');

Fundamentals of Database System
Insert into Transection(Tid,Ttype,Tamount,TDate,Ano) values
('t01','NEFT', 10000,'2019-02-05','a01'),
('t02','RTGS', 500,'2019-01-15','a01'),
('t03','IMPES', 300,'2019-01-10','a02'),
('t04','IMPES', 500,'2019-04-25','a03'),
('t05','IMPES', 5600,'2019-03-05','a04'),
('t06','IMPES', 450,'2019-01-30','a05'),
('t07','IMPES', 555,'2019-04-15','a05'),
('t08','NEFT', 300,'2018-02-20','a06'),
('t09','NEFT', 500,'2018-12-05','a01'),
('t010','NEFT', 545,'2018-02-05','a04');
D.
Using the OrientalBank database we can create the different types of report for example:

Fundamentals of Database System
a. Customer Transaction report between the given time period
b. Average amount balance of customer account
c. Favourite transaction method between the customer
d. Customers most preferred branch
E.
Relational Database schema:
Customer (cid,cname)
Branch(bcode,bname)
Account(ano, atype, balance, bcode,cid)
Transaction(tid,type,tamout,tdate,ano)
F. Primary key:
Primary key in the database table is the unique identification of table; we can create one column as
a primary key in the table. For example we have student table, in the student table we have
multiple attribute like student id, student name etc. in this scenario we can choose the student id
as the primary key, because student id is always unique for every student, we may have same name
student in a class but student id is always unique.
In the OrientalBank database we have four tables and every table have one primary key. Followings
are they:
Table Name Primary Key
Customer cid
Branch bcode
Account ano
Transaction tid
2.
A.
Creating the four tables.
Create table customer(
cid varchar(5) primary key,

Fundamentals of Database System
cname varchar(20));
Create table Branch (
Bcode varchar(5) primary key,
BName varchar(20));
Create table Account (
Ano varchar(5) primary key,
Atype varchar(20),
Balance integer,
Bcode varchar(5)FOREIGN KEY REFERENCES Branch(Bcode),
cid varchar(5)FOREIGN KEY REFERENCES Customer1(cid)
);
Create table Transection(
Tid varchar(5) primary key,
Ttype varchar(20),
Tamount integer,
TDate Date,
Ano varchar(5)FOREIGN KEY REFERENCES Account(Ano)
);
B.
How many accounts have a balance greater than $500?
select count(ano) from account where Balance>500;
C.
Display the Name, of all accounts with a balance greater than the average balance.
SELECT * FROM Account
WHERE balance > (SELECT AVG(balance) FROM Account);

Fundamentals of Database System
D.
Find the ID, Name, Transaction number, date and type of the second account.
SELECT c.cname, c.cid, t.tid,t.tdate,t.ttype FROM customer c, branch
b ,Account a,Transection t where a.Ano=t.Ano and b.bcode=a.bcode and
c.cid=a.cid
GROUP BY c.cname, c.cid , t.tid,t.tdate,t.ttype having COUNT(c.cid)> 1;
E.
Find details of customers having their account in the second bank branch.
SELECT c.cname, c.cid FROM customer c, branch b ,Account a,Transection t where
a.Ano=t.Ano and b.bcode=a.bcode and c.cid=a.cid
GROUP BY c.cname, c.cid having COUNT(c.cid)> 1;
F.
List the numbers and names of all customers.
select * from customer;
G.
List the complete account table.
select * from account;
H.
List the number and name of all customers that are opened their accounts in the second branch
and that have a balance less than $1,000.
SELECT c.cname, c.cid FROM customer c, branch b ,Account a,Transection t where
a.balance < 1000 and a.Ano=t.Ano and b.bcode=a.bcode and c.cid=a.cid
GROUP BY c.cname, c.cid having COUNT(c.cid)> 1;
I.
List the number and name of all customers that are opened their accounts in the second branch or
that have a balance less than $1,000.
select c.cid , c.cname from customer c, Account a,branch b ,Transection t
where (a.balance< 1000) or (c.cid=a.cid

Fundamentals of Database System
and a.Ano=t.Ano and b.bcode=a.bcode) group by c.cname, c.cid having
COUNT(c.cid)> 1;
J.
List the number and name of all customers opened in the branch “Melbourne city”.
select c.cid, c.cname ,a.Ano from customer c, Account a,Branch b where
c.cid=a.cid and b.Bcode=a.Bcode and b.BName='Melbourne city';
K.
Find the total of the balances for all accounts.
select sum(balance) as "Total Balances" from Account;
3.
1 NF:
First Normal Form is defined in the tables. It defines that all the attributes in a relation must have
atomic domains. The values in an atomic domain are indivisible units (Elmasri, R 2010; Navathe, S.,
2010).
The given table is like:
PET
ID
PETNA
ME
PETT
YPE
PET
AG
E
OWNER VISITDATE PROCEDURE
246 ROVER DOG 12 SAM COOK JAN 13/2002
MAR 27/2002
APR 02/2002
01 – RABIES VACCINATION
10 - EXAMINE and TREAT WOUND
05 - HEART WORM TEST
1NF of this table is like:

Fundamentals of Database System
PET
ID
PETNA
ME
PETT
YPE
PET
AG
E
OWNER VISITDATE PROCEDURE
246 ROVER DOG 12 SAM COOK JAN 13/2002 01 – RABIES VACCINATION
246 ROVER DOG 12 SAM COOK MAR 27/2002 10 - EXAMINE and TREAT WOUND
246 ROVER DOG 12 SAM COOK APR 02/2002 05 - HEART WORM TEST
Each attribute must contain only one value.
2NF:
The table must be in 1NF and every non-prime attribute should be fully functionally dependent on
prime key attribute.
PET
ID
PETNA
ME
PETT
YPE
PET
AG
E
OWNER VISITDATE PROCEDUREID
246 ROVER DOG 12 SAM COOK JAN 13/2002 01
298 SPOT DOG 2 TERRY KIM JAN 21/2002 08
PROCEDUREID PROCEDURENAME
01 RABIES VACCINATION
10 EXAMINE and TREAT WOUND
05 HEART WORM TEST
3NF:
In Third Normal Form, it must be in Second Normal form and no nonprime attribute is transitively
dependent on prime key attribute

Fundamentals of Database System
PET
ID
VISITDATE PROCEDUREID
246 JAN 13/2002 01
298 JAN 21/2002 08
341 JAN 23/2002 01
PROCEDUREID PROCEDURENAME
01 RABIES VACCINATION
10 EXAMINE and TREAT WOUND
05 HEART WORM TEST
08 TETANUS VACCINATION
PETID PETTYPE PETNAME PETAGE OWNER
246 DOG ROVER 12 SAM COOK
298 DOG SPOT 2 TERRY KIM
341 CAT MORRIS 4 SAM COOK
519 BIRD TWEEDY 2 TERRY KIM
References:
[1]. Elmasri, R. and Navathe, S., 2010. Fundamentals of database systems. Addison-Wesley Publishing
Company.
[2]. Beynon-Davies, P., 2004. Database systems (p. 61). Basingstoke, UK: Palgrave Macmillan.
[3]. Ward, P. and Dafoulas, G., 2006. Database management systems. Cengage Learning EMEA.
[4]. Beaumont, R., An Introduction to Entity Relationship Diagrams (ERDs) A Practical Guide for
Beginners