Alliance Micro Solutions provides certified and advanced degree computer instructors and consultants

1. Introduction to PostgreSQL

1.1. Overview, key features, and benefits.

2. Installation and Configuration

2.1. Installing PostgreSQL on Windows/ Mac.
2.2. Using package managers for Linux/Mac.
2.3. Install and configure PostgreSQL tools (pgAdmin, and psql).
2.4. Verify installation (connect using psql and run basic command).
2.5. Modify postgresql.conf essential settings (memory, logging, and connection).
2.6. Modify Settings directly or use ALTER SYSTEM.
2.7. File Locations: typical file paths for different OS (Linux, Windows, macOS (SHOW config_file, or SHOW hba_file).
2.8. Adjust Connection Settings for remote access: listen_addresses parameter, Network Security Considerations.
2.9. Client Authentication Configuration: pg_hba.conf structure and syntax, and supported authentication methods (Trust, md5, and peer).
2.10. Lab:

Q2.10.1. Install postgreSQL on your device.
Q2.10.2. Start the PostgreSQL service.
Q2.10.3. Install and configure postgreSQL tools (pgAdmin, and psql).
Q2.10.4. Connect to postgreSQL using psql and run basic commands.
Q2.10.5. Locate configuration files (postgresql.conf, and pg_hba.conf).
Q2.10.6. View the file paths for configuration on Windows, Linux, and macOS.
Q2.10.7. Modify configuration settings (ALTER SYSTEM and SHOW commands in psql).
Q2.10.8. Adjust connection settings for remote access (listen_addresses in postgresql.conf).
Q2.10.9. Edit pg_hba.conf to allow a new user to connect using md5 authentication.
Q2.10.10. Add a new user, assign password and verify their access using psql.
Q2.10.11. Test different authentication methods (Trust, md5, and peer) by configuring pg_hba.conf.

3. Data Types and Table Basics

3.1. Numeric Types (Integer Types(int, bigint, and smallint), decimal types (decimal, numeric, float), and serial type (serial, and bigserial)).
3.2. Character types (char, varchar, and text).
3.3. Date/Time types (DATE, TIME, TIMESTAMP, interval, common operations, and time zone handling).
3.4. Boolean type (TRUE, FALSE, and NULL).
3.5. Arrays (definition and syntax).
3.6. Custom data types.
3.7. JSON data type.
3.8. Using PostgreSQL for vector storage.
3.9. Creating tables (CREATE TABLE), modifying tables (ALTER TABLE), and dropping tables (DROP TABLE and CASCADE).
3.10. Lab:

Q3.10.1. Create a table with different data types.
Q3.10.2. Modify a table to add and remove columns.
Q3.10.3. Practice creating custom types and using arrays.
Q3.10.4. Apply constraints (e.g.,CHECK constraints on numerical ranges or UNIQUE constraints).
Q3.10.5. Inserting and querying data using different data types.

4. SQL Fundamentals

4.1. SELECT statement essentials (SELECT syntax, using DISTINCT to remove duplicates, and using AS to rename columns or tables).
4.2. WHERE clause and filtering (basic filtering with WHERE).
4.3. Basic filtering using WHERE.
4.4. Comparison operators (=, !=, >, <, >=, <=).
4.5. Logical operators (AND, OR, NOT).
4.6. Pattern matching with LIKE.
4.7. Range filtering with BETWEEN.
4.8. NULL handling.
4.9. JOIN operations (INNER JOIN, and LEFT JOIN).
4.10. Basic aggregations (COUNT, SUM, AVG, MIN,  and MAX).
4.11. Grouping results with GROUP BY and HAVING clause.
4.12. Data modification (INSERT, UPDATE, and DELETE Operations).
4.13. Lab:

Q4.13.1. Write basic SELECT statements.
Q4.13.2. Apply filtering with WHERE clause.
Q4.13.3. Perform join operations.
Q4.13.4. Practice aggregation functions (COUNT, SUM, AVG, MIN, and MAX).
Q4.13.5. Group data using GROUP BY and apply conditions with HAVING.
Q4.13.6. Perform data modification using INSERT, UPDATE, and DELETE.

5. Database Design and Management

5.1. Database design principles.
5.2. Database design concepts.
5.3. Normalization task (1NF, 2NF, and 3NF).
5.4. Normalization example.
5.5. Trade off of denormalization.
5.6. Primary key selection for ensuring row uniqueness.
5.7. Foreign key implementation for maintaining referential integrity (examples of foreign key constraints are ON DELETE CASCADE and ON UPDATE CASCADE).
5.8. Table relationships (one-to-one, one-to-many, and many-to-many).
5.9. Constraint types and usage (NOT NULL, UNIQUE, CHECK, DEFAULT, Primary key, and foreign key).
5.10. Schema organization (What is a schema and benefits of schema organization (logical grouping, security and access control, better organization in large systems, and easier maintenance).
5.11. Lab:

Q5.11.1. Normalization task.
Q5.11.2. Create a simple database schema (create customers and orders tables, create tables for products system).
Q5.11.3. Implement foreign key constraints (use ON DELETE CASCADE and ON UPDATE CASCADE for referential integrity in the schema).
Q5.11.4. Apply constraints (such as NOT NULL, UNIQUE, DEFAULT, and CHECK).

6. Indexing and Performance

6.1. Indexing fundamental (B-Tree indexes (definition, structure, search mechanism, and why use B-trees) and other index types (HASH, JIN, BRIN, and GiST)).
6.2. When to create indexes (frequent use in WHERE, JOIN, ORDER BY, GROUP BY, high cardinality, covering indexes, composite indexes, and large tables).
6.3. When not to create indexes (small tables, Frequent writes, columns with low cardinality, columns with sparse usage)
6.4. Query performance analysis (introduction).
6.5. EXPLAIN and its output components (node type, relation, filter, cost, rows, and width).
6.6. Explain ANALYZE.
6.7. Identifying common query issues (sequential scans on large tables, missing or inefficient indexes, and cost estimation).
6.8. Query optimization techniques (efficient query design, index optimization, reducing query complexity, and autovacuum process).
6.9. Monitoring and troubleshooting query performance (using EXPLAIN and LOGS, real-time monitoring tools, and fixing performance issues).
6.10. Lab: Index creation and analysis

Q6.10.1. Create indexes.
Q6.10.2. Query optimization.
Q6.10.3. Performance tuning.
Q6.10.4. Autovacuum and index maintenance.

7. Transaction Management

7.1. Transaction Basics (ACID): ACID properties, and real world examples of Transactions.
7.2. Transaction life-cycle and control (Life cycle and Transaction control using BEGIN, COMMIT, ROLLBACK, and SAVEPOINT).
7.3. common errors in transaction management.
7.4. Transaction isolation levels (introduction, levels of isolation, trade-offs in isolation levels, and example scenarios).
7.5. Handling concurrent access (concurrency control, locks, optimistic vs. pessimistic concurrency control, and serializable transactions).
7.6. Deadlock prevention and resolution (deadlock basics, deadlock detection, and deadlock prevention strategies).
7.7. Lab:

Q7.7.1. Simulating basic transactions.
Q7.7.2. Experimenting with isolation levels.
Q7.7.3. Handling deadlocks.
Q7.7.4. Advanced scenarios.

8. Security Implementation

8.1 User and role management:

8.1.1. User and role concepts.
8.1.2. Creating, altering, and dropping users and roles.
8.1.3. Managing role membership.

8.2. GRANT and REVOKE operations.
8.3. Role of GRANT and REVOKE:

8.3.1. GRANT.
8.3.2. REVOKE.
8.3.3. Permission types.

8.4. Syntax for granting and revoking privileges.
8.5. Checking current privileges.
8.6. Auditing privileges.
8.7. Principle of least privilege.
8.8. Schema permissions (schema-level permissions, restricting access to sensitive data, and managing access to schema objects).
8.9. Object privileges (object types, privileges, GRANT, and REVOKE for object-level access).
8.10. Password policies (password complexity requirements, password expiration policies, and user authentication methods).
8.11. Basic backup and recovery (importance of Backups, Backup types (Logical vs. Physical), tools for automating backups, and restoring from backups).
8.12. Lab:

Q8.12.1. Create and manage PostgreSQL users and roles.
Q8.12.2. Set up schemas and configure access controls.
Q8.13.3. Back up and recovery practice.
Q8.13.4. Auditing privileges.
Q8.13.5. Role inheritance and permissions.