Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America.
Health Warning
Denormalization tends to be as much about the circumstances you find yourself in as about hard and fast rules. What I’m attempting to do here is to explore and demonstrate some of the techniques available to you when translating a Logical Data Model into a Physical Database. There are numerous solutions to each of the problems mentioned below, and I’m sure that many people will have their own personal favourites ( which will be completely different to mine !)
Application Requirements
The functional requirements of the sample application we’re going to build are, for the Copa America 2001 :
- Work out the final Group table
- Work out the qualifiers for the Quarter Final stages
- Hold the Results for the Quarter Finals and populate the teams for the Semi-Final fixtures
- Do the same for the Final and Third Place Play-off
- Record the Results for the Final and Third-Place Play-off
- List the top four teams – including the tournament record of the Champions
- Make German smile 🙂
Meet the Normal-Forms
In his work on set theory, Codd actually identified seven normal forms. For the purposes of database design however, we tend to concentrate on the first three. Remember, in terms of logical design we talk about Entities and their Attributes, as opposed to Tables and Columns.
First Normal Form – Each record is uniquely identified by a key
Second Normal Form – Each non-key attribute is dependent on the whole key
Third Normal Form – Each non-key attribute is dependent ONLY on the key
Third Normal Form, in the words of the old database programmer’s saw, can be summed up as,
“The Key, the Whole Key and nothing but the Key, so help me Codd”. Honestly, this line has them rolling in the aisles at the UKOUG Conference.
There are some who suggest that Third Normal Form is the holy grail of a physical database design. However, this approach does bring with it some significant disadvantages. Personally, I prefer the Goldilocks approach ( no, not upsetting the neighbours by forcing entry) – and applying a pragmatic approach to physical design. By judicious application of denormalization techniques we can arrive at a physical design which is, more or less, second-and-a-bit normal form, but which will lend itself to a flexible, stable and maintainable application.
Time for a Case Study…
The Copa America
This is essentially the South American Nations Soccer Championship. Participants include the 10 South American nations plus two invited teams (three in this particular instance), usually from elsewhere in the Americas.
The tournament is held in one country and played over a period of about a month. In 2001 Colombia were the host nation.
The twelve teams are drawn into three groups of 4. Each team plays the other teams in the group once. At the end of the group stages, the top two teams in each group, plus the two third place teams with the best records advance to the Quarter Finals.
From then on, the tournament adopts a straight knockout format – the winning Semi-Finalists go on to the final, with the losers meeting in a Third Place Play-off.
If we were to model a logical database design to hold details of this tournament (in 3NF naturally), we’d probably come up with something like this.
Entities
Teams – The teams in the tournament
Groups – The initial tournament stage where the teams are split into groups
Fixtures – the games played as part of the tournament
Venues – location of the games
Stages – The stages of the tournament ( Group, QF, SF, F, TPP)
Relations
A TEAM must be in one and only one GROUP
A GROUP must have many (4) TEAMS
A TEAM must play in many FIXTURES
A FIXTURE must have many (two) TEAMS
A VENUE must stage one, or more than one FIXTURES
A FIXTURE must have one and only one VENUE
A STAGE may have 0, 1 or more GROUPS
A GROUP must belong to one and only one STAGE
That’s it isn’t it ? Job done, go down the pub, these are the tables we need for our application. Why would we need to denormalize ?
Hmmm, let’s look at just one example – the relationship between TEAMS and FIXTURES.
Who’s top of the Group ?
It’s a simple question. It’s also one that users of our application are going to want an answer to. In fact, they’re likely to want to see the Group Table updated after each result is entered. Should be simple enough. Right, let’s create the tables, populate them and then write the SQL to get the answer . The tables first :
CREATE TABLE teams(
team_name VARCHAR2(30) PRIMARY KEY,
fifa_ranking NUMBER(3))
/
CREATE TABLE fixtures(
fixture_date DATE,
venue VARCHAR2(30),
team1 VARCHAR2(30) REFERENCES teams( team_name),
team2 VARCHAR2(30) REFERENCES teams( team_name),
team1_goals NUMBER(2),
team2_goals NUMBER(2),
CONSTRAINT fix_pk PRIMARY KEY( fixture_date, venue, team1, team2))
/
Yes, I know that there are always risks associated with relying on a date for uniqueness. I’ll return to this issue later on.
In the meantime, let’s make it easy and add all of the results from a single group. This one involves Colombia, Chile, Ecuador and Venezuela :
--
-- Teams first
--
INSERT INTO teams( team_name, fifa_ranking)
VALUES( 'COLOMBIA', 14)
/
INSERT INTO teams( team_name, fifa_ranking)
VALUES( 'CHILE', 31)
/
INSERT INTO teams( team_name, fifa_ranking)
VALUES( 'ECUADOR', 50)
/
INSERT INTO teams( team_name, fifa_ranking)
VALUES( 'VENEZUELA', 116)
/
--
-- Now the fixtures
--
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '11-07-01 18:00', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'ECUADOR', 'CHILE', 1, 4)
/
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '11-07-01 20:45', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'COLOMBIA', 'VENEZUELA', 2, 0)
/
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '14-07-01 16:15', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'CHILE', 'VENEZUELA', 1, 0)
/
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '14-07-01 18:30', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'COLOMBIA', 'ECUADOR', 1, 0)
/
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '17-07-01 18:30', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'ECUADOR', 'VENEZUELA', 4, 0)
/
INSERT INTO fixtures( fixture_date, venue, team1, team2,
team1_goals, team2_goals)
VALUES( TO_DATE( '17-07-01 20:45', 'DD-MM-RR HH24:MI'), 'BARRANQUILLA',
'COLOMBIA', 'CHILE', 2, 0)
/
This structure makes entering data a breeze. Each result only has to be entered once. Perfect.
Right, now for the query to get the final group table :
SELECT team_name, sum(games) as Pld, sum(gf) as GF, sum(ga) as GA, sum(points) as Pts, sum(gf) - sum(ga) as GD
FROM
(
SELECT team_name, COUNT(*) as games, SUM(team1_goals) AS GF, SUM( team2_goals) AS GA,
SUM(
CASE
WHEN team1_goals > team2_goals THEN 3
WHEN team1_goals = team2_goals THEN 1
ELSE 0
END) as points
FROM fixtures, teams
WHERE team1 = team_name
GROUP BY team_name
UNION
SELECT team_name, COUNT(*) as games, SUM(team2_goals) AS GF, SUM( team1_goals) AS GA,
SUM(
CASE
WHEN team2_goals > team1_goals THEN 3
WHEN team2_goals = team1_goals THEN 1
ELSE 0
END) as points
FROM fixtures, teams
WHERE team2 = team_name
GROUP BY team_name)
GROUP BY team_name
ORDER BY 5 desc, 6 desc
/
Hmmm, that was much harder work than it should have been. The more complex the query, the more code we have to write, the more issues we’re likely to have. I can hear the support team cursing me now as they wrestle to maintain this particular beast. If only I’d gone down the normalization route. I could have structured the database in such a way as to lend itself to an application containing simpler, cleaner, more efficient code. Support would’ve been happy ( well, as happy as they get), and Goldilocks would have approved.
Resolving the Many-to-many Relationship
OK, let’s turn the clock back. We want to implement a physical design that’s a bit more practical in terms of retrieving data that we’re going to need on a regular basis. Yep, we’re going to have to denormalize.
Now, we could simply create a view to resolve this many-to-many relationship and simply hide this complexity. Whilst this leaves the underlying structure intact, it does mean that you’ll have to run that particular query every time you want to get a group table.
Instead, we’re going to persist this data by creating a join table…
CREATE TABLE team_fixtures (
tm_team_name VARCHAR2(30) NOT NULL REFERENCES teams( team_name),
fix_fixture_date DATE NOT NULL,
fix_venue VARCHAR2(30) NOT NULL,
opp_team VARCHAR2(30) NOT NULL REFERENCES teams( team_name),
goals_for NUMBER(2),
goals_against NUMBER(2),
CONSTRAINT tf_fix_fk FOREIGN KEY (fix_fixture_date, fix_venue) REFERENCES fixtures( fixture_date, venue),
CONSTRAINT tf_pk PRIMARY KEY( tm_team_name, fix_fixture_date,
fix_venue))
/
Yes, we’re now storing each result twice ( once for each participating team). However, the volumes of data don’t prohibit this and the group query becomes so much easier…
SELECT tm_team_name, COUNT(*) as Pld,
SUM(goals_for) as GF,
SUM(goals_against) as GA,
SUM(
CASE
WHEN goals_for > goals_against THEN 3
WHEN goals_for = goals_against THEN 1
ELSE 0
END) as Pts,
SUM(goals_for) - SUM(goals_against) as GD
FROM team_fixtures
GROUP BY tm_team_name
ORDER BY 5 desc, 6 desc, 4 desc
/
But hold on, I hear you say, doesn’t that make entering the data rather more complex as you now have to insert each record three times into two different tables ?
Yes, you do have a point. There are a number of solutions available to us at this point. One of these is the current bete noir of the Oracle database world – the DML trigger.
If we know that the fixtures table can only ever be updated from our application front-end then we might consider automating the update of the teams_fixtures table in a stored procedure ( usually itself, held within a package). However, if the table is populated by something not passing through our application code, we will need something to make sure that our automated update gets done.
In terms of maintainability, you’d probably want to keep the code to populate the join table in a package and call it from the trigger. For the purposes of clarity, I’ve left it in-line in the trigger in this example…
CREATE OR REPLACE TRIGGER fix_aiud
AFTER INSERT OR UPDATE OR DELETE
ON fixtures
FOR EACH ROW
--
-- Need an AFTER trigger to avioid
-- ORA-02291: Integrity constraint( name) violated - parent key not found
--
BEGIN
IF INSERTING THEN
INSERT INTO team_fixtures(
tm_team_name, fix_fixture_date, fix_venue,
opp_team, goals_for, goals_against)
VALUES( :new.team1, :new.fixture_date, :new.venue,
:new.team2, :new.team1_goals, :new.team2_goals);
INSERT INTO team_fixtures(
tm_team_name, fix_fixture_date, fix_venue,
opp_team, goals_for, goals_against)
VALUES( :new.team2, :new.fixture_date, :new.venue,
:new.team1, :new.team2_goals, :new.team1_goals);
ELSIF UPDATING THEN
UPDATE team_fixtures
SET opp_team = :new.team2,
goals_for = :new.team1_goals,
goals_against = :new.team2_goals
WHERE tm_team_name = :new.team1
AND fix_fixture_date = :new.fixture_date
AND fix_venue = :new.venue;
UPDATE team_fixtures
SET opp_team = :new.team1,
goals_for = :new.team2_goals,
goals_against = :new.team1_goals
WHERE tm_team_name = :new.team2
AND fix_fixture_date = :new.fixture_date
AND fix_venue = :new.venue;
ELSE
--
-- Deleting a record
--
DELETE FROM team_fixtures
WHERE tm_team_name IN (:old.team1, :old.team2)
AND fix_fixture_date = :old.fixture_date
AND fix_venue = :old.venue;
END IF;
END;
/
Is that all there is to it then ? Just bang in a couple of join tables and go on our merry way? Not quite. There are a couple of other denormalization techniques that are widely used, at least one of which is the cause of much wailing and gnashing of teeth.
Synthetic Keys
The question here is a simple one – should we create a key which, although not inherently connected to the data, does garuantee uniqueness of the records ?
As always, the answer to such questions is “it depends”.
As we can see, we do have a Natural Key in the FIXTURES table – the venue and the fixture_date. Hmmm, whilst this does serve to uniquely identify all of the fixtures we’ve looked at, there is an inherent risk in relying on dates to uniquely identify records.
For example, what if we wanted our application to handle results for various international tournaments, not just the Copa America in 2001.
Now imagine that Scotland is hosting one of these tournaments. The three largest stadia in Scotland ( Ibrox, Parkhead and Hampden Park) are all in one city – Glasgow.
Now, the value of the venue column is being set to the name of a city, not the name of the ground itself. So, it would be perfectly possible to have two matches being played at exactly the same time at the same venue.
We could address this by extending the Primary Key on Fixtures to include both participating teams (see, I told you I’d come back to this). This would cause some overhead as the supporting index would also have to hold this information ( unless you went down the route of using an index organised table). The Foreign Key on TEAM_FIXTURES would also need to be extended.
This approach becomes far more problematic when you consider that you may know details of when and where a FIXTURE will take place before you know which teams will be playing.
As an alternative, using a Synthetic Key ( essentially a sequence number), would actually reduce the size of the Foreign Key on TEAM_FIXTURES whilst adding only a small column to the Primary Key on the FIXTURES table.
Some designers choose to use a Synthetic Key wherever possible. This does have certain streamlining advantages. However, the cost of this is that you can end up with records containing multiple sequence numbers that don’t mean anything by themselves. This can make life interesting when trying to work through data issues that come up after go-live.
So, if we go down the route of a Synthetic Key for fixtures, our FIXTURES and TEAM_FIXTURES tables will now actually look like this :
CREATE TABLE fixtures(
id NUMBER,
fixture_date DATE NOT NULL,
venue VARCHAR2(30) NOT NULL,
team1 VARCHAR2(30) REFERENCES teams( team_name),
team2 VARCHAR2(30) REFERENCES teams( team_name),
team1_goals NUMBER(2),
team2_goals NUMBER(2),
CONSTRAINT fix_pk PRIMARY KEY( id))
/
CREATE TABLE team_fixtures (
tm_team_name VARCHAR2(30),
fix_id NUMBER,
opp_team VARCHAR2(30) NOT NULL REFERENCES teams( team_name),
goals_for NUMBER(2),
goals_against NUMBER(2),
CONSTRAINT tf_team_fk FOREIGN KEY (tm_team_name) REFERENCES teams( team_name),
CONSTRAINT tf_fix_fk FOREIGN KEY (fix_id) REFERENCES fixtures( id),
CONSTRAINT tf_pk PRIMARY KEY( tm_team_name, fix_id))
/
Consolidating Tables and Columns
We can go further. For example, we could remove the goals columns from the FIXTURES table altogether and just hold it in TEAM_FIXTURES. However, this would make things a bit complex if you wanted to keep track of the number of goals scored in the tournament overall.
Perhaps a more common example of this is removing those small reference tables that usually proliferate in a Logical Design.
We can already see that there is potential to remove the VENUES entity altogether as the venue is already held in the FIXTURES table. Whether we do this or not depends on what information you want about your venue (do you need to know the stadium capacity, for example ?)
We have another example here – the STAGES and GROUPS entities…
CREATE TABLE STAGES (
stage_code VARCHAR2(5) PRIMARY KEY,
description VARCHAR2(30))
/
INSERT INTO stages( stage_code, description)
VALUES('GROUP', 'Group Stage')
/
INSERT INTO stages( stage_code, description)
VALUES('QF', 'Quarter-Finals')
/
INSERT INTO stages( stage_code, description)
VALUES('SF', 'Semi-Finals')
/
INSERT INTO stages( stage_code, description)
VALUES('TPP', 'Third Place Play-Off')
/
INSERT INTO stages( stage_code, description)
VALUES('F', 'Final')
/
CREATE TABLE groups(
group_name VARCHAR2(7) PRIMARY KEY,
stage_code VARCHAR2(5) REFERENCES stages(stage_code),
team1 VARCHAR2(30) REFERENCES teams( team_name),
team2 VARCHAR2(30) REFERENCES teams( team_name),
team3 VARCHAR2(30) REFERENCES teams( team_name),
team4 VARCHAR2(30) REFERENCES teams( team_name))
/
INSERT INTO groups(
group_name, stage_code, team1,
team2, team3, team4)
VALUES(
'GROUP A', 'GROUP', 'CHILE',
'COLOMBIA', 'ECUADOR', 'VENEZUELA')
/
Now, we know that each team in the tournament must be in a group. We also know that major international tournaments only have one group stage ( yes, I know that there have been exceptions to this rule, but mercifully, the idea of multiple group stages was finally abandoned after the 1982 World Cup).
Given this, we can hold the group information as a column in the teams table, rather than in a separate entity, thus removing the need for the GROUPS table altogether.
As for the STAGES table…well, major international tournaments tend to have between 8 and 32 teams so the number of stages can vary. For example, a 32-team World Cup would have an additional knockout round before the Quarter-Final stage. Probably a bad example there as I’ve just had a flashback to last summer.
Moving swiftly on, the point is that there is a case for retaining the STAGES table, but you could also move the stage information to the fixtures table. Each fixture must be part of a stage. It would be quite simple to just add a stage column into that table. The relatively few valid values for a stage could be validated by means of a check constraint…
CREATE TABLE fixtures(
id NUMBER,
fixture_date DATE NOT NULL,
venue VARCHAR2(30) NOT NULL,
stage VARCHAR2(5),
home_team VARCHAR2(30),
away_team VARCHAR2(30),
home_goals NUMBER(2),
away_goals NUMBER(2),
home_pens NUMBER(2),
away_pens NUMBER(2),
CONSTRAINT fix_pk PRIMARY KEY( id),
CONSTRAINT fix_stage_check CHECK (stage IN ('GROUP', 'QF', 'SF', 'TPP', 'F')))
/
There is another technique which is widely used – consolidating small reference tables into a larger generic lookup or domain table. This does have the advantage of avoiding proliferation of small tables. However, there some pitfalls to this approach. Firstly, addition of data to these tables has to be controlled as they can effectively define relations in the model. Secondly, the fact that they are in a single table means that these relations cannot be defined explicitly in the physical model, thus opening up the possibility of data corruption if the data integrity code you write to enforce these relationships has any bugs, or is by-passed.
Time sensitive data
Now, if we pretend for a moment that we’re back on the eve of the tournament, rather than looking at it in retrospect, another element comes into play. The fact is that, whilst we know which teams will participate in the Group fixtures, we don’t know who will be playing in the knock-out stages.
This is a consideration as will know the date and venue of some fixtures before we know the teams that are playing in those fixtures. This means that we need to remove the Foreign Key constraint to the TEAMS table on the team columns in the FIXTURES table. Of course, we could have addressed this by having a separate RESULTS table, but it would have had exactly the same Primary Key as the FIXTURES table (i.e. fixture date, venue, team1 and team2). This would effectively mean storing this data twice , unless you used a Synthetic Key as the Primary Key in FIXTURES in which case you could join the two tables on that column alone. This would still leave you needing to join the tables every time you wanted to get a result of a given fixture.
Documentation
As I’ve said all the way along, there is no definitive right or wrong answer here. It is always dependent on the circumstances in which your application needs to work. However, it always helps to have documentation of easily to hand to help you work out exactly how the data in your application hangs together.
Of course, RI constraints tend to be a pretty effective way of achieving this. Oracle however, does provide an additional tool for this purpose, table and column comments.
Whilst this is a useful and under-used feature, there are far more definitive and effective ways to ensure that the application is self-documenting. Firstly, object and column naming can really help in working out just what is going on. Secondly, RI constraints set out clearly the relationships between the tables ( not to mention feeding the Optimizer with extra information which will help performance).
In the next section, I’ve added some comments so that I’ll know just what on earth I was thinking when I re-visit this application in six months time and realise that all the formal documentation I wrote for it is in the bottom of a filing cabinet somewhere.
Campiones!
Finally, here’s our finished application (well, the database end of it, at least) …
--
-- TEAMS table
--
CREATE TABLE teams(
team_name VARCHAR2(30),
group_id VARCHAR2(1) NOT NULL,
fifa_ranking NUMBER(3) NOT NULL,
CONSTRAINT tm_pk PRIMARY KEY (team_name),
CONSTRAINT group_id_check CHECK (group_id IN ('A','B','C')))
/
COMMENT ON TABLE teams IS
'Abbr = TM - Countries participating in the tournament.'
/
COMMENT ON COLUMN teams.team_name IS
'The name of the country'
/
COMMENT ON COLUMN teams.group_id IS
'The tournament group the team is drawn in for the group stage (A,B or C).'
/
COMMENT ON COLUMN teams.fifa_ranking IS
q'[The Country's FIFA rankings at the start of the tournament.]'
/
--
-- FIXTURES table
--
CREATE TABLE fixtures(
id NUMBER,
fixture_date DATE NOT NULL,
venue VARCHAR2(30) NOT NULL,
stage VARCHAR2(5),
home_team VARCHAR2(30),
away_team VARCHAR2(30),
home_goals NUMBER(2),
away_goals NUMBER(2),
home_pens NUMBER(2),
away_pens NUMBER(2),
CONSTRAINT fix_pk PRIMARY KEY( id),
CONSTRAINT fix_stage_check CHECK (stage IN ('GROUP', 'QF', 'SF', 'TPP', 'F')))
/
COMMENT ON TABLE fixtures IS
'Abbr = FIX - Fixtures for the tournament - NOTE due to time sensitive data, FKs to teams(team_name) not included.'
/
COMMENT ON COLUMN fixtures.id IS 'Synthetic Key for fixtures table'
/
COMMENT ON COLUMN fixtures.fixture_date IS
'Date and time the fixture is to be played.'
/
COMMENT ON COLUMN fixtures.venue IS
'Where the fixture is to be played.'
/
COMMENT ON COLUMN fixtures.stage IS
'Tournament stage that this fixture is part of. Must be one of GROUP, QF, SF, TPP, F'
/
COMMENT ON COLUMN fixtures.home_team IS
'The first team the fixture is between.'
/
COMMENT ON COLUMN fixtures.away_team IS
'The second team he fixture is between.'
/
COMMENT ON COLUMN fixtures.home_goals IS
'Goals scored by the home team in the fixture'
/
COMMENT ON COLUMN fixtures.away_goals IS
'Goals scored by the away team in the fixture'
/
COMMENT ON COLUMN fixtures.home_pens IS
'If this is a knockout fixture and goes to a penalty shootout, the number of penalties scored by the home team.'
/
COMMENT ON COLUMN fixtures.away_pens IS
'If this is a knockout fixture and goes to a penalty shootout, the number of penalties scored by the away team.'
/
--
-- TEAM_FIXTURES table
--
CREATE TABLE team_fixtures(
tm_team_name VARCHAR2(30) CONSTRAINT tf_tm_fk REFERENCES teams(team_name),
fix_id NUMBER CONSTRAINT tm_fix_fk REFERENCES fixtures(id),
opp_tm_team_name VARCHAR2(30) NOT NULL REFERENCES teams( team_name),
goals_for NUMBER(2),
goals_against NUMBER(2),
pens_for NUMBER(2),
pens_against NUMBER(2),
CONSTRAINT tf_pk PRIMARY KEY( tm_team_name, fix_id))
/
COMMENT ON TABLE team_fixtures IS
'Abbr = tf. Join table between TEAMS and FIXTURES to hold the results for each team.'
/
COMMENT ON COLUMN team_fixtures.tm_team_name IS
'The team name - FK to TEAMS (team_name).'
/
COMMENT ON COLUMN team_fixtures.fix_id IS
'The fixture ID - FK to FIXTURES (id)'
/
COMMENT ON COLUMN team_fixtures.opp_tm_team_name IS
'Opposing team for this fixture - FK to TEAMS (team_name).'
/
COMMENT ON COLUMN team_fixtures.goals_for IS
'Goals scored by tm_team_name in this fixture'
/
COMMENT ON COLUMN team_fixtures.goals_against IS
'Goals conceded by tm_team_name in this fixture'
/
COMMENT ON COLUMN team_fixtures.pens_for IS
'In a knockout game penalty-shootout - penalties scored by tm_team_name'
/
COMMENT ON COLUMN team_fixtures.pens_against IS
'In a knockout game penalty-shootout - penalties conceded by tm_team_name'
/
Incidentally, if you want to see any comments ….
-- -- Table comments -- SELECT table_name, comments FROM all_tab_comments WHERE table_name = 'TEAM_FIXTURES' / -- -- Column comments -- SELECT column_name, comments FROM all_col_comments WHERE table_name = 'TEAM_FIXTURES' /
Now we need a sequence to generate the values for the FIXTURE.ID
CREATE SEQUENCE fix_id_seq
START WITH 1
INCREMENT BY 1
NOCYCLE
/
…and the trigger to keep TEAM_FIXTURES in synch with updates to the FIXTURES table (NOTE – we’re never going to delete records from FIXTURES in this particular application so I haven’t included deletions in this trigger). This time, I’ve split the actual DML out into a package since there are times when an update to FIXTURES will cause new records to be inserted into TEAM_FIXTURES. So, the package first :
CREATE OR REPLACE PACKAGE sync_tf_pkg IS
PROCEDURE ins_tf_pr(
p_fix_id fixtures.id%TYPE,
p_home_team fixtures.home_team%TYPE,
p_away_team fixtures.away_team%TYPE,
p_home_goals fixtures.home_goals%TYPE DEFAULT NULL,
p_away_goals fixtures.away_goals%TYPE DEFAULT NULL,
p_home_pens fixtures.home_pens%TYPE DEFAULT NULL,
p_away_pens fixtures.away_pens%TYPE DEFAULT NULL);
PROCEDURE upd_tf_pr(
p_home_team fixtures.home_team%TYPE,
p_away_team fixtures.away_team%TYPE,
p_fix_id fixtures.id%TYPE,
p_home_goals fixtures.home_goals%TYPE,
p_away_goals fixtures.away_goals%TYPE,
p_home_pens fixtures.home_pens%TYPE DEFAULT NULL,
p_away_pens fixtures.away_pens%TYPE DEFAULT NULL);
END sync_tf_pkg;
/
CREATE OR REPLACE PACKAGE BODY sync_tf_pkg AS
PROCEDURE ins_tf_pr(
p_fix_id fixtures.id%TYPE,
p_home_team fixtures.home_team%TYPE,
p_away_team fixtures.away_team%TYPE,
p_home_goals fixtures.home_goals%TYPE DEFAULT NULL,
p_away_goals fixtures.away_goals%TYPE DEFAULT NULL,
p_home_pens fixtures.home_pens%TYPE DEFAULT NULL,
p_away_pens fixtures.away_pens%TYPE DEFAULT NULL) IS
BEGIN
INSERT INTO team_fixtures(
tm_team_name, fix_id, opp_tm_team_name,
goals_for, goals_against, pens_for,
pens_against)
VALUES(
p_home_team, p_fix_id, p_away_team,
p_home_goals, p_away_goals, p_home_pens,
p_away_pens);
--
-- Now need to swap the home and away teams around so we get can
-- insert the record for the "away" team
--
INSERT INTO team_fixtures(
tm_team_name, fix_id, opp_tm_team_name,
goals_for, goals_against, pens_for,
pens_against)
VALUES(
p_away_team, p_fix_id, p_home_team,
p_away_goals, p_home_goals, p_away_pens,
p_home_pens);
END ins_tf_pr;
PROCEDURE upd_tf_pr(
p_home_team fixtures.home_team%TYPE,
p_away_team fixtures.away_team%TYPE,
p_fix_id fixtures.id%TYPE,
p_home_goals fixtures.home_goals%TYPE,
p_away_goals fixtures.away_goals%TYPE,
p_home_pens fixtures.home_pens%TYPE DEFAULT NULL,
p_away_pens fixtures.away_pens%TYPE DEFAULT NULL) IS
BEGIN
UPDATE team_fixtures
SET goals_for = p_home_goals,
goals_against = p_away_goals,
pens_for = p_home_pens,
pens_against = p_away_pens
WHERE tm_team_name = p_home_team
AND fix_id = p_fix_id;
--
-- Again, need to swap the home and away teams around so we get can
-- insert the record for the "away" team
--
UPDATE team_fixtures
SET goals_for = p_away_goals,
goals_against = p_home_goals,
pens_for = p_away_pens,
pens_against = p_home_pens
WHERE tm_team_name = p_away_team
AND fix_id = p_fix_id;
END upd_tf_pr;
END sync_tf_pkg;
/
Now for the trigger itself :
CREATE OR REPLACE TRIGGER fix_aiu_trg
AFTER INSERT OR UPDATE OR DELETE on fixtures
FOR EACH ROW
DECLARE
l_dummy PLS_INTEGER;
CURSOR c_fix_exists( cp_fix_id fixtures.id%TYPE) IS
SELECT 1
FROM team_fixtures
WHERE fix_id = cp_fix_id;
BEGIN
IF INSERTING THEN
--
-- We only want to populate team_fixtures if the two
-- teams are known
--
IF :new.home_team IS NOT NULL AND :new.away_team IS NOT NULL THEN
sync_tf_pkg.ins_tf_pr(
p_fix_id => :new.id,
p_home_team => :new.home_team,
p_away_team => :new.away_team,
p_home_goals => :new.home_goals,
p_away_goals => :new.away_goals,
p_home_pens => :new.home_pens,
p_away_pens => :new.away_pens);
END IF;
ELSIF UPDATING THEN
--
-- Check to see if we've already got this fixture in TEAM_FIXTURES
-- if not then add it. If so, then just do an update
--
OPEN c_fix_exists( :new.id);
FETCH c_fix_exists INTO l_dummy;
IF c_fix_exists%FOUND THEN
CLOSE c_fix_exists;
sync_tf_pkg.upd_tf_pr(
p_home_team => :new.home_team,
p_away_team => :new.away_team,
p_fix_id => :new.id,
p_home_goals => :new.home_goals,
p_away_goals => :new.away_goals,
p_home_pens => :new.home_pens,
p_away_pens => :new.away_pens);
ELSE
CLOSE c_fix_exists;
sync_tf_pkg.ins_tf_pr(
p_fix_id => :new.id,
p_home_team => :new.home_team,
p_away_team => :new.away_team,
p_home_goals => :new.home_goals,
p_away_goals => :new.away_goals,
p_home_pens => :new.home_pens,
p_away_pens => :new.away_pens);
END IF;
END IF;
END;
/
Next up, populate the TEAMS and FIXTURES tables with data as it was known before the tournament began…
DECLARE
PROCEDURE ins( p_team VARCHAR2, p_group VARCHAR2,
p_ranking VARCHAR2, p_guest VARCHAR2 DEFAULT NULL) IS
BEGIN
INSERT INTO teams(
team_name, group_id, fifa_ranking)
VALUES(
p_team, p_group, p_ranking);
END ins;
BEGIN
--
-- GROUP A teams
--
ins('COLOMBIA', 'A', 18);
ins('CHILE', 'A', 35);
ins('ECUADOR', 'A', 44);
ins('VENEZUELA', 'A', 112);
--
-- GROUP B teams
--
ins('BRAZIL', 'B', 2);
ins('MEXICO', 'B', 15);
ins('PERU', 'B', 57);
ins('PARAGUAY', 'B', 10);
--
-- GROUP C teams
--
ins('COSTA RICA', 'C', 47);
ins('HONDURAS', 'C', 51);
ins('URUGUAY', 'C', 40);
ins('BOLIVIA', 'C', 66);
END;
/
DECLARE
l_stage VARCHAR2(5);
PROCEDURE ins( p_date IN DATE, p_venue IN VARCHAR2, p_stage VARCHAR2,
p_home VARCHAR2 DEFAULT NULL, p_away VARCHAR2 DEFAULT NULL)
IS
BEGIN
INSERT INTO fixtures(
id, fixture_date, venue,
stage, home_team, away_team)
VALUES(
fix_id_seq.NEXTVAL, p_date, p_venue,
p_stage, p_home, p_away);
END ins;
BEGIN
l_stage := 'GROUP';
--
-- GROUP A fixtures
--
ins( p_date => TO_DATE('11-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'ECUADOR', p_away => 'CHILE');
ins( p_date => TO_DATE('11-07-01 20:45', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'COLOMBIA', p_away => 'VENEZUELA');
ins( p_date => TO_DATE('14-07-01 16:15', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'CHILE', p_away => 'VENEZUELA');
ins( p_date => TO_DATE('14-07-01 18:30', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'COLOMBIA', p_away => 'ECUADOR');
ins( p_date => TO_DATE('17-07-01 18:30', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'ECUADOR', p_away => 'VENEZUELA');
ins( p_date => TO_DATE('14-07-01 20:45', 'DD-MM-RR HH24:MI'),
p_venue => 'BARRANQUILLA', p_stage => l_stage,
p_home => 'COLOMBIA', p_away => 'CHILE');
--
-- GROUP B fixtures
--
ins( p_date => TO_DATE('12-07-01 17:30', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'PERU', p_away => 'PARAGUAY');
ins( p_date => TO_DATE('12-07-01 19:45', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'BRAZIL', p_away => 'MEXICO');
ins( p_date => TO_DATE('15-07-01 16:00', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'BRAZIL', p_away => 'PERU');
ins( p_date => TO_DATE('15-07-01 18:15', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'PARAGUAY', p_away => 'MEXICO');
ins( p_date => TO_DATE('18-07-01 17:30', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'PERU', p_away => 'MEXICO');
ins( p_date => TO_DATE('18-07-01 19:45', 'DD-MM-RR HH24:MI'),
p_venue => 'CALI', p_stage => l_stage,
p_home => 'BRAZIL', p_away => 'PARAGUAY');
--
-- GROUP C fixtures
--
ins( p_date => TO_DATE('13-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'BOLIVIA', p_away => 'URUGUAY');
ins( p_date => TO_DATE('13-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'HONDURAS', p_away => 'COSTA RICA');
ins( p_date => TO_DATE('16-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'URUGUAY', p_away => 'COSTA RICA');
ins( p_date => TO_DATE('16-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'HONDURAS', p_away => 'BOLIVIA');
ins( p_date => TO_DATE('16-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'BOLIVIA', p_away => 'COSTA RICA');
ins( p_date => TO_DATE('16-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'MEDELLIN', p_stage => l_stage,
p_home => 'HONDURAS', p_away => 'URUGUAY');
--
-- Quarter-Finals
--
l_stage := 'QF';
ins( p_date => TO_DATE( '22-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'PEREIRA', p_stage => l_stage);
ins( p_date => TO_DATE( '22-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'ARMENIA', p_stage => l_stage);
ins( p_date => TO_DATE( '23-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'MANIZALES', p_stage => l_stage);
ins( p_date => TO_DATE( '23-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'ARMENIA', p_stage => l_stage);
--
-- Semi-Finals
--
l_stage := 'SF';
ins( p_date => TO_DATE( '25-07-01 18:00', 'DD-MM-RR HH24:MI'),
p_venue => 'PEREIRA', p_stage => l_stage);
ins( p_date => TO_DATE( '26-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'MANIZALES', p_stage => l_stage);
--
-- Third Place Play-off
--
l_stage := 'TPP';
ins( p_date => TO_DATE( '28-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'BOGOTA', p_stage => l_stage);
--
-- Final
--
l_stage := 'F';
ins( p_date => TO_DATE( '29-07-01 20:15', 'DD-MM-RR HH24:MI'),
p_venue => 'BOGOTA', p_stage => l_stage);
END;
/
Let’s throw all the group results in. As two teams will only play each other once at each tournament stage, we can use the two teams and the stage in the predicate to ensure we update the correct row :
DECLARE
PROCEDURE upd(
p_home VARCHAR2, p_away VARCHAR2,
p_home_goals NUMBER, p_away_goals NUMBER) IS
BEGIN
UPDATE fixtures
SET home_goals = p_home_goals,
away_goals = p_away_goals
WHERE home_team = p_home
AND away_team = p_away
AND stage = 'GROUP';
END upd;
BEGIN
--
-- Group A
--
upd('ECUADOR', 'CHILE', 1, 4);
upd('COLOMBIA', 'VENEZUELA', 2,0);
upd('CHILE', 'VENEZUELA', 1,0);
upd('COLOMBIA', 'ECUADOR', 1,0);
upd('ECUADOR', 'VENEZUELA', 4,0);
upd('COLOMBIA', 'CHILE', 2,0);
--
-- Group B
--
upd('PERU', 'PARAGUAY', 3,3);
upd('BRAZIL', 'MEXICO', 0,1);
upd('BRAZIL', 'PERU', 2,0);
upd('PARAGUAY', 'MEXICO', 0,0);
upd('PERU', 'MEXICO', 1,0);
upd('BRAZIL', 'PARAGUAY', 3,1);
--
-- Group C
--
upd('BOLIVIA','URUGUAY', 0,1);
upd('HONDURAS', 'COSTA RICA', 0,1);
upd('URUGUAY', 'COSTA RICA', 1,1);
upd('HONDURAS', 'BOLIVIA', 2,0);
upd('BOLIVIA', 'COSTA RICA', 0,4);
upd('HONDURAS', 'URUGUAY', 1,0);
END;
/
That should be enough for us to work out the final group tables. Remember that as well as working out the final tables, we’re going to need to determine which two of the third placed teams have the best records…so we’re going to cheat and create a view…
CREATE OR REPLACE VIEW group_standings_vw AS
SELECT tm.group_id,
tf.tm_team_name as Team,
COUNT(tf.tm_team_name) as Pld,
SUM(tf.goals_for) as GF,
SUM(tf.goals_against) as GA,
SUM(
CASE
WHEN tf.goals_for > tf.goals_against THEN 3
WHEN tf.goals_for = tf.goals_against THEN 1
ELSE 0
END) as Pts,
SUM(tf.goals_for) - SUM(tf.goals_against) as GD
FROM denorm.team_fixtures tf, denorm.fixtures fix, denorm.teams tm
WHERE tf.fix_id = fix.id
AND tf.tm_team_name = tm.team_name
AND fix.stage = 'GROUP'
GROUP BY tm_team_name, group_id
/
The query for the final group tables now becomes a bit simpler :
column team format a20
column pld format 999
column gf format 99
column ga format 99
column gd format 99
accept group_id prompt 'Enter the Group (A,B, or C) : '
SELECT team, pld, gf, ga, pts, gd
FROM (
SELECT group_id, team, pld, gf, ga, pts, gd,
RANK() OVER ( PARTITION BY group_id ORDER BY pts desc, gd desc, gf desc) position
FROM group_standings_vw
)
WHERE group_id = '&group_id'
ORDER BY position
/
Run this and we get :
SQL> @group_tables.sql Enter the Group (A,B, or C) : A old 7: WHERE group_id = '&group_id' new 7: WHERE group_id = 'A' TEAM PLD GF GA PTS GD -------------------- ---- --- --- ---------- --- COLOMBIA 3 5 0 9 5 CHILE 3 5 3 6 2 ECUADOR 3 5 5 3 0 VENEZUELA 3 0 7 0 -7 SQL> @group_tables.sql Enter the Group (A,B, or C) : B old 7: WHERE group_id = '&group_id' new 7: WHERE group_id = 'B' TEAM PLD GF GA PTS GD -------------------- ---- --- --- ---------- --- BRAZIL 3 5 2 6 3 MEXICO 3 1 1 4 0 PERU 3 4 5 4 -1 PARAGUAY 3 4 6 2 -2 SQL> @group_tables.sql Enter the Group (A,B, or C) : C old 7: WHERE group_id = '&group_id' new 7: WHERE group_id = 'C' TEAM PLD GF GA PTS GD -------------------- ---- --- --- ---------- --- COSTA RICA 3 6 1 7 5 HONDURAS 3 3 1 6 2 URUGUAY 3 2 2 4 0 BOLIVIA 3 0 7 0 -7
We know that the top two in each group are through to the Quarter-Finals, but which two of the third place teams will join them ?
SELECT team, pld, gf, ga, pts, gd
FROM (
-- Rank each team within their groups
SELECT group_id, team, pld, gf, ga, pts, gd,
RANK() OVER ( PARTITION BY group_id ORDER BY pts desc, gd desc, gf desc) position
FROM group_standings_vw
)
WHERE position = 3
ORDER BY pts desc, gd desc, gf desc
/
TEAM PLD GF GA PTS GD
-------------------- ---- --- --- ---------- ---
URUGUAY 3 2 2 4 0
PERU 3 4 5 4 -1
ECUADOR 3 5 5 3 0
Now let’s put it all together to give us a list of the eight Quarter-Finalists.
--
-- Dear Support Team,
-- sorry about the nested in-line views in the second half
-- of the UNION below. On the plus side, we only need to
-- run this query once - at the end of the Group stage.
-- Have put some comments in to try and make it clearer.
-- Love and Kisses, Mike
--
SELECT team -- all teams finishing first or second in the groups
FROM (
-- Rank the teams within their group
SELECT team, pts, gd, gf,
RANK() OVER (PARTITION BY group_id
ORDER BY pts desc, gd desc, gf desc) position
FROM group_standings_vw )
WHERE position <= 2 -- get the top two from each group
UNION
SELECT team -- the two 3rd placed teams with the best record
FROM (
-- get all of the teams finishing 3rd in a group
SELECT team,
RANK() OVER( ORDER BY pts desc, gd desc, gf desc) tpr
FROM (
SELECT team, pts, gd, gf,
RANK() OVER( PARTITION BY group_id ORDER BY pts desc, gd desc, gf desc) position
FROM group_standings_vw
)
WHERE position = 3
)
WHERE tpr < 3 -- just get the two teams with the best records
/
I couldn’t find out what the criteria was for determine which teams would meet in the Quarter-Finals so I’ll manually enter the data here :
UPDATE fixtures
SET home_team = 'CHILE',
away_team = 'MEXICO'
WHERE fixture_date = TO_DATE('22-07-01 18:00', 'DD-MM-RR HH24:MI')
AND venue = 'PEREIRA'
AND stage = 'QF'
/
UPDATE fixtures
SET home_team = 'URUGUAY',
away_team = 'COSTA RICA'
WHERE fixture_date = TO_DATE('22-07-01 20:15', 'DD-MM-RR HH24:MI')
AND venue = 'ARMENIA'
AND stage = 'QF'
/
UPDATE fixtures
SET home_team = 'BRAZIL',
away_team = 'HONDURAS'
WHERE fixture_date = TO_DATE('23-07-01 18:00', 'DD-MM-RR HH24:MI')
AND venue = 'MANIZALES'
AND stage = 'QF'
/
UPDATE fixtures
SET home_team = 'COLOMBIA',
away_team = 'PERU'
WHERE fixture_date = TO_DATE('23-07-01 20:15', 'DD-MM-RR HH24:MI')
AND venue = 'ARMENIA'
AND stage = 'QF'
/
From this point on, we can automate adding the teams to the appropriate fixtures as we get the results. In the Semi-Finals, the winners of the first Quarter-Final play the winners of the second ( and the winners of the other two Quarter-Finals also play each other). After that, the losing semi-finalists meet in the Third Place Play-Off and the winners meet in the final.
CREATE OR REPLACE PROCEDURE ko_fixture_draw_prc(
p_stage IN fixtures.stage%TYPE) IS
--
-- This procedure works out which teams go into which
-- fixture at the next knockout stage of the competition
-- starting from the Quarter-Finals.
-- Parameters :
-- p_stage - the current stage of the competition
--
-- Functionality :
-- 1) Get the fixture ids for the next stage
-- NOTE -for the Semi-finals we rely on the fact that the
-- fixture.id increments by one for each fixture so that the
-- first semi-final will have the lower fixture.id
-- 2) Work out which teams are going through and allocate
-- them to the appropriate fixture
--
TYPE typ_fix_ids IS TABLE of fixtures.id%TYPE INDEX BY PLS_INTEGER;
tbl_fix_ids typ_fix_ids;
TYPE typ_teams IS TABLE OF teams.team_name%TYPE INDEX BY PLS_INTEGER;
tbl_teams typ_teams;
e_incomplete_results EXCEPTION;
BEGIN
IF p_stage = 'QF' THEN
--
-- Get the Semi-Final Fixture IDs
--
SELECT id BULK COLLECT INTO tbl_fix_ids
FROM fixtures
WHERE stage = 'SF'
ORDER BY id;
ELSE
--
-- Get the final and Third Place Play-Off
-- ids. We want the final to be the first fixture id
-- as well always assign the two winners from the previous round
-- to the first fixture ID we get back.
--
SELECT id BULK COLLECT INTO tbl_fix_ids
FROM fixtures
WHERE stage IN ( 'F', 'TPP')
ORDER BY stage;
END IF;
--
-- Now work out the winners from the current stage
--
SELECT
CASE
WHEN home_goals > away_goals THEN home_team
WHEN NVL(home_pens,0) > NVL(away_pens,0) THEN home_team
ELSE away_team
END as winners BULK COLLECT INTO tbl_teams
FROM fixtures
WHERE stage = p_stage
ORDER BY id;
UPDATE fixtures
SET home_team = tbl_teams(1),
away_team = tbl_teams(2)
WHERE id = tbl_fix_ids(1);
IF (p_stage = 'QF' AND tbl_teams.COUNT <4 )
OR (p_stage = 'SF' AND tbl_teams.COUNT < 2)
THEN
RAISE e_incomplete_results;
END IF;
IF p_stage = 'QF' THEN
--
-- assign the other two winners to the second semi-final
--
UPDATE fixtures
SET home_team = tbl_teams(3),
away_team = tbl_teams(4)
WHERE id = tbl_fix_ids(2);
ELSE
--
-- Need to find the losing semi-finalists then assign them
-- to the Third Place Play-Off
--
tbl_teams.DELETE;
SELECT
CASE
WHEN home_goals < away_goals THEN home_team
WHEN NVL(home_pens,0) < NVL(away_pens,0) THEN home_team
ELSE away_team
END as losers BULK COLLECT INTO tbl_teams
FROM fixtures
WHERE stage = 'SF';
UPDATE fixtures
SET home_team = tbl_teams(1),
away_team = tbl_teams(2)
WHERE id = tbl_fix_ids(2);
END IF;
EXCEPTION
WHEN e_incomplete_results THEN
RAISE_APPLICATION_ERROR(-20000,
'Not all results have been entered for this stage');
END;
/
The knockout stage – sudden death. The tension is rising ….
UPDATE fixtures
SET home_goals = 0,
away_goals = 2
WHERE home_team = 'CHILE'
AND away_team = 'MEXICO'
AND stage = 'QF'
/
UPDATE fixtures
SET home_goals = 2,
away_goals = 1
WHERE home_team = 'URUGUAY'
AND away_team = 'COSTA RICA'
AND stage = 'QF'
/
UPDATE fixtures
SET home_goals = 0,
away_goals = 2
WHERE home_team = 'BRAZIL'
AND away_team = 'HONDURAS'
AND stage = 'QF'
/
UPDATE fixtures
SET home_goals = 3,
away_goals = 0
WHERE home_team = 'COLOMBIA'
AND away_team = 'PERU'
AND stage = 'QF'
/
Incidentally, it’s worth mentioning here that getting the results for the games from a particular stage is now quite simple. Thanks to our sensible approach to denormalization, this requires a query against a single table :
select home_team, away_team, home_goals||' -' || away_goals as result from denorm.fixtures where stage = 'QF' /
Now run the procedure to add the semi-final teams to the appropriate fixture records…
exec ko_fixture_draw_prc('QF')
PL/SQL procedure successfully completed.
SQL> select id, home_team, away_team
2 from fixtures
3 where stage = 'SF';
ID HOME_TEAM AWAY_TEAM
---------- -------------------- --------------------
42 MEXICO URUGUAY
43 HONDURAS COLOMBIA
SQL> commit;
Add the results of the Semi-Finals…
UPDATE fixtures
SET home_goals = 2,
away_goals = 1
WHERE home_team = 'MEXICO'
AND away_team = 'URUGUAY'
AND stage = 'SF'
/
UPDATE fixtures
SET home_goals = 0,
away_goals = 2
WHERE home_team = 'HONDURAS'
AND away_team = 'COLOMBIA'
AND stage = 'SF'
/
Run our procedure again…
SQL> exec ko_fixture_draw_prc('SF')
PL/SQL procedure successfully completed.
SQL> SELECT stage, home_team, away_team
2 FROM fixtures
3 WHERE stage IN ('F', 'TPP');
STAGE HOME_TEAM AWAY_TEAM
--------------- -------------------- --------------------
TPP URUGUAY HONDURAS
F MEXICO COLOMBIA
Third-Place Play-off result…
UPDATE fixtures
SET home_goals = 2,
away_goals = 2,
home_pens = 4,
away_pens = 5
WHERE home_team = 'URUGUAY'
AND away_team = 'HONDURAS'
AND stage = 'TPP'
/
And now the Final…
UPDATE fixtures
SET home_goals = 0,
away_goals = 1
WHERE home_team = 'MEXICO'
AND away_team = 'COLOMBIA'
AND stage = 'F'
/
As a final flourish, list the top four teams in order, together with their tournament record…
set serveroutput on size 1000000
DECLARE
--
-- Find the top 4 teams. Then display the tournament record
-- of the champions
--
l_winners teams.team_name%TYPE;
l_runner_up teams.team_name%TYPE;
l_third teams.team_name%TYPE;
l_fourth teams.team_name%TYPE;
CURSOR c_get_teams( cp_stage fixtures.stage%TYPE) IS
SELECT
CASE
WHEN home_goals > away_goals THEN home_team
WHEN NVL(home_pens, 0) > NVL( away_pens, 0) THEN home_team
ELSE away_team
END as winners,
CASE
WHEN home_goals < away_goals THEN home_team
WHEN NVL(home_pens, 0) < NVL(away_pens, 0) THEN home_team
ELSE away_team
END as losers
FROM fixtures
WHERE stage = cp_stage;
CURSOR c_record( cp_team_name team_fixtures.tm_team_name%TYPE) IS
SELECT COUNT(fix_id) as Pld,
SUM(
CASE
WHEN goals_for > goals_against THEN 1
WHEN NVL(pens_for, 0) > NVL(pens_against, 0) THEN 1
ELSE 0
END) as won,
SUM(
CASE
WHEN goals_for = goals_against
AND NVL(pens_for,0) = NVL(pens_against, 0) THEN 1
ELSE 0
END) as drawn,
SUM(
CASE
WHEN goals_for < goals_against
OR NVL(pens_for,0) < NVL(pens_against, 0) THEN 1
ELSE 0
END) as lost,
SUM( goals_for) as GF,
SUM( goals_against) as GA
FROM team_fixtures
WHERE tm_team_name = cp_team_name;
r_record c_record%ROWTYPE;
BEGIN
OPEN c_get_teams('F');
FETCH c_get_teams INTO l_winners, l_runner_up;
CLOSE c_get_teams;
OPEN c_get_teams('TPP');
FETCH c_get_teams INTO l_third, l_fourth;
CLOSE c_get_teams;
--
-- Now get the tournament record of each team
--
DBMS_OUTPUT.PUT_LINE('Copa America 2001 Champions - '||l_winners);
DBMS_OUTPUT.PUT_LINE('Runners Up - '||l_runner_up);
DBMS_OUTPUT.PUT_LINE('Third Place - '||l_third);
DBMS_OUTPUT.PUT_LINE('Fourth Place - '||l_fourth);
OPEN c_record( l_winners);
FETCH c_record INTO r_record;
CLOSE c_record;
DBMS_OUTPUT.PUT_LINE(q'[The Champion's Tornament Record :]');
DBMS_OUTPUT.PUT_LINE('Pld '||r_record.pld||' Won '||r_record.won||' Drawn '||r_record.drawn
||' Lost '||r_record.lost||' GF '||r_record.gf||' GA '||r_record.ga);
END;
/
And we get….
Copa America 2001 Champions - COLOMBIA Runners Up - MEXICO Third Place - HONDURAS Fourth Place - URUGUAY The Champion's Tornament Record : Pld 6 Won 6 Drawn 0 Lost 0 GF 11 GA 0 PL/SQL procedure successfully completed. SQL>
I’ll leave German with his recollections of Francisco Maturana’s men sweeping to glory, aided by the goals of Victor Aristizabal.
In the meantime, I’m off to write “Goldilocks and the Three Normal Forms”.
Share this:
The Anti-Kyte 
Well, mission accomplished … i laughed a lotttt … i’d say long enough …
LikeLike
I hope you were laughing at the memory of Columbia’s win rather than the database design 🙂
LikeLike
Beautiful Mind!…great coding design…brilliant work!
Thanks alot for the ideas..
LikeLike