PLEASE HELP ME FINISH THIS CORRECTLY! We will create a concerts database: create

Question

PLEASE HELP ME FINISH THIS CORRECTLY!

We will create a concerts database: create the following tables that will store information about performance types (ex. song, dance, etc.), about performers, arenas (for performances) and concerts.

Save your work as Yourname_lab2_1.sql

Here is the schema for these tables, with primary key underlined and foreign keys in RED 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. Allstate 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 .

Write and execute the SQL statements to create the 4 tables given above. Choose appropriate data types for each column, and declare primary key and foreign key constraints as appropriate.

Insert rows in the PerformanceTypes table, for type ‘singer’, ‘dancer’ and ‘comedian’.

Write the SQL statement to modify your Performers table to add a new column called DateOfBirth. Choose the appropriate data type for this column. The values in the column could be null.

Insert one row in the Performers table with PerformerID 1, name John Dow, address Annapolis, MD, and performance type ‘singer’.

Try now to insert a performer row with a PerformanceTypeName that does not exist in PerformanceTypes table (ex. ‘painter’). What happens? Show your work with errors.

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’.

Change the DateOfBirth of the performer with id 1 to be ‘1990-03-02’.

Remove the performer ‘Matt Smith’ from the Performers table.

Write the SQL statement to remove performance type ‘singer’ from the PerformanceTypes table.

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 the SQL statement to update the performance type name for ‘dancer’ in PerformanceTypes to be ‘dance performer’.

Execute the statement above. Does the query succeed?

Write the SQL statements to insert 3 rows with ids 1, 2, and respectively 3 into the Arenas table.

Write the SQL statements to insert 4 rows in the Concerts table, two of them for arena 1, and two for arena 2.

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.

Create a new ‘Dancers’ table with the same structure as the Performers table

Insert all performers of type ‘dancer’ from the Performers table into the Dancers table

Explanation / Answer

PerformanceType table creation

create table PerformanceTypes(PerformanceTypeName varchar2(20),Primary Key(PerformanceTypeName));

Performers table creation

create table Performers(PerformerID int(5), FirstName varchar2(20), LastName varchar2(20), Address varchar2(20),Primary Key(PerformerID),PerformanceTypeName varchar2 references PerformanceTypes(PerformanceTypeName));

Arenas Table creation

create table Arenas(ArenaID int(5) primary key, ArenaName varchar2(20), City varchar2(20), Capacity int(5));

Concers table creation

create table Concerts(PerformerID int references Performers(PerformerID), ArenaID int references Arenas(ArenaID), ConcertDate date)

Adding DateofBirth coloumn to performers table

ALTER TABLE Performers
ADD DateofBirth Date;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.