StudyModeUpload
Homework Assignment #4
Problems #65 - #94 from page 311. Please provide your answer after each problem and submit the file with your answers through Blackboard.
EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. 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. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P7.65.
Figure P7.65 EliteVideo ERD
65. Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.
CREATE TABLE PRICE ( PRICE_CODE INTEGER NOT NULL UNIQUE, PRICE_DESCRIPTION CHAR(15) NOT NULL, PRICE_RENTFEE VARCHAR(3) NOT NULL, PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL, PRIMARY KEY (PRICE_CODE)); CREATE TABLE MOVIE ( MOVIE_NUM INTEGER NOT NULL UNIQUE, MOVIE_TITLE VARCHAR(35) NOT NULL, MOVIE_YEAR INTEGER NOT NULL, MOVIE_COST NUMBER NOT NULL, MOVIE_GENRE CHAR(6) NOT NULL, PRICE_CODE INTEGER, PRIMARY KEY (MOVIE_NUM), FOREIGN KEY (PRICE_CODE)); CREATE TABLE VIDEO ( VID_NUM INTEGER NOT NULL UNIQUE, VID_INDATE DATE NOT NULL, MOVIE_NUM INTEGER NOT NULL, PRIMARY KEY (VID_NUM), FOREIGN KEY (MOVIE_NUM));
CREATE TABLE DETAILRENTAL ( RENT_NUM INTEGER NOT NULL,
Problems #65 - #94 from page 311. Please provide your answer after each problem and submit the file with your answers through Blackboard.
EliteVideo is a startup company providing concierge DVD kiosk service in upscale neighborhoods. EliteVideo can own several copies (VIDEO) of each movie (MOVIE). For example, the store may have 10 copies of the movie “Twist in the Wind”. “Twist in the Wind” would be one MOVIE and each copy would be a VIDEO. 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. DETAILRENTAL is the bridge table to resolve this relationship. The complete ERD is provided in Figure P7.65.
Figure P7.65 EliteVideo ERD
65. Write the SQL code to create the table structures for the entities shown in Figure P7.65. The structures should contain the attributes specified in the ERD. Use data types that would be appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.
CREATE TABLE PRICE ( PRICE_CODE INTEGER NOT NULL UNIQUE, PRICE_DESCRIPTION CHAR(15) NOT NULL, PRICE_RENTFEE VARCHAR(3) NOT NULL, PRICE_DAILYLATEFEE VARCHAR(3) NOT NULL, PRIMARY KEY (PRICE_CODE)); CREATE TABLE MOVIE ( MOVIE_NUM INTEGER NOT NULL UNIQUE, MOVIE_TITLE VARCHAR(35) NOT NULL, MOVIE_YEAR INTEGER NOT NULL, MOVIE_COST NUMBER NOT NULL, MOVIE_GENRE CHAR(6) NOT NULL, PRICE_CODE INTEGER, PRIMARY KEY (MOVIE_NUM), FOREIGN KEY (PRICE_CODE)); CREATE TABLE VIDEO ( VID_NUM INTEGER NOT NULL UNIQUE, VID_INDATE DATE NOT NULL, MOVIE_NUM INTEGER NOT NULL, PRIMARY KEY (VID_NUM), FOREIGN KEY (MOVIE_NUM));
CREATE TABLE DETAILRENTAL ( RENT_NUM INTEGER NOT NULL,