Projects

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

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

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

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

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

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).

Movie Complex Software

In order to understand how exactly a business uses the database as a there place to store and retrieve the information. So we will take an example of a movie complex business business.

In order to maintain the shows, show timings, ticket sales we need to create the following tables.

Create the following tables to practice PL/SQL writing.

movie_complex table stores the information about the complexes. Following are the attributes of the table.

movie_theaters – each complex can have more halls, so now we need to have another table to store all the halls in each complex. So we will create a primary key which includes complex_id, hall_no columns.

We will create a table in which we will have all the movies played in the movie complex.

show_timings – each hall runs a particular show for a period of time,

day_to_day_shows

movie_tickets

Help Desk Application