1. (20 points).  You have to design the database for  a video -rental. The shop rents movies ( DVDs and  VHS videos), and also sales new and used movies to the public. In order to rent  a movie, a person must be enrolled at the store. The store has different branches, and  a person  enrolled in one branch  could rent a video at another branch. The database will also store, for each person, the movies she/he rented, the branch were it was rented, when were these movies returned, and so on. In summary, the database must support all the usual operations occurring at a video rental store. You will design the Entity-relationship model for this database, and its corresponding relational model. The number of entities and relationships should not exceed 10 and 20, and should not be less than 5 and 10, respectively.

2. (20 points)  Give an equivalent relational model for the following E/R diagram: click here.

3. (a) (25  points.) You are asked to design the database that underlies the Amazon.ca's  website (http://www.amazon.ca)  using the E/R model. Your design should capture the essential entity and relationship sets, including books, videos, DVDs, CDs, and should also be able to support billing operations, including the "shopping cart". You must decide what information to leave out in order to keep the design down to a manageable size, of the order of about 10 entity sets and 20 relationship sets. Note, however that this is JUST an estimation. 

- Note that this assignment is open-ended. There is no single right answer, as the site is complex and different students will choose to include/exclude different aspects of it. 

- The first step is to browse and query the web site to understand what information is provided, how it is organized, and what the constraints are. 

- The next step is to give a detailed E/R diagram describing your design. Explain any constraints that are not obvious.. Names given to entities, attributes and relationships should be clear enough in order to keep textual explanation to a minimum. Also list the information that you decide not to include in your design, explaining why.

(b) (35 points.) Write an SQL database schema for the database of part (a)  using the appropriate CREATE TABLE commands. Pick suitable data types for each attribute domain. Hand in a printout of the commands you use to create your database schema. Show the response of db2 to a request to describe each of your relation schemas. Use the input file, output file and history file mechanism to record your input SQL commands and the system's output. 

Execute five INSERT commands to insert tuples into one of your relations. Show the response of db2 and the relation that results when you issue a SELECT * command.
Develop a substantial amount of data for your database and load it into your relations using the SQL IMPORT command. See the db2 tutorial  for information on how to bulk-load data. To create the data, write a program in any programming language you like that creates large files of records in ASCII delimited format as expected by the IMPORT command. Load the data into your relations. If you are using real data, your program will need to transform the data into files of records conforming to your  schema. Otherwise, write a program to fabricate data; your program will generate either random or nonrandom (e.g., sequential) records conforming to your schema. Note that it is both fine and expected for your data values--strings especially-- to be meaningless gibberish. The point of generating large amounts of data is so that you can experiment with a database of realistic size, rather than the small "toy" databases often used in classes. The data you generate and load should have the following characteristics: 

·At least two relations with thousands of tuples 

·At least one additional relation with hundreds of tuples 

When writing a program to fabricate data, there are two important points to keep in mind:

1. Be sure not to generate duplicate values for key attributes. 

2. Your database will almost certainly include relations that are expected to join with each other.  In generating data, be sure to generate values that actually do join--otherwise all of your interesting queries will have empty results! One way to guarantee joinability is to generate the values in one relation, then use the generated values to select joining values for the other relation. For example, suppose  you have a student, course and enrolled  relations. You could generate course numbers first (either sequentially or randomly), then use these numbers to fill in a courseNo values in the enrolled  relation. 

Submit a tar.gz file containing the following: