Question: The CEO of Dallas Bowling Inc. wants to build a data mart to keep trac

Question

Question:

The CEO of Dallas Bowling Inc. wants to build a data mart to keep track of bowler’s performance. Specifically, he wants to know each bowlers’ number of won games and average handicap score by each tournament and by each week. 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 Tourneyl TourneyDate PK TOURNEY MATCHES PK FK MATCH GAMES TourneylD Lanes OddLane TeamID FK EvenLaneTeamID FK CPK GameNumber CPK WinningTeamID FK TEAMS TeamID TeamName PK FK BOWLERS BOWLER SCORES MatchID GameNumber CPK PK BowlerFirstName CPK BowlerZipCode TeamID 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. Whena 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 gettwogame wins will win the match. The 'match_games' table shows the winner of each game, and "bowler_scores table shows the score of each bowlerwho played for that game. All bowlers' and teams' information such as team captain, and bowlername, are recorded in 'bowlers' and 'teams' tables. The figure above shows the logical schema of Dallas Bowling Inc.'s OLTP database

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.