Where, Consider the following Database schema: Company (name, city, country) Dancer (did, name, birthyear, country) Show...

 

Transcribed Image Text:

Where, Consider the following Database schema: Company (name, city, country) Dancer (did, name, birthyear, country) Show (sid, title, choreographer, composer, year) Role (did, sid, role, company) [4 marks] Company stores information about dance companies. The attributes name, city, and country are all string; we assume that all companies have unique names. • • Dancer stores information about individual dancers. did is a unique integer id for each dancer, name is a string with the dancer's name, birthyear is an integer, and the dancer's native country is a string. Show stores information about ballet shows (dances). Each show has a unique integer id sid, string attributes for the show title, choreographer, and composer, and an integer year in which the show was created. • Role stores information about which dancers have been in which shows, the name of the role (part) they danced, and the company where they danced that part in that particular show. The dancer and show id's are integers, the role and company names are strings. A dancer may have danced multiple roles in the same show at the same company, or danced the same role in the same show for different companies, and so forth. • Several attributes in Role are foreign keys: did references did in Dancer, sid references sid in Show, and company references name in Company. Write the SQL queries for each of the following: a. Write the CREATE TABLE command needed to create the Dancer and Role tables. b. Write the SELECT query to show, for every dancer who has performed the role Black Swan' in the show "Swan lake' for one or more companies, list the name of the dancer and the company name(s), sorted by dancer name. If the dancer has performed that role for more than one company, there should be one line of output for each dancer, company pair. The companies can be listed in any order. c. Write the SELECT query to List the dancer ids (did) and names of all dancers born on or before 1950 and who have danced in at least three different shows. If a dancer has danced different roles in the same show, it still only counts once in the total number of shows. Each dancer/did pair should only be listed once. d. Write the SELECT query to show, for every dancer who has danced for one or more companies in a different country than where they were bom, list the name of the dancer and the names of those companies. Where, Consider the following Database schema: Company (name, city, country) Dancer (did, name, birthyear, country) Show (sid, title, choreographer, composer, year) Role (did, sid, role, company) [4 marks] Company stores information about dance companies. The attributes name, city, and country are all string; we assume that all companies have unique names. • • Dancer stores information about individual dancers. did is a unique integer id for each dancer, name is a string with the dancer's name, birthyear is an integer, and the dancer's native country is a string. Show stores information about ballet shows (dances). Each show has a unique integer id sid, string attributes for the show title, choreographer, and composer, and an integer year in which the show was created. • Role stores information about which dancers have been in which shows, the name of the role (part) they danced, and the company where they danced that part in that particular show. The dancer and show id's are integers, the role and company names are strings. A dancer may have danced multiple roles in the same show at the same company, or danced the same role in the same show for different companies, and so forth. • Several attributes in Role are foreign keys: did references did in Dancer, sid references sid in Show, and company references name in Company. Write the SQL queries for each of the following: a. Write the CREATE TABLE command needed to create the Dancer and Role tables. b. Write the SELECT query to show, for every dancer who has performed the role Black Swan' in the show "Swan lake' for one or more companies, list the name of the dancer and the company name(s), sorted by dancer name. If the dancer has performed that role for more than one company, there should be one line of output for each dancer, company pair. The companies can be listed in any order. c. Write the SELECT query to List the dancer ids (did) and names of all dancers born on or before 1950 and who have danced in at least three different shows. If a dancer has danced different roles in the same show, it still only counts once in the total number of shows. Each dancer/did pair should only be listed once. d. Write the SELECT query to show, for every dancer who has danced for one or more companies in a different country than where they were bom, list the name of the dancer and the names of those companies.

Answer rating: 100% (QA)

Step 1 of 4 a Write the CREATE TABLE command needed to create the Dancer and Role tables 1 CREATE TABLE Dancer 2 3 6 8 CREATE TABLE Role 9 2 13 14 15 ... View the full answer