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:
id | name | address | phone |
---|---|---|---|
1 | Erik | 1600 Pennsylvania Ave. | 555-867-5309 |
2 | Joe | 1 Francis Way | NULL |
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.
id | name | address_id |
---|---|---|
1 | Erik | 2 |
2 | Joe | 1 |
3 | Barbara | 1 |
4 | Spot | 2 |
id | address |
---|---|
1 | 1 Francis Way |
2 | 1600 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:
id | name |
---|---|
1 | Erik |
2 | Sean |
3 | Scarlett |
4 | Tom |
5 | Darcy |
6 | Max |
name_id | parent_id |
---|---|
2 | 4 |
2 | 5 |
3 | 1 |
3 | 6 |
Indicate how you might determine a family lineage, and then describe who is related to whom.