Database Design for Managing Personal Expenses and Savings

This assignment presents a database design for personal finance management. The design includes three tables: expenseCategory, expenseDetails, and monthlySaving. The expenseCategory table stores different types of expenses. The expenseDetails table tracks the day, month, year, and total amount spent for each expense category. The monthlySaving table records the day, month, year, monthly salary, and savings amount. The database design allows users to enter data on a daily, monthly, or yearly basis. The assignment also includes SQL queries to display annual salary, total expense amounts per category, and total savings. These queries allow users to analyze their financial data effectively. The design focuses on flexibility and ease of use, enabling users to manage their finances efficiently.

Problem analysis and description
I assumed myself as professional. My assumed problem is to deal with the daily home based
expenses and how I can save money monthly or daily bases. This database design solve my
problem. I have created three table one table represent the expense category on which I spent
money. These expense category can be increases by in future that is why I have created this as
separate table. The second table is designed to store the day, month, year and the total amount
of expense according to each expense category. According to this table design I am free to
enter record on daily basis or on monthly basis or year basis so this removed my headache to
maintain data according to particular period of time. I have created one more table that is third
table in which I can store details of salary and saving. In this table user have to enter the day,
month, year and the monthly salary and monthly saving amount. Again this design makes free
user to enter data on daily or monthly or yearly basis this is his/her own choice how they want
to maintain their records. This type of database give freedom to user. User can save money
according to particular days interval it may be 10 or 15 it is totally depends on user choice.
Database Schema
1. expenseCategory(expenseTypeID, expenseType)
2. expenseDetails(id,expenseTypeID,day,month,year,totalAmount)
3. monthlySaving(id,day,month,year,monthySalary,savingAmount)

Queries
Display annual salary for each year
SELECT MonthlySaving.yearr, Sum(MonthlySaving.monthlySalary) AS SumOfmonthlySalary
FROM MonthlySaving
GROUP BY MonthlySaving.yearr;
Display total expense amount on each expense category yearly
SELECT expenseCategory.expenseTypeID, expenceDetails.yearr, Sum(expenceDetails.totalAmount) AS
SumOftotalAmount
FROM expenceDetails INNER JOIN expenseCategory ON expenceDetails.expenseTypeID =
expenseCategory.expenseTypeID
GROUP BY expenseCategory.expenseTypeID, expenceDetails.yearr;
Display total expense amount on each expense category monthly
SELECT expenseCategory.expenseTypeID, expenceDetails.monthh, Sum(expenceDetails.totalAmount) AS
SumOftotalAmount
FROM expenceDetails INNER JOIN expenseCategory ON expenceDetails.expenseTypeID =
expenseCategory.expenseTypeID
GROUP BY expenseCategory.expenseTypeID, expenceDetails.monthh;
Display total expenses, annual salary and total saving amount yearly
SELECT expenceDetails.yearr, Sum(expenceDetails.totalAmount) AS Total_Expense,
Sum(MonthlySaving.savingAmount) AS Total_Saving, Sum(MonthlySaving.monthlySalary) AS
Annual_Salary
FROM MonthlySaving, expenceDetails INNER JOIN expenseCategory ON expenceDetails.expenseTypeID
= expenseCategory.expenseTypeID
GROUP BY expenceDetails.yearr;

Display total saving by year
SELECT MonthlySaving.yearr, Sum(MonthlySaving.savingAmount) AS Annual_Saving_Amount
FROM MonthlySaving
GROUP BY MonthlySaving.yearr;