- 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.
- 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'.
- 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.
- 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.