1/25
Flashcards covering database types, Docker setup, PostgreSQL commands, schema design, normalization, joins, indexing, procedural logic, and transaction controls.
What are the primary structural differences between Relational and NoSQL databases?
Relational databases organize data into tables with rows and columns and use SQL (e.g., MySQL, PostgreSQL, Oracle). NoSQL databases are designed for handling unstructured, semi-structured, or rapidly changing data (e.g., MongoDB, Cassandra, Redis).
What PowerShell command serves as the 'nuclear option' to clean up Docker Desktop directory data if reinstallation fails?
Remove-Item -Path "C:\ProgramData\DockerDesktop" -Recurse -Force
How are Docker Hub, Docker Images, and Docker Containers defined?
Docker Hub is a registry similar to GitHub for Docker Images; Docker Images are bundled snapshots and instructions used to create containers; and a Docker Container is a running instance of a Docker Image.
What are the five sublanguages of SQL and an example command for each?
What are the three levels of the Three-Schema Approach in database design?
How are entities, relationships, attributes, and keys visually represented in an Entity Relationship Diagram (ERD)?
Entities are represented as rectangles, relationships as diamonds, attributes as ovals, and keys as underlined attributes within an oval.
What are the core requirements for First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF)?
1NF requires attribute domains to contain only atomic (indivisible) values and single values per tuple. 2NF requires every non-key column to be fully dependent on the primary key. 3NF requires every non-key column to not be transitively dependent on the primary key.
What are the four ACID properties of database transactions?
How do OLTP and OLAP systems differ in architecture and use cases?
OLTP (Online Transaction Processing) is used for APIs and transactional systems with highly normalized tables. OLAP (Online Analytical Processing) uses fewer, wider, and typically denormalized tables optimized for analytics.
How does a Star Schema differ from a Snowflake Schema in data warehousing?
A Star Schema places one central fact table surrounded directly by simple dimension tables. A Snowflake Schema normalizes dimension tables further into sub-dimensional tables.
Why should NUMERIC/DECIMAL be used for financial data in PostgreSQL instead of REAL or DOUBLE PRECISION?
NUMERIC and DECIMAL provide exact numeric precision required for monetary calculations, whereas REAL and DOUBLE PRECISION are floating-point types subject to rounding errors.
Which extension and function are used by default in modern PostgreSQL to automatically generate UUID primary keys?
The extension "pgcrypto" using the DEFAULT gen_random_uuid() function.
What are the four regular expression matching operators in PostgreSQL?
~ (case-sensitive match), ~* (case-insensitive match), !~ (case-sensitive non-match), and !~* (case-insensitive non-match).
What is the purpose of the RETURNING clause in PostgreSQL DML statements?
It allows INSERT, UPDATE, or DELETE statements to directly return modified or generated values (such as newly generated IDs or timestamps) without requiring a separate SELECT query.
How is an UPSERT constructed in PostgreSQL?
By appending ON CONFLICT (column_name) DO UPDATE SET column = EXCLUDED.column to an INSERT statement, where EXCLUDED refers to the record that caused the conflict.
What are the main functional differences between PostgreSQL UDFs (User-Defined Functions) and Stored Procedures?
Functions MUST return a value (or void) and CANNOT perform Transaction Control Language (TCL) operations. Stored Procedures have optional return values, are executed with CALL, and CAN perform TCL operations.
What is the difference between an INNER JOIN and a CROSS JOIN?
An INNER JOIN returns combined rows that satisfy an explicit join condition. A CROSS JOIN combines every row from the left table with every row from the right table without a match filter, producing a Cartesian product.
What default index type is created in PostgreSQL and which operations is it suited for?
B-Tree index. It is suitable for =,
What do the PostgreSQL JSONB operators @>, <@, and && evaluate?
@> tests if the left JSONB value contains the right structure; <@ tests if the left JSONB value is contained within the right structure; && tests if there is any overlap between the two structures.
When should a Generalized Inverted Index (GIN) be used instead of a B-Tree index in PostgreSQL?
GIN indexes should be used when querying composite or multi-valued data types that B-Trees cannot handle effectively, such as JSONB, array data types (e.g., TEXT[]), and full-text search.
What is a Partial Index in PostgreSQL?
An index built over a subset of a table specified by a WHERE clause (e.g., CREATE INDEX idx_users_active ON users (created_at) WHERE is_active = True;).
What is the functional and performance difference between UNION and UNION ALL?
UNION combines result sets and eliminates duplicate rows. UNION ALL combines result sets while retaining all duplicates, making UNION ALL faster because it avoids duplicate removal.
What is the fundamental difference between JOINS and UNIONS?
JOINS combine columns from different tables side-by-side to make the result set wider. UNIONS combine rows from queries with matching structures to stack them vertically, making the result set longer.
What two components are required to implement a database trigger in PostgreSQL?
How does Multi-Version Concurrency Control (MVCC) work during row updates in PostgreSQL?
When a row is updated, PostgreSQL creates a new version of the row rather than overwriting it. It tracks row visibility for concurrent transactions using hidden system fields (xmin and xmax) and transaction snapshots.
What is the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?
EXPLAIN displays the execution plan and estimated costs without running the query. EXPLAIN ANALYZE actually executes the query and returns real execution times and actual row counts.