HW#5 --- last modified March 02 2019 21:18:03..
Solution set.
Due date: May 17
Files to be submitted:
ObjectOriented.sql
MusicBand.dtd
IsolationTest.java
Hw5Problems.pdf
Purpose: To learn more about concurrency control. To experiment with
locking in Oracle. To experiment a little with OO and XML in Oracle.
Specification:
To begin do problems 9.2.4, 9.4.3, 9.5.2, 9.8.2 out of the book and submit them in the file
Hw5Problems.pdf .
For the coding part of the homework, I would like you to create a table in Oracle called MusicBand.
This table should have three columns: Name, BandFacts, Discography. Name should be a varchar. BandFacts
should be a composite type made using create type. Finally, Discography should be of XMLTYPE and you should define
a suitable DTD for listing a sequence of record albums. (Note: Oracle 8i without a patch doesn't support
XMLTYPE -- I had forgotten this. So if you are doing this on the school database make the DTD, but only
use a create type like BandFacts for this column.)
Insert half a dozen test rows into this table.
See the HW5 solutions from my 2003 version of this class for examples of using create type and XMLTYPE as well as
inserting into the resulting table. Submit your script for creating the table and inserting the rows as
ObjectOriented.sql . It should be noted that you aren't getting Oracle to do validation against your DTD,
you are creating a DTD to say you have done this once and to understand that such validation would be possible.
Next write a Java program IsolationTest.java which opens two separate connections to the Oracle database at school.
(If you write your program with your Oracle server at home, you should make sure it is clear how to get your
program to work
with the schools database by having a comment at the start of your code explaining what needs to be
changed.) Set
the isolation level for one connection to be read committed. Do this directly with an executeUpdate. Then do a
sequence of interleaving operations
between the two connections, printing to the screen what you are doing at each step.
Your interleaving operations should
be such that the connection which is in the read committed isolation level only performs reads, reads the same row
twice, but sees a different value each time. This is called an unrepeatable read. Next have your program close and
reestablish the two connections. For the connection which only performed reads the first time, now set the isolation
level to serializable. Have the program do
the same interleaving now as the first time, printing the results to the screen. This time the read should be the same
in both cases. What this experiment illustrates is how long Oracle holds locks automatically obtained
for a transaction is dependent on the isolation level. In the case of the read committed isolation level, Oracle can
release the shared lock right after the operation. It does not wait until the end of the transaction.
Point Breakdown
Departmental coding guidelines for Java followed |
1pt
|
Book problems (1pt each) |
4pts
|
MusicBand DTD | 1pt
|
MusicBand table as described and a couple of inserts done | 2pts
|
Isolation.java works as described | 2pts
|
Total | 10pts |
|