8.1. A small example database

Here is a conceptual schema for a database that we will use throughout the rest of this book. It is expanded from the examples used in Chapters 2-4 that were for simpler illustrations mapping of schema fragments to SQL creation and population statements.

You do not heed to completely familiarize yourself with it now- we will be delving into portions of it as we learn about the different operators and practice applying them to relations in the database.

You can and should download This Small Creature Database Schema and keep a copy for your own reference. You may find it useful to have it to refer to as you are reading about the relational operators and example charts that refer to the entities in this diagram, which match the relational tables created below.

What follows below is a somewhat long file of SQL code to create the relations for a database matching the above schema (entities are mapped to relations and foreign keys are used for relationships). There are also commands to insert sample data for the database. As we study each operation in the rest of this book, we will use portions of this database. At that time, as we use them, we will provide more insights about the instances, or rows of data that are in each relation.

If you wish to try this data in your own SQLite database system, you can download CreatureDB.sql

-- ------------------ town -- ------------------------------- DROP TABLE IF EXISTS town; CREATE TABLE town ( townId VARCHAR(3) NOT NULL PRIMARY KEY, townName VARCHAR(20), State VARCHAR(20), Country VARCHAR(20), townNickname VARCHAR(80), townMotto VARCHAR(80) ); -- order matches table creation: -- id name state country -- nickname motto INSERT INTO town VALUES ('p', 'Philadelphia', 'PA', 'United States', 'Philly', 'Let brotherly love endure'); INSERT INTO town VALUES ('a', 'Anoka', 'MN', 'United States', 'Halloween Capital of the world', NULL); INSERT INTO town VALUES ('be', 'Blue Earth', 'MN', 'United States', 'Beyond the Valley of the Jolly Green Giant', 'Earth so rich the city grows!'); INSERT INTO town VALUES ('b', 'Bemidji', 'MN', 'United States', 'B-town', 'The first city on the Mississippi'); INSERT INTO town VALUES ('d', 'Duluth', 'MN', 'United States', 'Zenith City', NULL); INSERT INTO town VALUES ('g', 'Greenville', 'MS', 'United States', 'The Heart & Soul of the Delta', 'The Best Food, Shopping, & Entertainment In The South'); INSERT INTO town VALUES ('t', 'Tokyo', 'Kanto', 'Japan', NULL, NULL); INSERT INTO town VALUES ('as', 'Asgard', NULL, NULL, 'Home of Odin''s vault', 'Where magic and science are one in the same'); INSERT INTO town VALUES ('mv', 'Metroville', NULL, NULL, 'Home of the Incredibles', 'Still Standing'); INSERT INTO town VALUES ('le', 'London', 'England', 'United Kingdom', 'The Smoke', 'Domine dirige nos'); INSERT INTO town VALUES ('sw', 'Seattle', 'Washington', 'United States', 'The Emerald City', 'The City of Goodwill'); -- ------------------ creature -- ------------------------------- 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); -- ------------------ skill -- ------------------------------- DROP TABLE IF EXISTS skill; CREATE TABLE skill ( skillCode VARCHAR(3) NOT NULL PRIMARY KEY, skillDescription VARCHAR(40), maxProficiency INTEGER, -- max score that can be achieved for this skill minProficiency INTEGER, -- min score that can be achieved for this skill origin_townId VARCHAR(3) REFERENCES town(townId) -- foreign key ); INSERT INTO skill VALUES ('A', 'float', 10, -1,'b'); INSERT INTO skill VALUES ('E', 'swim', 5, 0,'b'); INSERT INTO skill VALUES ('O', 'sink', 10, -1,'b'); INSERT INTO skill VALUES ('U', 'walk on water', 5, 1,'d'); INSERT INTO skill VALUES ('Z', 'gargle', 5, 1,'a'); INSERT INTO skill VALUES ('B2', '2-crew bobsledding', 25, 0,'d'); INSERT INTO skill VALUES ('TR4', '4x100 meter track relay', 100, 0,'be'); INSERT INTO skill VALUES ('C2', '2-person canoeing', 12, 1,'t'); INSERT INTO skill VALUES ('THR', 'three-legged race', 10, 0,'g'); INSERT INTO skill VALUES ('D3', 'Australasia debating', 10, 1,NULL); INSERT INTO skill VALUES ('PK', 'soccer penalty kick', 10, 1, 'le'); -- Note that no skill originates in Philly or Metroville or Asgaard -- ------------------ teamSkill -- ------------------------------- DROP TABLE IF EXISTS teamSkill; CREATE TABLE teamSkill ( skillCode VARCHAR(3) NOT NULL PRIMARY KEY references skill (skillCode), teamSize INTEGER ); INSERT INTO teamSkill VALUES ('B2', 2); INSERT INTO teamSkill VALUES ('TR4', 4); INSERT INTO teamSkill VALUES ('C2', 2); INSERT INTO teamSkill VALUES ('THR', 2); INSERT INTO teamSkill VALUES ('D3', 3); -- ------------------ achievement -- ------------------------------- DROP TABLE IF EXISTS achievement; CREATE TABLE achievement ( achId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, creatureId INTEGER, skillCode VARCHAR(3), proficiency INTEGER, achDate TEXT, test_townId VARCHAR(3) REFERENCES town(townId), -- foreign key FOREIGN KEY (creatureId) REFERENCES creature (creatureId), FOREIGN KEY (skillCode) REFERENCES skill (skillCode) ); -- Bannon floats in Anoka (where he aspired) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (1, 'A', 3, datetime('now'), 'a'); -- Bannon swims in Duluth (he aspired in Bemidji) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (1, 'E', 3, datetime('2017-09-15 15:35'), 'd'); -- Bannon floats in Anoka (where he aspired) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (1, 'A', 3, datetime('2018-07-14 14:00'), 'a'); -- Bannon swims in Duluth (he aspired in Bemidji) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (1, 'E', 3, datetime('now'), 'd'); -- Bannon doesn't gargle -- Mieska gargles in Tokyo (had no aspiration to) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (5, 'Z', 6, datetime('2016-04-12 15:42:30'), 't'); -- Neff #3 gargles in Blue Earth (but not to his aspired proficiency) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be'); -- Neff #3 gargles in Blue Earth (but not to his aspired proficiency) -- on same day at same proficiency, signifying need for arbitrary id INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be'); -- Neff #3 keeps trying to gargle on the same day, with varying results INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be'); -- Neff #4 gargles in Anoka INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (4, 'Z', 3, datetime('2018-06-10'), 'a'); -- Beckham achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (11, 'PK', 10, datetime('1998-08-15'), 'le'); -- Kane achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (12, 'PK', 10, datetime('2016-05-24'), 'le'); -- Rapinoe achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (13, 'PK', 10, datetime('2012-08-06'), 'le'); -- Godzilla achieves PK in Tokyo poorly with no date -- had not aspiration to do so- did it on a dare ;) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (8, 'PK', 1, NULL, 't'); -- -------------------- -------------------- ------------------- -- Thor achieves three-legged race in Metroville (with Elastigirl) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (9, 'THR', 10, datetime('2018-08-12 14:30'), 'mv'); -- Elastigirl achieves three-legged race in Metroville (with Thor) INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (10, 'THR', 10, datetime('2018-08-12 14:30'), 'mv'); -- Kermit 'pilots' 2-person bobsledding (pilot goes into contribution) -- with Thor as brakeman (brakeman goes into contribution) in Duluth, -- achieve at 76% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (7, 'B2', 19, datetime('2017-01-10 16:30'), 'd'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (9, 'B2', 19, datetime('2017-01-10 16:30'), 'd'); -- 4 people form track realy team in London: -- Neff #4, Mieska, Myers, Bannon -- achieve at 85% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (4, 'TR4', 85, datetime('2012-07-30'), 'le'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (5, 'TR4', 85, datetime('2012-07-30'), 'le'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (2, 'TR4', 85, datetime('2012-07-30'), 'le'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (1, 'TR4', 85, datetime('2012-07-30'), 'le'); -- Thor, Rapinoe, and Kermit form debate team in Seattle, WA and -- achieve at 80% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (9, 'D3', 8, datetime('now', 'localtime'), 'sw'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (13, 'D3', 8, datetime('now', 'localtime'), 'sw'); INSERT INTO achievement (creatureId, skillCode, proficiency, achDate, test_townId) VALUES (7, 'D3', 8, datetime('now', 'localtime'), 'sw'); -- no 2-person canoeing achievements, but some have aspirations -- ------------------ aspiration -- ------------------------------- DROP TABLE IF EXISTS aspiration; CREATE TABLE aspiration ( -- foreign key creatureId INTEGER NOT NULL REFERENCES creature(creatureId), -- foreign key skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), aspiredProficiency INTEGER, desired_townId VARCHAR(3) REFERENCES town(townId), -- foreign key PRIMARY KEY (creatureId, skillCode) ); -- Bannon aspires float in Anoka with proficiency of 3 INSERT INTO aspiration VALUES (1,'A',3,'a'); -- Bannon aspires swim in Bemidji with proficiency of 4 INSERT INTO aspiration VALUES (1,'E',4,'b'); -- Bannon aspires gargling in Blue Earth with proficiency of 3 INSERT INTO aspiration VALUES (1,'Z',3,'be'); -- Myers aspires float with proficiency of 3 INSERT INTO aspiration VALUES (2,'A',3,NULL); -- Neff #3 aspires float in Bemidji with proficiency of 8 INSERT INTO aspiration VALUES (3,'A',8,'b'); -- Neff #3 aspires gargling in Blue Earth with proficiency of 5 INSERT INTO aspiration VALUES (3,'Z',5,'be'); -- Neff #4 aspires swim in Greenville with proficiency of 3 INSERT INTO aspiration VALUES (4,'E',3,'g'); -- Mieska aspires gargling in Duluth with proficiency of INSERT INTO aspiration VALUES (5,'Z',10,'d'); -- Carlis aspires gargling in London with proficiency of INSERT INTO aspiration VALUES (6,'Z',3,'le'); -- Kermit aspires swim in Bemidji with proficiency of INSERT INTO aspiration VALUES (7,'E',3,'b'); -- Godzilla aspires sink in Tokyo with proficiency of INSERT INTO aspiration VALUES (8,'O',4,'t'); -- Beckham, Kane, and Rapinoe aspire to achieve PK at maxProficiency in London INSERT INTO aspiration VALUES (11,'PK',10,'le'); INSERT INTO aspiration VALUES (12,'PK',10,'le'); INSERT INTO aspiration VALUES (13,'PK',10,'le'); -- Kermit aspires to achieve 2-person bobsledding at proficiency 20 in Duluth INSERT INTO aspiration VALUES (7,'B2',20,'d'); -- Bannon and Mieska aspire to achieve 4x100 meter track relay at -- proficiency of 85 in Seattle, WA. INSERT INTO aspiration VALUES (1,'TR4',85,'sw'); INSERT INTO aspiration VALUES (5,'TR4',85,'sw'); -- Thor, Rapinoe, and Kermit form debate team in Seattle, WA and -- asppire to achieve at 80% of maxProficiency INSERT INTO aspiration VALUES (9,'D3',8,'sw'); INSERT INTO aspiration VALUES (13,'D3',8,'sw'); INSERT INTO aspiration VALUES (7,'D3',8,'sw'); -- no 2-person canoeing achievements, but some have aspirations -- Carlis and Bannon aspire to achieve 2-person canoeing in Bemidji -- with proficiency of 9 INSERT INTO aspiration VALUES (6,'C2',9,'b'); INSERT INTO aspiration VALUES (1,'C2',9,'b'); -- Thor, Elastigirl do not aspire to anything -- ------------------ role -- ------------------------------- DROP TABLE IF EXISTS role; CREATE TABLE role ( roleName VARCHAR(20) NOT NULL PRIMARY KEY ); INSERT INTO role VALUES ('first leg'); -- 4x100 track INSERT INTO role VALUES ('second leg'); -- 4x100 track INSERT INTO role VALUES ('third leg'); -- 4x100 track INSERT INTO role VALUES ('anchor leg'); -- 4x100 track INSERT INTO role VALUES ('pilot'); -- 2-crew bobsled INSERT INTO role VALUES ('brakeman'); -- 2-crew bobsled INSERT INTO role VALUES ('right leg'); -- 3-legged race INSERT INTO role VALUES ('left leg'); -- 3-legged race INSERT INTO role VALUES ('stern paddler'); -- 2-person canoeing INSERT INTO role VALUES ('bow paddler'); -- 2-person canoeing INSERT INTO role VALUES ('first speaker'); -- Australasia debating INSERT INTO role VALUES ('second speaker');-- Australasia debating INSERT INTO role VALUES ('team captain'); -- Australasia debating -- ------------------ contribution -- ------------------------------- DROP TABLE IF EXISTS contribution; CREATE TABLE contribution ( creatureId INTEGER NOT NULL REFERENCES creature(creatureId), achId INTEGER NOT NULL REFERENCES achievement(achId), skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), roleName VARCHAR(20) REFERENCES role(roleName), PRIMARY KEY (creatureId, achId) ); -- Thor (right leg) achieves three-legged race in Metroville (with Elastigirl (left leg)) INSERT INTO contribution VALUES (9, 12, 'THR', 'right leg'); INSERT INTO contribution VALUES (10, 13, 'THR', 'left leg'); -- Kermit 'pilots' 2-crew bobsledding -- with Thor as brakeman INSERT INTO contribution VALUES (7, 14, 'B2', 'pilot'); INSERT INTO contribution VALUES (9, 15, 'B2', 'brakeman'); -- -- keep track relay, have 4 people: -- Neff #4 (first leg), Mieska(second leg), Myers (third leg), Bannon (anchor leg) INSERT INTO contribution VALUES (4, 16, 'TR4', 'first leg'); INSERT INTO contribution VALUES (5, 17, 'TR4', 'second leg'); INSERT INTO contribution VALUES (2, 18, 'TR4', 'third leg'); INSERT INTO contribution VALUES (1, 19, 'TR4', 'anchor leg'); -- Thor (second speaker), Rapinoe (team captain), and Kermit (first speaker) form debate team INSERT INTO contribution VALUES (7, 22, 'D3', 'first speaker'); INSERT INTO contribution VALUES (9, 20, 'D3', 'second speaker'); INSERT INTO contribution VALUES (13, 21, 'D3', 'team captain'); -- -- no 2-person canoeing contributions, but some have aspirations -- ------------------ aspiredContribution -- ------------------------------- DROP TABLE IF EXISTS aspiredContribution; CREATE TABLE aspiredContribution ( creatureId INTEGER NOT NULL REFERENCES creature(creatureId), skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), roleName VARCHAR(20) REFERENCES role(roleName), PRIMARY KEY (creatureId, skillCode) ); -- no 2-person canoeing contributions, but Carlis and Bannon have aspirations INSERT INTO aspiredContribution VALUES (6, 'C2', 'stern paddler'); INSERT INTO aspiredContribution VALUES (1, 'C2', 'bow paddler'); -- Bannon and Mieska aspire to contribute to achieve 4x100 meter track relay -- Bannon contributed in his aspired to role, Mieska had a different -- aspired to role than he ultimately contributed to INSERT INTO aspiredContribution VALUES (1, 'TR4', 'anchor leg'); INSERT INTO aspiredContribution VALUES (5, 'TR4', 'third leg'); -- Kermit aspires to contribute to piloting bobsled INSERT INTO aspiredContribution VALUES (7, 'B2', 'pilot'); -- Thor, Rapinoe and Kermit aspire to contribute to debate INSERT INTO aspiredContribution VALUES (7, 'D3', 'first speaker'); INSERT INTO aspiredContribution VALUES (9, 'D3', 'second speaker'); INSERT INTO aspiredContribution VALUES (13, 'D3', 'team captain'); -- Elastigirl, others not aspiring to contribute to anything

For starters, let’s just examine one of the relations that is central to this database, which represents a fictional scenario of creatures of various types both aspiring to and achieving skills, some of them while contributing to a team. Here is our main cast of characters, each creature in the creature relation:

Creature

creatureId	creatureName	creatureType	reside_townId	idol_creatureId
1	Bannon	person	p	10
2	Myers	person	a	9
3	Neff	person	be	NULL
4	Neff	person	b	3
5	Mieska	person	d	10
6	Carlis	person	p	9
7	Kermit	frog	g	8
8	Godzilla	monster	t	6
9	Thor	superhero	as	NULL
10	Elastigirl	superhero	mv	13
11	David Beckham	person	le	9
12	Harry Kane	person	le	11
13	Megan Rapinoe	person	sw	10

Important

Note how we carefully use a singular name for the relation. This convention is important to understanding how the relational algebra operations work and we believe makes it easier to master relational data analysis. We will use this convention throughout this book. The way we think of this is that each row represents one creature, and the name of a relation is for one of these instances.

In the next chapter we will begin our exploration of relational algebra and precedence charts by starting with the unary operators.

You have attempted of activities on this page