Oracle assignment on keys/indexes/constraints with some views/reports thrown in
First problem: I have two tables with no keys associated with them. I want
to put a primary key on the first table and then put a foreign key on a column called
DEPTNO which will link into the second table. Explain what I have to do to accomplish this.
(Include both tables in your discussion.)
Second problem: Create a table using all 5 of the constraints explained. Test each
of the constraints by adding a record that is valid and by adding records that violate each
of the constraints (test each one separately).
Third problem: Create a table with no constraints. Alter the table by adding each
of the five constraints covered. Again test with a valid record and a records that violate
each of the constraints (test each one separately).
Fourth problem: Add an index to an existing table or create a new table and put an index
on it.
Fifth problem: Assume that you run an oil company. The information that you have
includes: customer id number, customer name, customer address(need easy access to street,
city, state and zip), capacity of tank, house type code (for each house type code carry the
average summer, fall, spring and winter use), current charge, amount past due, delivery
information for the last five years including the date of the delivery, the number of gallons
delivered and the price per gallon for each delivery that is made.
Set up the necessary tables in a relational database. Use primary indexes on all tables. Use
each of the five constraint types that were discussed. (Use "creative" license if needed).
Put data in each of the tables.
Needless to say, the tables should be a well designed relational database.
Sixth problem: Create a view containing information from three tables.
Seventh problem: Create a report based on the view. The report should include headers,
totals, breaks and three other components included in my examples.