CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(30), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER (2)); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE (PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3,1), PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE (MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL, MOVIE_YEAR CHAR(4), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR(15), PRICE_CODE CHAR(1), CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE); CREATE TABLE VIDEO (VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY, VID_INDATE DATE, MOVIE_NUM CHAR(4), CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE); CREATE TABLE DETAILRENTAL (RENT_NUM CHAR(4), VID_NUM CHAR(5), DETAIL_FEE NUMBER(3,1), DETAIL_DUEDATE DATE, DETAIL_RETURNDATE DATE, DETAIL_DALYLATEFEE NUMBER(2), CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM), CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL, CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

CREATE TABLE MEMBERSHIP

( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY,

MEM_FNAME VARCHAR(30) NOT NULL,

MEM_LNAME VARCHAR(30) NOT NULL,

MEM_STREET VARCHAR(30),

MEM_CITY VARCHAR(10),

MEM_STATE CHAR(2),

MEM_ZIP CHAR(5),

MEM_BALANCE NUMBER (2));

CREATE TABLE RENTAL

( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY,

RENT_DATE DATE,

MEM_NUM CHAR(3),

CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP);

CREATE TABLE PRICE

(PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY,

PRICE_DESC VARCHAR(20),

PRICE_RENTFEE NUMBER (3,1),

PRICE_DAILYATFEE NUMBER(3,1));

CREATE TABLE MOVIE

(MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY,

MOVIE_NAME VARCHAR(30) NOT NULL,

MOVIE_YEAR CHAR(4),

MOVIE_COST NUMBER(5,2),

MOVIE_GENRE VARCHAR(15),

PRICE_CODE CHAR(1),

CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE);

CREATE TABLE VIDEO

(VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY,

VID_INDATE DATE,

MOVIE_NUM CHAR(4),

CONSTRAINT VIDEO_MOVIE_NUM_FK FOREIGN KEY (MOVIE_NUM) REFERENCES MOVIE);

CREATE TABLE DETAILRENTAL

(RENT_NUM CHAR(4),

VID_NUM CHAR(5),

DETAIL_FEE NUMBER(3,1),

DETAIL_DUEDATE DATE,

DETAIL_RETURNDATE DATE,

DETAIL_DALYLATEFEE NUMBER(2),

CONSTRAINT DETAILRENTAL_RENT_VID_PK PRIMARY KEY(RENT_NUM, VID_NUM),

CONSTRAINT DETAILRENTAL_RENT_FK FOREIGN KEY (RENT_NUM) REFERENCES RENTAL,

CONSTRAINT DETAILRENTAL_VID_FK FOREIGN KEY (VID_NUM) REFERENCES VIDEO);

INSERT INTO MEMBERSHIP VALUES('102', 'Tami', 'Dawson', '2632 Takli Circle', 'Norene', 'TN', '37136', 11);

INSERT INTO MEMBERSHIP VALUES('103', 'Curt', 'Knight', '4025 Cornell Court', 'Flatgap', 'KY', '41219', 6);

INSERT INTO MEMBERSHIP VALUES('104', 'Jamal', 'Melendez', '788 East 145th Avenue', 'Quebeck', 'TN', '38579', 0);

INSERT INTO MEMBERSHIP VALUES('105', 'Iva', 'Mcclain', '6045 Musket Ball Circle', 'Summit', 'KY', '42783', 15);

INSERT INTO MEMBERSHIP VALUES('106', 'Miranda', 'Parks', '4469 Maxwell Place', 'Germantown', 'TN', '38402', 5);

INSERT INTO RENTAL VALUES('1001', TO_DATE('01-MAR-09', 'DD-MM-YY'), '103');

INSERT INTO RENTAL VALUES('1002', TO_DATE('01-MAR-09', 'DD-MM-YY'), '105');

INSERT INTO RENTAL VALUES('1003', TO_DATE('02-MAR-09', 'DD-MM-YY'), '102');

INSERT INTO PRICE VALUES('1', 'Standard', 2.0, 1);

INSERT INTO PRICE VALUES('2', 'New Release', 3.5, 3);

INSERT INTO PRICE VALUES('3', 'Discount', 1.5, 1);

INSERT INTO PRICE VALUES('4', 'Weekly Special', 1, 0.5);

INSERT INTO MOVIE VALUES('1234', 'The Cesar Family Christmas', 2007, 39.95, 'FAMILY', '2');

INSERT INTO MOVIE VALUES('1235', 'Smokey Mountain Wildlife', 2004, 59.95, 'ACTION', '3');

INSERT INTO MOVIE VALUES('1236', 'Richard Goodhope', 2008, 59.95, 'DRAMA', '2');

INSERT INTO MOVIE VALUES('1237', 'Beatnik Fever', 2007, 29.95, 'COMEDY', '2');

INSERT INTO MOVIE VALUES('1238', 'Constant Companion', 2008, 89.95, 'DRAMA', '2');

INSERT INTO MOVIE VALUES('1239', 'Where Hope Dies', 1998, 25.49, 'DRAMA', '1');

INSERT INTO MOVIE VALUES('1245', 'Time to Burn', 2005, 45.49, 'ACTION', '3');

INSERT INTO MOVIE VALUES('1246', 'What He Doesnt Know', 2006, 58.29, 'COMEDY', '1');

INSERT INTO VIDEO VALUES('54321', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');

INSERT INTO VIDEO VALUES('54324', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');

INSERT INTO VIDEO VALUES('54325', TO_DATE('18-JUN-08', 'DD-MM-YY'), '1234');

INSERT INTO VIDEO VALUES('34341', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');

INSERT INTO VIDEO VALUES('34342', TO_DATE('22-JAN-07', 'DD-MM-YY'), '1235');

INSERT INTO VIDEO VALUES('34366', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');

INSERT INTO VIDEO VALUES('34367', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');

INSERT INTO VIDEO VALUES('34368', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');

INSERT INTO VIDEO VALUES('34369', TO_DATE('02-MAR-09', 'DD-MM-YY'), '1236');

INSERT INTO VIDEO VALUES('44392', TO_DATE('21-OCT-08', 'DD-MM-YY'), '1237');

INSERT INTO DETAILRENTAL VALUES('1001', '34342', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('02-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1001', '54321', 2, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('03-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1002', '54321', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('04-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '54325', 3.5, TO_DATE('04-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '54321', 2, TO_DATE('06-MAR-09', 'DD-MM-YY'), TO_DATE('09-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1003', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '34367', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 3);

INSERT INTO DETAILRENTAL VALUES('1003', '34341', 2, TO_DATE('07-MAR-09', 'DD-MM-YY'), TO_DATE('07-MAR-09', 'DD-MM-YY'), 1);

INSERT INTO DETAILRENTAL VALUES('1002', '44392', 3.5, TO_DATE('05-MAR-09', 'DD-MM-YY'), TO_DATE('05-MAR-09', 'DD-MM-YY'), 3);

Alter the VIDEO table to include an attribute named VID_STATUS to store character
data up to 4 characters long. The attribute should have a constraint to enforce the
values ("IN", "OUT", and "LOST")

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.