IT360 Lab 4: SQL Joins, DDL, and DML
DUE: Thursday February 7, 2013, 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
Performers(PerformerID, FirstName, LastName, Address, PerformanceTypeName) – Each performer has a performedID (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 a performer row with a PerformanceTypeName that does not exist in PerformanceTypes table (ex. ‘painter’). 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 are 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)
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. Does the query succeed? What are the effects and why? (list all rows from Performers and look at the values)
9. a) Write the SQL statements to insert 3 rows with ids 1, 2, and respectively 3 into the Arenas table. 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.
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’ 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
b) Delete from the Performers table all performers of type ‘dancer’ (or ‘dance performer’ whichever is appropriate for your case).
c) Find all 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 execute 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. 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 Thursday February 7, 2013 at 2359) and paper copies (due before start of lab on February 8, 2013):