SQL — удалённая работа в Москве

учебный проект по SQL. Небольшой учебный проект по SQL. Дедлайн: 04.06 22:30. Проект на английском языке. Полностью о проекте: Instructions: 1. A design and implementation document which clearly explains the project must be prepared. This document (DOC) must consist of the following: a. ER diagram of your database, b. Explanations of your design and assumptions, especially for the first three questions, c. SQL query (SELECT) scripts of the query requirements and the first ten rows of outputs of the queries. 2. You must prepare an SQL file (SQL) that includes CREATE/ALTER/INSERT/UPDATE scripts. For questions 4,5, and 6, you should create only one SQL script file similar to hr-schema-mysql.sql on the Blackboard. Please test your script. I will run your SQL script file to evaluate your project. 3. The SQL statements should be written in a good style (indentation, etc.) and be heavily commented on. 4. The deadline is absolute; there will be no extension. 5. The codes at the end of the questions indicate whether your solution will be in the report document (DOC) or the SQL script (SQL). 6. In your report, for questions after the 6th question, it is sufficient to share only the first ten records of the results of your SQL queries in a table with your SQL query. Questions: Read the scenario below carefully and answer the following questions accordingly. You started to work as a database administrator for UEFA. You are asked to design a database containing information about European Football Leagues, including countries, leagues, seasons, teams, and players. Each league belongs to only one country, but one country may have many leagues, such as first, second, super, etc. Historically each team has been in many leagues for some period of time, but the current league of each team is only one. Similarly, each player has played for one or many teams, but the current team of each player is only one. For that purpose, you can create two date attributes to save start and end dates. For each past league, you must hold which team was champion. For each player, you must save how much he earned from each played team. Suppose there is a many-to-many relation between any two tables. In that case, you should specify for which tables this relation exists and convert one many-to-many relation to two one-to-many relation with additional third table. 2 1. Identify the tables in your database together with their attributes. (DOC-20) 2. Identify your database's primary and foreign keys by listing the attributes involved clearly. Explain your reasons. (DOC-10) 3. Draw your database's entity-relationship (ER) diagram by clearly showing tables, attributes, and primary and foreign keys. You can use lucid.app or diagrams.net to draw ER diagram. Or you can produce from ER diagram from MySQL Workbench after creating tables. (DOC-10) 4. Prepare SQL scripts that will create the tables in your database and their primary and foreign keys. (SQL-10) 5. Add other constraints to your tables and explain why you need these constraints. (SQL-5) 6. Prepare insert statements and insert at least ten records into every table. (SQL-5) 7. Find the team with the most champions in each country. (SQL-4) 8. Find out which teams each player plays for. (SQL-4) 9. Please find the total income earned by each player during his career. (SQL-4) 10. Please find out how many foreign players (the countries of a player and his team are different) currently play in each team. (SQL-4) 11. Find out how many foreign players played for each team in the past. (SQL-4) 12. Find the current league of each team. (SQL-4) 13. Find out in which league each team was in the five years after January 1, 2000 (You can change this date according to your inserted data.) (SQL-4) 14. Find all players who played in league L1 but never played in team T1 in league L1 (You can change T1 and L1 according to your records). (SQL-4) 15. Find players who played in country C1 and country C2 (You can change C1 and C2 according to your records). (SQL-4) 16. Find teams that have been in league L1 but have never been champions in that league (You can change L1 according to your records). (SQL-4).