Database Design and Implementation Assignment

DATABASE SYSTEMS DATABASE
DESIGN & IMPLEMENTATION

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
Construct data models according to the business rules and showing primary key, foreign key,
entity and attributes................................................................................................................3
TASK 2............................................................................................................................................5
Normalization.........................................................................................................................5
TASK 3............................................................................................................................................6
Create Data definition language of database table.................................................................6
TASK 4..........................................................................................................................................15
Screenshot of database table and insert record into the table...............................................15
1. ....................................................................................................................................................15
TASK 5..........................................................................................................................................16
Solve queries........................................................................................................................16

INTRODUCTION
TASK 1
Construct data models according to the business rules and showing primary key, foreign key,
entity and attributes.
Entity- It is a real world entity of particular object in the form of entities. In database
management system, all data are stored in the form of table containing information such as
customer, account, bank, debit card, department and loan etc.
Attributes- Attributes is an instance of data in database system. Attribute is quality of
characteristic of particular person and place.
Primary key-This is main concept that specify the relationship between tables and
columns. It can be designed for representing unique identity in database table.
Foreign key-It is a group of data in a table that is relational database and link between
two tables.
Data Model Diagram

Figure 1: Data Model

TASK 2
Normalization
Normalization is a process or technique which help in reducing large table into smaller table. It
will generate dependency of data and reduces redundancy.
Figure 2: 3NF
3NF is a step of normalization which assist for building an effective database effectively
and efficiently.
According to this table, it can be concluded that customer_id storing the information and
details of customers and branch_id is storing the detail and information of branch and bank
information. Branch holds the customer information. In case, there is a need to link between two
tables by using foreign key. This key assist in creating relationship between tables.
In customer table, Branch_id is pointing or reference corresponding column in customer
table. It is store the branch id value in customer database table. It will show the data dependency.

Suppose, Customer wants the branch information so that they will determine branch id
for particular customer while customer detail already save in branch.
In Account table, this data base table store details of accounts such as account_name,
account_id, account_balance, account_number etc. It is required for customers to mention
customer_id in particular account.
According to account table, it can be concluding that Customer_Id is storing in Account
table so it can easily determine particular customers’ details of their id’s.
TASK 3
Create Data definition language of database table
Account database table:

Branch table:-

Customer table-

Debit Card Table-

Department database table-

Employee database table-

Loan table-

Loan repayment table-

Withdrawal table-

TASK 4
Screenshot of database table and insert record into the table
1.
2.
3.
4.
5.
6.

7.
TASK 5
Solve queries
a) Find the total number of accounts that are co-owned (Joint Bank Accounts)
SQL Code:
SELECT Account.Acc_number, Account.Customer_id, Account.Acc_name, Account.Acc_type,
Account.Acc_id
FROM Account
WHERE (((Account.Acc_number) In (SELECT [Acc_number] FROM [Account] As Tmp
GROUP BY [Acc_number] HAVING Count(*)>1 )))
ORDER BY Account.Acc_number;
Output:

b) List all accounts that have a ‘Personal’ loan linked to it (You need to a have a loan type called
‘Personal’ to test this query).
SQL Code:
SELECT Account.Acc_id, Account.Acc_number, Loan.Loan_type, Account.Acc_name
FROM Account INNER JOIN Loan ON Account.Acc_id = Loan.Account_id
WHERE (((Loan.Loan_type)="Personal Loan"));
Output:

c)The name of every department, each of the branches, along with the manager-name for that
department.
SQL Code:
SELECT Department.Department_name, Branch.Branch_name, Employee.[Employee Name],
Employee.Employee_position
FROM Branch INNER JOIN (Department INNER JOIN Employee ON
Department.Department_id = Employee.Department_id) ON Branch.Department_id =
Department.Department_id
WHERE (((Employee.Employee_position)="Manager"));
Output:

d) List the names of managers who make more than the average manager salary.
SQL Code:
SELECT Employee.Employee_id, Employee.Salary
FROM Employee
GROUP BY Employee.Employee_id, Employee.Salary
HAVING (((Employee.Salary)>5000));
Output: