Pi-Chrome Project

Objective: To build a small relational database using PostgreSQL to learn some fundamentals about relational databases.

Materials: Chromebook, Ethernet Cable, Micro SD card w/ Ubuntu Linux 22.04 LTS Image, OLED Display, Raspberry Pi, USB-C Power Supply

In this lesson, we are going to build a very simple relational database that models a person and their things that they have and/or are a part of.  We could fill our database with much more information than we have, but this should give us the basic idea of how a relational database works and an appreciation for what Django does for us.

Here are some starting points regarding our database:

• Name of database: person_things
• Tables: person, shoe_instance, club, person_club
• Relationships: person to shoe_instance (one to 0 or many), person to club (0 or many to 0 or many)

The following is a description of the things and their corresponding attributes and the relationships that will exist in our database.  A person has a name, age, grade.  A shoe instance has a color, size, make, and model.  A club has a name and meeting day.  A person can have zero or many pairs of shoes.  A shoe instance has one person that owns the shoes.  A person can be part of zero or many clubs.  A club can have zero or many members.  These attributes and relationships are illustrated in the UML (Unified Modeling Language) diagram below.

We’ll let the database auto generate the id for each table.  The id will be our primary key for each table and it can not be repeated within the table.  The purpose of the primary key is to have an attribute that uniquely identifies each row in the database.  Sometimes we can use one of the attributes to uniquely identify each row of data, but this does not always work.  Take for example the Person table.  There could be multiple people that have the same name, age, and grade, which would make it difficult to distinguish between these rows.  Most relational databases can auto generate primary keys, so we’ll take that route with the exception of the person_club table which acts as a “join table”.  The primary key will be the concatenation of the person_id and club_id.

Let’s build our database using the tables and data below.

You can use the github repository in the resource links to build your database.  The complete database dump is supplied in the repository, but it is recommended that you work through each of the SQL statements to build the database from scratch and populate it with data.  Once you have built the database, try several queries.  I have provided a couple of sample queries in the repository to get you started.

The whole purpose of this lesson is to help you get acquainted with relational databases.  In practice, for our Django applications, we won’t have to worry about the SQL queries to populate our web pages with data.  Django has this really cool feature called the Object Relational Mapper (ORM) which does all of this behind the scene for us.  Regardless, this lesson is good to go through, as it gives us an appreciation for what Django offers us.

Resource Links:

person_things database SQL statements: https://github.com/korgul100/rdb_basics_postgres

PostgreSQL: https://www.postgresql.org/

W# Schools SQL tutorial: https://www.w3schools.com/sql/

Pictures:

Person Table

Shoe Instance Table

Club Table

Person_Club "Join" Table

UML Diagram for Person_Things Database

Videos: None