1.2General Knowledge of Relational Databases

The previous section covered PostgreSQL as software and as a project. This section focuses on the relational database concept that PostgreSQL implements. Before Chapter 3 introduces individual SQL statements (SELECT, CREATE TABLE, and so on), understanding the basic data-model concepts and the three-way classification of SQL will make the rest of the material much easier to follow.

1.2.1The relational data model and the role of a DBMS

• The relational data model represents data as a set of tables (relations). A table is a collection of rows (records/tuples), and each row consists of values for a set of columns (attributes).
• A primary key is a column (or set of columns) that uniquely identifies each row in a table. A foreign key is a column in one table that references another table's primary key, expressing a relationship between tables.
• A DBMS (database management system) is the software foundation responsible for persisting data and making retrieval efficient, plus concurrency control for multiple users, recovery from failures, guaranteeing transaction consistency, and managing access privileges.

1.2.2General SQL knowledge and its three-way classification (DDL/DML/DCL)

• SQL (Structured Query Language) is a standardized language for operating on relational databases. It has a common syntax not tied to a specific vendor, and PostgreSQL is known for its high conformance to standard SQL.
• DDL (Data Definition Language) defines the structure (schema) of tables, views, indexes, and the like. Representative examples: CREATE, ALTER, DROP.
• DML (Data Manipulation Language) operates on the data itself stored in tables. Representative examples: SELECT (query), INSERT (add), UPDATE (modify), DELETE (remove).
• DCL (Data Control Language) controls access privileges on database objects. Representative examples: GRANT (grant a privilege) and REVOKE (revoke a privilege). Some classifications also place transaction control statements like COMMIT/ROLLBACK under DCL.

1.2.3Database design and normalization

• Database design is the work of deciding which columns belong in which tables and how tables relate to each other, based on the data a business needs to handle. The quality of this design has a major long-term effect on data integrity, maintainability, and performance.
• Normalization is a design technique that reorganizes data crammed into one table into multiple tables, aiming to reduce redundancy and prevent update inconsistencies. It proceeds according to a series of staged criteria (normal forms).
• Normalization proceeds through staged normal forms: first normal form (1NF) removes repeating groups so each column holds a single value; second normal form (2NF) separates out columns that depend on only part of a composite primary key (partial functional dependency); third normal form (3NF) separates out columns that depend on non-key columns (transitive functional dependency). In practice, organizing up to 3NF is a common target.
• Insufficient normalization tends to store the same information redundantly across multiple rows, making it easy for an update to miss some rows—an update anomaly. Normalization sorts out this redundancy and these anomalies, re-expressing relationships with foreign keys instead.

Walk through designing a fictional student-management system to touch every concept in this section. Suppose the person in charge first considers a single "students" table that stores name and contact info plus the enrolled course name directly as a column. Once a student takes multiple courses, that student's name and contact info get duplicated once per course, and an address change can easily miss some rows—an update anomaly. This is where normalization comes in: split the design into a "students" table and a "courses" table, add an "enrollments" table to represent the relationship between them, and have each table's primary key referenced as a foreign key, eliminating the duplication of names and course titles. Once the table structure is settled, first use DDL's CREATE TABLE to create the three tables and define the necessary constraints (primary keys, foreign keys). Next, use DML's INSERT to load real data; day to day, SELECT queries enrollment status, and an address change only requires an UPDATE on a single row in the "students" table (thanks to normalization, the update stays in one place). Finally, grant office staff read-only access to the students table with DCL's GRANT SELECT, restricting update privileges to administrators only. Behind this whole flow, the DBMS (PostgreSQL, in this case) is quietly handling concurrency control and guaranteeing transaction consistency.

1.2.4Section summary

• The relational data model = tables (rows × columns) with relationships expressed via primary/foreign keys. A DBMS underpins concurrency, recovery, and transactions
• SQL's three categories: DDL (CREATE/ALTER/DROP — define structure) / DML (SELECT/INSERT/UPDATE/DELETE — manipulate data) / DCL (GRANT/REVOKE — control privileges)
• The goal of normalization is reducing redundancy and preventing update anomalies (not query speed by itself)