Database Systems Concepts and Design

This assignment delves into various aspects of database systems. It presents a set of SQL queries to retrieve specific information from a hypothetical database schema, focusing on concepts like joins, conditions, and aggregate functions. It also explores normalization by illustrating the transition from an un-normalized form to different normal forms (1NF, 2NF, 3NF), emphasizing data integrity and structure.

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 =

12MANAGING DATA IN DATABASES
Transaction.Br.Value) AND (Account.ano = Transaction.Actyp.Value) AND (Account.ano =
Transaction.tid)
WHERE (([Customer].[Branch]="Adeliade" & [Account].[balance]<1000));
Query for I
SELECT Customer.cid, 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 =
Transaction.Br.Value) AND (Account.ano = Transaction.Actyp.Value) AND (Account.ano =
Transaction.tid)
WHERE (([Customer].[Branch]="Adelaide")) OR (([Account].[balance]<1000));
Query for J
SELECT Customer.cid
FROM Customer
WHERE ((Customer.Branch) = "Melbourne");
Query for K
SELECT Account.balance
FROM Account
WHERE (((Account.balance)=Sum([Account].[balance])));
Question 3: Normalization
Un-normalized form
PET [PetId, PetName, PetType, PetAge, Owner, (Visitdate, ProcedureNo,
ProcedureName)]

13MANAGING DATA IN DATABASES
First Normal form
PET [PetId, PetName, PetType, PetAge, Owner, Visitdate, ProcedureNo,
ProcedureName]
Second Normal form
PET [PetId, PetName, PetType, PetAge, Owner]
PET_VISIT [PetId, Visitdate, ProcedureNo, ProcedureName]
Third Normal form
PET [PetId, PetName, PetType, PetAge, OwnerId (FK)]
PET_VISIT [PetId, Visitdate, ProcedureNo (FK)]
PROCEDURE [ProcedureNo, ProcedureName]

14MANAGING DATA IN DATABASES
Bibliography
[1.] Abadi, D., Boncz, P., Harizopoulos, S., Idreos, S. and Madden, S., 2013. The design and
implementation of modern column-oriented database systems. Foundations and
Trends® in Databases, 5(3), pp.197-280.
[2.] Abadi, D., Boncz, P., Harizopoulos, S., Idreos, S. and Madden, S., 2013. The design and
implementation of modern column-oriented database systems. Foundations and
Trends® in Databases, 5(3), pp.197-280.
[3.] Coronel, C. and Morris, S., 2016. Database systems: design, implementation, &
management. Cengage Learning.
[4.] Elmasri, R. and Navathe, S.B., 2015. Fundamentals of database systems. Pearson.
[5.] Elmasri, R. and Navathe, S.B., 2015. Fundamentals of database systems. Pearson.
[6.] Harrington, J.L., 2016. Relational database design and implementation. Morgan
Kaufmann.
[7.] Harrington, J.L., 2016. Relational database design and implementation. Morgan
Kaufmann.
[8.] Kaur, K. and Rani, R., 2013, October. Modeling and querying data in NoSQL databases.
In Big Data, 2013 IEEE International Conference on (pp. 1-7). IEEE.