This question strictly uses mysql.

Please use the database attached below for all questions.

Referential Integrity

Lets make sure our music database contains consistent data with good data integrity.

- (a) ALTER your tables so that no columns can contain a NULL value. All fields must be mandatory.

- (b) ALTER the bandmembers table so that the EndYear is set to 9999 if the band member is still currently in the band. You can use the DEFAULT value for that.

- (c) Define a Primary Key / Foreign Key relationship between Band Name in the bandmembers and

bandrecognition table and the bandinfo table - to ensure that it is impossible to have a Band Name in either table bandmembers or bandrecognition table which is not already in the bandinfo table.

- (d) Add a set of CONSTRAINTS on the bandinfo table to the following columns to ensure that their data values are meaningful: Formation Year, Current Status, Number of Releases, Number of Band Members, Genre

- (e) Add a set of CONSTRAINTS on the bandrecognition table to the following columns to ensure that their data values are meaningful: Nomination, Year

- (f) Add a set of CONSTRAINTS on the bandmembers table to the following columns to ensure that their data values are meaningful: Start Year, End Year, Role

- (g) Build a TRIGGER called bandmemnum which ensures that any time a Band Member is added or removed from the bandmembers table that the Number of Band Members in the bandinfo table is updated accordingly

- (h) Build a TRIGGER called bandmemstart which ensures that any time a Band Member is added or updated in the bandmembers table that the Band Member Start Year is not before the Band Formation Year in the bandinfo table.

Database running on mysql server.

CREATE DATABASE music; USE music; CREATE TABLE bandinfo ( BandName VARCHAR(255) NOT NULL, FormationYear INT(4) NOT NULL, CurrentStatus ENUM('Active', 'Inactive') NOT NULL, BaseCity VARCHAR(255) NOT NULL, BaseCountry VARCHAR(255) NOT NULL, NumberOfBandmembers INT(1) NOT NULL, NumberOfReleases INT(1) NOT NULL, Genre VARCHAR(255) NOT NULL, releases JSON, PRIMARY KEY (BandName) ); INSERT INTO bandinfo ( BandName, FormationYear, CurrentStatus, BaseCity, BaseCountry, NumberOfBandmembers, NumberOfReleases, Genre ) VALUES ( "BTS", 2010, "Active", "Soel", "South Korea", 5, 25, "KPop" ), ( "Mirror", 2018, "Active", "HongKong", "China", 12, 1, "Cantopop" ), ( "Wallows", 2017, "Active", "Los Angelos", "United States", 3, 3, "Rock" ), ( "The Beatles", 1960, "Inactive", "Liverpool", "United Kingdom", 4, 54, "Rock" ), ( "The Rolling Stones", 1962, "Active", "London", "United Kingdom", 3, 71, "Rock" ), ( "Led Zeppelin", 1968, "Inactive", "London", "United Kingdom", 4, 8, "Rock" ), ( "Nirvana", 1987, "Inactive", "Aberdeen", "United States", 3, 18, "Rock" ), ( "Coldplay", 1997, "Active", "London", "United Kingdom", 4, 9, "Rock" ), ( "Twice", 2015, "Active", "Soel", "South Korea", 9, 7, "KPop" ), ( "Green Day", 1987, "Active", "Los Angelos", "United States", 3, 13, "Rock" ), ( "Pink Floyd", 1965, "Inactive", "London", "United Kingdom", 4, 15, "Rock" ), ( "The Beach Boys", 1961, "Active", "Hawthorne", "United States", 5, 29, "Rock" ), ( "Simon & Garfunkel", 2009, "Inactive", "New York", "United States", 2, 5, "Folk" ), ( "U2", 1976, "Active", "Dublin", "Ireland", 4, 14, "Rock" ), ( "BLACKPINK", 2016, "Active", "Soel", "South Korea", 4, 3, "KPop" ), ( "The Kinks", 1963, "Inactive", "London", "United Kingdom", 4, 24, "Rock" ), ( "Maneskin", 2016, "Active", "Rome", "Italy", 4, 4, "Rock" ), ( "AC/DC", 1973, "Active", "Sydney", "Australia", 5, 17, "Rock" ), ( "One Direction", 2010, "Inactive", "London", "United Kingdom", 5, 5, "Pop" ), ( "The Kooks", 2004, "Active", "Brighton", "United Kingdom", 4, 6, "Pop" ); CREATE TABLE bandrecognition ( BandName VARCHAR(255) NOT NULL, Award VARCHAR(255) NOT NULL, Nomination ENUM('N', 'A') NOT NULL, Year INT(4) NOT NULL ); INSERT INTO bandrecognition (BandName, Award, Nomination, Year) VALUES ("BTS", "American Music Award", "A", 2021), ("Mirror", "Chill Club Awards", "A", 2022), ("Wallows", "MTV Video Music Awards", "N", 2021), ("The Beatles", "Grammy Award", "A", 1968), ("The Rolling Stones", "Grammy Award", "A", 1995), ("Led Zeppelin", "Grammy Award", "A", 2014), ("Nirvana", "MTV Video Music Awards", "A", 1994), ("Coldplay", "Brit Award", "A", 2004), ("Twice", "MAMA Award", "A", 2021), ("Green Day", "Juno Award", "A", 2006), ("Pink Floyd", "Grammy Award", "N", 1981), ("The Beach Boys", "Kids Choice Award", "N", 1989), ("Simon & Garfunkel", "Grammy Award", "N", 1969), ("U2", "Golden Globe Award", "N", 2010), ("BLACKPINK", "Teen Choice Award", "A", 2019), ("The Kinks", "NME Award", "N", 1965), ("Maneskin", "iHeartRadio Music Award", "A", 2022), ("AC/DC", "Echo Award", "N", 2006), ("One Direction", "Brit Award", "A", 2012), ("The Kooks", "NME Award", "N", 2007); ALTER TABLE bandinfo UPDATE bandinfo SET releases = '{"type": "Single", "year": 2021, "title": "Butter"}' WHERE BandName = 'BTS'; UPDATE bandinfo SET releases = '{"type": "Single", "year": 2019, "title": "Are You Bored Yet?"}' WHERE BandName = 'Wallows'; UPDATE bandinfo SET releases = '{"type": "Album", "year": 2020, "title": "The Album"}' WHERE BandName = 'BLACKPINK'; UPDATE bandinfo SET releases = '{"type": "Single", "year": 1991, "title": "Something in the Way"}' WHERE BandName = 'Nirvana'; UPDATE bandinfo SET releases = '{"type": "Album", "year": 2000, "title": "Parachutes"}' WHERE BandName = 'Coldplay'; CREATE TEMPORARY TABLE bandmembers ( BandName VARCHAR(255) NOT NULL, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255) NOT NULL, Role VARCHAR(255) NOT NULL, StartYear INT(4) NOT NULL, EndYear INT(4) NOT NULL ); INSERT INTO bandmembers ( BandName, LastName, FirstName, Role, StartYear, EndYear ) VALUES ("The Beatles", "Lennon", "John", "Guitar", 1960, 1969), ( "The Rolling Stones", "Jones", "Brian", "Lead Guitarist", 1962, 1969 ), ("Led Zeppelin", "Bonham", "John", "Drums", 1968, 1980), ( "The Beach Boys", "Campbell", "Glen", "Bass", 1964, 1965 ), ("The Kinks", "Gosling", "John", "Piano", 1970, 1978), ("Green Day", "Kiffmeyer", "John", "Drums", 1987, 1993);