Relational project:

You run the town tax department. You need to design the tables that you will need for the tax system. Try to think of the things that the town would need to know to calculate taxes and produce tax bills. Some things to consider are listed below. Feel free to add anything you think is missing or would make the system match the system in your city or town:


You need to be able to identify the property officially so you might keep the following information:

Property id # assigned by the town

Section #

Lot #

Volume and page of deed

Address

You need to keep the assessed values:

Assessed value for land

Assessed value for property

Assessed value for other

Different types of properties have different tax rates, for example homes, commercial properties and farms all are taxed at different rates. Tax rates are determined annually after the town budget has been established.

Tax type code

Tax type description

Tax rate

The bill needs to contain the following information:

Total amount due

Quarterly payment

You also need to deal with the fact that people do not pay their taxes on time and they may have accumulated past due:

Late fee

Outstanding/not paid taxes

Outstanding/not paid late fees

I am suggesting that the tax bill be sent to the primary owner who is the person or couple responsible for the payment. For this person I need the address and phone number. There may also be secondary owners of the property and I want to keep a list of them along with their addresses and phone numbers.

Owner (primary responsible for bill)

Secondary owner(s) list

Owner address (primary owner)

Owner phone (primary owner)

Comments:

For our example, assume one person can own multiple properties but each property has only one primary owner.

Secondary owners should be listed on the bill.

The tax type determines the tax rate tax rates are calculated each year after the town budget has been established.

Don't worry about dealing with payments.

Assignment: (due in two weeks)

  1. Using the rules for establishing a relational database, develop the tables that you will need to handle the taxes for the town. Include what you would use as the primary key for each table.
  2. Implement the tables that you designed in Oracle. That means setup the tables and put some data into them. Do not worry about officially defining indexes.
  3. Run 5 different queries using different features to access information from multiple tables. (At least one query should use three tables).

It would be reasonable to have lots of questions so email away. Also feel free to send me your initial design before you implement it in Oracle. I will get back to you with comments.