The Matthias Knecht Tae Kwon Do Case Study

1. The Matthias Knecht Tae Kwon Do Case Study BHAMBRA Manjyot KOHLI Garima MANGAL Storay TAWHEED Zureen Distinction Assignment, Autumn 2007

2. Introduction to the The Matthias Knecht Tae Kwon Do Case Study This database is reflective of a Tae Kwon Do challenge and introduces us to all the entities that are involved in such a challenge and the relationships existing between them. In a typical tournament fighters engage in a challenge and are both judged on their performance. Competitors choosing to undergo training receive adequate preparation from professional club members. Alternatively competitors can still compete possessing private skills.

3. KnClub KnClubID KnClubName KnTrainer KnFighter KnTrainerID KnFighterID KnTraining KnTrainerName KnFederation KnFighterName KnFighterID * KnFederationName KnTrainerBirth KnFighterBirth KnTrainerID * KnTrainerGender KnRules KnFighterGender KnClubID * KnFighterWeightclass KnFederationName * KnChallenge KnFigherID1* KnFigherID2* KnFighterGender1 KnFighterGender1 KnJudge KnFighterGender2 KnFighterGender2 KnJudgeID KnFighterWeightclass1 KnFighterWeightclass1 KnJudgeName KnFighterWeightclass2 KnFighterWeightclass2 KnJudgeBirth KnRingID KnRingID * * KnFederationName * KnJudgeID KnJudgeID * * KnWinner KnWinner KnLoser KnLoser KnRing KnRing KnRingID KnSize KnSize The ERD for Matthias Knecht Tae Kwon Do

4. SQL Queries on a Single Entity/ Table

5. Project [using “select”] SELECT KnFighterID, KnFighterName, KnFighterWeightclass FROM KnFighter; This SELECT query reports the fighters ID number, the fighters name and his/her weight class. The columns listed after SELECT are the ones that are displayed in the table

6. Restrict [using “where”] Allows the selection of the specific rows of interest from the table Using the same sql as the previous slide; the output can be further restricted by simply adding an extra line: SELECT KnFighterID, KnFighterName, KnFighterWeightclass FROM KnFighter WHERE KnFighterWeightClass = 5; This will restrict the output to showing only those fighters who are categorised as being in the fifth weight class

7. Restrict [using “where”] Rows highlighted here from the original table are the ones that match the criteria in the ‘restrict’ query using ‘where’. In each of the matching records, the fighter weight class is equal to 5.

8. Project and Restrict Combo Allows specification of any combination of columns and rows  for instance: The following sql would be used to restrict the output to showing fighter names and weight classes of those fighters who have a weight class [knfighterweightclass] of no more than 3 [<= 3]: SELECT knFighterName, knFighterWeightClass FROM knFighter WHERE KnFighterWeightClass <= 3;

9. Project and Restrict Combo The result of the previous query would be as seen below: Here, only those fighters with a weight class of 3 or less are listed

10. IS NULL Using the IS NULL restriction, the result shows a record with a field where no value has been inserted. For instance; to display all the challenges where there was no winner as the match may have been a tie or it may have been uncompleted; the winner field [knwinner] would be null and the query would be written as follows: SELECT knfighterid1, knfighterid2, knwinner, knloser FROM knchallenge WHERE knwinner IS NULL; This query would display the following result:

11. Using the IS NOT NULL restriction, the result ensures that the field has a value inserted. For instance; to display all the judges and the birth year where their birth year is known [i.e. IS NOT NULL], the query would be written as follows: SELECT knjudgename, knjudgebirth FROM knjudge WHERE knjudgebirth IS NOT NULL This query would display the following result: IS NOT NULL

12. IN Used on data to generate required results – works similarly to a ‘where’ function as seen below: This is the same as writing: SELECT * FROM KnTrainer WHERE KnClubID = ‘3' OR KnClubID = ‘10'; A query using IN looks like: SELECT * FROM KnTrainer WHERE KnClubID IN (‘3',‘10'); Either way; the output will be:

13. NOT IN Used to eliminate some records that match the specified criteria – also works similarly to a ‘where’ function as seen below: A query using NOT IN would look like: SELECT * FROM KnTrainer WHERE KnTrainerGender NOT IN (‘M'); This is the same as writing: SELECT * FROM KnTrainer WHERE KnTrainerGender <> ‘M‘; Either way; the output will be:

14. Ordering columns The order in which the columns appear in the table is dependent on the order the columns placed in the SQL following “SELECT” For instance: SELECT KnTrainerID, KnTrainerName FROM KnTrainer WHERE KnTrainerBirth > 1980;

15. Ordering rows using “order by” This command is used where the results need to be ordered in a specific way – i.e. ascending or descending. For instance: SELECTKnTrainerName, KnTrainerBirth FROMKnTrainer WHERE KnTrainerBirth > 1980 ORDER BY KnTrainerBirth; Note: the same result would be generated for: SELECTKnTrainerName, KnTrainerBirth FROMKnTrainer WHERE KnTrainerBirth > 1980 ORDER BY KnTrainerBirth ASC;

16. Using the “AS” function allows the count column to be renamed For instance, for such a query: SELECT knwinner, count (*) as number_of_wins FROM knchallenge WHERE knwinner = 200020 GROUP BY knwinner; The count column counting the number of wins by this fighter will be renamed as number_of_wins as seen below: Calculating – with the use of ‘AS’ Or even for something simpler; could have SELECT knwinner as WINNER FROM knchallenge

17. Built-in functions – Count(*) COUNT(*) – a function that counts all the rows in a table you specify For instance: SELECT knWinner, count (*) as number_of_wins FROM knChallenge WHERE knWinner = 200020 GROUP BY knWinner; Note: always requires a group by function when counting all – specifies what is being used to group and count This result shows how many times this fighter has won a challenge

18. Built-in functions – Count(X) COUNT(X) – a function that counts all the rows in that column (“X”) For instance; the following will count the number of entries made for ‘knWinner’ SELECTcount (knWinner) FROM knChallenge Furthermore; SELECTcount (distinct knWinner) FROM knChallenge would count all the ‘distinct’ values in that column – i.e. values that occur only once. So here, it would count each time a fighter wins a fight their first time

19. Built-in functions [Sum/Avg] SUM– gives the total for a specified column SELECTsum (knSize) FROM knRing AVERAGE SELECTavg (knSize) FROM knRing This could be renamed using the AS function by doing: SELECTavg (knSize) as Average_Ring_Size FROM knRing

20. Built-in functions [Min/Max] MIN– gives the minimum value for that column So the smallest ring where a challenge can be held is: SELECTmin (knSize) FROM knRing MAX– returns the maximum input for that column So the highest weight class a fighter can belong to is: SELECTmax (knFighterWeightClass) FROM knFighter

21. LIKE – and its operators The LIKE function allows the query to be more specified by either specifying what the entries must start with [using the %] operator or indicating, for instance, a letter that must be present in the result. The following will return results for names that start with the letter ‘M’ SELECT KnFighterName FROM KnFighter WHERE KnFighterName LIKE ‘M%’ Alternatively; a query can be used to return results with a name containing‘er’ : SELECT KnFighterName FROM KnFighter WHERE KnFighterName LIKE ‘%er%’

22. LIKE – and its operators For instance, to find a name containing the letter ‘l’ but the it being the 2nd letter in the name: SELECT knFighterName FROM knFighter WHERE knFighterName LIKE ‘_l%’ The NOT LIKE function can also be used to do the opposite: For Instance – names that DO NOT contain the letter ‘a’ SELECT knFighterName FROM knFighter WHERE knFighterName NOTLIKE ‘%a%’

23. DISTINCT This function works to eliminate any duplicate rows For instance, to view how many different weight classes there are, use: SELECT DISTINCT KnFighterWeightClass FROM KnFighter; Furthermore; the total different weight classes are: SELECT Count (DISTINCT KnFighterWeightClass) FROM KnFighter;

24. Inserting rows The following insert statement can be used to insert statements into the KnFighter table: INSERT INTO KnFighter VALUES (200001,'Karl Dall',1981,'M',5); This is representative of the fields: INSERT INTO KnFighter VALUES (KnFighterID,KnFighterName,KnFighterBirth,KnFighterGender,KnFighterWeightclass); so here: KnFighterID = 200001 KnFighterName = Karl Dall KnFighterBirth = 1981 KnFighterGender = M KnFighterWeightClass = 5

25. Foreign Keys and Natural Joins

26. Primary keys Foreign keys Club ID and Trainer This illustrates that the primary keys of one table can be foreign keys in another table

27. The Natural Join This function, using existing tables, creates a new table by matching columns and adding those that are not common So to join the table KnTrainer and the table KnClub the following query would be used to display this new table where all columns from both tables are displayed: SELECT * FROM KnTrainer natural join KnClub;

28. Cross Product The Cross product function is simple another method of joining two tables (as opposed to the “natural join” on the previous So once again, to join the tables KnTrainer and KnClub using the cross product, the query would be: SELECT * FROM KnTrainer, KnClub WHERE KnClub.KnClubid = KnTrainer.KnClubid; The sql is written as table name followed by the DOT followed by column name and then the name of the other table with the same column separated by ‘=’. This clarifies that the two columns which appear in different tables are actually the same. Thus disambiguating the columns. • NOTE: If you don’t use a * after SELECT, and you specify “KnClubid”, you will need a to use a dot. The two columns that are the same

29. Cross Product As we can see, the result from both the Natural Join and the Cross Product is the same, the only difference is the cross product defines the common columns and which table they are extracted from

30. More Cross Product The Cross Product function can also like other functions be restricted to displaying certain columns and rows in addition to select data fulfilling certain conditions placed by the sql For instance, to view the birth, and federation name of judges beginning with ‘K’ the insert statement would be: Select KnJudgeName, KnJudgeBirth, KnJudge.KnFederationName FROM KnFederation, KnJudge WHEREKnFederation.KnFederationName = KnJudge.KnFederationName AND KnJudgeName LIKE ‘K%’; The KnFederation needs to be assigned a table from which it will be extracted since it exists in two tables and although it is the same, the program does not recognise this and thus it must be pointed out [which is where the Cross Product comes in]

31. Cross Product The output of the query from the previous slide would be: As we can see, only the columns specified are shown as compared with the KnTrainer and KnClub tables where all the columns from the two tables were shown

32. More cross Product Similarly a query where we want to the find the trainer ID, name and which club they belong to would look like : Where the KnClubID has been extracted from the KnClub table to the KnTrainer table

33. Entities and Relationships

34. KnTraining − KnTrainer1:m relationship The ERD on slide 3, shows a one to many relationship, like this one The Actual Tables … Foreign Key

35. KnChallenge − KnJudge 1:m relationship The ERD on slide 3, shows a one to many relationship, like this one Actual Tables … Foreign Key

36. KnFighter − KnTrainer m:m relationship The ERD on slide 3, shows a many to many relationship, like this one The table “Training”

37. KnFighter − KnTrainer m:m relationship • These are the different tables associated with this relationship The table “KnFighter” The table “KnTraining” The table “KnTrainer”

38. KnFighter − KnTrainer m:m relationship • These are the different tables associated with this relationship The table “KnFighter” The table “KnTraining” The table “KnTrainer”

39. Group by, sub-queries and complex joins

40. GROUP BY Reporting the number of fighters within each fighter weight class SELECT count(knfightername) as fighters, knfighterweightclass) FROM knfighter GROUP BY knfighterweightclass;

41. HAVING – like WHERE, but after the grouping Report the number of fighters in each class below weight-class 5 count(knfightername) as fighters, knfighterweightclass) FROM knfighter GROUP BY knfighterweightclass; HAVING COUNT knfighterweightclass <5;

42. Sub Queries • A query within a query Report all females with a birth year greater than the average birth year of females in weight-class 3. SELECT knfighterbirth, knfightergender FROM knfighter WHERE knfightergender ='F‘ and knfighterweightclass =3 and knfighterbirth >= (select avg (knfighterbirth) from knfighter);

43. Using subqueries to find the maximum (or minimum) Find the highest weight-class of female competitors. • SELECT distinct knfightergender, knfighterweightclass FROM knfighterWHERE knfightergender ='F' and knfighterweightclass >= (select max(knfighterweightclass) from knfighter where knfightergender ='F');

44. Alternate way to find the maximum (or minimum): “ALL” Give pizza and prices for pizzas that are more expensive than all Italian pizzas. select knfightergender, knfighterweightclass from knfighter where knfightergender ='F' and knfighterweightclass >= ALL ( select knfighterweightclass from knfighter where knfightergender ='F');

45. Another ALL example select distinct knfightergender, knfighterweightclass from knfighter where knfightergender ='F' and knfighterweightclass <= ALL ( select knfighterweightclass from knfighter where knfightergender ='F'); this is equivalent to … WHERE knfightergender = (select min(knfighterweightclass);

46. ANY operator List federation names with at least ‘one’ female trainer Select distinct knfederationname From kntrainer Where knfederationname = ANY (select knfederationname from kntrainer where kntrainergender ='F');

47. In: an Alternate to ANY List federation names with at least ‘one’ female trainer Select distinct knfederationname From kntrainer Where knfederationname IN (select knfederationname from kntrainer where kntrainergender ='F');

48. Left Outer Join Where the table will always contain all the records of the left table regardless of whether matching records of the right table exist or not. In the case where there is no data in that particular field of the right table, null placed in the table. The query for a left outer join where the table KnTrainer is the left table and KnClub is the right, i.e. KnCub is being joined to the KnTrainer using a common table i.e. KnClubid, would be as follows: Select * from KnTrainer left join KnClub using (KnClubid);

49. Right outer Join A right outer join works in exactly the same way as a left outer join however the tables will be reversed. In that, it will be the right table that is the “main” table or the table where every record will be joined at least once and where, in the left table there are no matching rows, a null will be placed in the left table for those rows. In the case of this database, there are no null values and thus the two tables will be exactly the same.

50. supervises emp Self-Join • Join a table to itself • Usually involve a self-referencing relationship • Useful to find relationships among rows of the same table