Question: The CEO of Dallas Bowling Inc. wants to build two data marts to keep t

Question

Question:

The CEO of Dallas Bowling Inc. wants to build two data marts to keep track with team’s performance. He is concerned with how effective each team win a home game and win a visiting game. To measure the effectiveness of each team, he wants to use home win match percentage and visiting win match percentage. In addition, he wants to know the best player(s) of the match who scored the highest handicap score. Provide 4-step dimensional model in the SQL comment section and create data mart solution in SQL for the dimension model that you propose

TOURNAMENTS TourneylD TourneyDate TourneyLocation PK TOURNEY MATCHES MatchlD TourneylD OddLaneTeamID FK EvenLaneTeamlID FK PK Fk MATCH GAMES MatchID GameNumber CPK WinningTeamID FK CPK TEAMS PK TeamID TeamName CaptainlD FK BOWLERS BOWLER SCORES MatchID GameNumber CPK BowlerlD RawScore HandicapScore WonGame CPK BowlerID BowlerLastName BowlerFirstName BowlerMiddlelnit BowlerStreetAddress BowlerCity BowlerState BowlerZipCode BowlerPhoneNumber TeamID PK CPK FK Business scenario: Dallas Bowling Inc. has an OLTP database to record its tournament data. There are 6 tables stored inside the database When a tournament is announced, its date and location are stored in 'tournaments' table. The information of tournament match is kept in 'tourney_matches' table. When a match is played, the home team ID is classified as 'odd lane team ID', and the visiting team ID is classified as 'even lane team ID For each bowling match, two teams will play 3 games to decide the winner of the match, and the first team get two game wins will win the match. The 'match_games' table shows the winner of each game, and 'bowler_scores' table shows the score of each bowler who played for that game. All bowlers' and

Explanation / Answer

1)SELECT b.*FROM Bowlers bWHERE b.BowlerID IN (SELECT bs.BowlerIDFROM Bowler_Scores bsWHERE bs.rawscore <= 100);

2)SELECT t.*FROM Tournaments AS tWHERE t.tourneyID NOT IN (SELECT tm.TourneyIDFROM Tourney_Matches AS tm);

3)SELECT T.teamnameFROM Teams AS tWHERE t.teamid IN (SELECT tm.OddLaneTeamIDFROM tourney_matches AS tm)AND t.teamid NOT IN (SELECT tm.evenlaneteamIDFROM tourney_matches AS tm);

4)SELECT b.bowlerfirstname, b.bowlerlastnameFROM bowlers bWHERE b.teamid NOT IN (SELECT t.teamidFROM teams t);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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