Relational Database Concepts and Workstation Safety

What Is a Relational Database?

A relational database organizes data into tables (also called relations). Each table represents one type of thing — a customer, an order, a product. Rows in a table are called records or tuples; columns are called attributes or fields. The relational model was formalized by E.F. Codd in 1970 and remains the foundation of most production data systems you will work with as a data analyst.

Relational databases are powerful because they reduce data duplication and let you connect information across tables using shared values. PostgreSQL, the open-source database system used in this course, implements the relational model and follows the SQL standard closely.

Core Concepts: Entities, Attributes, and Relationships

An entity is a real-world thing your database tracks — for example, a Customer or an Invoice. An attribute is a property of that entity — for example, a customer's last name or email address. When you design a table, each entity becomes a table and each attribute becomes a column.

A relationship is a meaningful link between two entities. Relationships are represented by storing a key value from one table inside another table. The three common relationship types are:

• One-to-one — one record in Table A matches exactly one record in Table B.
• One-to-many — one record in Table A matches many records in Table B (most common).
• Many-to-many — many records in Table A match many records in Table B; usually resolved with a third junction table.

Keys: Primary and Foreign

A primary key is a column (or combination of columns) whose value uniquely identifies every row in its table. No two rows may share the same primary key value, and it may never be NULL. Common choices are an auto-incrementing integer (called a serial or identity column in PostgreSQL) or a UUID.

A foreign key is a column in one table that references the primary key of another table. It enforces referential integrity — the database prevents you from storing a foreign key value that does not exist in the referenced table. For example, an `orders` table might have a `customer_id` column that is a foreign key pointing to `id` in the `customers` table.

Normalization: 1NF, 2NF, and 3NF

Normalization is the process of organizing tables to reduce redundancy and prevent update anomalies. You need to know the first three normal forms for your certification exams.

First Normal Form (1NF): Every column holds only atomic (indivisible) values; there are no repeating groups. A cell must not contain a list or multiple values.

Second Normal Form (2NF): The table is in 1NF, and every non-key column depends on the whole primary key, not just part of it. This matters when a primary key spans multiple columns.

Third Normal Form (3NF): The table is in 2NF, and no non-key column depends on another non-key column (no transitive dependencies). Each piece of information lives in exactly one place.

Higher normal forms exist but are beyond the scope of this lesson.

Components of a PostgreSQL Installation

When you open the class VM, you will find a running PostgreSQL 16 instance. Key components to recognize:

• postgres server process — the background service that handles all connections and queries.
• psql — the command-line client you use to send SQL statements and view results.
• pg_hba.conf — the host-based authentication file that controls who may connect and how.
• postgresql.conf — the main configuration file for memory, logging, and network settings.
• pgAdmin (optional GUI) — a browser-based interface for managing databases visually.

The default superuser account is named `postgres`. You should treat this account like a root account on a Linux system — use it only for administration.

Cybersecurity Controls for Database Workstations

Database workstations handle sensitive data, so security practices are not optional.

Weak passwords are the most common entry point for attackers. Use long, unique passphrases for every database account, and never leave the default `postgres` password unchanged in any environment beyond a local practice VM.

Unencrypted connections expose data in transit. PostgreSQL supports TLS (Transport Layer Security) for client connections. In production, always require encrypted connections by setting `ssl = on` in `postgresql.conf` and enforcing it in `pg_hba.conf`.

Unrestricted superuser access violates the principle of least privilege. Create role-specific database users with only the permissions their job requires. Analysts typically need SELECT on specific schemas — not CREATE, DROP, or superuser rights.

Additional controls include keeping PostgreSQL patched to the current minor release, logging failed login attempts, and locking workstations when stepping away.

Ergonomic Safety at the Database Workstation

Data analysts spend long hours at computers. Poor workstation setup causes musculoskeletal injuries over time. OSHA's computer workstation guidance identifies these key adjustments:

• Chair height: Set so feet rest flat on the floor (or a footrest) and thighs are approximately parallel to the floor. Knees should be at roughly a 90-degree angle.
• Monitor distance: Position the monitor approximately arm's length away (roughly 20–26 inches). The top of the screen should be at or slightly below eye level to reduce neck strain.
• Keyboard and mouse placement: Keep them at elbow height so wrists remain neutral (not bent up or down). Use a wrist rest only during pauses, not while actively typing.
• Lighting: Reduce glare on the monitor by positioning the screen perpendicular to windows.
• Breaks: Follow the 20-20-20 rule — every 20 minutes, look at something 20 feet away for 20 seconds — to reduce eye strain.

Document your workstation setup in the lab log at the start of every lab session. This creates a record that your environment meets safety standards and builds the habit of safety documentation required in professional data environments.