Question

EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. Each store can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have ten copies of the movie Titanic. In this case, Titanic would be an instance in the MOVIE table, while each rentable copy would be an instance in the VIDEO table. A rental transaction (RENTAL) involves one or more VIDEOs being rented to a member (MEMBERSHIP). A video can be rented many times over its lifetime, therefore, there is a M:N relationship between RENTAL and VIDEO. The DETAIL_RENTAL table is the bridge table to resolve this relationship. When a video is rented, one can obtain the daily rental fee and late fees from the PRICE table since the VIDEO is connected to PRICE through the MOVIE table. The E-R diagram is shown in the figure below MEMBERSHIP DETAIL RENTAL pk İmermid pk, fki rent id pk, fk2 vid id mem fname mem Iname mem street mem city mem state mem zip mem balance RENTAL detail fee detail duedate detail returndate detail_dailylatefee pk rent id rent date fk1 mem id MOVIE PRICE pk movie id VIDEO movie title movie year movie cost movie genre pk price id pk vid id price description price rentfee price dailylatefee vid indate fkl movie id kl price id Your objective in this assignment is to (a) create the SQL statements that will, when executed, create a database containing the tables and their relationships as shown above; and (b) to create SQL statements necessary to populate those tables with data. Use the SQLite Database Browser to enter and run your SQL statements to create your database. You must first click the "New Database" button and give your database a name. Then, dismiss the window that wants to build a table. Click the SQL tab, put your SQL code in the appropriate area, and click the run button to execute it Choose data types for the fields within the tables based on the type of field and data stored. Create primary keys for all tables, and foreign keys where appropriate. Once the tables are created, populate them with data using the data file provided alongside this document in Blackboard. The data file contains

Answer 1

CREATE TABLE MEMBERSHIP (

mem_id INT PRIMARY KEY NOT NULL,

mem_fname TEXT,

mem_lname TEXT,

mem_street TEXT,

mem_city TEXT,

mem_state TEXT,

mem_zip TEXT,

mem_balance REAL

);

CREATE TABLE RENTAL (

rent_id INT PRIMARY KEY,

rent_date DATE NOT NULL,

mem_id INT NULL,

FOREIGN KEY mem_id REFERENCES MEMBERSHIP (mem_id)

);

CREATE TABLE PRICE (

price_id INT PRIMARY KEY,

price_description TEXT,

price_rentfee REAL,

price_dailylatefee REAL

);

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 3 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.