Lesson1

Question 1

What theorem outlines the basics of distributed systems?

Answer 1

CAP Theorem

The CAP Theorem states that it is impossible for a distributed system to simultaneously provide all three of the following guarantees: Consistency, Availability, and Partition Tolerance.

Question 2

What are the components of SQL?

Answer 2

• DDL (Data Definition Language)
• DQL (Data Query Language)
• DML (Data Manipulation Language)
• TCL (Transaction Control Language)
• DCL (Data Control Language)

These components are essential for managing and manipulating relational databases.

Question 3

What security issue can occur due to user input in SQL queries?

Answer 3

SQL Injection

SQL Injection occurs when an attacker is able to manipulate SQL queries by injecting malicious input.

Question 4

What are the common causes of SQL injection?

Answer 4

• User input is not checked
• Complete SQL statements are sent to the server
• Permission problems

These causes highlight the importance of input validation and proper permission management.

Question 5

How can SQL injections be avoided?

Answer 5

• Always validate your input
• Don’t send complete SQL statements to the server
• Use roles with correct permissions

These practices help secure SQL queries against injection attacks.

Question 6

What is a Prepared Statement?

Answer 6

A pre-defined SQL statement with placeholders that is validated or escaped before execution.

Prepared Statements are used to enhance security and performance by preventing SQL injection.

Question 7

What are the advantages of using Prepared Statements?

Answer 7

• Avoids SQL injection
• Improves performance by allowing multiple executions

Prepared statements are compiled once and can be reused, reducing overhead.

Question 8

What SQL command is used to create a Prepared Statement in PostgreSQL?

Answer 8

PREPARE

The PREPARE command allows the creation of a prepared statement with specified parameters.

Question 9

What is the purpose of Stored Procedures?

Answer 9

To execute independent programs that extend SQL’s functionality and improve performance.

Stored Procedures allow for complex operations and reduce communication overhead between client and server.

Question 10

What is the difference between Stored Procedures and Stored Functions?

Answer 10

• Stored Procedures: Independent programs without a return type.
• Stored Functions: Can be used within SQL queries and return a value.

Both are stored in the database but serve different purposes.

Question 11

What is PL/pgSQL?

Answer 11

PostgreSQL’s default procedural language for writing stored procedures and functions.

PL/pgSQL allows for control structures, variables, and loops, enhancing SQL capabilities.

Question 12

How do you declare a variable in PL/pgSQL?

Answer 12

Using the DECLARE section with syntax: variable_name type [DEFAULT value];

Variables must be declared before being used in PL/pgSQL.

Question 13

What is the purpose of the RETURN statement in functions?

Answer 13

To define a function’s result or to terminate the function without returning a value.

RETURN can also be used to yield results in PL/pgSQL.

Question 14

What type of integrity does custom integrity refer to?

Answer 14

Application-dependent, complex constraints based on multiple conditions.

Examples include constraints on exam attempts or salary adjustments.

Question 15

What is the syntax to create a Stored Procedure in PL/pgSQL?

Answer 15

CREATE [ OR REPLACE ] PROCEDURE <functionname>(<parameters>) AS \$\$ <functionbody> \$\$ LANGUAGE plpgsql;</functionbody></parameters></functionname>

The procedure can be executed manually using the CALL keyword.

Question 16

What is the basic loop structure in PL/pgSQL?

Answer 16

LOOP … END LOOP;

This creates an endless loop that must be exited using a condition.

Question 17

What is a trigger in the context of databases?

Answer 17

A database object that automatically executes a specified function in response to certain events.

Triggers can enforce complex integrity rules and automate tasks.

Question 18

What is custom integrity in database management?

Answer 18

Custom integrity refers to specific rules that ensure data validity and consistency based on defined conditions.

Examples include enforcing maximum exam attempts or ensuring prerequisites for degree programs.

Question 19

What is the role of application code in ensuring custom integrity?

Answer 19

Application code is used to enforce custom integrity rules directly within the application logic.

It is important to not trust client-side validation.

Question 20

What are stored procedures and functions used for in custom integrity?

Answer 20

Stored procedures/functions are custom code used to ensure data integrity by executing complex logic on the database side.

An example is verifying bank transfer conditions.

Question 21

What is a trigger in SQL?

Answer 21

A trigger is a stored function that automatically executes in response to certain events on a table.

Supported events include INSERT, UPDATE, and DELETE.

Question 22

What types of events can trigger an action in SQL?

Answer 22

Triggers can respond to the following events:
* INSERT
* UPDATE
* DELETE
* TRUNCATE

TRUNCATE is specific to PostgreSQL and is a fast way to delete all rows.

Question 23

What does the BEFORE trigger event do?

Answer 23

The BEFORE trigger is executed before the event operation to check conditions and potentially abort the operation.

Example: Checking if bank balance is sufficient for a transfer.

Question 24

What does the AFTER trigger event do?

Answer 24

The AFTER trigger is executed after the event operation to perform additional actions, such as updating statistics.

This can improve performance by avoiding complex aggregations.

Question 25

What does the INSTEAD OF trigger do?

Answer 25

The INSTEAD OF trigger replaces the operation logic of the triggering event. ## Footnote Example: Using UPDATE to flag rows as deleted instead of actually deleting them.

Question 26

What SQL command is used to create a trigger in PostgreSQL?

Answer 26

CREATE TRIGGER is used to create a trigger in PostgreSQL. ## Footnote The syntax includes specifying the trigger name, event, table, and the function to execute.

Question 27

What is the significance of the RETURN type in functions used for triggers?

Answer 27

The return type must be TRIGGER, allowing the function to control the outcome of the triggering event. ## Footnote The function can either return null or a row matching the table schema.

Question 28

What are the special variables accessible within trigger functions?

Answer 28

Trigger functions can access: * NEW: new values for inserted/updated columns * OLD: old values before the update * TG_*: meta information about the trigger ## Footnote TG_ARGV4 can be used to access additional parameters.

Question 29

What is an example of a trigger function that enforces upper case values?

Answer 29

A trigger function can convert first and last names to upper case before insertion or update. ## Footnote Example code demonstrates setting NEW.first_name and NEW.last_name to upper case.

Question 30

What happens if a trigger function returns null?

Answer 30

Returning null from a trigger function aborts the current operation (INSERT, DELETE, UPDATE). ## Footnote This allows for custom INSTEAD OF logic.

Question 31

How can exceptions be used in trigger functions?

Answer 31

Exceptions can be raised to abort operations based on specific conditions, such as preventing salary reductions. ## Footnote An example function raises an exception if the new salary is less than the old salary.

Question 32

What is the difference between Query Shipping and Data Shipping?

Answer 32

Query Shipping executes computations on the DBMS, while Data Shipping pulls raw data to the client for processing. ## Footnote This distinction affects performance and resource usage.

Question 33

What impact do triggers have on database performance?

Answer 33

Triggers can impact performance as they add additional code execution for every event. ## Footnote The choice between FOR ROW and FOR STATEMENT affects how often the trigger executes.

Question 34

What is the effect of using the WHEN block in triggers?

Answer 34

The WHEN block activates the trigger only under certain conditions, potentially enhancing performance by skipping unnecessary executions. ## Footnote This can prevent the execution of the function body if the condition is not met.

Question 35

What is a key disadvantage of using stored procedures and triggers?

Answer 35

A key disadvantage is the maintenance of multiple code bases, including client application and in-database logic. ## Footnote This can complicate development and updates.