Generate the names of all customers and the movies they have checked out.10/14/97
(The
e-mail archive might have more information related to this homework.)[10/8/97] Study the description of the "Movies" database passed out today and think about how you might design such a database in the relational model.
[10/10/97] Use Microsoft Access to implement the schema for "Movies" passed out today. I.e., create the tables with the indicated columns; use appropriate types; define keys and referential integrity constraints where appropriate. Populate the tables by importing the text files we will make available.
[10/15/97] Write queries in the Relational Algebra to answer the following questions (you may use aggregate functions and grouping if necessary). You do not have to run these in Access, or type them; it is enough to handwrite them, legibly. It is not necessary to show the actual table that would result.
Assume we want the name of an entity unless it says otherwise. So "list the customers" means "list the names of the customers", etc.
Generate the names of all customers and the movies they have checked out.Produce a list of movies checked out before a certain (fixed) date (say, 10/15/97) along with the customer who has checked out the movie.Generate a list of movies that have suppliers. Generate a list of movies that do not have suppliers.If Megavideo went out of business, what suppliers could supply all the same movies that Megavideo did?Generate a table that shows for each supplier the number of movies supplied and the minimum, maximum, and average price for a movie.Which customer has the most movies currently checked out?Which customers are we most concerned about whether they return their movies promptly? (We are "concerned" if the customer has the store's only copy of that movie checked out, and there is no copy of that movie on order). Do this without any aggregate functions if possible...For Friday, Oct. 17 Monday Oct. 20: Turn in at least 5 of these (any 5) in Relational Algebra. Please use the same numbers as above to identify them.
For Wednesday, Oct. 18 Friday Oct. 24: Turn in at least 5 of these written in SQL (please use the numbers above to identify them). Test them using Microsoft Access. For at least one, print off the resulting relation (hopefully one which is not too big!) For the others, just indicate how many rows were in the output relation.