[Solved] CSC365 Project1

Q1 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.

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

Q2Suppose 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[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[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.

Q5write, 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.