Scarlett SchoolLessonsAboutContact

RDBMS Lesson 2: Tables

Databases are composed of tables. A table is a linear arrangement of rows & columns, similar to a grid. If you've ever used Excel or Google Sheets, each sheet is a kind-of free form table.

In a database, tables have what's called a schema, which is the way the table is organized. This is primarily a description of the table, by way of its columns. Each column has a name, a data type, and other properties that determine how it functions. The table itself also has a name, and many tables can exist in a database, in fact, the relational nature of a RDBMS demands it, as we'll see later.

As previously mentioned, tables consist of rows and columns. Columns are named, live on the vertical axis, and describe the different kinds of data in the rows, which live on the horizontal axis. Rows are keyed by one or several columns, which means that is how they are looked up. Each row is an expression of the columns in the schema. Row members may either be the data type expressed in the column, such as a number or text, or NULL, which means that there is no data.

Example:

idnameaddressphone
1Erik1600 Pennsylvania Ave.555-867-5309
2Joe1 Francis WayNULL

In the example, Erik has an ID of 1, an address of "1600 Pennsylvania Ave.", and a phone number of 555-867-5309. Joe has an ID of 2 and no phone number, indicated by NULL.

When tables have a relationship, they are joined. These two tables are joined to express multiple people with the same address.

idnameaddress_id
1Erik2
2Joe1
3Barbara1
4Spot2
idaddress
11 Francis Way
21600 Pennsylvania Ave.

The address_id in the first table is joined against the id in the second table. This relates 2 addresses to 4 people. This is useful for a variety of reasons that we will see later.

Assignment

Given the following two tables:

idname
1Erik
2Sean
3Scarlett
4Tom
5Darcy
6Max
name_idparent_id
24
25
31
36

Indicate how you might determine a family lineage, and then describe who is related to whom.

© 2023-2025 Erik Hollensbe. All Rights Reserved.