October

Student Name
Student ID        2
Data Analysis
Submitted By
Course
Professor
Date
Section 1: Fluency in Data Analysis and SQL
1.1 Query to return different regions and a count of all customers who belongs to every Region.
Select customer_region.region_id, region.name, count(customer_region.customer_id) as CountOfCustomers
from customer_region, region WHERE
customer_region.region_id=region.id
group by customer_region.region_id, region.name;
1.2 Query to return all customers whose name consist of the string ‘Will’ and their total loan values.
Select customers.name, Sum(loans.loan_amount) As TotalLoanValue
from customers, loans WHERE
Loans.customer_id=customers.id AND
customers.name like '%Will%'
group by customers.name;
1.2 Query that returns a count of customers who have no loans as ‘CountNoLoan’.
Select count(customers.id) as CountNoLoan
from customers where NOT EXISTS
(Select customer_id from loans);
1.4 Query that returns the single region which has high risk of defaulting customers who returns the top defaulting region as ‘TopdefaultingRegion’, the CustomerCount for the region and the average amount all customers default for the region as ‘Averagedefault’.
Select region.name, count(loans.customer_id) as CustomerCount, Round(avg(loans.loan_amount),2) as AverageDefault
from loans, region, customer_region
WHERE
loans.customer_id=customer_region.id and
customer_region.region_id=region.id
and loans.defaulted =1
group by region.name
order by count(loans.customer_id) desc;
-
1.5 Conclusions regarding the data
In this, we have given with four tables – Customer_region, customers, loans and region. Customers and region are master tables whereas customer_region and loans are child tables that are linked with these master tables. In above queries, we have found the number of customers belongs to a region, their loan values and the defaulted customers from each region. The database helps in maintaining the data integrity and consistency and chances of redundancy decreases. The data is about customers who belongs to various regions and their loan amount with the specification that whether the customer is defaulted or not.
Section 2: Requirements Elicitation and Analysis
Functional Requirements
The functional requirements of the system will be as following:
System Login – In order to prevent any unauthorized use of the system, every user will be provided with different login privileges on the basis of their role.
Online Order Booking – With the implementation of the system, the system will be able to make an online order...