University Database Design and SQL Queries: MN405 Assignment 1

This document presents a comprehensive solution to a data and information management assignment (MN405). The assignment focuses on database design and implementation using MS Access, including the creation of tables (Customer, Branch, Account, Transaction), and the input of data. It involves examples of report cards and shorthand representation of the E-R diagram. Furthermore, the solution demonstrates the implementation of various SQL commands for querying the database. The assignment also covers database normalization, presenting the un-normalized form, first normal form, second normal form, and third normal form. The document concludes with a bibliography of relevant sources.

Running head: MANAGING DATA IN DATABASES
MN405 - Data and Information Management
Assignment 1 (T2 2017)
Student Name:
University Name:

1MANAGING DATA IN DATABASES
Table of Contents
Question 1: Design of the database in MS Access.........................................................................2
A. Description of the provided E-R diagram...............................................................................2
B. Creation of 4 tables in MS Access..........................................................................................2
Table Customer......................................................................................................................2
Table Branch.......................................................................................................................... 3
Table Account........................................................................................................................ 3
Table Transaction...................................................................................................................4
C. Input of data.......................................................................................................................... 4
Table Customer......................................................................................................................4
Table Branch.......................................................................................................................... 5
Table Account........................................................................................................................ 5
Table Transaction...................................................................................................................6
D. Examples of report cards.......................................................................................................6
Report 1................................................................................................................................. 6
Report 2................................................................................................................................. 7
Report 3................................................................................................................................. 7
Report 4................................................................................................................................. 8
E. Shorthand representation of the E-R diagram.......................................................................8
F. Definition of primary key and primary key of each table in ‘OrientalBank’ database............8
Primary key of each table in the database.............................................................................9
Question 2: Implementing SQL commands....................................................................................9
Query for A................................................................................................................................ 9
Table customer...................................................................................................................... 9

2MANAGING DATA IN DATABASES
Table Account........................................................................................................................ 9
Table Branch.......................................................................................................................... 9
Table Transaction...................................................................................................................9
Query for B................................................................................................................................ 9
Query for C...............................................................................................................................10
Query for D.............................................................................................................................. 10
Query for E...............................................................................................................................10
Query for F............................................................................................................................... 10
Query for G.............................................................................................................................. 10
Query for H.............................................................................................................................. 10
Query for I................................................................................................................................11
Query for J............................................................................................................................... 11
Query for K...............................................................................................................................11
Question 3: Normalization...........................................................................................................11
Un-normalized form................................................................................................................ 11
First Normal form.................................................................................................................... 12
Second Normal form................................................................................................................12
Third Normal form................................................................................................................... 12
Bibliography............................................................................................................................. 13

3MANAGING DATA IN DATABASES
Question 1: Design of the database in MS Access
A. Description of the provided E-R diagram
The E-R diagram that has been provided reflects that there are four relations/tables in
the database describing banking application details, details of projects, work assigned for each
branch and account, namely BRANCH, ACCOUNT, CUSTOMER and TRANSACTION. The
relationship between the entities shows that Customer holds an Account and each Account is
contained in Branch. Transaction is performed within Account.
B. Creation of 4 tables in MS Access
Table Customer

4MANAGING DATA IN DATABASES
Table Branch
Table Account

5MANAGING DATA IN DATABASES
Table Transaction
C. Input of data
Table Customer

6MANAGING DATA IN DATABASES
Table Branch
Table Account

7MANAGING DATA IN DATABASES
Table Transaction
D. Examples of report cards
Report 1

8MANAGING DATA IN DATABASES
Report 2
Report 3

9MANAGING DATA IN DATABASES
Report 4
E. Shorthand representation of the E-R diagram
F. Definition of primary key and primary key of each table in ‘OrientalBank’ database
A primary key is considered as a special column in relational database table or even
combination of columns required for uniquely identifying the existing records in table. It is
defined as a unique value for each row of data.

10MANAGING DATA IN DATABASES
Primary key of each table in the database
Table Name Primary Key Description
Customer cid This refers to unique number for representing
customers.
Account ano Unique number for a particular account.
Branch bcode Branch code which has account.
Transaction tid Unique number for a particular transaction.
Question 2: Implementing SQL commands
Query for A
Table customer
CREATE TABLE CUSTOMER (cid INT NOT NULL, cname VARCHAR (20) NOT NULL,
PRIMARY KEY (ID));
Table Account
CREATE TABLE Account (ano INT NOT NULL, atype VARCHAR (20) NOT NULL, balance INT
NOT NULL, PRIMARY KEY (ano));
Table Branch
CREATE TABLE Branch (bcode INT NOT NULL, atype VARCHAR (20) NOT NULL, balance
INT NOT NULL, PRIMARY KEY (bcode));
Table Transaction
CREATE TABLE Transaction (tid INT NOT NULL, tdate Date NOT NULL, ttype VARCHAR
(10) NOT NULL, tamount INT, PRIMARY KEY (tid));
Query for B
SELECT Account.balance
FROM Account
WHERE (((Account.balance)>500));

11MANAGING DATA IN DATABASES
Query for C
SELECT Transaction.tid
FROM Transaction
WHERE (((Account.balance)>Avg ("balance")));
Query for D
SELECT Transaction.tid, Transaction.tdate, Transaction.ttype
FROM [Transaction]
WHERE ([Transaction].[Actyp]="A2");
Query for E
SELECT Customer.cid, Customer.cname
FROM Customer
WHERE (([Customer].[Branch]="Adelaide"));
Query for F
SELECT Customer.cid, Customer.cname
FROM Customer;
Query for G
SELECT Account.ano, Account.atype, Account.balance
FROM Account;
Query for H
SELECT Account.ano, Customer.cname
FROM ((Branch INNER JOIN Account ON (Branch.bcode = Account.ano) AND
(Branch.bcode = Account.ano)) INNER JOIN Customer ON (Customer.cid = Account.ano) AND
(Branch.bcode = Customer.Branch) AND (Branch.bcode = Customer.cid)) INNER JOIN
[Transaction] ON (Customer.cid = Transaction.Cust.Value) AND (Branch.bcode =