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:

You need to keep the assessed values:

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.

The bill needs to contain the following information:

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

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.

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

Secondary owners should be able to be listed on the bill.

Remember that a property can have multiple secondary owners and a owner (primary or secondary) can own multiple properties.

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.

First problem: Set up the necessary tables for this database and populate them with data. You need to create the appropriate primary keys and foreign keys. You need to demonstrate establishing the keys when you create the table and you also need to demonstrate altering the table to establish keys. Your choice where you decide to do each!

Second problem: You need to demonstrate each of the other constraints by using them on one or more of the tables. Again demonstrate constraints established when the table is created and constraints established after the table has been created. (If you didn't read this until after you had established all of the tables - create some random table and establish a constraint on it!)

NOTE: The syntax to use not null when you alter a table involves using modify.

Third problem: You need to establish a separate index on one of the fields on your tables.

Fourth problem: Test all of your constraints to make sure that they are acting appropriately.