CMSC424 - Database DesignSQL Assignment (Parts A and B)Fall 2007Prof. Nick RoussopoulosTA: Sam HuangPart A Due Date: Tuesday, October 2, 2007 at 12:30 pmPart B Due Date: Tuesday, October 9, 2007 at 12:30 pmLast modified October 7, 2007This is actually two separate assignments, broken into two parts (Part A and Part B). Thedue dates are different (see above).These assignments consists of the student (you) constructing SQL queries for a supplieddata set. The queries present in Part A are meant to be introductory, to get you familiar withthe Oracle SQL interface, while the Part B is more involved.The database you will be writing queries about contains information concerning the Associ-ation of Tennis Professionals. It contains information about players, matches and tournamentsconcerning the ATP. A copy of the relationships in the database, as well as a few sample en-tries, is included at the end of this file. Primary keys are underlined. If you are unfamiliarwith tennis terminology (especially concerning scoring), a quick description can be found onWikipedia athttp://en.wikipedia.org/wiki/Tennis#Manner of play.Several assumptions about the database are included below:• The source of the data used for this database only contained information about the ATP(men’s tennis) and not the WTA (women’s tenn is). All players involved are male; wehave no information pertaining to women’s singles or doubles, or mixed doubles.• While it should be clear that Player’s PID and Tournament’s TID field should be unique,this is also true for Match’s MID field; no IDs are re-used throughout different tourna-ments. Thus, a Match’s MID field uniquely represents it across all Matches known toexist. This avoids having to compare Tournament IDs as well as Match IDs for severalof the queries.• When a player participates in a tournament, he first has to register. For the purposes ofour database, this includes receiving a registration number for the tournament. Becausethe ATP manages all of the tournaments in our database, we assume that a registrationnumber is unique throughout all of the tournaments. This means that given a only aregistration number, we can determine the tournament it corresponds to.1• A Player’s ID (Player.PID) is only used in our database for registration in tournaments.All other locations the Player is referred to by the RegistrNum (notably in the Winnerattribute of MatchResults).• Two players participating in doubles tennis as a team share the same registration num-ber. Note that several players register for both singles and doubles tennis in the sametournament, players in this category get two separate registration numbers.• Both a player registered for singles play or two people registered for doubles play u nderthe same number (i.e. are playing with each other) can both be referred to as a team:a team of one or a team of two.• In most tournaments, the top fraction of the players registering are seeded, to helpspread out the players expected to perform well (this makes the later matches moreinteresting!). Seed values are given on a per-tournament basis. Players who do notreceive a seed in a particular tournament have a NULL value instead.• The Tournament relation stores the number of rounds the tournament has as an at-tribute, which is an integer. The Round attribute of the Match relation is similar. Avalue of 1 corresponds to the first round, 2 to the second, and so on. Match.Round doesnot say “Quarterfinals,” “Semifinals,” or “Finals.” You will have to figure out how tocompute what rounds correspond to these. Don ’t try to hard-code values in (i.e. if youlook at the database and realize that the US open singles had a total of 7 rounds, so theSemifinals is round 6). Rather, use a nested query to help.• Each entity in the Set relation contains information about a particular set of some match.The attributes involving the “Winner” refer to the winner of the winner of the match,not necessarily the set (similarly for the “Loser” attributes). In cases where the set wasdetermined by a tie-breaker, the TieBreaker relation h as WinnerTB and LoserTB, whichcontain the points won in the tie-breaker game. Again, the “Winner” column of theTieBreaker refers to the winner of the match, not necessarily the set.• Sometimes players will need to retire from a match, which counts as a forfeit. If thishappens, we keep records for all completed sets of the match. For the set in which theplayer retired, the WinnerGames and LoserGames attributes hold the number of gameswon and lost. The RetiredMatch relation holds all Match IDs where the loser retired.• Many of the queries will require some date manipulation. Specifically, you may have toextract the year a particular tournament was held. Assume that no tournaments wraparound years (i.e. late December-early January), and only concern yourself with theyear of the start date.Directions for using Oracle: Oracle can be accessed through any WAM or GLUEsystem. At the prompt, type “tap oraclient” to set up all the environmental variables, etcneeded to use oracle. It will ask for the database SID, for which you should enter “dbclass1.”To enter the oracle e nvironment, type “sqlplus.” You should be prompted for a user-name2and password, which you will be supplied with. After entering t hese, you will see a promptthat looks like SQL>. You’re now in the Oracle environment.The official documentation for Oracle can be found at http://www.oracle.com/technology-/documentation/.Information specific to this project will be given out through email. Expect to be directedto download a compressed file containing the data set and instructions for loading it intoOracle. In the meantime, you might want to try writing out some of the queries by hand toget a feel for how to structure them.How to hand in your work: You should be making two separate submissions, one forPart A and one for Part B. Please included solutions to all chosen problems (see below) in afile named <lastname>-A.sql for Part A, and a file named <lastname>-B.sql for Part B.Use SQL-style comments at the start of each query to s pecify what question the query willsolve. IMPORTANT: If you do not signify what problem a query addresses, you will receiveno points for that query.Aside from comments made inline with your SQL queries, all other comments or notes tothe grader should be made in a file named README. Do not include a spool of the output
View Full Document
Unlocking...