Home Account Application
This is a small application is used to keep track of income and spending (expenditure) of a house. This application can have the different types of payments you do, to see how much you spend in each category every month. What is the total income of yours in a particular month or six month etc etc.
We need to create the following tables. Each table has some description and examples so that we will get a better picture of the application.
The TRAN_CODES table is used to hold the reason for the spending, example we are paying electric bills, phone bills, rents etc ect. Instead of typing all the stuff every month we can group some of the expenditure in various codes.
Example
REN - Rent is the Description
SHP -
Shopping
GIF - Gifts
MOT - Mortgage
TRAN_CODES
| COLUMN_NAME | CONSTRAINTS |
| TRAN_CODE | char(3) primary key not null |
| TRAN_SHORT_DSC | varchar(10) not null |
| TRAN_LONG_DESC | varchar(50) null |
This table is used to hold the different types of payments you made while purchasing stuff. Per example we can have just CREDITCARD, CHECK, CASH as payment types or if you have more than one credit card you can have MASTERCRD, AMEXCRD,NATVISACRD, FIRSTVISA, CHECK, CASH etc. This value is refered in the accounts table to track on which mode we pay the money.
PAYMENT_TYPE
MASTERCRD - Master Card
AMEXCRD - Master Card
NATVISACRD - Nations Bank
Visa Card
FIRSTVISA - First USA Visa Card
CHECK - Check Payment
CASH -
Cash Payment
| COLUMN_NAME | CONSTRAINTS |
| PAYMENT_TYPE | CHAR(10) primary key not null |
| PAYMENT_DESC | VARCHAR(20) not null |
This table will have the incoming money like salary, gift checks etc etc. The incoming date is nothing but income_date like 01-04-2000 2000$ as salary, 01-05-2000 1234$ etc etc. If you get 2 different incomes on the same date i.e. one of your salary and another your wife salary then in the same date you will have 2 records but your seq_no will be 1 and 2. This is the reason we have to make income_date and seq_no together as Primary Key.
MONEY_IN
| COLUMN_NAME | CONSTRAINTS |
| INCOME_DATE | date this is used to keep track of which month we earned this money |
| SEQ_NO | integer unique with in the same month and year of income_date |
| DESC | varchar(50) From where or how we earn this money |
| AMOUNT | number(9,2) Amount we earned. |
This is used to record the day to day expenditure. If you spend grocery on 04-10-2000 and also gas on the same date then your ACT_DATE is same where as seq_no will be 1 and 2. This is the reason we need to create the primary key of accounts table as ACT_DATE and SEQ_NO. The TRAN_CODE is a foreign key which refers to the primary key on TRAN_CODES table. The PAYMENT_TYPE is a foreign key which refers to the primary key on PAYMENT_TYPES table.
ACCOUNTS
| COLUMN_NAME | CONSTRAINTS |
| ACT_DATE | not null DATE date on which you spend the money. |
| SEQ_NO | not null integer unique with in the same date. |
| TRAN_CODE | not null char(3) foreign key to the tran_code in TRAN_CODES table. |
| PAYMENT_TYPE | not null char(10) foreign key to the payment_type in PAYMENT_TYPE table. |
| DESC | varchar(50) Description of the purchase or spending. |
| AMOUNT | number(9,2) Amount Spend |
This table gets poulated every month end. If you want to calculate all over again then we can truncate the table and recalculate and load the data to this table.
ACT_SUMMARY
| COLUMN_NAME | CONSTRAINTS |
| MONTH_YEAR | char(6) in 'mmyyyy' format |
| INCOME | number(9,2) not null > 0 |
| SPENDING | number(9,2) amount sum from accounts |
Creating Tables
Create table TRAN_CODES as per the column definition given above.
Create table PAYMENT_TYPES as per the column defintions.
Create table MONEY_IN as per the column definitions.
Create table ACCOUNTS
Create table ACT_SUMMARY
Modify Existing Table
Add another column called crt_date to tran_codes table.
Alter the tran_codes table and make the long_tran_desc as a not null column.
Alter table accounts add a check constraint to the amount column to accept only the value greater than 0.
Alter table accounts and make the ACT_DATE and SEQ_NO together as primary key.
Creating Views
Create a view to display ACT DATE, ACCOUNTS.SEQ_NO, TRAN_SHORT_DSC, ACCT DESC and AMOUNT from ACCOUNTS and TRANS_CODES table.
From the view try to display the date and amount spend for each date.
From the view display the amount spent on transaction wise for the current month.
Write a stored procedure which insert rows into act_summary table at the end of every month.
Write a function which returns the sum of income for a particular month and year in 'mmyyyy' format.
Write a function which returns the sum of income for a date ( just manipulate with month and year of the date)
Write a function which returns the sum of spending for a particular month and year in 'mmyyyy' format.
Write a function whcih returns the sum if spending for a date ( manipulate with month and year of the date)
Write a function which returns the sum of spending for a particular month and tran_code, send the month and year in 'mmyyyy' format.
Write a function which returns the sum of spending for a particular date and tran_code, (manipulate the month and year in 'mmyyyy' format).