# 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

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