-
Write three PL/SQL stored functions or procedures (See the
PL/SQL Guide)
to perform operations on your PDA database.
Each should be nontrivial, illustrating a feature or features such as
local variables, multiple SQL statements, loops, and branches.
In addition, at least one should involve a cursor.
We encourage you to be imaginative.
However, here are some sorts of things you might try if you can't think
of something more interesting:
- a)
- Compute some aggregate value from a relation and use that
value to modify values in that or another relation.
- b)
- Create a new relation and load it with values computed from
one or more existing relations.
- c)
- Enforce a constraint by searching your database for violations
and fixing them in some way.
Submit two files: functions.sql
and functions.log. The first
file should contain the PL/SQL that declares your functions and
procedures. The
second file should contain be a script
recording of your sqlplus session showing
successful compilation of your function/procedures and the execution of
queries that use these functions/procedures successfully. You should
also include additional queries that
demonstrate the functions/procedures have
had their intended effect.
-
Write two Oracle Triggers.
(See
The Triggers Guide for a
synopsis of Oracle triggers.)
You should also check The
Guide to Nonstandard Oracle Features for some important restrictions on triggers.
Submit your triggers in a file triggers.sql
and a script triggers.log, showing the triggers declared.
Also, the script should show, for each
trigger, the effect of two database modifications.
One modification should trigger the trigger, and the other not.
Show in the script queries that demonstrate that the trigger has an
effect in the first case and not in the second.
-
Declare some data types (UDT's) that could be used with the data of your PDA.
(See
The Object-Relational Guide for a
synopsis of Oracle O-R features.)
You need to choose types such that:
-
There are at least two methods included among these types; not every
type must have methods, however.
-
At least one type is suitable as a column type in your application (in
an additional relation that you will add to your PDA --- see Part (4)
below --- not necessarily in
the existing relations).
-
At least one type is suitable as a row type in your application (for an
additional relation).
-
At least one type is a table type, defined from one of the other
types you declared.
For declarations, don't forget type bodies when there are methods.
Also remember that the slash is needed to cause compilation.
Submit two files: datatypes.sql
and datatypes.log. The first
file should contain SQL statements declaring different data types. The
second file should contain a recording of your sqlplus session showing
successful execution of your SQL statements.
-
Declare some relations that could hold data associated with your
PDA.
Choose relations so that:
-
At least one relation has a column type defined in (3)
for one of its attributes.
-
At least one relation should
be of a row type that you defined in (3).
-
At least one relation should have a table type defined in (3) for one of
its columns.
-
At least one reference type (REF) must be involved, either in
your relation declarations or in the types from (1) themselves.
Note that it is possible for one relation to satisfy all these
conditions, but it is up to you how many new relations you choose to create.
Write INSERT statements to populate your new relations from the
data in the original relations of your PDA.
Submit two files: relations.sql
and relations.log.
The first
file should contain SQL statements to create and to populate
relations. The second file should contain a recording of your sqlplus
session showing successful compilation of your declarations and
correct execution of your insertion statements.
-
Write some queries on your relations from (4).
Among these queries, you should demonstrate the following features at
least once:
-
Use of methods.
-
Access of data within a column type.
-
Use of a nested relation's value in a FROM clause (i.e.,
extract a nested table from the component of some tuple).
-
Following REF's.
Note:
Technically, you could write one query to do all of these operations,
but we suggest you write a larger number of simpler queries.
Submit two files: queries.sql
and queries.log
The first file should
contain SQL queries over your relations. The second file should
contain a recording of your sqlplus session showing successful
execution of your queries. Write brief descriptions of SQL statements
as comments in the .sql file.