PostgreSQL Architecture, Schema Design, and Relational Constraints

PostgreSQL Environment & System Architecture

  • Apporto Virtual Lab Infrastructure:
    • The virtual training environment runs on Apporto launching Modular Cyber Lab via the topology file untitled.gns3.
    • The network environment integrates nodes including Ubuntu26.04.1-1, Switch1, NAT1, Windows10-1, and Windows-11-1.
    • Default virtual machine operating system credentials:
    • Username: student
    • Password: student
    • Starting the virtual environment requires selecting the node, choosing Start, and selecting Console.

GNS3 Network Topology in Apporto Environment

  • Accessing the Command Line Interface:

    • Access the terminal via the desktop graphical user interface by selecting Show Apps in the bottom-left corner and searching for Terminal, or by pressing Ctrl + Alt + T.
  • Input Conventions and Text Formatting Precautions:

    • Direct Keyboard Entry: Commands must be manually typed or pasted with care. Interactive terminal pasting requires Ctrl + Shift + V (standard Ctrl + V does not execute paste within the Ubuntu terminal).
    • Quotation Marks: Ensure all quotes are straight ASCII single quotes (') rather than typographic curly quotes (‘ or ’). Typographic curly quotes cause immediate syntax parsing errors in PostgreSQL.
    • Command Terminators:
    • psql Meta-commands: Commands initiated with a backslash (such as \password, \du, \dt, \conninfo) operate within the psql terminal client and do not take a semicolon.
    • SQL Statements: Data Definition Language (DDL) and Data Manipulation Language (DML) statements must terminate with a semicolon (;). Multi-line input continues until a semicolon is received, shifting the psql prompt from = to - (e.g., from postgres=# to postgres-#).
  • PostgreSQL Package Installation on Ubuntu:

    • Update local package indices: bash sudo apt update     
    • Administrative privilege execution prompts for the student user password. Terminal security suppression hides entered characters entirely during input.
    • Benign repository notices (such as red Err or 404 Not Found messages) can occur on lab virtual machine networks and do not impede installation.
  • Package installation: bash sudo apt install -y postgresql     

  • Installs the PostgreSQL server daemon, default database templates, and the interactive command-line interface tool psql.

User, Role, and Authentication Configuration

  • Initial Superuser Access:

    • PostgreSQL initializes an operating system user named postgres matching the internal database administrator superuser: bash sudo -u postgres psql     
    • Bypasses password validation locally via operating system peer authentication, resulting in the interactive prompt postgres=# (the = signifies database connection, while # denotes superuser privileges).
  • Superuser Credential Configuration:

    • Set the internal administrative password: text postgres=# \password postgres     
    • Enter password postgres twice as prompted.
    • Security Best Practice Warning: A trivial password such as postgres or password is strictly limited to isolated lab environments. Production systems must never use trivial passwords, as standard dictionary attacks target default accounts first.
  • Role Management Architecture:

    • In PostgreSQL, user accounts are formalized as roles. Roles can own database objects, hold privileges, and be permitted or denied direct login access.
    • Operating continuously as the superuser violates security compartmentalization. Operational roles must be created with restricted privileges.
  • Role Creation Parameters and Syntax:

    • Syntax format: sql CREATE ROLE role_name NOINHERIT LOGIN PASSWORD 'secret_password';     
    • Parameter Definitions:
    • CREATE ROLE <name>: Names the role. Role identifiers are case-insensitive and converted to lowercase internally; snake_case naming is standard.
    • NOINHERIT: Dictates that the role does not automatically inherit rights from group roles it may be assigned to; rights must be granted directly.
    • LOGIN: Bestows interactive authentication privileges. Without LOGIN, a role functions only as a security group.
    • PASSWORD '<secret>': Assigns an encrypted authentication token wrapped inside single quotes.
    • Alternative Syntax: CREATE USER <name> WITH PASSWORD '<secret>'; is functionally equivalent to CREATE ROLE, but enables LOGIN by default.
  • Project Roles Created:

    • Music user role: sql CREATE ROLE music_user NOINHERIT LOGIN PASSWORD 'music123'; &nbsp;&nbsp;&nbsp;&nbsp;
  • University data handler role: sql CREATE ROLE uni_handler NOINHERIT LOGIN PASSWORD 'uniData123'; &nbsp;&nbsp;&nbsp;&nbsp;

  • Role Verification:

    • Run \du to inspect role attributes, confirming No inheritance and login access rights.

Database Initialization & Session Management

  • Database Creation Specifications:

    • A single PostgreSQL instance manages multiple isolated databases, each administered by an assigned owner role.
    • Creation syntax: sql CREATE DATABASE database_name WITH OWNER = role_name ENCODING = 'UTF8' CONNECTION LIMIT = -1; &nbsp;&nbsp;&nbsp;&nbsp;
    • Parameter Analysis:
    • OWNER = <role>: Designates the role with full administrative oversight over all underlying relations.
    • ENCODING = 'UTF8': Establishes variable-width character encoding capable of handling universal multilingual text safely (e.g., diacritics in names like "Björk" or "Beyoncé").
    • CONNECTION LIMIT = -1: Defines maximum simultaneous client connections (−1-1 signifies unlimited concurrent access, which is the system default).
  • Databases Created:

    • Media catalog database: sql CREATE DATABASE music WITH OWNER = music_user ENCODING = 'UTF8' CONNECTION LIMIT = -1; &nbsp;&nbsp;&nbsp;&nbsp;
  • University management database: sql CREATE DATABASE university_data WITH OWNER = uni_handler ENCODING = 'UTF8' CONNECTION LIMIT = -1; &nbsp;&nbsp;&nbsp;&nbsp;

  • Database Inspection & Session Switching:

    • Listing Databases: \l lists databases, owners, character sets, and collation providers (press q to dismiss long lists).
    • Session Connection: \c <dbname> <username> transitions active client sessions. Example: \c music postgres or \c university_data uni_handler.
    • Verifying Active Session: \conninfo outputs active parameters (database name, user, local Unix socket path /var/run/postgresql, port 5432).
    • Direct Terminal Login: bash psql university_data uni_handler &nbsp;&nbsp;&nbsp;&nbsp;
    • Prompts for uniData123 and binds the user directly to university_data=> without requiring superuser escalation.
  • Schema Security Permissions:

    • Modern PostgreSQL versions restrict schema modification in public. When initializing tables under a newly provisioned role, grant public schema rights as superuser: sql \c university_data postgres GRANT ALL ON SCHEMA public TO uni_handler; \c university_data uni_handler &nbsp;&nbsp;&nbsp;&nbsp;

PostgreSQL Data Types & Relational Constraints

  • Common Primitive Data Types:

    • integer (or int): 4-byte signed whole number, spanning values from −2,147,483,648-2{,}147{,}483{,}648 up to approximately 2.1×1092.1 \times 10^9 (2,147,483,6472{,}147{,}483{,}647).
    • bigint: 8-byte signed whole number, utilized for identifiers and counts exceeding 4-byte limits (e.g., 9,000,000,0009{,}000{,}000{,}000).
    • real: 4-byte single-precision floating-point number delivering approximately 66 decimal digits of precision (e.g., 9.999.99).
    • double precision: 8-byte double-precision floating-point number delivering approximately 1515 decimal digits of precision (e.g., 3.141592653589793.14159265358979).
    • text: Variable-length character string without arbitrary size bounds (e.g., 'Abbey Road').
    • date: Calendar date formatted according to ISO 8601 standard as 'YYYY-MM-DD' (e.g., '1969-09-26').
    • boolean: 1-byte logical state accepting true or false (abbreviated in output as t or f).
  • The Six Fundamental Constraints:

    • Primary Key (PRIMARY KEY):
    • Uniquely identifies each record across a table. Ensures all entries are strictly unique and prohibits NULL values. Enforces an underlying btree index.
    • Unique (UNIQUE):
    • Enforces uniqueness across a target column or column combination. Differs from primary keys by allowing NULL entries and permitting multiple unique declarations per relation.
    • Foreign Key (FOREIGN KEY ... REFERENCES):
    • Implements referential integrity by establishing dependencies between a child column and a parent table's primary or unique key.
    • Enforces order dependency: parent tables must exist before dependent foreign keys can be declared; parent records must exist before dependent child values can be inserted.
    • Check (CHECK):
    • Evaluates data against an explicit logical predicate prior to storage. If the expression evaluates to false, the mutation aborts.
    • Example syntax: sql CREATE TABLE transactions ( transaction_id int GENERATED ALWAYS AS IDENTITY, product_id int, price real CHECK (price > 0.0), PRIMARY KEY (transaction_id) ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    • Prevents illogical input, such as negative pricing or out-of-bound percentages.
  • Not Null (NOT NULL):

    • Mandates the presence of actual data in a column, rejecting NULL allocations.
  • Default (DEFAULT):

    • Pre-populates a column with a predefined literal or computed value when omitted in an INSERT statement: sql CREATE TABLE customer_info ( customer_id int GENERATED ALWAYS AS IDENTITY, member boolean DEFAULT false, PRIMARY KEY (customer_id) ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    • Caveat: A DEFAULT triggers only when the column is completely omitted in the insert command. An explicit entry of NULL overwrites the default with NULL unless paired with a NOT NULL constraint.
  • User-Defined Domains (CREATE DOMAIN):

    • A domain functions as an alias over an underlying base data type bundled with a permanent, reusable integrity check: sql CREATE DOMAIN positive_price AS real CHECK (VALUE > 0); &nbsp;&nbsp;&nbsp;&nbsp;
  • In this context, VALUE acts as the placeholder variable for data evaluated against the domain.

  • Advantages:

    • Eliminates redundant code across schemas.
    • Enforces uniform data validation rules.
    • Modifications to business logic occur in a single location rather than requiring edits across dozens of tables.
  • Domains are listed using \dD.

  • Domain usage example: sql CREATE TABLE product ( product_id int NOT NULL, product_name text, price positive_price, PRIMARY KEY (product_id) ); &nbsp;&nbsp;&nbsp;&nbsp;

  • Inserting (1, 'Notebook', 2.49) succeeds, whereas (2, 'Pen', 0) triggers a check constraint violation.

Cardinality & Relational Modeling

  • Structural Relationship Types:

    • One-to-One (1:11:1):

    • Each row in Table A maps to at most one row in Table B, and vice versa.

    • Implementation: Defined using a FOREIGN KEY that also carries a UNIQUE constraint.

    • Example Implementation (Person & Passport): ```sql CREATE TABLE person ( person_id int GENERATED ALWAYS AS IDENTITY, person_name text, PRIMARY KEY (person_id) );

      CREATE TABLE passport ( passport_id int NOT NULL, person_id int UNIQUE, PRIMARY KEY (passport_id), FOREIGN KEY (person_id) REFERENCES person (person_id) ON UPDATE CASCADE ON DELETE CASCADE );       ```

    • GENERATED ALWAYS AS IDENTITY handles auto-increment sequence numbering (1,2,3,…1, 2, 3, \dots), stopping manual assignment.

    • Adding (5001, 1) to passport succeeds. Attempting to add a secondary passport (5002, 1) fails immediately due to passport_person_id_key unique constraint.

  • One-to-Many (1:N1:N):

    • A single parent row maps to multiple child rows, but each child points to exactly one parent record.

    • Implementation: Defined using a standard FOREIGN KEY on the child table without adding a UNIQUE constraint.

    • Example Implementation (Author & Book): ```sql CREATE TABLE author ( author_id int GENERATED ALWAYS AS IDENTITY, author_name text, PRIMARY KEY (author_id) );

      CREATE TABLE book ( book_id int NOT NULL, book_name text, author_id int, PRIMARY KEY (book_id), FOREIGN KEY (author_id) REFERENCES author (author_id) ON UPDATE CASCADE ON DELETE CASCADE );       ```

    • Inserting multiple books for a single author (Things Fall Apart and Arrow of God referencing author_id = 1) executes without collision.

    • Many-to-Many (M:NM:N):

    • Multiple rows in Table A map to multiple rows in Table B.

    • Implementation: Cannot be joined directly with a single foreign key. Requires an intermediate junction (linking) table carrying two distinct foreign keys, turning an M:NM:N relationship into two separate 1:N1:N relationships.

    • Example: student and section linked via grade_report.

  • Referential Cascade Actions:

    • ON UPDATE CASCADE: Automatically updates dependent foreign key references in child tables when the parent primary key changes.
    • ON DELETE CASCADE: Automatically deletes child records when their parent record is dropped.
    • Usage Distinction: ON DELETE CASCADE is essential for dependent data that has no meaning without its parent (e.g., student grades or course prerequisites). It should be omitted when automatic deletion would wipe out important operational history (e.g., deleting a class should not delete enrolled students, nor should dropping a course delete active teaching sections).

University Database Schema Implementation

University Database Relational Tables

  • Table Dependency & Execution Sequence:

    • Tables must be declared in strict sequential order so that referenced primary keys exist before the dependent foreign keys are defined:
    1. student (Independent parent)
    2. course (Independent parent)
    3. section (Child referencing course)
    4. grade_report (Junction referencing student and section)
    5. prerequisite (Junction referencing course twice)
  • Table 1: student:

  CREATE TABLE student (
    name text,
    student_number int NOT NULL,
    class int,
    major text,
    PRIMARY KEY (student_number)
  );
&nbsp;&nbsp;```

* **Table 2: `course`**:

sql CREATE TABLE course ( course_name text, course_number text NOT NULL, credit_hours int, department text, PRIMARY KEY (course_number) );   ```

  • Type Consistency Note: course_number is typed as text because identifiers contain alphanumeric sequences (e.g., 'CS1310', 'MATH2410'). Foreign keys that reference it must also use text.

    • Table 3: section:
  CREATE TABLE section (
    section_identifier int NOT NULL,
    course_number text NOT NULL,
    semester text,
    year int,
    instructor text,
    PRIMARY KEY (section_identifier),
    FOREIGN KEY (course_number) REFERENCES course (course_number)
    ON UPDATE CASCADE
  );
&nbsp;&nbsp;```
  - Uses `ON UPDATE CASCADE` without delete cascading to prevent accidental loss of section records if a course entry is modified.

* **Table 4: `grade_report`**:

sql CREATE TABLE grade_report ( student_number int NOT NULL, section_identifier int NOT NULL, grade text, PRIMARY KEY (student_number, section_identifier), FOREIGN KEY (student_number) REFERENCES student (student_number) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (section_identifier) REFERENCES section (section_identifier) ON UPDATE CASCADE ON DELETE CASCADE );   ```

  • Implements a composite primary key (student_number, section_identifier).

    • Table 5: prerequisite: sql CREATE TABLE prerequisite ( course_number text NOT NULL, prerequisite_number text NOT NULL, PRIMARY KEY (course_number, prerequisite_number), FOREIGN KEY (course_number) REFERENCES course (course_number) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (prerequisite_number) REFERENCES course (course_number) ON UPDATE CASCADE ON DELETE CASCADE ); &nbsp;&nbsp;
    • Self-referencing conceptual mapping linking two columns within the same parent relation (course).

Schema Alteration & Table Maintenance

  • Modifying Existing Relations (ALTER TABLE):

    • Adding Columns: sql ALTER TABLE class ADD COLUMN room text; &nbsp;&nbsp;&nbsp;&nbsp;
    • New columns added to populated tables are set to NULL by default.
  • Renaming Columns: sql ALTER TABLE class RENAME COLUMN room TO room_number; &nbsp;&nbsp;&nbsp;&nbsp;

  • Enforcing Not-Null Constraints: sql ALTER TABLE student ALTER COLUMN student_number SET NOT NULL; &nbsp;&nbsp;&nbsp;&nbsp;

  • Adding Named Constraints: sql ALTER TABLE student ADD CONSTRAINT student_number_unique UNIQUE (student_number); &nbsp;&nbsp;&nbsp;&nbsp;

    • Performance Caveat: Adding a UNIQUE constraint to an established primary key column creates an identical redundant btree index in PostgreSQL. While useful for syntax practice, it is omitted in production.
    • Explicitly naming constraints makes error messages easier to read and allows simple removal later via ALTER TABLE tablename DROP CONSTRAINT constraint_name;.
  • Practice Teardown & Schema Hygiene:

    • Temporary demo relations must be dropped in reverse dependency order to prevent foreign key errors: sql DROP TABLE passport; DROP TABLE person; DROP TABLE book; DROP TABLE author; DROP TABLE transactions; DROP TABLE customer_info; DROP TABLE product; DROP DOMAIN positive_price; &nbsp;&nbsp;&nbsp;&nbsp;
  • Check that the active database contains only the five production university tables: text university_data=> \dt &nbsp;&nbsp;&nbsp;&nbsp;

Questions & Guided Tasks

  • Q: What is the relationship between the STUDENT table and the GRADE_REPORT table?

    • Answer: One-to-many (1:N1:N). A single student can have multiple grade reports across semesters, but each grade report entry belongs to exactly one student.
  • Q: What is the relationship between the COURSE table and the PREREQUISITE table?

    • Answer: One-to-many (1:N1:N). A single course can be listed multiple times within the prerequisite directory (either requiring multiple prerequisites or acting as a prerequisite for multiple other courses).
  • Q: What does the keyword NOINHERIT mean when creating a new role?

    • Answer: It prevents the role from automatically inheriting permissions from any group roles it belongs to. Privileges must be granted directly to the role.
  • Q: What does ENCODING mean when creating a database, and why is UTF-8 so widely used?

    • Answer: Encoding dictates the internal character set used to store text. UTF8 is the global standard because it can encode characters from almost every written language, ensuring international names, symbols, and diacritics are stored accurately without data loss.
  • Q: What are the primary keys of the STUDENT and COURSE tables?

    • Answer: STUDENT uses student_number. COURSE uses course_number.
  • Q: Identify foreign keys across the university schema and the parent tables they reference.

    • Answer:
    • section(course_number) references course(course_number).
    • grade_report(student_number) references student(student_number).
    • grade_report(section_identifier) references section(section_identifier).
    • prerequisite(course_number) references course(course_number).
    • prerequisite(prerequisite_number) references course(course_number).
  • Q: How is each row identified in the GRADE_REPORT table?

    • Answer: By a composite primary key combining student_number and section_identifier.
  • Q: What is the advantage of using a domain rather than writing a CHECK constraint in each table?

    • Answer: Centralized definition and maintainability. A domain is created once and reused across many tables. If validation rules change, updating the domain updates the check across all matching columns immediately.
  • Q: Which column in the course table needs to be NOT NULL and UNIQUE?

    • Answer: course_number.
  • Q: In the grade_report table, which two columns must be NOT NULL and why?

    • Answer: student_number and section_identifier. Both are required because they form the composite primary key, and primary key components can never contain NULL.
  • Q: What other table requires two NOT NULL columns for its primary key?

    • Answer: prerequisite, whose composite primary key is formed by course_number and prerequisite_number.
  • Q: Should a section be allowed to exist without an associated course?

    • Answer: No. Every section must represent an active teaching instance of an approved course. Prevent missing course values by enforcing a NOT NULL constraint: sql ALTER TABLE section ALTER COLUMN course_number SET NOT NULL; &nbsp;&nbsp;&nbsp;&nbsp;

Diagnostics, Recovery, and Troubleshooting

  • Catch-Up Scripts & Recovery Procedures:

    • When previous labs are incomplete, automated recovery scripts configure the database environment up to the required starting point:
    • Week1_Lab2_catchup.txt: Installs configuration files, creates database users, and applies authentication rules from scratch.
    • Week1_Lab3_catchup.txt: Sets up all five base university tables and constraints.
    • Week2_Lab1_catchup.txt: Verifies table structures and populates data for subsequent labs.
    • Execution Method: Open the file in the virtual machine, copy its contents, and paste directly into the Linux terminal prompt using Ctrl + Shift + V (do not run inside psql).
  • Comprehensive Troubleshooting Matrix:

    • **`password authentication failed for user