Database systems assessment part B

Task

Your task is to implement your database design from Assignment Part A using SQL. You will be using the MySQL Query Browser program to complete this part of the assignment.

Logging In:

Refer to the file “MySQL Login Instructions.pdf” to learn how to access your MySQL account. You will be using your assignment schema for your assignment. That is, if your student number is 123, you should double click and select the schema called 123_assn.

Instructions:

Using various SQL commands you have learned, perform the following SQL queries:

• CREATE all tables (don’t forget your keys)
• INSERT all tuples of data (min, 3 rows per table)

After you have created all your tables and data, run SQL statements to perform the following:

• DELETE one row of data from each table

• 
  UPDATE one row of data from each table
• ALTER 3 tables by adding a new attribute And also:
• Prepare SELECT statement to display o Summary of the concert for each musician (multi table select) o Number of albums and songs for each record company (multi table select) o Most popular concert location (the most gigs are played there) o Other 7 useful query (at least 3 aggregate functions used)

NOTE: Not many of you used Foreign Keys in your previous assignment. You can add foreign keys to this assignment, which will help with some of the select statements (multitable). So if an entity belongs to another entity or is linked in some way, foreign key them.

E.G Artist/Musician may belong to a band. So the artist can have a foreighn key to a band, but it is allowed to be null. A music video is directed by a Director. So a video has a non nullable foreign key to the director table