HI5033 Database Design Case Study Sample

HI5033 Database Design Case Study

Assignment Task

Objectives

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to reinforce the lecture material and give students practice at applying Database design techniques, as well as providing a relational schema using DDL statements and relational DML statements to demonstrate sophisticated access of the Database to be built.

Each group needs to choose a particular type of Information System, research the storage requirements then design and build a possible database for that system. For instance, a group may choose a POS system for a supermarket, a small online savings bank, a stock management system for a financial firm etc.

Deliverable Description

The deliverable should consist of four parts, detailed as follows:

1. Requirement specification

Your group must provide an overview of the selected system and specify its requirements. These requirements should directly relate to the tasks outlined in the subsequent three parts.

2. Database ER Model

The ER Model should represent the database structure you plan to build for the group case study. It must be presented using Crow's Foot notation and accurately reflect the database to be built. The model should provide a realistic depiction of the data requirements for the chosen system and include at least 10 entity types.

3. Database Schema DDL Statements

You are required to create Schema DDL statements to build your database. These statements should be included in a script and demonstrated in class to your tutor. You will need to run the script to show the creation of the database and submit it through Blackboard. Your tables should include all appropriate primary and foreign keys, as well as any relevant constraints.

4. Sophisticated DML Statements

You are required to construct SQL INSERT statements to populate all your tables with initial data, ensuring that each table contains at least 5 rows. You have the freedom to choose appropriate data for insertion. Additionally, you must create 5 SQL statements to demonstrate sophisticated data access. These statements must involve joins between multiple tables (Inner Joins only are acceptable). The test data should be sufficient to effectively demonstrate the use of your SQL statements. These statements should be included in a script and presented in class to your tutor.

Solution

Requirement Specification

Customer data, accounts, transactions, and services are all managed by a bank's database system. It guarantees the effectiveness and security of financial operations, including loan processing, cash transfers, account management, and transaction histories. Customers, bank staff, and administrators are among the many user roles that the system supports, for MBA assignment expert guaranteeing seamless communication as well as the accuracy and accessibility of data in real time.

The major functionalities of the system are as follows:

Database ER Model

Database Schema DDL Statements

Sophisticated DML Statements

Queries

1. Total Balance Held by Customers of a Specific Branch

This query calculates the total balance of all accounts belonging to customers of a specific branch.

SELECT
b.branch_name,
SUM(a.balance) AS total_branch_balance
FROM
ACCOUNT a
INNER JOIN CUSTOMER c ON a.customer_id = c.customer_id
INNER JOIN BRANCH b ON c.branch_id = b.branch_id
WHERE
b.branch_id = 1
GROUP BY
b.branch_name;

2. Find Customers with Loans Pending Approval

This query lists customers who have loan applications still pending, including their details.

SELECT
c.name AS customer_name,
c.email,
l.loan_amount,
l.loan_date
FROM
LOAN l
INNER JOIN CUSTOMER c ON l.customer_id = c.customer_id
WHERE
l.loan_status = 'Pending';

3. Identify Accounts with the Highest Transaction Volume

This query identifies the top 3 accounts with the most transactions.

SELECT
a.account_id,
COUNT(t.transaction_id) AS transaction_count
FROM
TRANSACTION t
INNER JOIN ACCOUNT a ON t.sender_account_id = a.account_id OR t.receiver_account_id = a.account_id
GROUP BY
a.account_id
ORDER BY
transaction_count DESC
LIMIT 3;

4. List All Complaints Resolved by a Specific Employee

This query retrieves complaints resolved by a particular employee using nested queries to focus on resolved cases.

SELECT
cmp.complaint_id,
cmp.issue,
cmp.resolved_date
FROM
COMPLAINT cmp
WHERE
cmp.employee_id = 1
AND cmp.status = 'Resolved';

5. Retrieve Details of Recent Transactions above a Threshold

This query retrieves transactions with amounts greater than a specified threshold in the last 2 years days.

SELECT
t.transaction_id,
t.transaction_date,
t.amount,
t.transaction_type
FROM
TRANSACTION t
WHERE
t.amount > 250
AND t.transaction_date >= NOW() - INTERVAL '2 years';

Bibliography

Christudas, B. and Christudas, B., 2019. MySQL (pp. 877-884). Apress.

Laeven, L. and Valencia, F., 2020. Systemic banking crises database II. IMF Economic Review, 68, pp.307-361.