Transcript The Matthias Knecht Tae Kwon Do Case Study BHAMBRA Manjyot

The Matthias Knecht Tae
Kwon Do Case Study
Distinction Assignment, Autumn 2007
BHAMBRA Manjyot
KOHLI
Garima
MANGAL Storay
TAWHEED Zureen
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.
2
The ERD for Matthias Knecht Tae Kwon Do
KnClub
KnClubID
KnClubName
KnTrainer
KnFighter
KnTrainerID
KnFighterID
KnTraining
KnTrainerName
KnFederation
KnFighterID *
KnTrainerBirth
KnFederationName
KnTrainerID *
KnTrainerGender
KnRules
KnFighterName
KnFighterBirth
KnFighterGender
KnClubID *
KnFighterWeightclass
KnFederationName *
KnChallenge
KnFigherID1*
KnFigherID2*
KnFighterGender1
KnJudge
KnFighterGender2
KnJudgeID
KnFighterWeightclass1
KnJudgeName
KnFighterWeightclass2
KnJudgeBirth
KnRingID *
KnFederationName *
KnJudgeID *
KnWinner
KnLoser
KnRing
KnRingID
KnSize
3
SQL Queries on a Single
Entity/ Table
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
KnFighter!D
KnFighterName
KnFighterWeightClass
200001
Karl Dall
5
200002
Martin Knecht
5
200003
Liz Taylor
5
200004
Young Parawan
3
200005
Bruce Lee
3
200006
Elisabeth Brown
3
200007
Christoph Knecht
3
200008
Lars Augenstein
4
200009
Lisa Mueller
4
200010
Larry Wahnhold
4
200011
Chin Moban
4
200012
Chels McGyvan
4
200013
Nathan Johhns
3
200014
Karl Mueller
2
200015
Linda Sureban
2
200016
Isidor Kraus
4
200017
Jet Lee
2
200018
Ling Suk Pran
2
200019
Janina Federholzer
3
200020
Mario Kleinschmid
4
200021
Jana Kralle
5
200022
Ingwan Young Kam
5
5
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
KnFighter!D
KnFighterName
KnFighterWeightClass
200001
Karl Dall
5
200002
Martin Knecht
5
200003
Liz Taylor
5
200021
Jana Kralle
5
200022
Ingwan Young Kam
5
6
Restrict [using “where”]
KnFighter!D
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.
KnFighterName
KnFighterWeightClass
200001
Karl Dall
5
200002
Martin Knecht
5
200003
Liz Taylor
5
200004
Young Parawan
3
200005
Bruce Lee
3
200006
Elisabeth Brown
3
200007
Christoph Knecht
3
200008
Lars Augenstein
4
200009
Lisa Mueller
4
200010
Larry Wahnhold
4
200011
Chin Moban
4
200012
Chels McGyvan
4
200013
Nathan Johhns
3
200014
Karl Mueller
2
200015
Linda Sureban
2
200016
Isidor Kraus
4
200017
Jet Lee
2
200018
Ling Suk Pran
2
200019
Janina Federholzer
3
200020
Mario Kleinschmid
4
200021
Jana Kralle
5
200022
Ingwan Young Kam
5
7
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
WHERE
knFighter
KnFighterWeightClass <= 3;
8
Project and Restrict Combo
The result of the previous query would be as seen below:
KnFighter!D
KnFighterName
KnFighterWeightClass
200004
Young Parawan
3
200005
Bruce Lee
3
200006
Elisabeth Brown
3
200007
Christoph Knecht
3
200013
Nathan Johhns
3
200014
Karl Mueller
2
200015
Linda Sureban
2
200017
Jet Lee
2
200018
Ling Suk Pran
2
200019
Janina Federholzer
3
Here, only those fighters with a weight class of 3 or less are listed
9
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
FROM
WHERE
knfighterid1, knfighterid2, knwinner, knloser
knchallenge
knwinner IS NULL;
This query would display the following result:
Knfighterid1
Knfighterid2
200020
200010
Knwinner
Knloser
200010
10
IS NOT NULL
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:
Knjudgename
knjudgebirth
Young Sik Park
1963
Kwung Do Den
1944
Hannes Maurer
1969
Dolo Dobendan
1983
Nunzio Caico
1954
Hung Kwan Do
1931
Klaus Meier
1977
Raymond Lister
1961
Boris Becker
1955
Karl Heinz Hannebu1/4her
1933
11
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:
KnTrainerID
KnTrainerName
KnTrainerBirth
KnTrainerGender
KnClubID
KnFederationName
100003
Lizz Tough
1946
F
3
ITF
100010
Chelsea Lai
1983
F
10
WTF
100011
Matthias Sammer
1963
M
10
WTF
100012
Kim Kwak
1967
M
10
WTF
100014
Susi Ingelheimer
1947
F
3
ITF
12
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:
KnTrainerID
KnTrainerName
KnTrainerBirth
KnTrainerGender
KnClubID
KnFederationName
100003
Lizz Tough
1946
F
3
ITF
100004
Lollo Ingenheim
1975
F
4
ITF
100007
Claudia Schiffer
1953
F
7
WTF
100009
Lara Croft
1975
F
9
WTF
100010
Chelsea Lai
1983
F
10
WTF
100014
Susi Ingelheimer
1947
F
3
ITF
13
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;
KnTrainerID
KnTrainerName
100010
Chelsea Lai
100013
Kwak Kum Sik
100015
Andre Agassi
14
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:
SELECT
FROM
WHERE
ORDER BY
KnTrainerName, KnTrainerBirth
KnTrainer
KnTrainerBirth > 1980
KnTrainerBirth;
KnTrainerName
KnTrainerBirth
Chelsea Lai
1983
Andre Agassi
1985
Kwak Kum Sik
1985
Note: the same result would be generated for:
SELECT
KnTrainerName, KnTrainerBirth
FROM
KnTrainer
WHERE
KnTrainerBirth > 1980
ORDER BY KnTrainerBirth ASC;
15
Calculating – with the use of ‘AS’
Using the “AS” function allows the count column to be renamed
For instance, for such a query:
SELECT
FROM
WHERE
GROUP BY
knwinner, count (*) as number_of_wins
knchallenge
knwinner = 200020
knwinner;
The count column counting the number of wins by this fighter will be
renamed as number_of_wins as seen below:
Knwinner
number_of_wins
200020
3
Or even for something simpler; could have SELECT knwinner as WINNER
FROM
knchallenge
16
Built-in functions – Count(*)
COUNT(*) – a function that counts all the rows in a table you specify
For instance:
SELECT
FROM
WHERE
GROUP BY
knWinner, count (*) as number_of_wins
knChallenge
knWinner = 200020
knWinner;
Knwinner
number_of_wins
200020
3
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
17
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’
SELECT
FROM
count (knWinner)
knChallenge
count
20
Furthermore;
SELECT
FROM
count (distinct knWinner)
knChallenge
count
14
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
18
Built-in functions [Sum/Avg]
SUM – gives the total for a specified column
SELECT
FROM
sum (knSize)
knRing
Sum
59
AVERAGE
SELECT
FROM
avg (knSize)
knRing
avg
11.8
This could be renamed using the AS function by doing:
SELECT
FROM
avg (knSize) as Average_Ring_Size
knRing
Average_Ring_Size
11.8
19
Built-in functions [Min/Max]
MIN – gives the minimum value for that column
So the smallest ring where a challenge can be held is:
SELECT
FROM
min (knSize)
knRing
Min
9
MAX – returns the maximum input for that column
So the highest weight class a fighter can belong to is:
SELECT
FROM
max (knFighterWeightClass)
knFighter
Max
5
20
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%’
KnFighterName
Martin Knecht
Mario Kleinschmid
Alternatively; a query can be used to return results with a name containing ‘er’ :
SELECT KnFighterName
FROM
KnFighter
WHERE KnFighterName LIKE ‘%er%’
KnFighterName
Lisa Mueller
Karl Mueller
Janina Federholzer
21
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
FROM
WHERE
knFighterName
knFighter
knFighterName LIKE ‘_l%’
knFighterName
Elisabeth Brown
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 NOT LIKE ‘%a%’
KnFighterName
Bruce Lee
Christoph Knecht
Jet Lee
22
DISTINCT
This function works to eliminate any duplicate rows
For instance, to view how many different weight classes there are, use:
SELECT
FROM
DISTINCT KnFighterWeightClass
KnFighter;
KnFighterWeightClass
2
3
4
5
Furthermore; the total different weight classes are:
SELECT
FROM
Count (DISTINCT KnFighterWeightClass)
KnFighter;
Count
4
23
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,KnFighterWeight
class);
so here: KnFighterID
KnFighterName
KnFighterBirth
KnFighterGender
KnFighterWeightClass
= 200001
= Karl Dall
= 1981
=M
=5
24
Foreign Keys and Natural
Joins
Club ID and Trainer
KnClubid
KnClubname
1
Black Tiger Tae-Kwon-Do
2
Sydney Fight Club
3
Do-Kwon-Dan
4
Budo Club Toronto
5
TKD e.V. Freiburg
6
Hiko-Dotan
This illustrates that the primary keys of
one table can be foreign keys in another
table
Primary keys
KnTrainerid
KnTrainername
KnTrainerbirth
KnTrainergender
KnClubid
KnFederationname
100001
Hannes Mariz
1977
M
1
ITF
100002
Young Kawan
1967
M
2
ITF
100015
Andre Agassi
1985
M
2
ITF
Foreign keys
26
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;
KnTrainerid
KnTrainername
KnTrainerbirth
KnTrainergender
KnClubid
KnFederationname
KnClubname
100001
Hannes Mariz
1977
M
1
ITF
Black Tiger TaeKwon-Do
100002
Young Kawan
1967
M
2
ITF
Sydney Fight
Club
100015
Andre Agassi
1985
M
2
ITF
Sydney Fight
Club
27
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 two
columns
that are
the same
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.
28
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
KnTrainerID
KnTraineNname
KnTrainerBirth
KnTrainerGender
KnClubID
KnFederationName
KnClubname
100001
Hannes Mariz
1977
M
1
ITF
Black Tiger TaeKwon-Do
100002
Young Kawan
1967
M
2
ITF
Sydney Fight
Club
100015
Andre Agassi
1985
M
2
ITF
Sydney Fight
Club
29
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
WHERE KnFederation.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]
30
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
KnJudgeName
KnJudgeBirth
KnFederationName
Kwung Do Dan
1944
WTF
Klaus Meier
1977
ITF
Karl Heinz Hannebucher
1933
ITF
31
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 :
KnTrainerID
KnTrainerName
KnClubID
100001
Hannes Mariz
1
100002
Young Kawan
2
100003
Lizz Tough
3
100004
Lollo Ingenheim
4
Where the KnClubID has been extracted from the KnClub
table to the KnTrainer table
32
Entities and Relationships
KnTraining − KnTrainer
1:m relationship
The ERD on slide 3, shows a one to many relationship, like
this one
The Actual Tables …
Foreign Key
KnTrainerID other columns
KnTrainerID KnFighterID
100016
…
100016
200004
… etc …
…
100016
200005
34
KnChallenge − KnJudge
1:m relationship
The ERD on slide 3, shows a one to many relationship, like
this one
Actual Tables …
Foreign Key
KnJudgeID
other columns
KnJudgeID KnFighterID1 KnFighterID2
30006
…
30006
200004
200005
… etc …
…
30006
200003
200021
35
KnFighter − KnTrainer m:m relationship
The ERD on slide 3, shows a many to many
relationship, like this one
The table “Training”
KnFighterID
KnTrainerID
200001
100016
200001
100012
200002
100015
200002
100011
36
KnFighter − KnTrainer m:m relationship
• These are the different tables associated with
this relationship
The table “KnFighter”
The table “KnTraining”
KnFighterID
KnFighterName
…
200001
Karl Dall
…
200002
Martin Knecht
…
The table “KnTrainer”
KnTrainerID
KnTrainerName
…
100016
Bruce Perrot
…
100012
Kim Kwak
…
100015
Andre Agassi
…
100011
Matthias Sammer
…
37
KnFighter − KnTrainer m:m relationship
• These are the different tables associated with
this relationship
The table “KnFighter”
The table “KnTraining”
KnFighterID KnTrainerID
KnFighterID
KnFighterName
…
200001
Karl Dall
…
200002
Martin Knecht
…
The table “KnTrainer”
KnTrainerID
KnTrainerName
…
200001
100016
100016
Bruce Perrot
…
200001
100012
100012
Kim Kwak
…
200002
100015
100015
Andre Agassi
…
200002
100011
100011
Matthias Sammer
…
38
Group by, sub-queries and
complex joins
GROUP BY
Reporting the number of fighters within each fighter weight class
SELECT
count(knfightername) as fighters,
knfighterweightclass)
FROM knfighter
GROUP BY knfighterweightclass;
fighters
knfighterweightclass
5
5
7
4
6
3
4
2
(4 rows)
40
HAVING – like WHERE, but
after the grouping
Report the number of fighters in each class below weightclass 5
count(knfightername) as fighters,
knfighterweightclass)
FROM knfighter
GROUP BY knfighterweightclass;
HAVING COUNT knfighterweightclass <5;
fighters
knfighterweightclass
7
4
6
3
4
2
(4 rows)
41
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);
knfighterbirth
knfightergende
r
knfighterweig
htclass
1976
F
3
AVG
1995
1995
F
F
3
3
(2 rows)
42
Using subqueries to find the
maximum (or minimum)
Find the highest weight-class of female competitors.
• SELECT distinct knfightergender,
knfighterweightclass
FROM knfighter WHERE knfightergender ='F' and
knfighterweightclass >= (select
max(knfighterweightclass) from knfighter where
knfightergender ='F');
knfightergender
knfighterweightclass
F
5
(1 rows)
43
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');
knfightergender
knfighterweightclass
F
5
F
5
(1 rows)
44
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);
45
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');
knfederationname
ITF
WTF
46
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');
knfederationname
ITF
WTF
47
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);
KnClubid
KnTrainerid
KnTrainername
knTrainerbirth
KnTrainergender
KnFederationname
KnClubname
1
100001
Hannes Mariz
1977
M
ITF
Black Tiger
TaeKwonDo
2
100002
Young Kawan
1967
M
ITF
Sydney Fight
Club
2
100006
Bruce Perrot
1945
M
ITF
Sydney Fight
Club
48
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.
49
Self-Join
• Join a table to itself
• Usually involve a self-referencing
relationship
• Useful to find relationships
supervises
among rows of the same
emp
table
50
Querying a recursive
relationship
SELECT distinct t1.kntrainername, t2.kntrainername AS trainername, t1.knclubID,
t2.knclubID AS ClubID
FROM kntrainer t1, kntrainer t2
WHERE t1.kntrainername = t2.kntrainername
AND t1.knclubID = t2. knClubID;
kntrainername
Trianername
KnclubID
ClubID
Andre Aggassi
Andre Aggassi
2
2
Bruce Perrot
Bruce Perrot
2
2
Chelsea Lai
Chelsea Lai
10
10
Claudia Schiffer
Claudia Schiffer
7
7
Hannes Mariz
Hannes Mariz
1
1
Ken Ferret
Ken Ferret
6
6
Kim Kwak
Kim Kwak
10
10
Matthias Sammer
Matthias Sammer
10
10
51
Data Integrity with SQL
KnClubID
KnClubName
1
Black Tiger Tae-Kwon-Do
2
Sydney Fight Club
3
Do-Kwan-Dan
4
Budo Club Toronto
5
1. TKD e.V. Freiburg
6
Hiko-Dotan
7
Wollongong Crows
8
Korean National Team
9
Dobok-Hin-Dokan
10
Matthias Knecht TKD Team
Primary Key
KnTrainer
Table
Foreign Keys
KnClub
Table
Foreign Key
from KnClub Table
KnTrainerID
KnTrainerName
KnTrainerBirth
KnTrainerGender
KnClubID
KnTrainerID
100001
Hannes Mariz
1977
M
1
ITF
100002
Young Kawan
1967
M
2
ITF
100003
Liz Tough
1946
F
3
ITF
100004
Lollo Ingenheim
1975
F
4
ITF
100005
Kwan Do Inkuban
1934
M
5
ITF
100006
Ken Ferret
1963
M
6
WTF
100007
Claudia Schiffer
1953
F
7
WTF
100008
Mr. Pink
1966
M
8
WTF
100009
Lara Croft
1975
F
9
WTF
100010
Chelsea Lai
1983
F
10
WTF
100011
Matthias Sammer
1963
M
10
WTF
100012
Kim Kwak
1967
M
10
WTF
100013
Kwak Kum Sik
1985
M
6
WTF
100014
Susi Ingelheimer
1947
F
3
ITF
100015
Andre Agassi
1985
M
2
100016
Bruce Perrot
1945
M
2
53
ITF
ITF
Creating the linked tables
----------------------------------------------------------------------------------------------------------------CREATE TABLE KnClub (
KnClubID
INTEGER NOT NULL,
KnClubName
VARCHAR(30),
CONSTRAINT PKKnClub PRIMARY KEY (KnClubID)
);
----------------------------------------------------------------------------------------------------------------CREATE TABLE KnTrainer (
KnTrainerID
KnTrainerName
KnTrainerBirth
KnTrainerGender
KnClubID
KnFederationName
INTEGER NOT NULL,
CHAR(30),
INTEGER,
CHAR(1),
INTEGER NOT NULL,
CHAR(3) NOT NULL,
CONSTRAINT PKKnTrainer PRIMARY KEY (KnTrainerID),
CONSTRAINT FKKnClubID FOREIGN KEY (KnClubID) REFERENCES KnClub
ON UPDATE CASCADE,
As well as other constraints that are needed…[excluded here]
);
-----------------------------------------------------------------------------------------------------------------
54
CHECK Statements
CREATE TABLE KnFighter
( KnFighterID, KnFighterName, KnFighterBirth, KnFighterGender,
KnFighterWeightclass
CONSTRAINT PKKnFighter PRIMARY KEY (KnFighterID),
CONSTRAINT ValidKnFighterGender CHECK (KnFighterGender IN ('M', 'F')),
CONSTRAINT ValidKnFighterBirth CHECK (KnFighterBirth BETWEEN 1900 AND
2007),
CONSTRAINT ValidKnFighterWeightclass CHECK (KnFighterWeightclass BETWEEN
1 AND 7),
CONSTRAINT ValidFighterID CHECK (KnFighterID LIKE '2%')
);
55
SQL Syntax for Actions
CREATE TABLE KnTraining
(
KnFighterID
KnTrainerID
INTEGERNOT NULL,
INTEGERNOT NULL,
CONSTRAINT PKKnTraining PRIMARY KEY(KnFighterID, KnTrainerID),
CONSTRAINT FKKnFighterID FOREIGN KEY (KnFighterID) REFERENCES KnFighter
ON DELETE RESTRICT
ON UPDATE CASCADE,
So here, a deletion of a KnFighterID entry in the
KnFighter table WONT affect this table – i.e. the fighter
will still be displayed in the KnTraining Table
CONSTRAINT FKKnTrainerID FOREIGN KEY (KnTrainerID) REFERENCES KnTrainer
ON DELETE RESTRICT
ON UPDATE CASCADE,
An update to an entry in KnTrainer ID in the tabe
KnTrainer, will be followed through to this table too. i..e
changes made in that table are also reflected here.
CONSTRAINT ValidTraining CHECK (KnFighterID <> KnTrainerID)
);
56
Normalization
An example of 1st normal form
KnFighterID
KnTrainerID
KnFightername
KnFighterweightclass
KnClubID
KnTrainerName
KnTrainerBirth
200006
100009
Elisabeth Brown
3
9
Lara Croft
1975
200012
100008
Chels McGyvan
4
8
Claudia Schiffer
1953
200006
100015
Elisabeth Brown
3
2
Andre Agassi
1985
200012
100009
Chels McGyvan
4
9
Lara Croft
1975
The primary key
This is basically where redundancies
and more than one primary key exist
in the table
KnFighterID  KnFighterName, KnFighterGender,
KnFighterWeightclass
KnTrainerID  KnTrainerName, KnTrainerBirth key
KnTrainerID, KnFighterID  KnClubID
Solution: split into two or more Tables.
58
Solution to 1st normal form: Second Normal
Form
KnFighterID
KnFightername
KnFighterGender
KnFighterweightclass
200006
Elisabeth Brown
F
3
200012
Chels McGyvan
F
4
200006
Elisabeth Brown
F
3
200012
Chels McGyvan
F
4
The
respective
primary
keys of
each table
KnFighterID
KnTrainerID
200006
100009
200012
100008
200006
100015
200012
100009
KnTrainerID
KnTrainerName
KnTrainerBirth
100009
Lara Croft
1975
100008
Claudia Schiffer
1953
100015
Andre Agassi
1985
100009
Lara Croft
1975
Different tables have been
created to reduce
redundancies in the table
itself. Creating a table which
joins the KnFighter Table to
the KnTrainer table allows
respective information of
each table to be stored in it
only.
59
An example of 2nd normal form
KnTrainerID
KnTrainerName
KnTrainerBirth
KnClubID
KnClubName
100009
Lara Croft
1975
9
Dobok-Hin-Doken
100008
Claudia Schiffer
1953
8
Korean National Team
100015
Andre Agassi
1985
2
Sydney Fight Club
100009
Lara Croft
1975
9
Dobok-Hin-Doken
The primary key
KnTrainerID  KnTrainerName,
KnTrainerBirth
KnClubID  KnClubName
Is where redundancies in the form of
extra information about the foreign
keys exist
key
Solution: split into two or more Tables.
60
Solution to 2nd normal form: Third Normal
Form
KnTrainerID
KnTrainerName
KnTrainerBirth
KnClubID
100009
Lara Croft
1975
9
100008
Claudia Schiffer
1953
8
100015
Andre Agassi
1985
2
100009
Lara Croft
1975
9
The respective primary keys of each table
KnClubID
KnClubName
9
Dobok-Hin-Doken
8
Korean National Team
2
Sydney Fight Club
9
Dobok-Hin-Doken
Leaving only the KnClubID in the
KnTrainer table removes also having
the names of those trainers in that
table. This makes it efficient as there is
only one ID to a trainer therefore
removing redundant data. In other
words only information strictly related
to the respective table apart from the
61
foreign keys is left.
Boyce-Codd normal form (BCNF)
The basic principle of BCNF is that it does not allow multiple primary keys to
exist in an entity when only one is needed to identify the relation. As this
creates multiple dependencies for an entity for one relation and thus
creating redundant data.
The Mattias Knect Tae Kwon Do database is in the 3NF, however since there
are no functional dependencies it also complies with BCNF.
An example of where the database would not comply with BCNF is when for
example:
If the Training table also included KnTrainerName and KnFighterName in
addition to the IDs of each and these two were also the primary keys.
Basically making it so that the
primary key is related to each
attributive keys
Key
62
An Example of Creating a View
CREATE VIEW fighterweight
AS SELECT knFighterID, knFighterName,
knFighterWeightClass
FROM knFighter
WHERE knFighterWeightClass >3;
63
An Example of Querying a View
• Query exactly as if a
table
SELECT knFighterName,
knfighterWeightClass
FROM fighterweight;
Knfightername
knfighterweightclass
Karl Dall
5
Martin Knecht
5
Liz Taylor
5
Lars Augenstein
4
Lisa Mueller
4
Larry Wahnhold
4
Chin Moban
4
Chels McGyvan
4
Isidor Kraus
4
Mario Kleinschmid
4
Jana Kralle
5
Ingwan Young Kam
5
64