Before you keep reading...

Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.

Support Runestone Academy Today

Before you keep reading...

Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.

Support Runestone Academy Today

13.11. Same Relation A and B, M - 1 match over cols Aid(D),Bid(E)

For this case and the next, we will concentrate on a single relation and what we can do with that same relation being the A and B input relations of a Match Join.

Let’s focus on the Creature relation, whose conceptual schema fragment and columns in its relation are as follows:

English Query:

Find each creature with its idol creature data.

Precedence Chart:

For this case, let’s look at the reflexive relationship. In this case we know from the shape that a Creature can be the idol of many Creatures, but each Creature has one idol Creature. We therefore have a M - 1 situation when matching over idol_creatureId and creatureId. This means we have this precedence chart for the Match join:

SQL Syntax for this reflexive query

In the example below, I chose to make the query look similar to the times from the set operator chapter in the first tab. In the second tab it is shortened. Once you see how the query contains the Times, you will probably prefer the second tab’s syntax.

SELECT C1.creatureId AS C1_creatureId, C1.creatureName AS C1_creatureName, C1.creatureType AS C1_creatureType, C1.reside_townId AS C1_reside_townId, C1.idol_creatureId AS C1_idol_creatureId, C2.creatureName AS idol_creatureName, C2.creatureType AS idol_creatureType, C2.reside_townId AS idol_reside_townId, C2.idol_creatureId AS idol_idol_creatureId FROM creature C1, creature C2 WHERE C1.idol_creatureId = C2.creatureId;

SELECT C1.*, C2.creatureName AS idol_creatureName, C2.creatureType AS idol_creatureType, C2.reside_townId AS idol_reside_townId, C2.idol_creatureId AS idol_idol_creatureId FROM creature C1, creature C2 WHERE C1.idol_creatureId = C2.creatureId;

DROP TABLE IF EXISTS creature; CREATE TABLE creature ( creatureId INTEGER NOT NULL PRIMARY KEY, creatureName VARCHAR(20), creatureType VARCHAR(20), reside_townId VARCHAR(3) REFERENCES town(townId), -- foreign key idol_creatureId INTEGER, FOREIGN KEY(idol_creatureId) REFERENCES creature(creatureId) ); INSERT INTO creature VALUES (1,'Bannon','person','p',10); INSERT INTO creature VALUES (2,'Myers','person','a',9); INSERT INTO creature VALUES (3,'Neff','person','be',NULL); INSERT INTO creature VALUES (4,'Neff','person','b',3); INSERT INTO creature VALUES (5,'Mieska','person','d', 10); INSERT INTO creature VALUES (6,'Carlis','person','p',9); INSERT INTO creature VALUES (7,'Kermit','frog','g',8); INSERT INTO creature VALUES (8,'Godzilla','monster','t',6); INSERT INTO creature VALUES (9,'Thor','superhero','as',NULL); INSERT INTO creature VALUES (10,'Elastigirl','superhero','mv',13); INSERT INTO creature VALUES (11,'David Beckham','person','le',9); INSERT INTO creature VALUES (12,'Harry Kane','person','le',11); INSERT INTO creature VALUES (13,'Megan Rapinoe','person','sw',10);

Note that in the above example, you need to scroll to the right to see all of the columns when paring each Creature with its idol.

Q-4: Which creatures are missing from the result? Why?

You have attempted of activities on this page