Hw 2 Sql The Database You Will Use For This Assignment Conta

Hw 2 Sqlthe Database You Will Use For This Assignment Contains Infor

HW 2 - SQL The database you will use for this assignment contains information related to Major League Baseball (MLB) about players, teams, and games. The relations are: Players(playerID, playerName, team, position, birthYear); Teams(teamID, teamName, home, leagueName); Games(gameID, homeTeamID, guestTeamID, date). Your task is to write specific SQL queries based on this schema that work correctly with any valid database instance conforming to it, without modification.

Paper For Above instruction

The Major League Baseball (MLB) database offers a comprehensive schema capturing core elements of players, teams, and games. The relations include Players, Teams, and Games, with each relation designed to uniquely identify entities using primary keys. The Players relation holds details about individual players, including a unique playerID, playerName, team, position, and birthYear. The Teams relation records team-specific information such as a unique teamID, teamName, home city, and league membership—either "National" or "American." The Games relation logs game details such as gameID, identifiers of the home and guest teams, and the date of each game.

This schema is designed with normalization in mind, minimizing redundancy and facilitating effective data retrieval. For example, each player is uniquely identified by playerID, allowing for unambiguous reference. Team data is similarly keyed by teamID, ensuring each team’s attributes are stored singularly. The Games relation references teams via their IDs, establishing clear foreign key relationships that uphold referential integrity constraints. These constraints require that each homeTeamID and guestTeamID in Games must correspond to existing entries in the Teams relation, maintaining consistency.

To explore and query this database effectively, various SQL commands can be employed. For example, one can retrieve players born in a specific year and associated with a certain team, identify teams without a pitcher, list players who have participated in the National League, or find all games where the Phillies served as the home team. These queries utilize JOINs, WHERE conditions, subqueries, and set operations, demonstrating the schema’s flexibility for analytical tasks.

In addition, more complex queries include identifying teams that played against one specific opponent but not another, discovering pairs of players who have shared team membership at any point, and inferring special events such as World Series games from patterns in the data—specifically, games between teams from different leagues. These require combining relational algebra concepts with SQL expressions to detect such scenarios accurately.

Furthermore, the schema can be extended to better handle historical data about players’ team memberships over time, which is not directly captured in the current design. By adding an intermediary membership table with playerID, teamID, and temporal attributes (e.g., start and end dates), the data model can record multiple team affiliations per player without redundancies. This enhancement allows for more detailed historical analyses and aligns more closely with real-world complexities.

Altogether, the schema provides a robust foundation for storing, querying, and analyzing MLB data, supporting a range of straightforward to advanced database operations necessary for comprehensive sports analytics and reporting.

Answer to the assignment queries

Part 1: SQL queries

(a) Print the names of all players who were born in 1970 and played for the Braves.

SELECT playerName


FROM Players


WHERE birthYear = 1970 AND team = 'Braves';

(b) Print the names of teams that do not have a pitcher.

SELECT t.teamName


FROM Teams t


LEFT JOIN Players p ON p.team = t.teamName AND p.position = 'Pitcher'


WHERE p.playerID IS NULL;

(c) Print names of all players who have played in the National League.

SELECT DISTINCT p.playerName


FROM Players p


JOIN Teams t ON p.team = t.teamName


WHERE t.leagueName = 'National';

(d) Print all gameIDs with Phillies as the home team.

SELECT g.gameID


FROM Games g


JOIN Teams t ON g.homeTeamID = t.teamID


WHERE t.teamName = 'Phillies';

Part 2: More advanced SQL queries

(a) Print all teamIDs where the team played against the Phillies but not against the Braves.

SELECT DISTINCT g1.homeTeamID AS teamID


FROM Games g1


WHERE g1.guestTeamID IN (


SELECT teamID FROM Teams WHERE teamName = 'Phillies'


)


AND g1.homeTeamID NOT IN (


SELECT teamID FROM Teams WHERE teamName = 'Braves'


);

(b) Print all tuples (playerID1, playerID2, team) where playerID1 and playerID2 are (or have been) on the same team, avoiding duplicates and self-references.

SELECT DISTINCT p1.playerID AS playerID1, p2.playerID AS playerID2, p1.team


FROM Players p1


JOIN Players p2 ON p1.team = p2.team AND p1.playerID

(c) Print tuples where two teams from different leagues played against each other (implying World Series).

SELECT g.homeTeamID AS teamID1, t1.leagueName AS league1, g.guestTeamID AS teamID2, t2.leagueName AS league2, g.date


FROM Games g


JOIN Teams t1 ON g.homeTeamID = t1.teamID


JOIN Teams t2 ON g.guestTeamID = t2.teamID


WHERE t1.leagueName  t2.leagueName;

(d) List all cities that have a team in all leagues.

SELECT home


FROM Teams t1


WHERE NOT EXISTS (


SELECT DISTINCT leagueName FROM Teams


WHERE leagueName NOT IN (


SELECT leagueName FROM Teams t2 WHERE t2.home = t1.home


)


);

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.

Related Assignments