Banner

 

6 - One-to-One and Recursive Relationships
Slide Exercises

Several Olympic events are team sports (e.g., basketball, relays) and some involve a pair of athletes (e.g., kayaking, rowing, beach volleyball)

Draw a data model to record these details


Find the names of employees in the same department as their boss.
SELECT wrk.empfname, boss.empfname, boss.deptname 	
FROM emp wrk JOIN emp boss
ON wrk.bossno = boss.empno
WHERE wrk.deptname = boss.deptname;

Design a database to record details of all Olympic cities


Add details of the last three summer Olympics’ cities.
Use SQL to determine which city was the host before London in 2008.

INSERT INTO city (cityName, cityNation) VALUES ('Athens', 'Greece'); 
INSERT INTO city (cityName, cityNation) VALUES ('London', 'United Kingdom'); 
INSERT INTO city (cityName, cityNation) VALUES ('Beijing', 'China');  
INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName) VALUES (2004, 'Summer', 28, 'Athens'); 

INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2008, 'Summer', 29, 'Beijing', 2004, 'Summer'); 
INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2012, 'Summer', 30, 'London', 2008, 'Summer'); 

SELECT cityName FROM olympics WHERE olympicYear = (SELECT prior_olympicYear  
FROM olympics 
WHERE cityName = 'London'
AND olympicYear = 2008);

In a round-robin tournament, each contestant meets all other contestants in turn

In the Olympics, it is common for an event with a large pool of contestants to be broken into groups, with a round-robin tournament in each group to determine who advances from the group to the next level

Design a data model to record details of a round-robin competition


Model the following situations

friendship

prerequisite

matrix


Insert data in the round-robin database for the 2012 Football (Soccer) competition for Group A, with four teams

How many ties were there in Group A?

Use the ISO two-character country code to identify countries.

INSERT INTO group (groupID) VALUES ('A');

INSERT INTO contestant (contestantID, groupID) VALUES ('AE', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('GB', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('SN', 'A');
INSERT INTO contestant (contestantID, groupID) VALUES ('UY', 'A');

INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('AE', 'UY', 1, 2, '20120726');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'SN', 1, 1, '20120726');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'AE', 3, 1, '20120729');
INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('SN', 'UY', 2, 0, '20120729');

SELECT count(*) FROM contest WHERE score1 = score2;

Model a diagram.

Language

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 02-Dec-2022