Mini-case: Mega-promoter Topic: Bulld Tables in an SQL Database As a mega-tour-promoter 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 database designer. Working from a well-validated UML class diagram (see below), you developed a logical database design in schema notation (also called relational notation) (see below-er). After you finished creating your logical database design, you annotated each attribute with a data type, so you didn't have to think about that while you writing SQL to build the tables. INSTRUCTIONS 1. Create tables for the MegaPromotor database following the specifications in your Logical Database Design. Be sure to include the prefix, tbl on each of your table names, as discussed in class. For example, a the table for the ACTS class should be 'tblActs' 2. 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. 3. 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 4. 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 5. You may write Select statements to test that the data appear in your tables, but be sure to delete the Select statements before you submit the assignment for grading. HEADLINERS Publicity Facts PREVIOUS ACT NAMES ACTS Identify 0.. Prior Act Name 1.1 Act Name (CK) Solo-or-Band 0.. Hired For LIVE PERFORMANCE CONTRACTS 0.. CONCERT TOURS Opener-or-main-act Performance Fee Comprise CONCERTS Tour Name Start Date End Date 1..1 0... Profit 1..1 Reserved with 0..1 VENUES FACILITIES CONTRACTS Reserved By VenueName (CK1) City (CK1) Country (CK1) 1..1 0.. Date Time Venue Cost Logical Database Design in Schema Notation for the MegaPromotor Databse: Highlight Rotate Markup ACTS (ActID:int, Act Name:varchar(128) not null unique, Solo_or_band-varchar(4) -- ActiD starts at 1001 and auto-increments by 1 CONCERT TOURS (touriD:int, TourName:varchar(128) not null, StartDate:date, EndDate:date) unique (TourName) - TouriD starts at 2001 and auto-increments by 1 CONCERTS (ConcertID:int, Profit:int, TouriD:int not null) fk TouriD references CONCERTTOURS - Concertid starts at 3001 and auto-increments by ? VENUES (VenuelDzint, VenueName varchar(128) not null, City varchar(128) not null, Country varchar(128) not null) unique (VenueName, City, Country) 1- Venuel starts at 4001 and outo Increments by 1 FACILITIES CONTRACTS (FacContractiDant, Concertine, Venuel.ine, EventDateTime:dotetime, VenueCost:it) fk Concertid not null references CONCERTS fk VenuelD not null references VENUES unique (Concerti, Venuel, EventDateTime) - FocContract starts at 6001 and auto increments by 1 FACILITIES CONTRACTS (FacContractID:int, ConcertID:int, VenueID:int, EventDateTime:datetime, VenueCost:int) fk Concertid not null references CONCERTS fk VenuelD not null references VENUES unique (ConcertiD, VenuelD, EventDateTime) --FocContract starts at 6001 and auto-increments by 1 HEADLINERS (ActID:int, PublicityFacts:varchar(128)) fk ActID references ACTS PERFORMANCE CONTRACTS (PerfContractID:int, ActiD.int, Concertid:int, Opener_or_main_activarchar(8), PerformanceFee:int) fk ActiD references ACTS fk ConcertiD references CONCERTS -- PerfContractiD starts at 5001 and auto-increments by 1 PREVIOUS ACT NAMES (ActID:int, PriorActName varchar(128)) fk ActID references ACTS; DATA FOR MEGAPROMOTOR TABLES Copy these tables and paste 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'); 3 > 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, 2884) (598, 2004) (225, 2004) (725, 2084), (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(Venue ID, 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' > 3 tigight laters Marris Load data into the FACILITIESCONTRACTS Table Schema FACILITIESCONTRACTS (FacContractID, ConcertID, Venue ID, EventDateTine, Venuecost) (3001, 4801, 2012-10-28 09:34:53', 1895498), (3802, 4082, '2015-07-07 02:26:54', 514251 ), (3803, 4003, 2013-04-07 15:17:15', 1888668), (3084, 4804, 2012-07-28 21:18:55', 999984), (3085, 4085, 2014-03-05 08:39:58 763930), (3886, 4886, '2018-02-09 18:17:43', 832094), (3087, 4807, 2012-03-28 18:34:43', 627939), (3888, 4088, '2012-12-27 21:41:41 982572), (3899, 4009, 2016-09-26 19:35:53', 784923 ), (381e, 4010, 2017-01-09 23:24:16', 478484), (3011, 4801, 2012-01-18 11:42:37 913862) (3012, 40e2, '1973-11-19 05:35:80 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, 4886, 1973-03-20 20:54:51', 879682 ), (3017, 4007, '1974-11-14 04:20:20', 801523), (3018, 4008, '1972-08-18 15:37:59 744929), (3019, 4099, '1974-08-08 02:03:19', 1071044), (3eze, 4010, '1971-12-21 02:43:09 1087707) (3021, 4001, '1973-02-20 17:09:24', 996878 (3022, 4002, '1984-11-87 09:11:41, 995097 (3023, 4003, '1984-01-02 05:53:40, 634303), (3024 4004, '1984-05-02 23:50:01 528830 3025 4085, 1984-02-15 00:49:41 857259 (3026, 4006, '1985-01-24 18:45:08 852934 (3027, 4007, 1985-03-19 10:56:06'739843 3828 4008, 1986-05-16 10:29:36 620803 526559 3029 4009, '1986-05-07 17:38:01 1 28345 BARA 4A1A 1984 A2.11 21:42:41 (3030, 4010, '1984-02-11 21:42:41', 628345 ), (3031, 4001, '1984-11-14 04:12:56', 546484 ), (3032, 4802, '1986-06-26 23:21:50', 598918 ), (3033, 4003, '1986-05-08 14:23:59', 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), 5 (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, 4908, 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, 796427 ), (3053, 4003, 2011-08-26 22:22:33', 561385), (3053, 4883, '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:84', 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, 4084, '2018-08-23 05:42:04', 607525 ), (3065, 4005, 2028-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!' Highlight Rotate Markus Load data into the Performancecontracts Table Star Schema: PERFORMANCECONTRACTS (PerfContract ID, ActID, Concertid, Opener_or_main_act, Performance fee) (1801, 3001, Opener', 889601), (1803, 3892, 'Main', 938145), (1885, 3083, 'Opener', 183628), (1807, 3084, 'Main', 861286), (1889, 3885,"Opener', 887687), (1011, 3086, 'Main 934944), (1013, 3887 Opener" 593185), (1015, 3008 Main', 797033), (1017, 3009, 'Opener', 549914), (1819, 3010, 'Main 115725), (1021, 3011, Opener', 448755), (1901, 3012, 'Main' 189741) (1003, 3013) 'Opener', 638518) (1005, 3014) 'Main 787623) (1807, 3015, Opener 571446) (10093016 Main 392296) (1011, 2017 Opener', 637314) (1013, 3018 Opener 149069) (1015 3019 Opener 305352) (1017, 3020 Opener 604415) 1019 3021 Opener' 393561). (1921, 3022 Main 791355) (1001, 3023 "Opener, 742177) 1803 3024 Main 494874) (1805, 3025 "Opener 312665) (1807 3026 Main' 155901) (18093027, 'Opener901523) Uz, SUZZMain791355), (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', 853948), (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), (1091, 3045, "Main', 273630) (1003, 3046, 'Main', 786986), (1005, 3947, 'Main', 330392), (1007, 3048, "Main', 295334) (1009, 3049, 'Main', 408498), (1011, 3850, 'Main', 304800) (1013, 3851, '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) (1001, 3024, 'Opener', 611181), (1003, 3025, 'Main', 308943), (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 1002 1003 1007 1006 1008 1011 1012 Quarymen Stolling Rones Bill Johnson '), Charles Burly '), James Hendricks Big Rick Robert Marleybone "), Guys who hang around the beach and stuff '); 3 . ( >