Question

In: Computer Science

Question 2. The following tables provide some example data that will be kept in the database....

Question 2. The following tables provide some example data that will be kept in the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1. Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands..

Table: actor

act_id |      act_fname       |      act_lname       | act_gender
    101 | James                | Stewart              | M
    102 | Deborah              | Kerr                 | F
    103 | Peter                | OToole               | M
    104 | Robert               | De Niro              | M
    105 | F. Murray            | Abraham              | M
    106 | Harrison             | Ford                 | M
    107 | Nicole               | Kidman               | F
    108 | Stephen              | Baldwin              | M
    109 | Jack                 | Nicholson            | M
    110 | Mark                 | Wahlberg             | M
    111 | Woody                | Allen                | M
    112 | Claire               | Danes                | F
    113 | Tim                  | Robbins              | M
    114 | Kevin                | Spacey               | M
    115 | Kate                 | Winslet              | F
    116 | Robin                | Williams             | M
    117 | Jon                  | Voight               | M
    118 | Ewan                 | McGregor             | M
    119 | Christian            | Bale                 | M
    120 | Maggie               | Gyllenhaal           | F
    121 | Dev                  | Patel                | M
    122 | Sigourney            | Weaver               | F
    123 | David                | Aston                | M
    124 | Ali                  | Astin                | F

Table: movie_cast

act_id | mov_id |              role

    101 |    901 | John Scottie Ferguson

    102 |    902 | Miss Giddens

    103 |    903 | T.E. Lawrence

    104 |    904 | Michael

    105 |    905 | Antonio Salieri

    106 |    906 | Rick Deckard

    107 |    907 | Alice Harford

    108 |    908 | McManus

    110 |    910 | Eddie Adams

    111 |    911 | Alvy Singer

    112 |    912 | San

    113 |    913 | Andy Dufresne

    114 |    914 | Lester Burnham

    115 |    915 | Rose DeWitt Bukater

    116 |    916 | Sean Maguire

    117 |    917 | Ed

    118 |    918 | Renton

    120 |    920 | Elizabeth Darko

    121 |    921 | Older Jamal

    122 |    922 | Ripley

    114 |    923 | Bobby Darin

    109 |    909 | J.J. Gittes

    119 |    919 | Alfred Borden

Table: movie

mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country
    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK
    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW
    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK
    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK
    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK
    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK
    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK
    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK
    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK
    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK
    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA
    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK
    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK
    914 | American Beauty                                    |     1999 |      122 | English         |            | UK
    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK
    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK
    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK
    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK
    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK
    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK
    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK
    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK
    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK
    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK
    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP
    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK
    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK
    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

Table: director

dir_id |      dir_fname       |      dir_lname
    201 | Fred                 | Caravanhitch
    202 | Jackie               | Claytonburry
    203 | Greene               | Lyon
    204 | Miguel               | Camino
    205 | George               | Forman
    206 | Antartic             | Scott
    207 | Stanlee              | Carbrick
    208 | Bryon                | Sanger
    209 | Roman                | Polanski
    210 | Paul                 | Thomas Anderson
    211 | Woody                | Allen
    212 | Hayao                | Miyazaki
    213 | Frank                | Darabont
    214 | Sam                  | Mendes
    215 | James                | Cameron
    216 | Gus                  | Van Sant
    217 | John                 | Boorman
    218 | Danny                | Boyle
    219 | Christopher          | Nolan
    220 | Richard              | Kelly
    221 | Kevin                | Spacey
    222 | Andrei               | Tarkovsky
    223 | Peter                | Jackson

Table: movie_direction

dir_id | mov_id
    201 |    901
    202 |    902
    203 |    903
    204 |    904
    205 |    905
    206 |    906
    207 |    907
    208 |    908
    209 |    909
    210 |    910
    211 |    911
    212 |    912
    213 |    913
    214 |    914
    215 |    915
    216 |    916
    217 |    917
    218 |    918
    219 |    919
    220 |    920
    218 |    921
    215 |    922
    221 |    923

Table: genres

gen_id |      gen_title
   1001 | Action
   1002 | Adventure
   1003 | Animation
   1004 | Biography
   1005 | Comedy
   1006 | Crime
   1007 | Drama
   1008 | Horror
   1009 | Music
   1010 | Mystery
   1011 | Romance
   1012 | Thriller
   1013 | War

Table: movie_genres

mov_id | gen_id
    922 |   1001
    917 |   1002
    903 |   1002
    912 |   1003
    911 |   1005
    908 |   1006
    913 |   1006
    926 |   1007
    928 |   1007
    918 |   1007
    921 |   1007
    902 |   1008
    923 |   1009
    907 |   1010
    927 |   1010
    901 |   1010
    914 |   1011
    906 |   1012
    904 |   1013

Table: rating

mov_id | rev_id | rev_stars | num_o_ratings
    901 |   9001 |      8.40 |        263575
    902 |   9002 |      7.90 |         20207
    903 |   9003 |      8.30 |        202778
    906 |   9005 |      8.20 |        484746
    924 |   9006 |      7.30 |
    908 |   9007 |      8.60 |        779489
    909 |   9008 |           |        227235
    910 |   9009 |      3.00 |        195961
    911 |   9010 |      8.10 |        203875
    912 |   9011 |      8.40 |
    914 |   9013 |      7.00 |        862618
    915 |   9001 |      7.70 |        830095
    916 |   9014 |      4.00 |        642132
    925 |   9015 |      7.70 |         81328
    918 |   9016 |           |        580301
    920 |   9017 |      8.10 |        609451
    921 |   9018 |      8.00 |        667758
    922 |   9019 |      8.40 |        511613
    923 |   9020 |      6.70 |         13091

Table: reviewer

rev_id |            rev_name
   9001 | Righty Sock
   9002 | Jack Malvern
   9003 | Flagrant Baronessa
   9004 | Alec Shaw
   9005 |
   9006 | Victor Woeltjen
   9007 | Simon Wright
   9008 | Neal Wruck
   9009 | Paul Monks
   9010 | Mike Salvati
   9011 |
   9012 | Wesley S. Walker
   9013 | Sasha Goldshtein
   9014 | Josh Cates
   9015 | Krug Stillo
   9016 | Scott LeBrun
   9017 | Hannah Steele
   9018 | Vincent Cadena
   9019 | Brandt Sponseller
   9020 | Richard Adams

Solutions

Expert Solution

Table: actor

Create table Actor(

act_id INT,

act_fname Varchar(255),

act_lname Varchar(255),

act_gender char(1),

Primary Key (act_id )
);

  • Insert into Actor (act_id ,act_fname ,act_lname ,act_gender) values (101, 'James', 'Stewart', 'M'), (102, 'Deborah ', 'Kerr ', 'F'), (103 , 'Peter ', 'OToole ', 'M'), .....);

Table: movie_cast

Create table movie_cast(

act_id INT,

mov_id INT,

role Varchar(255),

Primary Key (mov_id),

Foreign Key (act_id) Refrences actor(act_id),

Foreign Key (mov_id) Refrences movie(mov_id)

);

  • Insert into movie_cast(act_id ,mov_id ,role ) values (101, 901 , 'John Scottie Ferguson'), (102, 902, 'Miss Giddens'), (103 , 903, 'T.E. Lawrence '), .....);

Table: movie

Create table movie(

mov_id INT,

mov_title Varchar(255),

mov_year year,

mov_time INT,

mov_lang Varchar(255),

mov_dt_rel date,

mov_rel_country Varchar(255),

Primary Key (mov_id),

);

  • Insert into movie(mov_id ,mov_title ,mov_year ,mov_time ,mov_lang,mov_dt_rel, mov_rel_country ) values (901 , 'Vertigo ' ,'1958' ,128,'English ','1958-08-24', 'UK'), (902, 'The Innocents ','1961 ' ,100 ,'English ','1962-02-19', 'SW'), (903 , 'Lawrence of Arabia ' ,'1962 ' ,216 ,'English ','1962-12-11', 'UK'), , .....);

Table: director

Create table director(

dir_id INT,

dir_fname Varchar(255),

dir_lname Varchar(255),

Primary Key (dir_id ),

);

  • Insert into director(dir_id ,dir_fname,dir_lname ) values (201 , 'Fred ' ,' Caravanhitch'), (202, 'Jackie ','Claytonburry ' ), (203 , 'Greene ' ,'Lyon' ),......);

Table: movie_direction

Create table movie_direction(

dir_id INT,

mov_id INT,

Primary Key (dir_id ),

Foreign Key (dir_id) Refrences director(dir_id),

Foreign Key (mov_id) Refrences movie(mov_id)

);

  • Insert into director(dir_id ,mov_id) values (201 , 901), (202,902), (203 , 903),......);

Table: genres

Create table genres(

gen_id INT,

gen_title INT,

Primary Key (gen_id )

);

  • Insert into director(gen_id ,gen_title) values (1001, 'Action'), (1002 ,'Adventure'), (1003, 'Animation'),......);

Table: movie_genres

Create table movie_genres(

mov_id INT,

gen_id INT,

Primary Key (mov_id ),

Foreign Key (gen_id) Refrences genres(gen_id),

Foreign Key (mov_id) Refrences movie(mov_id)

);

  • Insert into director(mov_id,gen_id ) values (922 , 1001), (917 , 1002), (903, 1002),......);

Table: rating

Create table rating(

mov_id INT,

rev_id INT,

rev_stars Float,

num_o_ratings INT,

Primary Key (rev_id ),

Foreign Key (mov_id) Refrences movie(mov_id)

Foreign Key (rev_id ) Refrences reviewer(rev_id )

);

  • Insert into director(mov_id,rev_id ,rev_stars , num_o_ratings ) values (901 , 9001 , 8.40 , 263575), (902 , 9002 ,7.90 ,20207 ), (903, 9003 ,8.30 ,202778 ),......);

Table: reviewer

Create table reviewer(

rev_id INT,

rev_name Varchar(255),

Primary Key (rev_id )

);

  • Insert into director(rev_id ,rev_name ) values (9001 , 'Righty Sock'), (9002 ,' Jack Malvern' ), (9003 ,'Flagrant Baronessa' ),......);
If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you

venereology answered 1 year ago
Next > < Previous

Related Solutions

Please use an Access database with two tables to answer the following: use an example to...
Please use an Access database with two tables to answer the following: use an example to discuss the difference between a right, left, and inner join. Next, perform the left joint, right joint, and inner joint all on the each of the two tables.
QUESTION: The following tables describe the content of a relational database: a) Identify and classify the...
QUESTION: The following tables describe the content of a relational database: a) Identify and classify the tables as either entity or relationship The first step in building an E-R model is to identify the entities. Having identified the entities, the next step is to identify all the relationships that exist between these entities. Using the content of the relational database above: b) Using the relations in the relational database, explain how one can transform relationship in E-R model into a...
'driver', 'car', 'accident' and 'report' are names of some tables in the insurance database system. These...
'driver', 'car', 'accident' and 'report' are names of some tables in the insurance database system. These tables were created by executing the following SQL creation statements. CREATE TABLE driver( driverID INT NOT NULL PRIMARY KEY, name VARCHAR(30) NOT NULL, cityAddress VARCHAR(25) ); CREATE TABLE car( plateID INT NOT NULL PRIMARY KEY, model VARCHAR(20) NOT NULL, year YEAR(4) NOT NULL ); CREATE TABLE accident( reportNumber INT NOT NULL PRIMARY KEY, date Date NOT NULL, location VARCHAR(20) NOT NULL ); CREATE TABLE...
The following tables form part of a database (Flights Database) held in a relational DBMS: employee...
The following tables form part of a database (Flights Database) held in a relational DBMS: employee (empNo, empName, empSalary, empPosition) aircraft (aircraftNo, acName, acModel, acFlyingRange) flight (flightNo, aircraftNo, fromAirport, toAirport, flightDistance, departTime, arriveTime) certified (empNo, aircraftNo) Where:  employee contains details of all employees (pilots and non-pilots) and empNo is the primary key;  aircraft contains details of aircraft and C is the primary key.  flight contains details of flights and (flightNo, aircraftNo) form the primary key.  certified...
Question #2 - Review 'Cars Database'. From this data set develop the following: a. One bivariate...
Question #2 - Review 'Cars Database'. From this data set develop the following: a. One bivariate regression b. One multiple regression c. Provide an effective chart displaying each of the two regressions. Charts should be presentation ready with effective title and labels. d. Provide a brief description of each of the two regressions (please use a text box in your Excel spreadsheet for your descriptions). Also, be sure to correctly identify and describe your independent and dependent variables, linear equation...
What are some pros and cons to data mining? Provide an example of when data mining...
What are some pros and cons to data mining? Provide an example of when data mining was used and the outcome provided an incorrect assumption or issue. How can these types of situations be avoided in the future?
Provide 2 paragraphs to answer the following question. 1. What are some of the major different...
Provide 2 paragraphs to answer the following question. 1. What are some of the major different types of taxes? How are they levied and who pays each tax? What is meant by a tax’s progressivity? How is this determined?
Database reports provide us with the ability to further analyze our data, and provide it in...
Database reports provide us with the ability to further analyze our data, and provide it in a format that can be used to make business decisions. Discuss the steps that you would take to ensure that we create an effective report. What questions would you ask of the users?
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The...
Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT