PostgreSQL for Data Analysts: The Basic Concept for Deep Learning

byKHMERSIDE -
PostgreSQL for Data Analysts The Basic Concept for Deep Learning


PostgreSQL is an open-source, cross-platform, and feature-rich database system that can handle large and complex data sets with high performance and reliability. In this blog post, I will introduce you to some basic concepts related to PostgreSQL.

{getToc} $title={Table of Contents}

Relational Database Management System (RDBMS):

A Relational Database Management System (RDBMS) is a type of database management system that stores data in tables and establishes relationships between these tables. PostgreSQL is an example of an RDBMS.

Let's create a simple example to illustrate the concept of tables in PostgreSQL:

Example Scenario:
Suppose we are managing information about books in a library. We want to store details such as the book title, author, publication year, and ISBN.

1. Creating a Table:

We'll create a table named books to store this information. Each column in the table will represent a specific piece of information about a book. 
    
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    publication_year INTEGER,
    isbn VARCHAR(13)
);
  • The book_id column is the primary key, which uniquely identifies each book in the table.
  • title, author, publication_year, and isbn are columns representing different attributes of a book.
2. Inserting Data:

Now, let's insert some sample data into the books table:
    
INSERT INTO books (title, author, publication_year, isbn)
VALUES
          ('The Great Gatsby', 'F. Scott Fitzgerald', 1925,
      '9780743273565'),
          ('To Kill a Mockingbird', 'Harper Lee', 1960, '0061120081'),
    ('1984', 'George Orwell', 1949, '9780451524935');


3. Querying Data:

You can retrieve information from the books table using SQL queries. For example: 

SELECT * FROM books;

The result would be something like:

 
 
 book_id title author publication_year isbn 
 
 1 The Great Gatsby F. Scott Fitzgerald 1925 9780743273565 
 
 2 To Kill a Mockingbird Harper Lee 1960 0061120081 
 
 3 1984 George Orwell 1949 9780451524935

This is a simplified example, but it illustrates how PostgreSQL, as an RDBMS, allows you to organize and manage data in a structured way using tables and relationships.

Tables:

In PostgreSQL, data is organized into tables, which are collections of rows and columns. Each column has a specific data type, and each row represents a record.

Data Types:

PostgreSQL supports a wide range of data types, including integers, text, varchar, date, timestamp, and more. Understanding data types is crucial for designing efficient databases.
Understanding PostgreSQL data types is crucial for data analysts. Some common data types include:
  • INTEGER:
CREATE TABLE example (id INTEGER, name VARCHAR(50));
  • TEXT:
CREATE TABLE example (id INTEGER, description TEXT);
  • DATE:
CREATE TABLE example (id INTEGER, event_date DATE);
  • TIMESTAMP:
CREATE TABLE example (id INTEGER, last_updated TIMESTAMP);

Choosing the right data type ensures data accuracy and efficiency.

SQL (Structured Query Language):

SQL is the language used to interact with PostgreSQL databases. It includes commands like 

SELECT, INSERT, UPDATE, DELETE for querying and manipulating data.

Primary Key:

A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures data integrity and is used as a reference in relationships between tables.

In our example, the book_id column serves as the primary key. A primary key uniquely identifies each record in the table. It ensures that there are no duplicate records and provides a way to reference each book uniquely.

Foreign Key:

A foreign key is a column that establishes a link between data in two tables. It creates a relationship between tables, enforcing referential integrity.

Suppose we want to extend our database to include information about borrowers. We can create another table named borrowers with its own primary key (borrower_id). We can establish a relationship between the books and borrowers tables using a foreign key. For example: 
    
CREATE TABLE borrowers (
    borrower_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    book_borrowed_id INTEGER REFERENCES books(book_id)
);

In this example, the book_borrowed_id column in the borrowers table is a foreign key that references the book_id column in the books table. This establishes a relationship between the two tables, indicating that the book_borrowed_id must correspond to a valid book_id in the books table.

Indexes:

Indexes are structures that improve the speed of data retrieval operations on a database table. They are created on columns to quickly locate and access rows.

It's used to speed up data retrieval operations. In our books table, we might want to create an index on the title column for faster searches: 

CREATE INDEX idx_title ON books(title);

Normalization:

Normalization is the process of organizing data in a database to reduce redundancy and dependency. This helps in minimizing data anomalies and maintaining data integrity.

It is the process of organizing data to reduce redundancy and improve data integrity. In our example, we might decide to split the authors into a separate table: 
    
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(50)
);

-- Update the books table to reference the author_id

ALTER TABLE books
ADD COLUMN author_id INTEGER REFERENCES authors(author_id);

This reduces redundancy by storing author names in a single location (the authors table) and references them by their unique author_id in the books table.

Transactions and ACID Properties:

A transaction is a sequence of one or more SQL statements that are executed as a single unit. Transactions ensure the consistency and integrity of a database.
PostgreSQL follows ACID properties (Atomicity, Consistency, Isolation, Durability) to guarantee the reliability of database transactions.

Suppose a user wants to borrow a book. The operation involves updating both the books table (marking the book as borrowed) and the borrowers table (recording the borrower's information). This can be wrapped in a transaction to ensure that both updates succeed or fail together: 
    
BEGIN;

UPDATE books SET status = 'Borrowed' WHERE book_id = 1;
      INSERT INTO borrowers (name, book_borrowed_id) VALUES ('John Doe', 1);
    
COMMIT;

This ensures that either both the book status update and borrower information insertion succeed, or both fail. This adherence to ACID properties ensures data consistency and integrity.

These additional concepts expand upon the basics, demonstrating how PostgreSQL can handle more complex scenarios and relationships in a database.

Views:

A view is a virtual table based on the result of a SELECT query. It allows you to present data in a structured way without physically storing the data. And provide a way to represent complex queries or joins as a virtual table. For example, we can create a view that combines information from the books and borrowers tables: 
    
CREATE VIEW book_borrowers AS
SELECT books.title, borrowers.name
FROM books
JOIN borrowers ON books.book_id = borrowers.book_borrowed_id;

Now, querying this view provides a simplified way to see which books are currently borrowed and by whom: 

SELECT * FROM book_borrowers;

This encapsulation of complex queries into views can simplify data retrieval for analysts.

Triggers:

Triggers are sets of instructions that are automatically executed (or "triggered") in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE. The actions that automatically respond to events in the database. For instance, we can create a trigger to update a log table whenever a book is borrowed: 
    
CREATE TABLE borrow_log (
    log_id SERIAL PRIMARY KEY,
    book_id INTEGER,
    borrower_name VARCHAR(100),
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);    

CREATE OR REPLACE FUNCTION log_borrow()

RETURNS TRIGGER AS $$

BEGIN
    INSERT INTO borrow_log (book_id, borrower_name)
          VALUES (NEW.book_id, (SELECT name FROM borrowers WHERE
      book_borrowed_id = NEW.book_id));
    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER on_borrow

AFTER INSERT ON borrowers

FOR EACH ROW

EXECUTE FUNCTION log_borrow();

Now, every time a book is borrowed, and a new record is inserted into the borrowers table, the log_borrow trigger adds an entry to the borrow_log table.

Stored Procedures and Functions:

These are precompiled SQL queries that can be stored and executed on the database. Functions can return values, while procedures perform actions without returning a value.

Stored procedures and functions are sets of SQL statements that can be stored and executed on the database. They can accept parameters and return values. For example, let's create a function to calculate the average publication year of all books: 

    
CREATE OR REPLACE FUNCTION calculate_average_publication_year()
RETURNS INTEGER AS $$
DECLARE
    total_years INTEGER;
BEGIN
          SELECT AVG(publication_year) INTO total_years FROM books;
    
    RETURN total_years;

END;

$$ LANGUAGE plpgsql;

Now, you can call this function to get the average publication year: 

SELECT calculate_average_publication_year();

These concepts further enhance the capabilities of PostgreSQL, making it a powerful tool for managing and analyzing data. As a data analyst, understanding these features allows you to design efficient databases, perform complex queries, and automate tasks within the database system.

Security:

PostgreSQL provides user and role management for access control. Understanding how to set up and manage users, roles, and permissions is essential for database security. PostgreSQL provides robust security features for user authentication and access control. As a data analyst, you should be aware of managing users, roles, and permissions:
  • Creating Users:
CREATE USER analyst_user WITH PASSWORD 'secure_password';
  • Granting Permissions:
GRANT SELECT ON TABLE books TO analyst_user;
  • Roles:
CREATE ROLE librarian;
  • Assigning Roles:
GRANT librarian TO analyst_user;

These commands illustrate creating a user, granting specific permissions (e.g., SELECT on the books table), and creating a role (librarian) that can be assigned to users.

Managing Database Transactions:

Transactions ensure the integrity and consistency of data. For instance: 

BEGIN;

UPDATE books SET status = 'Borrowed' WHERE book_id = 1;

    INSERT INTO borrowers (name, book_borrowed_id) VALUES ('Jane Doe', 1);

COMMIT;

If any part of the transaction fails, the entire transaction is rolled back, preserving the consistency of the database.

Backup and Restore:

Regularly backing up your PostgreSQL database is essential for data recovery and maintenance:
  • Backup:
pg_dump -U username -h localhost -d dbname > backup.sql
  • Restore:
psql -U username -h localhost -d dbname < backup.sql

These commands demonstrate how to backup and restore a PostgreSQL database, ensuring data safety.

Conclusion:

As a data analyst, mastering these PostgreSQL concepts provides a solid foundation for managing, querying, and analyzing data efficiently. Whether designing a database schema, writing complex queries, or ensuring data security, a comprehensive understanding of these concepts is essential for effective PostgreSQL usage.

Tags:

You might like

View all
Previous Post Next Post
Share to other apps
Copy Post Link