Question

You have been asked to develop a database system to track the movies your customers viewed over the past summer. The database system must structured in such a way as to be able to produce a variety of reports related to the movies viewed: Movies Released During Summer Movie Genre Rating X-Men: Days of Future Past Action/Adventure PG-13 Maleficent Family and Kids PG Edge of Tomorrow Science Fiction PG-13 How to Train your Dragon 2 Family and Kids PG Transformers: Age of Extinction Action/Adventure PG-13 Planes: Fire and Rescue Family and Kids PG Hercules Action/Adventure PG-13 Guardians of the Galaxy Action/Adventure PG-13 Step Up: All In Drama PG-13 Teenage Mutant Ninja Turtles Action/Adventure PG-13 The Hundred Foot Journey Drama PG When the Game Stands Tall Drama PG Movies Viewed The following movies were viewed during the summer. (Information not displayed here, but that needs to be collected, includes address and any other pertinent identifying information for customers). Customer and Movie Viewed Numeric Feedback Rating* 1-5 Charlotte Osborne Maleficent 5 Edge of Tomorrow 5 How to Train your Dragon 2 4 Cory Simon Planes: Fire and Rescue 1 Hercules 2 Guardians of the Galaxy 1 Step Up: All In 3 Jan Cruz The Hundred Foot Journey 3 When the Game Stands Tall 4 Arnold Nunez X-Men: Days of Future Past 5 Maleficent 5 Edge of Tomorrow 4 How to Train your Dragon 2 4 Transformers: Age of Extinction 3 Gilberto Hale How to Train your Dragon 2 2 Transformers: Age of Extinction 2 Chad Olson When the Game Stands Tall 1 Jodi Knight Maleficent 1 Emanuel Ingram Teenage Mutant Ninja Turtles 5 The Hundred Foot Journey 5 When the Game Stands Tall 4 Carol Rodgers X-Men: Days of Future Past 4 Maleficent 4 Edge of Tomorrow 4 Freddie Simpson The Hundred Foot Journey 3 Ebony King Guardians of the Galaxy 3 Step Up: All In 5 Teenage Mutant Ninja Turtles 5 Kim Marshall Planes: Fire and Rescue 5 Hercules 4 Guardians of the Galaxy 4 Step Up: All In 1 * 1 – hated it, 2 –disliked it, 3 – neither liked nor disliked, 4 – liked it, 5 – loved it

Answer 1

SQL queries:

CREATE TABLE movies_watched_during_summer(
movie_name        VARCHAR(40)       PRIMARY KEY,
Genre           VARCHAR(30),
Rating           VARCHAR(5));

INSERT INTO movies_watched_during_summer VALUES('X-Men: Days of Future Past', 'Action/Adventure', 'PG-13');
INSERT INTO movies_watched_during_summer VALUES('Maleficent', 'Family and Kids', 'PG');
INSERT INTO movies_watched_during_summer VALUES('Edge of Tomorrow', 'Science Fiction','PG-13' );
INSERT INTO movies_watched_during_summer VALUES('How to Train your Dragon 2','Family and Kids', 'PG');
INSERT INTO movies_watched_during_summer VALUES('Transformers: Age of Extinction' ,'Action/Adventure' ,'PG-13');
INSERT INTO movies_watched_during_summer VALUES('Planes: Fire and Rescue', 'Family and Kids', 'PG');
INSERT INTO movies_watched_during_summer VALUES('Hercules','Action/Adventure' ,'PG-13');
INSERT INTO movies_watched_during_summer VALUES('Guardians of the Galaxy', 'Action/Adventure' ,'PG-13');
INSERT INTO movies_watched_during_summer VALUES('Step Up: All In' ,'Drama', 'PG-13');
INSERT INTO movies_watched_during_summer VALUES('Teenage Mutant Ninja Turtles', 'Action/Adventure' ,'PG-13');
INSERT INTO movies_watched_during_summer VALUES('The Hundred Foot Journey', 'Drama', 'PG');
INSERT INTO movies_watched_during_summer VALUES('When the Game Stands Tall', 'Drama','PG' );

CREATE TABLE movies_viewed(
customer_name       VARCHAR(30),
movie_name            VARCHAR(40) REFERENCES movies_watched_during_summer(movie_name),       -- foreign key taking reference of movie name from movies_watched_during_summer table
Feedback_rating       NUMBER CHECK (Feedback_rating BETWEEN 1 AND 5));

INSERT INTO movies_viewed VALUES('Charlotte Osborne', 'Maleficent', 5);
INSERT INTO movies_viewed VALUES('Charlotte Osborne Maleficent', 'Edge of Tomorrow', 5);
INSERT INTO movies_viewed VALUES('Charlotte Osborne','How to Train your Dragon 2', 4);

INSERT INTO movies_viewed VALUES('Cory Simon', 'Planes: Fire and Rescue', 1);
INSERT INTO movies_viewed VALUES('Cory Simon', 'Hercules', 2);
INSERT INTO movies_viewed VALUES('Cory Simon', 'Guardians of the Galaxy', 1);
INSERT INTO movies_viewed VALUES('Cory Simon', 'Step Up: All In', 3 );

INSERT INTO movies_viewed VALUES('Jan Cruz', 'The Hundred Foot Journey', 3);
INSERT INTO movies_viewed VALUES('Jan Cruz', 'When the Game Stands Tall', 4 );

INSERT INTO movies_viewed VALUES('Arnold Nunez', 'X-Men: Days of Future Past', 5);
INSERT INTO movies_viewed VALUES('Arnold Nunez', 'Maleficent', 5);
INSERT INTO movies_viewed VALUES('Arnold Nunez', 'Edge of Tomorrow', 4);
INSERT INTO movies_viewed VALUES('Arnold Nunez', 'How to Train your Dragon 2', 4);
INSERT INTO movies_viewed VALUES('Arnold Nunez', 'Transformers: Age of Extinction', 3);

INSERT INTO movies_viewed VALUES('Gilberto Hale', 'How to Train your Dragon 2', 2);
INSERT INTO movies_viewed VALUES('Gilberto Hale', 'Transformers: Age of Extinction', 2);

INSERT INTO movies_viewed VALUES('Jodi Knight', 'Maleficent', 1);

INSERT INTO movies_viewed VALUES('Chad Olson', 'When the Game Stands Tall', 1);

INSERT INTO movies_viewed VALUES('Emanuel Ingram', 'Teenage Mutant Ninja Turtles', 5);
INSERT INTO movies_viewed VALUES('Emanuel Ingram', 'The Hundred Foot Journey', 5);
INSERT INTO movies_viewed VALUES('Emanuel Ingram', 'When the Game Stands Tall', 4);

INSERT INTO movies_viewed VALUES('Carol Rodgers' ,'X-Men: Days of Future Past', 4);
INSERT INTO movies_viewed VALUES('Carol Rodgers' , 'Maleficent', 4);
INSERT INTO movies_viewed VALUES('Carol Rodgers' , 'Edge of Tomorrow', 4);

INSERT INTO movies_viewed VALUES('Freddie Simpson', 'The Hundred Foot Journey', 3);

INSERT INTO movies_viewed VALUES('Ebony King', 'Guardians of the Galaxy', 3);
INSERT INTO movies_viewed VALUES('Ebony King', 'Step Up: All In', 5);
INSERT INTO movies_viewed VALUES('Ebony King', 'Teenage Mutant Ninja Turtles', 5);

INSERT INTO movies_viewed VALUES('Kim Marshall', 'Planes: Fire and Rescue', 5);
INSERT INTO movies_viewed VALUES('Kim Marshall', 'Hercules', 4);
INSERT INTO movies_viewed VALUES('Kim Marshall', 'Guardians of the Galaxy', 4);
INSERT INTO movies_viewed VALUES('Kim Marshall', 'Step Up: All In', 1);

Output: