Assignment 1 Solution

Description

Q1[total 10: (-1 point for every mistake)]​Consider a table ​Payment​.It has the following fields: payment_id​,​customer_id​,​staff_id​,​amount​,​payment_date​.The ​payment_id​field identifies the tuple. The ​customer_id​and ​staff_id​fields are foreign keys to the ​Customer​and ​Staff ​table, respectively. It tells us which customer made the payment and which staff member processed the payment. The last field denotes the date when the payment was made. Obviously, a customer can make several payments on the same date. Complete the following picture by putting checkmarks where appropriate. The picture is for the ​Payment​table. For example, there is a checkmark for ​payment_id​and ​Primary key​because payment_id​is the primary key for the ​Payment​table.

Primary key

Candidate Key

Super key

payment_id

customer_id

payment_id,customer_id

amount, payment_date

payment_id,staff_id

For the following questions, download the assignment1.zip file from Polylearn.

Q2[total 20: 5 per table]​Suppose you want to record information about soccer players, their teams, the games where they played, and events (such as ‘a goal scored’) in each game. Create a ​Player​table that stores information about the soccer players. Create a ​Game​table that stores information about the games. Every game is between two teams. Create a ​Event​table recording all events occurred in each game. Create a ​Team​table that stores information about all the teams. Every soccer player belongs to exactly one team (i.e., add a ​team_id​field to the ​Player​table).

Submit the ​create table​statements for the four tables. Include primary key and foreign key constraints.

Q3[20: 10 per table]​Create INSERT statements to insert all the data given in supplied text files containing json data to the tables (statements for two of the tables are given. Your task it to crate statements for the remaining two tables). It is recommended that you use Python rather than Java. You need to create a file containing insert statements just like the provided teams.sql and players.sql files.

Q4[total 25] ​Create the tables in MySQL and populate them with the data using the insert statements including the ones you created. ​You should be able to insert all the data into the 4 tables without an error or warning. If you get an error or warning, that means that your table schema needs to be changed.

You can execute sql statements in a file from mysql shell with the following command: source [filename];

Replace the [filename] with a file name containing sql statements. You might need to prepend the path to the file to the filename if the file is not in your current directory on your machine.

Q5[total 25: 5 per query]​write, test, and submit the SQL for the following queries. Add the ​distinct keyword if you want to eliminate duplicates.

1. Print the names of soccer players that have scored a hat-trick (3 or more goals per game).

2. Print the names of teams that have one or more players that have scored a hat-trick.

3. Print the names of teams that have scored more than 3 goals in a single game.

4. Suppose that a team that won the most games win the championship. Write a query to find out which team won the most games. i.e. find the champion team.

5. Write a query to list all the teams that beat the champion team.

Zip your files containing the answer to Q1 and sql statements and submit it to polylearn. Do not forget to include your name in the files.