Consider the following conceptual schema of a sample database that contains info

Question

Consider the following conceptual schema of a sample database that contains information about the sport teams, the games played by the teams, and the players who belong to the teams PLAYER Home-team GAME TEAM first-name ID last-name ID position number ADI PLays-to rA |name .game-number ID enue 1 city home-g round Away-team referee score-home-team score-away-team Implement the BSON documents that contain information about two teams, two players per team and two games played by the teams. Information included in the BSON documents must be consistent with a conceptual schema given above. BSON documents must contain information about the names of classes, the names of associations and the identifiers of classes. See presentation slides for an extended format of sample BSON documents. Whenever it is possible, the documents must be created as the hierarchical structures Next, create a MongoDB script solution2.js and include into the script the statements that insert the BSON documents created earlier into a collection. A name of MongoDB database used and a name of a collection with your documents is up to you. Next, insert into the script a statement that displays the BSON documents in a pretty format.

Explanation / Answer

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

select * from Players where birthYear=1970 and team='Braves';

//This is quite a simple query using and condition

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

select teamName from Teams where teamName not in (

select distinct team from Players where position='Pitcher');

//Here we use subqueries to find out the team where pitchers are there, then filter those results which do not have any pitchers from the Teams table.

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

select playerName from Players where team in (select teamName from Teams where leagueName='National');

//Here we use subqueries to list out the teamNames where they have played National leagues and select the playerName from those Teams.

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

select gameID from Games where homeTeamID in (select teamID from Teams where teamName='Phillies');

//Here we have the subquery to query the teamId where teamName is Phillies. This in turn helps us to find the gameID where the homeTeamId is the teamID foreign key to primary key relationship.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.