Setup Project 2

SQL Database Environment Setup

Install the PostgreSQL relational database engine, manage database service states, configure secure user roles and databases, install DBeaver GUI, and load a complete retail database schema inside a Linux VM.

Environment
PostgreSQL / DBeaver / Linux
Difficulty
Beginner (1/5)
Course Module
SQL for Data Science
Deliverables
Retail DB Connection & SQL Test Query
1. System Architecture & Process Workflow

The diagram below represents the local SQL environment data flow. DBeaver functions as the graphical desktop frontend, sending SQL scripts over a local socket connection (TCP port 5432) to the PostgreSQL server daemon, which compiles the commands and updates relational tables stored on the VM system partition.

DBeaver GUI Client SQL Console Editor Connection: retail_db User: student localhost:5432 TCP Connection PostgreSQL Service SQL Parser & Planner Database: retail_db Owner: student Write Disk PG_DATA /var/lib/pg
2. Part 1: Step-by-Step Action Items & Key Execution Steps
STEP 1

Install PostgreSQL Database Packages

Use the apt package utility to install the database engine server alongside additional contributing extension libraries.

$ sudo apt update && sudo apt install -y postgresql postgresql-contrib
This command downloads PostgreSQL and configures local UNIX users and permissions inside the Ubuntu operating system shell.
STEP 2

Enable and Start the PostgreSQL Background Daemon

Register the PostgreSQL package as an systemd startup background service and initialize execution.

$ sudo systemctl start postgresql && sudo systemctl enable postgresql
This system service manager instruction boots the engine daemon and configures Linux to launch the SQL backend server during system start.
STEP 3

Verify PostgreSQL Service Operational Health

Check the system status logs to verify that the database process runs actively without warnings.

$ sudo systemctl status postgresql
This command prints active thread info. You should see a green "active (running)" flag; press **Q** on your keyboard to exit the log view.
STEP 4

Enter postgres Administrative CLI Environment

Switch your terminal system user context to the default admin user and launch the PostgreSQL command shell utility.

$ sudo -i -u postgres psql
This flags bash to open an interactive administrative session directly inside the database utility terminal prompt (indicated by `postgres=#`).
STEP 5

Create the Course database and Student User Role

Execute SQL statements inside the terminal to create our retail database and grant access privileges to the student role.

postgres=# CREATE DATABASE retail_db; postgres=# CREATE USER student WITH PASSWORD 'db_pass_123'; postgres=# GRANT ALL PRIVILEGES ON DATABASE retail_db TO student; postgres=# \q postgres=# exit
This creates the database container `retail_db`, creates the login credential role `student`, grants permissions, and exits back to your normal user account.
STEP 6

Install DBeaver GUI Database Client via Snap

Install the universal database client GUI package inside Ubuntu to manage databases visually.

$ sudo snap install dbeaver-ce
This downloads and registers the stable DBeaver community edition, adding desktop launcher configurations.
STEP 7

Create Database Connection inside DBeaver Client

Launch DBeaver and link the interface to your local PostgreSQL server database instance.

Click App Grid -> Search "dbeaver" -> Click DBeaver Icon -> Click plug icon (New Connection) -> Select PostgreSQL -> Click Next
This wizard path creates a local data link config sheet, pointing DBeaver to the PostgreSQL database drivers.
STEP 8

Enter Connection Credentials and Load Driver Files

Input Host, Database name, and credentials, then test and download driver libraries.

Set Database: retail_db -> Set Username: student -> Set Password: db_pass_123 -> Click "Test Connection" -> Click "Download" Drivers -> Click "Finish"
This GUI step downloads the required driver files and tests the PostgreSQL connection, linking DBeaver to `retail_db` successfully.
STEP 9

Open SQL Editor Console in DBeaver

Navigate the database connection tree in the sidebar and open a fresh database SQL console editor.

Right-click "retail_db" in Database Navigator -> Select "SQL Editor" -> Click "Open SQL Console" (or press F3)
This action opens a blank file interface linked directly to our database connection, ready to accept SQL query scripts.
STEP 10

Load Table Schemas and Populate Data

Paste the retail database SQL script template into the console and execute it.

Copy SQL script from Part 2 below -> Paste into DBeaver Console -> Click orange "Execute SQL Script" button (Alt + X)
This instruction creates the `customers`, `products`, and `orders` tables, inserting sample records into our local database.
STEP 11

Verify Database Tables and Records

Execute an aggregation query to verify relationships and confirm calculations work.

Clear console -> Enter SELECT aggregation query -> Press Alt + Enter (Execute Statement)
This statement queries relational columns, aggregating transaction logs into customer totals to confirm foreign key structures.
3. Operational Pipeline Architecture

The flowchart below outlines the database initialization pipeline. It traces the steps from database software installation, user role setup, GUI client installation, and database connection to schema load and verification query execution.

1. Install DB Install postgresql via apt utility apt install 2. DB Setup Create DB and user student role psql admin CLI 3. Install GUI Install DBeaver-CE universal client snap install 4. SQL Schema Execute SQL script defining tables DBeaver Editor 5. Verification Execute SELECT aggregation test GROUP BY / SUM
4. Part 2: Complete Deliverable Assets & Production Templates

To establish relational tables containing realistic sample business data, we will draft a database schema script. Below is a step-by-step SQL code breakdown followed by the final combined script.

Step-by-Step Code Construction

Lines 1 - 10

Define Customers Table Schema

Configure the customer storage table containing unique identifiers and demographics.

CREATE TABLE IF NOT EXISTS customers ( customer_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, city VARCHAR(50) );
This statement creates the table `customers`. `SERIAL` handles primary key index increments, and `UNIQUE` forces uniqueness constraints on email records.
Lines 11 - 18

Define Products Table Schema

Configure the inventory product catalog with pricing decimal structures.

CREATE TABLE IF NOT EXISTS products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category VARCHAR(50), price DECIMAL(10, 2) NOT NULL CHECK (price >= 0) );
This creates the catalog table `products`. `DECIMAL(10,2)` reserves standard floating-point precision for financial prices, and `CHECK` guarantees non-negative values.
Lines 19 - 29

Define Transaction Orders Table Schema

Establish transactional tables that link customers and products using constraints.

CREATE TABLE IF NOT EXISTS orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE, product_id INT REFERENCES products(product_id) ON DELETE RESTRICT, order_date DATE DEFAULT CURRENT_DATE, quantity INT NOT NULL CHECK (quantity > 0), total_amount DECIMAL(12, 2) NOT NULL );
This registers the transactional table `orders`. `REFERENCES` sets foreign key relationships, `ON DELETE CASCADE` removes transactions if users delete matching accounts, and `ON DELETE RESTRICT` protects inventory catalog associations.
Lines 30 - 45

Populate Sample Data Rows

Insert placeholder rows inside tables to simulate a mock business workspace.

INSERT INTO customers (first_name, last_name, email, city) VALUES ('John', 'Doe', 'john.doe@email.com', 'New York'), ('Jane', 'Smith', 'jane.smith@email.com', 'San Francisco'), ('Bob', 'Johnson', 'bob.johnson@email.com', 'Chicago'); INSERT INTO products (product_name, category, price) VALUES ('Notebook Computer', 'Electronics', 1200.00), ('Wireless Mouse', 'Electronics', 25.00), ('Mechanical Keyboard', 'Electronics', 89.99); INSERT INTO orders (customer_id, product_id, quantity, total_amount) VALUES (1, 1, 1, 1200.00), (1, 2, 2, 50.00), (2, 3, 1, 89.99), (3, 2, 1, 25.00);
These commands write initial mock profiles, stock entries, and sales purchase tickets into the respective database tables.

Combined SQL Schema Script

Save the consolidated blocks above as ~/setup_retail_db.sql, open it in DBeaver, and run the commands to configure your database:

-- setup_retail_db.sql - Database Schema and Sample Data Script CREATE TABLE IF NOT EXISTS customers ( customer_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, city VARCHAR(50) ); CREATE TABLE IF NOT EXISTS products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category VARCHAR(50), price DECIMAL(10, 2) NOT NULL CHECK (price >= 0) ); CREATE TABLE IF NOT EXISTS orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE, product_id INT REFERENCES products(product_id) ON DELETE RESTRICT, order_date DATE DEFAULT CURRENT_DATE, quantity INT NOT NULL CHECK (quantity > 0), total_amount DECIMAL(12, 2) NOT NULL ); -- Clear existing records to ensure idempotency TRUNCATE TABLE orders, products, customers RESTART IDENTITY CASCADE; INSERT INTO customers (first_name, last_name, email, city) VALUES ('John', 'Doe', 'john.doe@email.com', 'New York'), ('Jane', 'Smith', 'jane.smith@email.com', 'San Francisco'), ('Bob', 'Johnson', 'bob.johnson@email.com', 'Chicago'); INSERT INTO products (product_name, category, price) VALUES ('Notebook Computer', 'Electronics', 1200.00), ('Wireless Mouse', 'Electronics', 25.00), ('Mechanical Keyboard', 'Electronics', 89.99); INSERT INTO orders (customer_id, product_id, quantity, total_amount) VALUES (1, 1, 1, 1200.00), (1, 2, 2, 50.00), (2, 3, 1, 89.99), (3, 2, 1, 25.00);

Verification SELECT Query

Copy and run the following query script to aggregate transaction sales logs and verify database structures:

sql# SELECT c.first_name || ' ' || c.last_name AS customer_name, COUNT(o.order_id) AS total_orders, SUM(o.total_amount) AS total_spent FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name ORDER BY total_spent DESC;
This query merges customer accounts and transactions via a `LEFT JOIN`, groups records, calculates totals using `SUM`, and sorts columns to verify index integrity.
5. Deliverables Summary

Verify that the following configurations and outputs exist inside your project workspace.

Created Files / Templates

  • ~/setup_retail_db.sql - Structured SQL schema blueprint script.

Verification Artifacts / Execution Proof

  • Active connection settings in DBeaver Database Navigator sidebar.
  • Output values from the aggregation SELECT query.
  • Database tables visible under the schema namespace in the navigator tree.
6. Closing Explanation: Why We Did This & What It Accomplishes

Architectural Intent & Operational Impact

Why We Did This

What This Accomplishes

Data Science & AI/ML Training Program | Setup Project 2 | SQL Database Environment Setup