This is a take-home exam that is due by 8:45 pm on Wednesday, March 27, 2002. I’ll be in class (UH272) 5:00 – 8:45 pm on Wednesday, March 27. You’re to complete the exam on your own. Any evidence of collaboration will result in failing grades for the course for all parties involved. So guard your test. If you have to turn in your test earlier, ask Jo Ann to put it on my desk in my office. NO EMAIL SUBMISSION FOR THE TEST WILL BE ACCEPTED.

  1. A recreational basketball league for children divides kids into different divisions according to their grade level. Kindergartners are in Division A. The first and second graders are in Division B. And so on. Each division has a manager and multiple teams. Each team is coached by a volunteer who decides which professional team name to adopt for his/her team. The managers of all division have meetings before the season starts to decide schedules of games. Assume that all games are played on the same court. All games are refereed and scores are kept. However, the lengths of games differ from one division to another. At the end of the regular season, the teams will be ranked and selected for playoffs. The following list the structure of the tables. The boldfaced attributes indicate primary key. Assume that all tables are already created in a tablespace called "Basketball" in the database.
    Table Attributes Description
    Divisions Division_Id Id of a division
    Manager_Name Name of the manager for the division
    Manager_Email Email address of the manager
    Game_Length Number of minutes for each game in the division
    Teams Division_Id Id of the division to which a team belongs
    Team_Name Name of team
    Coach_Name Name of coach for the team
    Coach_Email Email address of the coach
    Number_Players Number of players in the team 
    Games Division_Id Id of the division that host the game
    Game_Date Date when the game is played
    Start_Time Time when the game starts
    Visitor Name of the visitor team
    Home Name of the home team
    Visitor_Score Points scored by the visitor team
    Home_Score Points scored by the home team
    Referee_Id Id of the referee for the game
    Referees Referee_Id Id of a referee
    Referee_Name Name of the referee
    Referee_Email Email address of the referee
    Referee_Qualification The highest division the referee is qualified for

    1. (15 points) Assume that the only responsibilities of the managers with regard to the database are to enter/modify game schedules before the season starts and view the ranking of teams. It is up to the referees to enter the scores of the games after games are played. List the SQL statements that are necessary to create a user account for the manager in Division B and grant him/her the minimum system as well as object privilege(s) to perform his/her tasks. Do not use any existing role. Remember, a manager should be allowed to access only the information for his/her own division.

    2. (20 points) Create a trigger on the "Games" table to ensure that the names of teams are valid within the division. Here's an example that illustrates violation of the constraint. There's a LA Lakers team in Division A but not in Division B. A game in Division B that shows LA Lakers as either home or visitor team is a violation of the constraint. 

    3. (10 points) After all game schedules are entered into the system, the manager in Division A decides to change the start time of three games. Write a transaction that attempts to update the games. If any of the new game time violates the primary key constraint of unique combination of "Game_Date" and "Start_Time", rollback all updates. Make sure that you use exceptional handling provided by PL/SQL to catch the exception.

    4. (10 points) Assume the three new games in c did not result in any time conflict. So the three updates statements were executed successfully. However, before the transaction reaches its commit point, a failure occurs. Discuss any redo and/or undo when the recovery technique being used is deferred update and immediate update, respectively.

    5. (15 points) Create a transaction that allows a referee to enter game scores and another transaction that allows a coach to see ranking of his/her team. Identify an interleaving schedule of the two transactions to illustrate the inconsistent analysis problem when dirty read is allowed. Explain whether the "SERIALIZABLE" isolation level can't prevent the inconsistent analysis problem.

    6. (15 points) Write a SQL query to help the manager in Division A to list the date and time of all April games in his/her division. In addition, for each game, this query shall also display the email address of both the home team coach and referee. Draw a relational algebra tree for the query and use the heuristic rules to transform the query into its most efficient form.

  2. (15 points) Draw a precedence graph for the following schedule. Explain whether the schedule is conflict serializable and view serializable. 

    S = [R1(Z), R2(Y), W2(Y), R1(X), W1(X), W1(Z), R2(X), R1(Y), W1(Y), W2(X)]

    Ri(Z)/Wi(Z) indicates a read/write by transaction i on data item Z.

  3. The end! Have a great spring break!