By the end of this, developers should be able to:
- Create tables with foreign key references.
- Create join tables to represent many-to-many relationships.
- Insert rows in join tables to create many-to-many relationships.
- Select data about many-to-many relationships using join tables.
- Fork and clone this repository.
- Create a new branch,
training
, for your work. - Install dependencies if necessary.
Our library has books and authors, but it won't be much of a library without borrowers. Our clinic has patients and doctors, but how do we schedule appointments? Our cookbook has ingredients and recipes, but only allows a particular ingredient in a single recipe.
- In our library, how do we connect
borrowers
tobooks
. - How should clinic administrators record appointments?
- What does our cookbook need to include ingredients in recipes?
Let's model these new entities (objects) and their relationships to our existing entities (objects).
In an RDBMs, we do this using join tables
We'll create SQL scripts in scripts/library
to add a borrowers
table and
populate it from data in the patients
table (since we only need a subset of
the columns from data/people.csv
).
Then we'll create a loans
table and populate it using INSERT
statements.
We'll create scripts in scripts/clinic
to add an appointments
table and
populate it using INSERT
statements.
We'll create scripts in scripts/cookbook
to add a recipe_ingredients
table
and populate it using INSERT
statements. Then we'll remove recipe_id
from
ingredients
.
We'll create scripts in scripts/library
to retrieve information about
borrowers, loans, and books.
What happens if we try to DELETE
a borrower or a book?
We'll create scripts in scripts/clinic
to retrieve information about patients,
doctors and appointments.
We'll create scripts in scripts/cookbook
to retrieve information about
recipes.
- Constraints - An overview of the variety of constraints that PostgreSQL provides.
- CREATE TABLE -
detailed documentation of PostgreSQL's version of
the SQL
CREATE TABLE
command. - ALTER TABLE -
detailed documentation of PostgreSQL's version of the
SQL
ALTER TABLE
command. - Index Introduction - The introductory section of the chapter on indexes in PostgreSQL.
- CREATE INDEX -
detailed documentation of PostgreSQL's version of the
SQL
CREATE INDEX
command. - UPDATE -
detailed documentation of PostgreSQL's version of the SQL
UPDATE
command. - INSERT -
detailed documentation of PostgreSQL's version of the
SQL
INSERT INTO
command. - Joins Between Tables - An introduction to querying multiple tables
- SELECT -
detailed documentation of PostgreSQL's version of the SQL
SELECT
command.
- All content is licensed under a CCBYNCSA 4.0 license.
- All software code is licensed under GNU GPLv3. For commercial use or alternative licensing, please contact legal@ga.co.