MySQL Create ArtiststudioContract Table containing ArtistID

MySQL

Create ArtiststudioContract Table containing ArtistID, StudioID, and a ContractDate (Date type). Change ArtiststudioContract table from question 1 to have foreign keys of ArtistID to Artists table and StudioID to Studios table Add SaleID to ArtiststudioContract table with foreign key to Salespeople table Add a record to ArtiststudioContract table indicating artist id 1 signed up contract sales person id 3 for studio id 2 and artist id 5 signed up with sales person id 1 with studio id 3 while both having contract date being Artist\'s entry date Artist ID 1 decide to leave the company, delete Artist id 1 from the Artist table Create ArtistPages table containing ArtistID (with ArtistID having foreign key to Artist table) and WebAddress Copy ArtistID and WebAddress from Artist table to ArtistPages table Delete the column WebAddress from the Artist table Artist id 5 decide to be a developer and decide to remove himself out of artist table. Delete Artist id 5 from artists table Create a view called ArtistNamePages containg ArtistID, ArtistName, Country and WebAddress from ArtistPages table

Solution

1. Create Table ArtistStudioContract

( ArtistID INT NOT NULL,

StudioID INT NOT NULL,

ContractDate Date,

2. FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID) ON DELETE CASCADE,

FOREIGN KEY (StudioID) REFERENCES Studios(StudioID) ON DELETE CASCADE,

3. SaleID INT,

FOREIGN KEY(SaleID) REFERENCES SalesPeople(SaleID)

)ENGINE = INNODB;

4. Insert into ArtistStudioContract values(1,3,NOW(),1);

Insert into ArtistStudioContract values(5,3,NOW(),1);

5. Delete from Artist where ArtistID =1; (ON DELETE CASCADE in CREATE TABLE);

6. Create Table ArtistPages

(ArtistID INT NOT NULL,

FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID),

WebAddress varchar(100));

7.

8. Alter table Artist Drop column WebAddress;

9. delete from Artist where ArtistID = 5;

10. Create View ArtistNamePages AS Select ArtistID,ArtistName,Country,WebAddress from ArtistPages;