DBMS-Assignment 2 Solved

Consider the data model for Inter-IIT Sports Meet in assignment for Lab Day 2. There is one change: Any participant can take part in any number of events (Team or Individual). Also, note that, there is (different) point for only the first three positions. For all other positions, there is zero point. The terms “Teams” and “IIT Names” are used interchangeably.  

 

For this system, we need to answer the queries mentioned below. Accordingly, you may update the ER-Diagram you have drawn and submitted against Lab Day 2. Draw the updated (or the previous one if there is no change) ER Diagram on paper. From the ER-Diagram, derive the tables using correct rules for relational model generation from ER model. Draw the tables on the piece of paper. Clearly mark the columns, primary keys and foreign keys as appropriate for each table. This forms your database schema.  

 

Create the tables as drawn on the paper in MySQL database using appropriate Create table SQL statements. You may choose appropriate data types for each column. Write SQL statements to answer the queries given below. You may test the SQLs by inserting appropriate rows in the tables. Note that, your database schema must correspond to the (updated) ER-Diagram you are submitting.   

 

Write your roll number and name on the piece of paper where you have drawn the ER diagram and the database schema. Note, the ER Diagram and the database schema must be drawn on paper and NOT using any tool.

List of Queries:

1.                List the players for each IIT sorted by IIT Name (IIT Name, Event Name, Player Id, Player Name).

2.                List the players who have participated in at most two events (IIT Name, Player Id, Player Name).

3.                List the players who have participated in one or more individual events but not in any team event sorted according to descending order of the number of individual events participated by him or her (Player Id, Player Name, IIT Name, Team Event Name).

4.                List the players along with their total individual event points and total team event points in ascending order of their team event points and within that in descending order of their individual event points (Player Id, Player Name, Total Team Event Points, Total Individual Event Points).

5.                List the teams that have scored more total team event points than total individual event points (IIT Name, Total Individual Event Points, Total Team Event Points).