LOGISTICS AND LATE POLICY REMINDER

• Turn-in: Please turn in your written work at the beginning of class. Please put your name and preferred email address on every page that you turn in - you'd be surprised how many times assignments mysteriously come apart. Please remember that written work will not be accepted electronically.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams may be neatly hand drawn.

• Deadline: The on-time deadline for all students is in class on the due date.

• Late policy: All written work is to be turned in during class on the day that it is due. Written work turned in after the deadline may be handed in at the beginning of the very next class will be accepted but penalized 50%, except that the penalty will not apply to the first two assignments handed in late.

THE ASSIGNMENT

1. The following questions are based on an "auction" database, with the following relations:

        Bids(auctionID, bidder, price, quantity)
        Auctions(auctionID, seller, item, quantity, expires)
        Ratings(seller, stars)
   

   The relations and attributes should be self explanatory, except perhaps for the last relation, which is intended to be a table of ratings that sellers have received from buyers. Each seller mentioned in Auctions has received 0 or more ratings, and each rating is in the form of a number of stars, from 1 to 5.

   (a)

   Define a view AuctionInfo(auctionID,item,avgStars) that gives the average number of stars of the seller of the auction with ID auctionID. However, if the seller has never been rated (i.e., doesn't appear in Ratings), then avgStars should be NULL.

   (b)

   Arrange that each seller bids 0 for the total number of items in the auction (i.e., perform an insertion into Bids based on information in Auctions).

   (c)

   Bidder "Sally" wishes to raise all her bids so they are $1 more than the highest bid so far in each auction in which she is participating. Show how to modify Bids to perform these changes.

   (d)

   Compute the average ratings of all the sellers in the Ratings table. Note that different sellers may be rated different numbers of times, so you cannot simply average the numbers of stars in each of the tuples of the relation.

2. We will be working with the following Movies database schema in the following eight questions.

   Movies(Title, Year, filmType, DirectedBy)
   Stars(Title, Year, StarName, Salary)
   Oscars(Title, Year, OscarAwardType, AwardedTo)
   BoxOffice(Title, Year, City, GrossSales)
   Ratings(Title, Year, Reviewer, Score)
   

   The Movies relation contains the list of all movies. The value of the filmType attribute may be 'horror', 'romance', 'thriller' etc. (Title, Year) form a key in the Movies relation. The Salary attribute in the Stars relation is the amount paid to StarName for working in the movie: (Title, Year). The Oscars relation has a tuple for each Oscar awarded to a movie. The only awards of interest to us are the Best Actor, Best Actress, and Best Director awards. A typical record in the Oscars relation would look like

   (Cleopatra, 1963, Best Actor, Rex Harrison).

   The BoxOffice relation records for each movie its gross sales at the box office for each city. Also, each movie is rated at a scale of 1-10 by many reviewers. The rating information is kept in the relation Ratings. Write SQL statements to produce tables that contain the following information. You may use views to store intermediate results.

   a)

   A table containing for each year, the movie that has the maximum TotalSalaryCost (total salary paid to stars).

   b)

   A table containing total gross sales of all movies that won some Oscar award in the 1960s.

   c)

   A table containing, for each director, the total number of his/her movies that have won an Oscar.

   d)

   Above question, except that consider only those movies that were rated 7 or above by at least two reviewers. You are REQUIRED to use views to answer this one.

   e)

   A table containing, for each star, the total number of movies for which he/she has won a Best Actor/Actress Oscar AND that have won at least two Oscars. You need not include any items for which this count is zero.

   f)

   A table containing the list of all directors who received the "Best Director" award, even though the movie for which they received the award had an average rating of less than 5.

   g)

   Write an UPDATE statement that changes the filmType of a movie that has won an Oscar to 'OscarWinner'.

   h)

   Write an INSERT statement to add, for each movie, one more rating score, equal to the average rating for that movie, to the relation Ratings. Let the reviewer for the newly added rating be 'AverageScore'.

3. You will be working with the following schema for the rest of this assignment.

   Employee(SSN, name, salary, DNo) 
   Department(DNo, DeptName, MgrSSN) 
   Project(PNo, location, ProjName) 
   HourLog(SSN, PNo, hours) 
   

   The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department's manager (MgrSSN). Each department has a only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo.

   a)

   Write an SQL declaration for the Department relation that expresses the following constraint: "Each department has one unique DNo."

   b)

   Write an SQL declaration for the HourLog relation that expresses the following constraint: "All employees that appear in the HourLog relation must also appear in the Employee relation."

   c)

   Write an SQL declaration for the Employee relation that expresses the following constraint: "Each employee must have a non-NULL name, and his/her salary must be no less than $30,000."

   d)

   Do (a) again by using a tuple-based check constraint in SQL.

   e)

   Do (b) again by using an attribute-based check constraint in SQL.

   f)

   Use an assertion in SQL to specify: "Each manager should have spent at least 100 hours on some project(s)."

   g)

   Use an assertion in SQL to specify: "Each employee who works for at least 200 hours on some project(s) should get a salary higher than $50,000."

   h)

   Write one trigger in SQL that prevents the average salary of the employee in the "CS" department from dropping below $60,000 by any deletion. You can only use the "for each row" option.

   i)

   Do (g) again. You can only use the "for each statement" option.

   j)

   Consider the referential integrity constraint in (b). Suppose we want to enforce the cascade policy for deletions on relation Employee. Write one or more triggers in SQL that implement this enforcement policy.

4. Views in SQL are virtual, meaning that they are not stored in the database but rather their definitions are used to translate queries referencing views into queries over base relations. One disadvantage of this approach is that views may effectively be computed over and over again if many queries reference the same view. An alternative approach is to materialize views: the contents of a view are computed and the result is stored in a database table, so that a reference to the view in a query can simply access the stored table. However, when contents of a base relation referenced in the view change, then the contents of the materialized view must be modified accordingly. For example, consider a base relation R(A,B), where A and B are of type integer and A is a key for R. A materialized view V that contains those tuples of R satisfying R.A > 5 can be created as follows:

   CREATE TABLE V (A int, B int)

   Initially, V is populated using the following SQL statement:

   INSERT INTO V
   SELECT * FROM R WHERE A > 5

   Now when we refer to view/table V in queries we obtain the desired result.

   If an INSERT statement is executed on R, then V must be modified accordingly. This behavior can be implemented using a trigger:

   CREATE TRIGGER VinsR
   AFTER INSERT ON R
   REFERENCING NEW_TABLE AS NT
   INSERT INTO V
   SELECT * FROM NT WHERE A > 5

   (a) Write another trigger VdelR to modify V after tuples are deleted from R.

   (b) Write another trigger VupdR to modify V after tuples in R are updated.

   (c) Write SQL statements and triggers to create, populate, and maintain a materialized view V that projects columns A and B from a base relation R(A,B,C). You may assume that all three attributes are of type integer and that A is a key for R.