Project 2

Project 2

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 are 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 MEMBERSHIP (

MEM_NUM CHAR(3) PRIMARY KEY,

MEM_FNAME VARCHAR2(15) NOT NULL,

MEM_LNAME VARCHAR2(15) NOT NULL,

MEM_STREET VARCHAR2(40),

MEM_CITY VARCHAR2(30),

MEM_STATE CHAR(2),

MEM_ZIP VARCHAR2(10),

MEM_BALANCE CHAR(2));

CREATE TABLE RENTAL (

RENT_NUM CHAR(4) PRIMARY KEY,

RENT_DATE DATE,

MEM_NUM CHAR(3)

FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);

CREATE TABLE PRICE (

PRICE_CODE CHAR(1) PRIMARY KEY,

PRICE_DESCRIPTION VARCHAR2(15),

PRICE_RENTFEE NUMBER(2,1),

PRICE_DAILYLATEFEE NUMBER(2,1));

CREATE TABLE MOVIE (

MOVIE_NUM CHAR(4) PRIMARY KEY,

MOVIE_TITLE VARCHAR(40),

MOVIE_YEAR CHAR(4),

MOVIE_COST NUMBER(4,2),

MOVIE_GENRE VARCHAR2(15),

PRICE_CODE CHAR(1),

FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);

CREATE TABLE VIDEO (

VID_NUM CHAR(5) PRIMARY KEY,

VID_INDATE DATE,

MOVIE_NUM CHAR(4),

FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);

CREATE TABLE DETAILRENTAL (

RENT_NUM CHAR(4),

VID_NUM CHAR(5),

DETAIL_FEE NUMBER(2,1),

DETAIL_DUEDATE DATE,

DETAIL_RETURNDATE DATE,

DETAIL_DAILYLATEFEE CHAR(1),

PRIMARY KEY (RENT_NUM, VID_NUM)

FOREIGN KEY (RENT_NUM) REFERENCES RENT,

FOREIGN KEY (VID_NUM) REFERENCES VIDEO);

68. Write the SQL command to change the movie year for movie number 1245 to 2010.

UPDATE MOVIE

SET MOVIE_YEAR = 2010

WHERE MOVIE_NUM = 1245;

69. Write the SQL command to change the price code for all action movies to price code 3.

UPDATE MOVIE

SET PRICE_CODE = 3

WHERE MOVIE_GENRE = ‘Action’;

70. Write a single SQL command to increase all price rental fee values by $0.50.

UPDATE PRICE

SET PRICE_CODE = PRICE_CODE +...