IT360 Lab 4: SQL Joins, DDL, and DML
DUE: Monday February 10, 2014, 2359 (paper copy BEFORE start of lab next day)
In this lab you will develop your database implementation skills by modifying and querying databases in MySQL using SQL.
Follow the tutorial on the course calendar – Lab 2 - to connect to MySQL.
In this lab, you will work on the PerformerConcerts database: you will create the following tables that will store information about performance types (ex. song, dance, etc.), about performers, arenas (for performances) and concerts. Here is the schema for these tables, with primary key underlined and foreign keys in italic:
PerformanceTypes(PerformanceTypeName) – types of performance (ex. singer, dancer, etc.)
Performers(PerformerID, FirstName, LastName, Address, PerformanceTypeName) – Each performer has an id (integer), a name, address and type of performance
Arenas(ArenaID, ArenaName, City, Capacity) - each arena has an id (integer), a name (Ex. Verizon Center), a city where the arena is located, and seating capacity of the arena
Concerts(PerformerID, ArenaID, ConcertDate) – each concert is given by one performer, on a given arena, at a given date (type ‘date’ in MySQL)
Now, write SQL queries to accomplish the following tasks. Save all queries in a file called yourlastname_yourfirstname_lab4.sql. Write the question number and English question as a comment before each SQL query (In MySQL Workbench, go to File-> New Query/Script tab. Copy-paste the queries below into the new script tab. Comment each question using /* */ and then write the SQL query below it. Save your script as yourlastname_yourfirstname_lab4.sql).
Important Note: Your queries should run correctly on any possible data, not just the sample data currently in the database.
b) Try now to insert another row into Performers table, but this time use ‘painter’ as the performance type (or any PerformanceTypeName that does not exist in PerformanceTypes table). What happens? (write your query and answer as a comment in yourlastname_yourfirstname_lab4.sql)
c) Insert 3 more rows in the Performers table, with names ‘Matt Smith’, ‘Jane Brown’, and respectively ‘Jennifer Shade’, and addresses ‘Baltimore, MD’, ‘New York, NY’, and respectively ‘Seattle, WA’. All these 3 performers should be of type ‘dancer’.
b) What would happen if there would be two performers with this name? (write your answer in yourlastname_yourfirstname_lab4.sql)
b) Execute the statement above, then list all rows from the PerformanceTypes table. Was ‘singer’ removed? Explain why the row was removed from the PerformanceTypes table, or why it was not removed. (write all your answers in yourlastname_yourfirstname_lab4.sql)
c) Write the SQL statement to remove performance type ‘comedian’ from the PerformanceTypes table.
d) Execute the statement above, then list all rows from the PerformanceTypes table. Was ‘comedian’ removed? Explain why the row was removed from the PerformanceTypes table, or why it was not removed. (write all your answers in yourlastname_yourfirstname_lab4.sql)
8. a) Write the SQL statement to update the performance type name for ‘dancer’ in PerformanceTypes to be ‘dance performer’.
b) Execute the statement above. What are the effects and why? (list all rows from PerformanceTypes and Performers and look at the values)
9. a) Write the SQL statements to insert 3 rows into the Arenas table with ids 1, 2, and respectively 3 and whatever values you want for the other columns. Execute the statements.
b) Write the SQL statements to insert 4 rows in the Concerts table, two of them for arena 1, and two for arena 2, and whatever values you want for the other columns.
c) Write the SQL statement to update all arenas with at least 2 concerts to have the seat capacity 50000. The query should work for any possible data in the database.
10. a) Create a new ‘Dancers’ table with the same structure as the Performers table (copy-paste-modify the create table statement you had for Performers. Note: remember that the constraints names need to be unique in the entire database)
b) Insert all performers of type ‘dancer’ (or ‘dance performer’ whichever is appropriate for your case) from the Performers table into the Dancers table. You should use only one SQL INSERT statement and your statement should work for any data possible in the Performers table.
11. (Effect of foreign key constraint on rows)
a) Change table Concerts to remove the foreign key constraint referencing the Performers table (if you did not give a name to the foreign key constraint when you created it, use MySQL Workbench to find out the name given by the database management system to the constraint (in the object browser, expand the table Concerts, then look at Foreign Keys))
b) Delete from the Performers table all performers of type ‘dancer’ (or ‘dance performer’ whichever is appropriate for your case).
c) Find all performer ids in the Concerts table that do not exist in the Performers table
d) Delete all rows from the Concerts table with a performer id that does not exist in the Performers table
12. (Effect of foreign key constraint on tables)
a) Write the SQL statement to remove the PerformanceTypes table.
b) Try executing the statement above. What happens? Why? (write your answers to yourlastname_yourfirstname_lab4.sql)
c) Change the Performers table to remove the foreign key constraint referencing the PerformanceTypes table. If needed, also change the Dancers table to remove the foreign key constraint referencing the PerformanceTypes table.
d) Execute the statement to remove the PerformanceTypes table. Does it succeed now? Why?
List all the ArenaID, name, city, capacity from the Arenas table, and for those who held any concerts, also list the performer id and date of the concert (these columns should be null for the arenas with no concerts).
Turn in BOTH electronic (due Monday February 10, 2014 at 2359) and paper copies (due before start of lab on February 11, 2014):