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, andWindows-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.
- The virtual training environment runs on Apporto launching Modular Cyber Lab via the topology file

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 pressingCtrl + Alt + T.
- Access the terminal via the desktop graphical user interface by selecting Show Apps in the bottom-left corner and searching for
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(standardCtrl + Vdoes 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., frompostgres=#topostgres-#).
- Direct Keyboard Entry: Commands must be manually typed or pasted with care. Interactive terminal pasting requires
PostgreSQL Package Installation on Ubuntu:
- Update local package indices:
bash sudo apt update - Administrative privilege execution prompts for the
studentuser password. Terminal security suppression hides entered characters entirely during input. - Benign repository notices (such as red
Error404 Not Foundmessages) can occur on lab virtual machine networks and do not impede installation.
- Update local package indices:
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
postgresmatching 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).
- PostgreSQL initializes an operating system user named
Superuser Credential Configuration:
- Set the internal administrative password:
text postgres=# \password postgres - Enter password
postgrestwice as prompted. - Security Best Practice Warning: A trivial password such as
postgresorpasswordis strictly limited to isolated lab environments. Production systems must never use trivial passwords, as standard dictionary attacks target default accounts first.
- Set the internal administrative password:
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. WithoutLOGIN, 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 toCREATE ROLE, but enablesLOGINby default.
- Syntax format:
Project Roles Created:
- Music user role:
sql CREATE ROLE music_user NOINHERIT LOGIN PASSWORD 'music123';
- Music user role:
University data handler role:
sql CREATE ROLE uni_handler NOINHERIT LOGIN PASSWORD 'uniData123'; Role Verification:
- Run
\duto inspect role attributes, confirmingNo inheritanceand login access rights.
- Run
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; - 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 ( 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;
- Media catalog database:
University management database:
sql CREATE DATABASE university_data WITH OWNER = uni_handler ENCODING = 'UTF8' CONNECTION LIMIT = -1; Database Inspection & Session Switching:
- Listing Databases:
\llists databases, owners, character sets, and collation providers (pressqto dismiss long lists). - Session Connection:
\c <dbname> <username>transitions active client sessions. Example:\c music postgresor\c university_data uni_handler. - Verifying Active Session:
\conninfooutputs active parameters (database name, user, local Unix socket path/var/run/postgresql, port5432). - Direct Terminal Login:
bash psql university_data uni_handler - Prompts for
uniData123and binds the user directly touniversity_data=>without requiring superuser escalation.
- Listing Databases:
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
- Modern PostgreSQL versions restrict schema modification in
PostgreSQL Data Types & Relational Constraints
Common Primitive Data Types:
integer(orint): 4-byte signed whole number, spanning values from up to approximately ().bigint: 8-byte signed whole number, utilized for identifiers and counts exceeding 4-byte limits (e.g., ).real: 4-byte single-precision floating-point number delivering approximately decimal digits of precision (e.g., ).double precision: 8-byte double-precision floating-point number delivering approximately decimal digits of precision (e.g., ).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 acceptingtrueorfalse(abbreviated in output astorf).
The Six Fundamental Constraints:
- Primary Key (
PRIMARY KEY): - Uniquely identifies each record across a table. Ensures all entries are strictly unique and prohibits
NULLvalues. Enforces an underlying btree index. - Unique (
UNIQUE): - Enforces uniqueness across a target column or column combination. Differs from primary keys by allowing
NULLentries 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) ); - Prevents illogical input, such as negative pricing or out-of-bound percentages.
- Primary Key (
Not Null (
NOT NULL):- Mandates the presence of actual data in a column, rejecting
NULLallocations.
- Mandates the presence of actual data in a column, rejecting
Default (
DEFAULT):- Pre-populates a column with a predefined literal or computed value when omitted in an
INSERTstatement:sql CREATE TABLE customer_info ( customer_id int GENERATED ALWAYS AS IDENTITY, member boolean DEFAULT false, PRIMARY KEY (customer_id) ); - Caveat: A
DEFAULTtriggers only when the column is completely omitted in the insert command. An explicit entry ofNULLoverwrites the default withNULLunless paired with aNOT NULLconstraint.
- Pre-populates a column with a predefined literal or computed value when omitted in an
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);
- A domain functions as an alias over an underlying base data type bundled with a permanent, reusable integrity check:
In this context,
VALUEacts 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) ); Inserting
(1, 'Notebook', 2.49)succeeds, whereas(2, 'Pen', 0)triggers a check constraint violation.
Cardinality & Relational Modeling
Structural Relationship Types:
One-to-One ():
Each row in Table A maps to at most one row in Table B, and vice versa.
Implementation: Defined using a
FOREIGN KEYthat also carries aUNIQUEconstraint.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 IDENTITYhandles auto-increment sequence numbering (), stopping manual assignment.Adding
(5001, 1)topassportsucceeds. Attempting to add a secondary passport(5002, 1)fails immediately due topassport_person_id_keyunique constraint.
One-to-Many ():
A single parent row maps to multiple child rows, but each child points to exactly one parent record.
Implementation: Defined using a standard
FOREIGN KEYon the child table without adding aUNIQUEconstraint.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 ApartandArrow of Godreferencingauthor_id = 1) executes without collision.Many-to-Many ():
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 relationship into two separate relationships.
Example:
studentandsectionlinked viagrade_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 CASCADEis 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

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:
student(Independent parent)course(Independent parent)section(Child referencingcourse)grade_report(Junction referencingstudentandsection)prerequisite(Junction referencingcoursetwice)
Table 1:
student:
CREATE TABLE student (
name text,
student_number int NOT NULL,
class int,
major text,
PRIMARY KEY (student_number)
);
```
* **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_numberis typed astextbecause identifiers contain alphanumeric sequences (e.g.,'CS1310','MATH2410'). Foreign keys that reference it must also usetext.- Table 3:
section:
- Table 3:
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
);
```
- 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 ); - Self-referencing conceptual mapping linking two columns within the same parent relation (
course).
- Table 5:
Schema Alteration & Table Maintenance
Modifying Existing Relations (
ALTER TABLE):- Adding Columns:
sql ALTER TABLE class ADD COLUMN room text; - New columns added to populated tables are set to
NULLby default.
- Adding Columns:
Renaming Columns:
sql ALTER TABLE class RENAME COLUMN room TO room_number; Enforcing Not-Null Constraints:
sql ALTER TABLE student ALTER COLUMN student_number SET NOT NULL; Adding Named Constraints:
sql ALTER TABLE student ADD CONSTRAINT student_number_unique UNIQUE (student_number); - Performance Caveat: Adding a
UNIQUEconstraint 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;.
- Performance Caveat: Adding a
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;
- Temporary demo relations must be dropped in reverse dependency order to prevent foreign key errors:
Check that the active database contains only the five production university tables:
text university_data=> \dt
Questions & Guided Tasks
Q: What is the relationship between the
STUDENTtable and theGRADE_REPORTtable?- Answer: One-to-many (). 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
COURSEtable and thePREREQUISITEtable?- Answer: One-to-many (). 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
NOINHERITmean 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
ENCODINGmean when creating a database, and why is UTF-8 so widely used?- Answer: Encoding dictates the internal character set used to store text.
UTF8is 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.
- Answer: Encoding dictates the internal character set used to store text.
Q: What are the primary keys of the
STUDENTandCOURSEtables?- Answer:
STUDENTusesstudent_number.COURSEusescourse_number.
- Answer:
Q: Identify foreign keys across the university schema and the parent tables they reference.
- Answer:
section(course_number)referencescourse(course_number).grade_report(student_number)referencesstudent(student_number).grade_report(section_identifier)referencessection(section_identifier).prerequisite(course_number)referencescourse(course_number).prerequisite(prerequisite_number)referencescourse(course_number).
Q: How is each row identified in the
GRADE_REPORTtable?- Answer: By a composite primary key combining
student_numberandsection_identifier.
- Answer: By a composite primary key combining
Q: What is the advantage of using a domain rather than writing a
CHECKconstraint 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
coursetable needs to beNOT NULLandUNIQUE?- Answer:
course_number.
- Answer:
Q: In the
grade_reporttable, which two columns must beNOT NULLand why?- Answer:
student_numberandsection_identifier. Both are required because they form the composite primary key, and primary key components can never containNULL.
- Answer:
Q: What other table requires two
NOT NULLcolumns for its primary key?- Answer:
prerequisite, whose composite primary key is formed bycourse_numberandprerequisite_number.
- Answer:
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 NULLconstraint:sql ALTER TABLE section ALTER COLUMN course_number SET NOT NULL;
- Answer: No. Every section must represent an active teaching instance of an approved course. Prevent missing course values by enforcing a
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