CSCE 4350 Database Design
Assigment 4
Total: 90 points
Issued: 11/11/2007
Due: 11/27/2007
................................


1. (20 points) Text book 10.2, 10.3
       10.2
       Give the DTD for an XML representation of the following nested-relational schema:
              Emp = (ename, ChildrenSet setof(Children), SkillsSet setof(Skills))
              Children = (name, Birthday)
              Birthday = (day, month, year)
              Skills = (type, ExamsSet setof(Exams))
              Emaxms = (year, city)
       10.3 Write a query in XPath on the DTD of Practice Exercise 10.2 to list all skill types in Emp.
         

2. (20 points)  Follow the following link to complete this question.
      http://www.cse.unt.edu/~huangyan/4350/Labs/Xquery.htm


3. (10 points)  The following two statements:
          U1: update branch set assets = assets - 100 where branch_name = 'Brighton';
          U2: update branch set assets = assets + 100 where branch_name = 'Downtown';
    
       (1) Log into your oracle account, use the banking database you created before.
       (2) Query the assets of 'Brighton' and  'Downtown' branches.
       (3) Type in "set autocommit on" under SQL prompt
       (4) Execute U1 then U2. Look at the assets of both branches.
       (5) Under SQL prompty, type in "set autocommit off"
       (6) Execute U1 then U2 again. Look at the assets of both branches.
       (7) Under SQL prompty, type in "roll back"
       (8) Exam the assets of  'Brighton' and  'Downtown' branches again.


     Explain what you see in each step.

4. (40 points)  
       (1) What is a transaction?
       (2) What is a schedule?
       (3) Explain what is conflict-serialiable of a schedule?
       (4) Given following transactions:
           T1: r1(x)w1(y)r1(x)r1(z)r1(y)w1(z)
           T2: w2(x)r2(x)r2(y)w2(y)
           T3: r3(x)r3(y)r3(z)w3(x)w3(z)

           and following schedules:

           S1: r1(x)w1(y)w2(x)r1(x)r2(x)r1(z)r3(x)r1(y)w1(z)r3(y)r2(y)w2(y)r3(z)w3(x)w3(z)
           S2: r1(x)w1(y)r1(x)w2(x)r2(x)r1(z)r3(x)r1(y)w1(z)r2(y)w2(y)r3(y)r3(z)w3(x)w3(z)

       Draw the precedence graph for each schedule. Which schedule(s) are conflict-serializable?


Submission instructions: 
   - Submit a hardcopy including answers to problems 1,3,4
   - For problem 2
         - Submit a file including the XQuery and result XML documents the four queries
         - user'project' program to submit. class code is 4350s001 project XML.