Name: _________________________________      Grade: _______________

Time: one hour and 20 minutes.

Answer all questions.  Five pages, four questions.

Notes: open only lecture notes and course-related documents posted in the course web page.

(1) A very small library wants a database to manage its collection.  A patron may check out books or search for books by authors, titles or categories from the computer.  A book may belong to more than one categories.  There may be more than one copies of the same book.  A librarian checks out books for the patrons as well as enters book collection information into the system.  Password protection is used to identify librarians.  A system administrator handles account information.  The library stores information about other libraries in the surrounding areas for inter-library loans.  There is at least one librarian who serves as the contact person for the surrounding libraries.

(a) Develop a reasonable use-case diagram to model the problem.(1) A very small library wants a database to manage its collection.  A patron may check out books or search for books by authors, titles or categories from the computer.  A book may belong to more than one categories.  There may be more than one copies of the same book.  A librarian checks out books for the patrons as well as enters book collection information into the system.  Password protection is used to identify librarians.  A system administrator handles account information.  The library stores information about other libraries in the surrounding areas for inter-library loans.  There is at least one librarian who serves as the contact person for the surrounding libraries.

(b) Construct a UML class diagram to model the database application by showing the classes, associations and multiplicities.  It is not necessary to show the attributes.
 

(2) Write a PL/SQL block to print out all voting questions and their numbers of choices to Oracle SQL*PLUS using the DBMS_OUTPUT package.  The output should looks like this:

Questions with number of choices:
What is your most favorite pet? : 5 choices.
Which parties do better in the Congress? : 3 choices.
Who should be the president of the United States? : 5 choices.
 

(3) Write a function AccountPersonName (Account Administrator.AccountName%TYPE) return VARCHAR2 to return the name of the person of the given administrator account name.  For example, if the tuple (100, 'Bun', 'Yue', 'yue@cl.uh.edu') is in the relation Person and the tuple ('yue',100, 'photos') is in the relation Administrator, then calling the function AccountPersonName('yue') will return the string 'Bun Yue'.    (4) It is decided to speed up the computation of voting statistics by creating a new table Vote_Stat(ChoiceID, VotedTimes). Both attributes are of the data types NUMBER(6).  The variable VotedTimes stored the number of times a ChoiceID has been voted.

(a) Give the SQL code to create the table appropriately.

(b) Analyzing the application, it is found that votes are never updated or deleted from the table Vote (H1_Vote in the homework).  Also, nearly most of the times, votes are added one vote per each SQL statement.  In rare occasions, many votes may be added in one SQL statement.  Write an efficient trigger to ensure that Vote_Stat is correctly updated when votes are added.
 

Use this relation schema for Questions 2 to 4.

Consider the tables for the voting problems in homework #1 & 2 with the following schema.

Person(PersonId, FirstName, LastName, EMailAddress)

Adminstrator(PersonId, AccountName, Password)
  PersonID is a foreign key of Person(PersonID).
  PersonID is a candidate key of Administrator.
  Alternatively, PersonID can be the primary key.

Vote(VoteID, PersonID, VoteTime, IPAddress, ChoiceID, VotingProblemID)
  PersonID is a foreign key of Person(PersonID).
  ChoiceID is a foreign key of Choice(ChoiceID).
  VotingProblemID is a foreign key of VotingProblem(VotingProblemID).

Comment(CommentID, VoteID, Description).
  Alternatively, Comment may be an attribute in Vote.
  VoteID is a foreign key of Vote(VoteID).

VotingProblem(VotingProblemID, SetterID, SetDate, Question, IsCurrent)
  SetterID is a foreign key of Administrator(PersonID).

Choice(ChoiceID, Order, Description, VotingProblemID)
  VotingProblemID is a foreign key of VotingProblem(VotingProblemID).

Keyword(KeywordID, Name, Description)

ProblemKeyword(VotingProblemID, KeywordID)
  VotingProblemID is a foreign key of VotingProblem(VotingProblemID).
  KeywordID is a foreign key of Keyword(KeywordID).

PostTime(PostTimeID, StartTime, EndTime, VotingProblemID)
  VotingProblemID is a foreign key of VotingProblem(VotingProblemID).

 In the homework, all relations have the prefix "H1_", you may or may not include this prefix in your anwer.