Databases and Tables

A database is a place where structured data is stored. Entities are groups of objects that share common characteristics. Objects are individual instances of entities.

We are going to study relational databases, where entities are tables and table rows are their objects.

To work with databases you need a DBMS, or database management system. This is a set of programs that allows you to create a database, fill it with new tables, and edit and display the contents of existing tables. In our course we will use PostgreSQL, one of the most popular DBMS.

Tables

A table is a set of rows and columns. Columns are called fields. They contain the object's features. Each field has a unique name and a specific data type. Table rows are called tuples or records. Each row contains information about one particular object. A cell is a unit where a row and column intersect.

Primary keys are used to give each row a unique identifier. Some tables use several fields at once as their primary keys; in such cases, they're called composite primary keys.

Types of Relationships Between Tables

When a column contains the values of the field of another table, it's called a foreign key. It's responsible for the relationship between the tables.

There are three types of relationships:

• one-to-one
• one-to-many
• many-to-many

In a one-to-one relationship, each row in a table is connected with one and only one row in the other table. It's as if a table has been split in half. This is a rare type of relationship and is predominantly used for security reasons.

In a one-to-many relationship, each row of a table matches multiple rows in another table.

In a many-to-many relationship, several rows from one table match several rows from another table. This type of relationship produces an association table, which combines the primary keys of both tables.

ER Diagrams

The structure of databases can be shown with ER (entity-relationship) diagrams. They show tables and the relationships between them.

Tables are shown as rectangles (boxes) with two parts. The table name goes in the upper part.

In the lower part we see lists of table fields with an indication of which keys they are related to: primary or foreign. The keys are usually marked PK (primary) or FK (foreign), but they can be also marked with a key icon, a #, or another symbol.

ER diagrams also show relationships. The end of the line connecting two tables indicates whether one or several values from one table match values from the other.

Here's a one-to-many relationship:

And here's a one-to-one relationship:

Database User Types

Many employees use a company's master database simultaneously. Each of them needs only certain data to do their job. Things must therefore be arranged such that users don't interfere in the others' work. Hence the need for database administrators. They manage user access, monitor the load on the system, take care of security, and make backups.

Databases are like living creatures, growing constantly. Database architects and developers make sure they grow healthily. These specialists' decisions determine the database's structure, integrity, and fullness, as well as its scaling possibilities (adding new tables, relationships, and functions). Architects and developers are responsible for the database's performance.

Data engineers are responsible for adding data to the database. They are also called ETL specialists, since they extract, transform, and upload data to databases.

Analysts, in this scheme, are typical users. They write queries to databases and retrieve the necessary data, which they then analyze and use to test hypotheses. Analysts work more closely than the others with the data it, and it's he who is the first to encounter missing fields or tables. Make it your habit to immediately communicate such "discoveries" to developers if you want such errors to be solved promptly.