Write SQL to determine which administrative units have two capitals and which have a shared capital.
If the identifier for CITY is (cityname, unitname, natname), then there is a foreign key constraint problem when you try to insert in ADMIN-UNIT-CITY the fact that Chandigarh is the capital of Punjab and Haryana. The foreign key constraint for the original model for ADMIN-UNIT-CITY requires Chandigarh to be in the admin unit Union Territory and thus will not let you specify it is in either Punjab or Haryana, which it is not.
The solution is to create cityid as the identifier of CITY. The resulting foreign key constraint only needs to check that the city exists in CITY.
This demonstrates the importance of testing your model with data, which I had done, but the problem did not surface until a student tried it with MySQL, and I had not tested it with MySQL.
CREATE TABLE nation ( natname VARCHAR(30), natpop INTEGER, natarea INTEGER, PRIMARY KEY(natname)); CREATE TABLE adminunit ( unitname VARCHAR(30), unittype VARCHAR(10), unitpop INTEGER, unitarea INTEGER, natname VARCHAR(30), PRIMARY KEY(natname,unitname), CONSTRAINT fknationadmin FOREIGN KEY(natname) REFERENCES nation(natname)); CREATE TABLE city ( cityid INTEGER AUTO_INCREMENT, cityname VARCHAR(30), citypop INTEGER, cityarea INTEGER, natcaptype VARCHAR(10), unitname VARCHAR(30), natname VARCHAR(30), PRIMARY KEY(cityid), CONSTRAINT fknationcity FOREIGN KEY(natname) REFERENCES nation(natname), CONSTRAINT fkcitynationadmin FOREIGN KEY(natname,unitname) REFERENCES adminunit(natname,unitname)); CREATE TABLE adminunitcity ( unitcaptype VARCHAR(10), cityid INTEGER, natname VARCHAR(30), unitname VARCHAR(30), PRIMARY KEY(natname,unitname, cityid), CONSTRAINT fkunit FOREIGN KEY(natname,unitname) REFERENCES adminunit(natname,unitname), CONSTRAINT fkcity FOREIGN KEY(cityid) REFERENCES city(cityid));Inserts for India cities discussed in item 5 of Table 7-3
INSERT INTO nation (natname) VALUES ('India');
INSERT INTO adminunit (unitname, natname) VALUES ('Union Territory', 'India');
INSERT INTO adminunit (unitname, unittype, natname) VALUES ('Punjab', 'State', 'India');
INSERT INTO adminunit (unitname, unittype, natname) VALUES ('Haryana', 'State', 'India');
INSERT INTO adminunit (unitname, unittype, natname) VALUES ('Jammu & Kashmir', 'State', 'India');
INSERT INTO city (cityname, unitname, natname) VALUES ('Chandigarh', 'Union Territory', 'India');
INSERT INTO city (cityname, unitname, natname) VALUES ('Jammu', 'Jammu & Kashmir', 'India');
INSERT INTO city (cityname, unitname, natname) VALUES ('Srinagar', 'Jammu & Kashmir', 'India');
INSERT INTO adminunitcity VALUES ('State', 1, 'India', 'Punjab');
INSERT INTO adminunitcity VALUES ('State', 1, 'India', 'Haryana');
INSERT INTO adminunitcity VALUES ('Summer', 2, 'India', 'Jammu & Kashmir');
INSERT INTO adminunitcity VALUES ('Winter', 3, 'India', 'Jammu & Kashmir');
Now for the queries.
SELECT unitname FROM adminunitcity
GROUP BY unitname having count(*) > 1;
SELECT unitname FROM adminunitcity
WHERE cityid IN (
SELECT cityid FROM adminunitcity
GROUP BY cityid HAVING COUNT(*) > 1);
The International Commission for Border Resolution Disputes requires a database to record details of which countries have common borders. Design the database. Incidentally, which country borders the most other countries?
Horse racing is a popular sport in some parts of the world. A horse competes in at most one race on a course at a particular date. Over time, a horse can compete in many races on many courses. A horse's rider is called a jockey, and a jockey can ride many horses and a horse can have many jockeys. Of course, there is only ever one jockey riding a horse at a particular time. Courses vary in their features, such as the length of the course and the type of surface (e.g., dirt or grass). Design a database to keep track of the results of horse races.
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |