Topic: Build Tables in an SQL Database

As a megatourpromoter of pop and rock concerts, you have promoted some of the biggest tours for some of the biggest acts in rock history. You are also a famous and talented systems analyst. Working from a well-validated UML class diagram, you developed a logical database design in schema notation. It appears on the next page.

INSTRUCTIONS

Build a database named MegaPromoter.

Create tables in the MegaPromotor database following the specifications in your Logical Database Design. Document your code fully with comments as demonstrated in the lectures. Your work will be faster and more accurate if you write the comments before you start writing code.

Four of the tables have Internal Object Identifiers for Primary Keys. The Logical Database Design specifies the starting value and increment for each Internal Object Identifier. Follow those specifications carefully

Starting on Page 3, there are data tables in SQL format. Paste them into INSERT statements in your script, and insert them into your tables.

Write SQL Queries to meet these requirements:

Show the complete contents of each table in turn.

Combine all the tables into one temporary table that shows all attributes of all tables, even where records in some tables dont match with records in others.

What Publicity Facts do we have on each Headliner whose act used to have a different name? Show only the ActName, the PiorActName, and the Publicity Facts.

At what venue did each of the acts play, and at what DateTime? List only ActNames, VenueNames, and EventDateTimes in chronological order.

Logical Database Design in Schema Notation for the MegaPromotor Databse:

ACTS (ActID, ActName not null unique, Solo_or_band) -- ActID should start with 1001 and increment by 1

CONCERT TOURS (tourID, TourName not null, StartDate, EndDate) unique (TourName) TourID should start with 2001 and increment by 1

CONCERTS (ConcertID, Profit, TourID not null) fk TourID references CONCERTTOURS ConcertIDs should start with 3001 and increment by 1

VENUES (VenueID, VenueName not null, City not null, Country not null) unique (VenueName, City, Country) -- VenueIDs should start with 4001 and increment by 1

FACILITIES CONTRACTS (FacContractID, ConcertID, VenueID, EventDateTime, VenueCost) fk ConcertID not null references CONCERTS fk VenueID not null references VENUES unique (ConcertID, VenueID, EventDateTime)

HEADLINERS (ActID, PublicityFacts) fk ActID references ACTS

PERFORMANCE CONTRACTS (PerfContractID, ActID, ConcertID, Opener_or_main_act, PerformanceFee) fk ActID references ACTS fk ConcertID references CONCERTS

PREVIOUS ACT NAMES (ActID, PrtiorActName) fk ActID references ACTS unique (PriorActName)

DATA FOR MEGAPROMOTOR TABLES

Copy these tables and past them into your MegaPromotor Script. Use an appropriate SQL clause to insert each into its appropriate table.

-------------------------------------

-- Load data into the ACTS Table --

-------------------------------------

-- Schema: ACTS (ActID, ActName, Solo_or_band)

('Beatles' , 'Band'),

('Rolling Stones' , 'Band'),

('Bob Dylan' , 'Solo'),

('Elvis Presley' , 'Solo'),

('Chuck Berry' , 'Solo'),

('Jimmi Hendrix' , 'Solo'),

('James Brown' , 'Solo'),

('Little Richard' , 'Solo'),

('Aretha Franklin' , 'Solo'),

('Ray Charles' , 'Solo'),

('Bob Marley' ,'Solo'),

('The Beach Boys' , 'Band'),

('Buddy Holly' , 'Solo'),

('Led Zeppelin' , 'Band'),

('Stevie Wonder' , 'Solo'),

('Sam Cooke' , 'Solo'),

('Muddy Waters' , 'Solo'),

('Marvin Gay' , 'Solo'),

('Velvet Underground', 'Band'),

('Bo Diddley' , 'Solo'),

('Otis Redding' , 'Solo');

----------------------------------------------

-- Load data into the CONCERT TOURS Table --

----------------------------------------------

-- Schema: CONCERT TOURS (tourID, TourName, StartDate, EndDate)

(' Out There Tour' , '2009-12-23','2018-03-03'),

('Heart of Rock and Roll Tour', '1971-06-14','1974-12-31'),

('Epic Wonder Tour' , '1984-03-01','1986-03-10'),

('Never Again Tour' , '1998-11-21','1999-12-31'),

('Rock the Stadium Tour' , '2011-11-16','2011-12-31'),

('Legends of Rock Tour' , '2018-01-01','2020-03-10');

----------------------------------------------

-- Load data into the CONCERTS Table --

----------------------------------------------

-- Schema: CONCERTS (ConcertID, Profit, TourID)

(134, 2001),

(372, 2001),

(303, 2001),

(209, 2001),

(544, 2001),

(705, 2001),

(211, 2001),

(306, 2001),

(395, 2001),

(468, 2001),

(343, 2001),

(567, 2002),

(707, 2002),

(298, 2002),

(419, 2002),

(128, 2002),

(300, 2002),

(584, 2002),

(462, 2002),

(701, 2002),

(640, 2002),

(495, 2003),

(556, 2003),

(720, 2003),

(139, 2003),

(370, 2003),

(713, 2003),

(466, 2003),

(245, 2003),

(286, 2003),

(393, 2003),

(360, 2003),

(126, 2003),

(640, 2004),

(318, 2004),

(145, 2004),

(325, 2004),

(739, 2004),

(573, 2004),

(187, 2004),

(598, 2004),

(225, 2004),

(725, 2004),

(484, 2005),

(463, 2005),

(656, 2005),

(327, 2005),

(315, 2005),

(490, 2005),

(242, 2005),

(288, 2005),

(574, 2005),

(720, 2005),

(168, 2006),

(447, 2006),

(390, 2006),

(437, 2006),

(441, 2006),

(631, 2006),

(523, 2006),

(416, 2006),

(591, 2006),

(385, 2006),

(289, 2006),

(262, 2006);

----------------------------------------------

-- Load data into the VENUES Table --

----------------------------------------------

-- Schema: VENUES(VenueID, VenueName, City, Country)

('Wembley Stadium' , 'London' , 'Great Britain'),

('Red Rocks Amphitheater', 'Denver' , 'USA' ),

('Mineirao Stadium' , 'Belo Horizonte', 'Brazil' ),

('Albert Hall' , 'London' , 'England' ),

('Circus Maximus' , 'Rome' , 'Italy' ),

('Gorge Amphitheatere' , 'George' , 'USA' ),

('Crosby Theater' , 'Santa Fe' , 'USA' ),

('Jay Pritzker Pavilion' , 'Chicago' , 'USA' ),

('Starlight Theater' , 'Kansas City' , 'USA' ),

('Hollywood Bowl' , 'Los Angeles' , 'USA' );

-----------------------------------------------------------

-- Load data into the FACILITIESCONTRACTS Table --

-----------------------------------------------------------

-- Schema FACILITIESCONTRACTS (FacContractID, ConcertID, VenueID, EventDateTime, VenueCost)

(3001, 4001,'2012-10-28 09:34:53', 1095490),

(3002, 4002,'2015-07-07 02:26:54', 514251 ),

(3003, 4003,'2013-04-07 15:17:15', 1008668),

(3004, 4004,'2012-07-20 21:18:55', 999984 ),

(3005, 4005,'2014-03-05 08:39:58', 763930 ),

(3006, 4006,'2018-02-09 10:17:43', 832094 ),

(3007, 4007,'2012-03-28 18:34:43', 627939 ),

(3008, 4008,'2012-12-27 21:41:41', 982572 ),

(3009, 4009,'2016-09-26 19:35:53', 704923 ),

(3010, 4010,'2017-01-09 23:24:16', 478484 ),

(3011, 4001,'2012-01-18 11:42:37', 913862 ),

(3012, 4002,'1973-11-19 05:35:00', 602192 ),

(3013, 4003,'1971-10-25 00:48:27', 524811 ),

(3014, 4004,'1974-02-02 22:31:38', 868666 ),

(3015, 4005,'1973-10-02 02:25:25', 618865 ),

(3016, 4006,'1973-03-20 00:54:51', 879682 ),

(3017, 4007,'1974-11-14 04:20:20', 801523 ),

(3018, 4008,'1972-08-18 15:37:59', 744929 ),

(3019, 4009,'1974-08-08 02:03:19', 1071044),

(3020, 4010,'1971-12-21 02:43:09', 1087707),

(3021, 4001,'1973-02-20 17:09:24', 996878 ),

(3022, 4002,'1984-11-07 09:11:41', 995097 ),

(3023, 4003,'1984-01-02 05:53:40', 634303 ),

(3024, 4004,'1984-05-02 23:50:01', 528830 ),

(3025, 4005,'1984-02-15 00:49:41', 857259 ),

(3026, 4006,'1985-01-24 18:45:08', 852934 ),

(3027, 4007,'1985-03-19 10:56:06', 739043 ),

(3028, 4008,'1986-05-16 10:29:36', 620803 ),

(3029, 4009,'1986-05-07 17:38:01', 526559 ),

(3030, 4010,'1984-02-11 21:42:41', 628345 ),

(3031, 4001,'1984-11-14 04:12:56', 546484 ),

(3032, 4002,'1986-06-26 23:21:50', 598918 ),

(3033, 4003,'1986-05-08 14:23:50', 1000973),

(3034, 4004,'1999-12-08 03:51:41', 925503 ),

(3035, 4005,'1998-09-18 11:14:56', 931756 ),

(3036, 4006,'1999-08-24 07:00:57', 782739 ),

(3037, 4007,'1999-02-17 10:47:20', 998110 ),

(3038, 4008,'1999-07-07 01:29:25', 468574 ),

(3039, 4009,'1998-08-15 19:54:19', 948084 ),

(3040, 4010,'1998-11-08 14:56:07', 605899 ),

(3041, 4001,'1999-06-18 16:40:52', 844254 ),

(3042, 4002,'1998-02-01 22:51:40', 459820 ),

(3043, 4003,'1999-10-17 07:37:00', 572271 ),

(3044, 4004,'2011-04-01 23:21:59', 774630 ),

(3045, 4005,'2011-01-24 23:24:38', 647938 ),

(3046, 4006,'2011-03-22 02:21:48', 592350 ),

(3047, 4007,'2011-11-17 04:16:46', 978394 ),

(3048, 4008,'2011-01-15 22:17:31', 593684 ),

(3049, 4009,'2011-01-03 21:36:02', 507163 ),

(3050, 4010,'2011-08-01 15:17:45', 486624 ),

(3051, 4001,'2011-09-27 01:07:17', 868737 ),

(3052, 4002,'2011-05-23 01:56:48', 790427 ),

(3053, 4003,'2011-08-26 22:22:33', 561385 ),

(3054, 4004,'2020-12-28 16:44:46', 474557 ),

(3055, 4005,'2018-10-28 19:43:20', 811787 ),

(3056, 4006,'2018-06-18 07:33:04', 932823 ),

(3057, 4007,'2019-07-25 15:43:13', 665634 ),

(3058, 4008,'2019-05-22 17:03:58', 963049 ),

(3059, 4009,'2018-07-08 15:49:17', 791943 ),

(3060, 4010,'2018-03-23 14:48:26', 927979 ),

(3061, 4001,'2018-09-01 08:49:02', 542138 ),

(3062, 4002,'2019-09-27 12:19:11', 873337 ),

(3063, 4003,'2020-06-09 21:18:38', 1009686),

(3064, 4004,'2018-08-23 05:42:04', 607525 ),

(3065, 4005,'2020-01-23 08:09:41', 1068667);

-----------------------------------------------------------

-- Load data into the HEADLINERS Table --

-----------------------------------------------------------

-- Schema: HEADLINERS (ActID, PublicityFacts)

(1001, 'Juciy Details!' ),

(1003, 'Scandal!' ),

(1005, 'Love Story!' ),

(1007, 'New movie!' ),

(1009, 'Engaged!' ),

(1011, 'Born in a log cabin!' ),

(1013, 'Grew up poor!' ),

(1015, 'Studied Accounting at Northwestern U!' ),

(1017, 'Wants to go to the moon!' ),

(1019, 'Seen at Monte Carlo!' ),

(1021, 'Ready to settle down!' );

---------------------------------------------------------------

-- Load data into the PerformanceContracts Table --

---------------------------------------------------------------

-- Schema: PERFORMANCECONTRACTS (PerfContractID, ActID, ConcertID, Opener_or_main_act, PerformanceFee)

(1001, 3001, 'Opener', 889601),

(1003, 3002, 'Main', 938145),

(1005, 3003, 'Opener', 183628),

(1007, 3004, 'Main', 861206),

(1009, 3005, 'Opener', 887687),

(1011, 3006, 'Main', 934944),

(1013, 3007, 'Opener', 593185),

(1015, 3008, 'Main', 797033),

(1017, 3009, 'Opener', 549914),

(1019, 3010, 'Main', 115725),

(1021, 3011, 'Opener', 448755),

(1001, 3012, 'Main', 189741),

(1003, 3013, 'Opener', 638518),

(1005, 3014, 'Main', 787623),

(1007, 3015, 'Opener', 571446),

(1009, 3016, 'Main', 392296),

(1011, 3017, 'Opener', 637314),

(1013, 3018, 'Opener', 149069),

(1015, 3019, 'Opener', 305352),

(1017, 3020, 'Opener', 604415),

(1019, 3021, 'Opener', 393561),

(1021, 3022, 'Main', 791355),

(1001, 3023, 'Opener', 742177),

(1003, 3024, 'Main', 494874),

(1005, 3025, 'Opener', 312665),

(1007, 3026, 'Main', 155901),

(1009, 3027, 'Opener', 901523),

(1011, 3028, 'Main', 963533),

(1013, 3029, 'Main', 853048),

(1015, 3030, 'Main', 397015),

(1017, 3031, 'Main', 428703),

(1019, 3032, 'Main', 538248),

(1021, 3033, 'Main', 134069),

(1001, 3034, 'Main', 799205),

(1003, 3035, 'Opener', 312182),

(1005, 3036, 'Main', 620399),

(1007, 3037, 'Opener', 916763),

(1009, 3038, 'Main', 117761),

(1011, 3039, 'Opener', 318641),

(1013, 3040, 'Main', 931355),

(1015, 3041, 'Opener', 882078),

(1017, 3042, 'Main', 261565),

(1019, 3043, 'Opener', 522963),

(1021, 3044, 'Main', 974698),

(1001, 3045, 'Main', 273630),

(1003, 3046, 'Main', 786986),

(1005, 3047, 'Main', 330392),

(1007, 3048, 'Main', 295334),

(1009, 3049, 'Main', 408498),

(1011, 3050, 'Main', 304800),

(1013, 3051, 'Opener', 348720),

(1015, 3052, 'Main', 108622),

(1017, 3053, 'Opener', 433426),

(1019, 3054, 'Main', 676757),

(1021, 3055, 'Opener', 812875),

(1001, 3056, 'Main', 471923),

(1003, 3057, 'Opener', 782095),

(1005, 3058, 'Opener', 500782),

(1007, 3059, 'Opener', 332001),

(1009, 3060, 'Opener', 134809),

(1011, 3061, 'Opener', 848570),

(1013, 3062, 'Opener', 477585),

(1015, 3063, 'Opener', 368862),

(1017, 3064, 'Opener', 497944),

(1019, 3065, 'Opener', 246984),

(1021, 3001, 'Main', 206756),

(1001, 3002, 'Opener', 839688),

(1003, 3003, 'Main', 837506),

(1005, 3004, 'Opener', 906728),

(1007, 3005, 'Main', 921733),

(1009, 3006, 'Opener', 814265),

(1011, 3007, 'Main', 559422),

(1013, 3008, 'Opener', 923802),

(1015, 3009, 'Main', 845776),

(1017, 3010, 'Opener', 569232),

(1019, 3011, 'Main', 216250),

(1021, 3012, 'Opener', 601800),

(1001, 3013, 'Main', 449857),

(1003, 3014, 'Opener', 991743),

(1005, 3015, 'Main', 623802),

(1007, 3016, 'Opener', 793561),

(1009, 3017, 'Opener', 549400),

(1011, 3018, 'Opener', 459926),

(1013, 3019, 'Opener', 882958),

(1015, 3020, 'Opener', 701151),

(1017, 3021, 'Opener', 937066),

(1019, 3022, 'Opener', 576664),

(1021, 3023, 'Main', 861459),

(1001, 3024, 'Opener', 611181),

(1003, 3025, 'Main', 308043),

(1005, 3026, 'Opener', 433033),

(1007, 3027, 'Main', 791063),

(1009, 3028, 'Opener', 540041),

(1011, 3029, 'Main', 646104),

(1013, 3030, 'Opener', 470010),

(1015, 3031, 'Main', 985283),

(1017, 3032, 'Opener', 776585),

(1019, 3033, 'Main', 201362),

(1021, 3034, 'Opener', 559303),

(1001, 3035, 'Main', 266456),

(1003, 3036, 'Opener', 137065),

(1005, 3037, 'Main', 869997),

(1007, 3038, 'Opener', 813251),

(1009, 3039, 'Main', 121199),

(1011, 3040, 'Opener', 328787),

(1013, 3041, 'Main', 335750),

(1015, 3042, 'Opener', 524412),

(1017, 3043, 'Main', 959720),

(1019, 3044, 'Opener', 228569),

(1021, 3045, 'Main', 473954),

(1001, 3046, 'Opener', 989596),

(1003, 3047, 'Main', 442484),

(1005, 3048, 'Opener', 802968),

(1007, 3049, 'Main', 453943),

(1009, 3050, 'Opener', 570224),

(1011, 3051, 'Main', 154880),

(1013, 3052, 'Opener', 798754),

(1015, 3053, 'Main', 906306),

(1017, 3054, 'Opener', 777973),

(1019, 3055, 'Main', 985185),

(1021, 3056, 'Opener', 992457),

(1001, 3057, 'Main', 783559),

(1003, 3058, 'Opener', 514032),

(1005, 3059, 'Main', 361742),

(1007, 3060, 'Opener', 631692),

(1009, 3061, 'Main', 291409),

(1011, 3062, 'Opener', 334306),

(1013, 3063, 'Main', 776634),

(1015, 3064, 'Opener', 517473),

(1017, 3065, 'Main', 334348);

---------------------------------------------------------------

-- Load data into the PriorNames Table --

---------------------------------------------------------------

-- Schema: PREVIOUSACTNAMES (ActID, PreviousActName)

( 1001 , ' Quarymen '),

( 1002 , ' Stolling Rones '),

( 1003 , ' Bill Johnson '),

( 1007 , ' Charles Burly '),

( 1006 , ' James Hendricks '),

( 1008 , ' Big Rick '),

( 1011 , ' Robert Marleybone '),

( 1012 , ' Guys who hang around the beach and stuff ');