Create a new database that holds the same tables and data as Question 2a's solution from...

   

Transcribed Image Text:

Create a new database that holds the same tables and data as Question 2a's solution from the Midterm Review Questions (which I put below this class activity's details for your convenience). Put the query you use for each task in a word document and submit it. Create Tables (create the same tables from Question 2a's solution). Don't forget to correctly set up the foreign key columns (see slide 12 from today's lecture). Insert the records of data into each table (use the same data from Question 2a's solution). Select all time slots where 'movie_id' is 7 (in the time slots and room table). Update the record of data from the rooms table where 'room_id' is 4 by changing the 'room_number' value to '1C' Delete a record of data from the movie tickets table where 'customer_id' equals 8. Drop the movie tickets table from your database. Question 2a From Midterm Review Questions Imagine you are designing a database for a movie theater. The database should have a table designed to hold all customers who enter the theater, a table holding all movie tickets bought by customers, a table containing the movies that are shown at the movie theater, and a table containing the time slots that a movie can be played during as well as the room the movie would be played in during that time slot (the movie theater has multiple movie rooms). You may also want to create a table for the theater rooms (to reduce redundancy). *Enter 3-4 test records of data for each table. Design the database tables for the scenario described above. Think of some possible attributes for each table. Write "PK" above each column that is a Primary Key and "FK" above each column that is a Foreign Key. Question 2a's Solution PK movie TicketID 1 2 3 PK customers customer.ID name 1 8 movie Tickets FK customerID 8 8 Max Daniels Meghan Ivan Stephan Lot FK timeslotID 3 7 5 PK movies movielD movieName 5 7 10 Cars Baby Driver Fist Fight PK timeSlotID 3 5 PK rooms roomiD roomNumber 2 7 timeSlotsAndRoom timeSlot 1:00 PM - 3:00 PM 3:00 PM - 5:00 PM 2:00 PM - 4:00 PM 2A 4A 3B FK room ID 2 7 FK movielD 7 7 10 Create a new database that holds the same tables and data as Question 2a's solution from the Midterm Review Questions (which I put below this class activity's details for your convenience). Put the query you use for each task in a word document and submit it. Create Tables (create the same tables from Question 2a's solution). Don't forget to correctly set up the foreign key columns (see slide 12 from today's lecture). Insert the records of data into each table (use the same data from Question 2a's solution). Select all time slots where 'movie_id' is 7 (in the time slots and room table). Update the record of data from the rooms table where 'room_id' is 4 by changing the 'room_number' value to '1C' Delete a record of data from the movie tickets table where 'customer_id' equals 8. Drop the movie tickets table from your database. Question 2a From Midterm Review Questions Imagine you are designing a database for a movie theater. The database should have a table designed to hold all customers who enter the theater, a table holding all movie tickets bought by customers, a table containing the movies that are shown at the movie theater, and a table containing the time slots that a movie can be played during as well as the room the movie would be played in during that time slot (the movie theater has multiple movie rooms). You may also want to create a table for the theater rooms (to reduce redundancy). *Enter 3-4 test records of data for each table. Design the database tables for the scenario described above. Think of some possible attributes for each table. Write "PK" above each column that is a Primary Key and "FK" above each column that is a Foreign Key. Question 2a's Solution PK movie TicketID 1 2 3 PK customers customer.ID name 1 8 movie Tickets FK customerID 8 8 Max Daniels Meghan Ivan Stephan Lot FK timeslotID 3 7 5 PK movies movielD movieName 5 7 10 Cars Baby Driver Fist Fight PK timeSlotID 3 5 PK rooms roomiD roomNumber 2 7 timeSlotsAndRoom timeSlot 1:00 PM - 3:00 PM 3:00 PM - 5:00 PM 2:00 PM - 4:00 PM 2A 4A 3B FK room ID 2 7 FK movielD 7 7 10

Answer rating: 100% (QA)

Based on the task outlined in the first image below are the SQL queries you would use to perform the specified actions using the tables from Question ... View the full answer