Note: The only favor I want from you to write the answers for the following questions including the SQL Queries? I am using the MS SQL Server. This is what I have. Read the below problem and upon that kindly answer the following questions.
Please answer within 24 hours.
Problem 3: Although you always wanted to be a professional gamer, somehow you followed your other dream and ended up becoming an expert in databases. Your old love is still there, so you decide to build a Esports website for game lovers. The core of this website is a database with a schema that captures all the information about Esports. The database should keep information about video game companies that develop games, such as their unique names, the dates they were founded, headquarters, etc. You will also include some hot Esports games developed by these companies in your database. Each game is uniquely identified by its name, and you also want to record the developer company, release date, and genre (MOBA, fighting, shooting, etc). Each game has exactly one developer company, and the developer company must also be in your database You personally are a huge fan of several professional players, so you want to add them into your database as well. Each player is uniquely identified by an ID, and their names, nationality, and date of birth must be recorded. To be included in our database, a player must play at least one game that is contained in our dataset. Also, for every game that a player plays, there must be exactly one team that the player represents for that game. Every gaming team has a unique name, and you also want to record its founding date, where it is based, the owner of the team (you can assume that the owner is not a player). A gaming team could be competing in multiple games. E.g., Team Liquid competes in games such as Hearthstone, Rocket League, Super Smash Bros., etc. Some of the players might be playing multiple games and competing for multiple teams at the same time. For example, the player Daryl Koh Pei Xiang, also known as iceiceice, plays StarCraft II for the team FXOpen e-Sports. He's also competing in the game Dota2 representing the team Evil Geniuses. Your database should keep track of the team that the player currently plays for, what game the player represents the team to play, his/her role on that team such as "support" or "carry". Each gaming team will participate in various tournaments, and your database should store information about them. A tournament represents a competition between teams playing the same game, and is associated with one game only. Each tournament can be uniquely identified by the name and the season, and you should also record the game of the tournament, start date, location that it is being held at, prize pool (the total prize money). You should also store the result of the tournaments by recording the top 3 winning teams, their ranks, and the prize that they won (e.g. 1 million dollars). Since this is an initial draft of your database design, you decide to make it simple. Therefore, you do not care about the individual match in the tournament, but only how many teams are competing in a tournament, and the final result of that tournament. (a) Design a database for the above scenario using the ER model. Draw the ER diagram, show the cardinalities of all relationships, and identify primary keys and any weak entities. List any assumptions you have as well. (b) Convert your ER diagram into a relational schema. Identify all tables, attributes, primary keys, and foreign keys. (C) Write SQL queries for the following questions. If you cannot answer a query using your schema, then you have to modify your solutions in (a) and (b) appropriately. (i) For each game developer company, list their name together with the number of the games they have developed for each genre. The result should be first sorted in ascending order by company name, and if there is a tie, break the tie by sorting by the number of games for each genre in descending order. (output schema: company name, game genre, count number) (ii) For each team that plays the game PUBG, output the average age of the team members that play the role "Attacker". (iii) For each team that plays the game Dota2, but has never participated in any Dota2 tournament in 2020, output the name of all its team members without duplicates. (iv) Output the name of the gaming team(s) that has ranked in the top 3 for every single tournament participated in during 2020. (d) Create tables in the database system, and insert some sample data (5-10 tuples per table, but choose an interesting and meaningful data set, so that queries do not output empty results). Then execute the queries in (c) and submit the screenshots of the queries and outputs
