Comprehensive Notes on Database Theory II
Comparison of WHERE and HAVING Clauses
In SQL, both the WHERE and HAVING clauses are utilized for filtering data, yet they function at different levels of the query execution. The WHERE clause is designed for the restriction of individual rows before any grouping occurs. Conversely, the HAVING clause is specifically used to restrict groups of data by evaluating aggregate functions after the grouping process has been completed.
Transaction Isolation and Persistence Mechanisms
A database transaction is considered isolated when it meets specific criteria regarding its visibility and state. A transaction should not be permanent until it is formally committed, and its intermediate states or results must remain invisible to other concurrent transactions until that confirmation occurs. This state of isolation is achieved through two primary mechanisms: cache-based mechanisms and non-cache-based mechanisms.
Differences Between Views and Procedures
A View is defined as a virtual table that provides a specific "view into the data." Its primary functions are to restrict access to sensitive or unnecessary data and to simplify the execution of complex queries for the user. A Procedure, on the other hand, consists of a set of operations that can be stored within the database and reused later. Procedures promote code reusability and enhance the overall efficiency of database operations.
DML Operations on Views and Tables with Primary Keys
It is possible to perform Data Manipulation Language (DML) operations—such as INSERT, UPDATE, and DELETE—on a view or a table that possesses a primary key, provided the view is a simple one. The existence of a primary key is crucial because it allows the system to identify the precise row that needs to be modified or deleted during the DML operation.
Column-Base Database Characteristics
Column-base (columnar) databases are optimized for specific performance metrics. They offer superior query performance because data can be accessed directly via columns, and they exhibit high levels of scalability. While they facilitate faster data reading, they tend to have slower data entry (insertion) speeds due to the complexity of their structure compared to row-based systems.
The EXISTS Operator and Logical Returns
The EXISTS operator is a logical operator used to determine the presence of rows within a subquery. It returns a value of TRUE if the subquery returns one or more rows. Conversely, if the subquery returns no rows, the EXISTS operator returns FALSE.
Shared and Exclusive Locking Mechanisms
Database locking is a procedure used to control concurrent access to data. A Shared Lock (or Read Lock) allows a transaction to read data but prohibits it from performing updates. This lock is "sharable," meaning multiple transactions can hold shared locks simultaneously. In contrast, an Exclusive Lock (or Write Lock) allows the owner to both read and update data, while other transactions must wait until the lock is released. If an Exclusive Lock is already held on a data item, no other transaction can obtain any type of lock on that data until it is freed.
The CAP Theorem in Shared-Data Systems
The CAP theorem applies to networked shared-data systems and asserts that it is impossible for such a system to simultaneously provide more than two of the following three guarantees: Consistency, High Availability, and Tolerance to network partitions.
Aggregate Functions and Selection Operators in SQL
Aggregate functions, such as , cannot be placed within a WHERE clause; they must be utilized within a HAVING clause. For example, is an aggregate function that returns the total number of rows in a requested table. Expressions like are invalid and result in a syntax error because returns a single scalar value, making it illogical to count it. Regarding syntax, the selection operator is implemented via the WHERE clause, while the projection operator is implemented via the SELECT clause.
Modifying Data Types in Existing Tables
It is possible to change the data type of a column after a table has been created and populated with data. However, careful consideration must be given to data conversion compatibility. For instance, converting an type to is generally permissible, but the inverse—converting to —may not be possible if the existing data contains non-numeric characters.
SQL Joins and Cartesian Products
In SQL, the expression FROM racuni, kupci results in a cross join (Cartesian product) between the two tables. More specific join types include: Equijoin, which connects tables based on equality (usually using the symbol ); Non-equijoin, which connects based on inequality using operators like or BETWEEN; and Self-join, where a table is joined with itself. In a self-join, it is possible for a Foreign Key field to be defined as NOT NULL, as the self-referential nature is independent of the nullability constraint.
Outer Join Variations
Outer joins are utilized to display rows that do not necessarily satisfy the join condition. A Left Outer Join returns all rows from the left table and matched values from the right. A Right Outer Join returns all rows from the right table and matched values from the left. A Full Outer Join returns all rows where there is a match in either table; if no match exists, NULL values are returned for the columns of the missing side.
The UNION Operator and Set Operations
The UNION operator combines the results of two SELECT statements into a single result set without duplicates. For a UNION to be valid, three conditions must be met: the queries must have the same number of columns, the columns must be in the same order, and the data types of corresponding columns must be identical. Other set operators include EXCEPT, which returns data present in the left table but not the right, and INTERSECT, which returns only the data common to both tables.
Primary and Foreign Key Management
While the CREATE TABLE command is used to build new tables, it is not the only way to manage constraints. A Primary Key can be added to an existing table using the syntax: \text{ALTER TABLE } \text{table_name } \text{ADD CONSTRAINT } \text{identifier } \text{PRIMARY KEY } (\text{column_name}). The column must contain unique values and no nulls. A Foreign Key can also be added via . During this process, existing records will have a NULL value for the new foreign key until a manual update is performed. The syntax is: \text{ALTER TABLE } \text{racuni } \text{ADD CONSTRAINT } \text{fk_kupci } \text{FOREIGN KEY } (\text{kupac_id}) \text{ REFERENCES } \text{kupci}(\text{sifra}).
Comparison of Procedures, Functions, and Triggers
Procedures and Triggers both support code reusability and can perform DML operations. However, a Trigger is executed automatically in response to an event, whereas a Procedure must be explicitly called via the EXEC command. Procedures can return values, while Triggers cannot. When comparing Procedures and Functions, both accept parameters and promote reusability, but Functions must return a value (scalar or table), whereas Procedures are primarily designed for executing DML operations and may or may not return a value.
ACID and BASE Approaches to Transactions
Transactions are units of work that must be completed fully or not at all. The ACID model is suitable for systems requiring high transactional integrity and consists of: Atomicity (all or nothing), Consistency (the database remains valid), Isolation (transactions do not interfere with one another), and Durability (data is saved permanently). The BASE model is suited for high scalability and availability, consisting of: Basically Available (flexible access), Soft state (data is not immediately stable), and Eventual consistency (data synchronizes over time).
Transaction Lifecycle and the Thomas Write Rule
A transaction typically begins automatically with the first database access within a session or after a COMMIT command. It can end due to a system crash, an unforeseen error, or explicit termination, which results in the cancellation of its results. The Thomas Write Rule is a specific protocol stating that if a transaction attempts to write an old value that has already been "overwritten" by a newer transaction, that old value is ignored rather than aborting the transaction entirely.
Simple vs. Complex Views
Simple views derive data from a single table, do not contain functions or grouping, and allow for DML operations. Complex views derive data from multiple tables, contain functions or groups, and generally do not permit DML operations.
Ordinary and Correlated Subqueries
A subquery is a SELECT statement nested within another. It can be placed in WHERE, HAVING, GROUP BY, FROM, SELECT, and ORDER BY clauses. An Ordinary Subquery can run independently of the outer query; it executes once and passes its result to the outer query. A Correlated Subquery depends on the outer query and executes for every row processed by the main query. The steps for a correlated subquery are: 1) Select a row from the main query, 2) Execute the subquery for that row, 3) Check if the condition is met, 4) Include the row in the result if the condition is TRUE, and 5) Repeat for every row.
Role Management and Deadlocks
A ROLE is a collection of zero or more privileges assigned to users who require the same level of access. Deadlocks occur when two or more transactions are stuck waiting for locks held by one another, creating a cycle of dependency that prevents further progress.
Multi-Level Database Architecture and NoSQL Models
Database definition occurs at three levels: the External level (how the user sees the data), the Conceptual level (the logical structure of the entire database), and the Internal level (physical storage in memory). Beyond relational models, Graph Databases store data as nodes and relationships, excelling at finding connections. Key-Value Databases offer high scalability and simple structures but are limited in query complexity (typically only two columns) and are less suited for complex relationships.
Hierarchical Key Definitions and Integrity Constraints
A Super Key is a set of one or more attributes that uniquely identifies a row. A Candidate Key is a minimal super key with no redundant attributes. The Primary Key is the candidate key chosen as the main identifier, while Unique/Alternate Keys are candidate keys not chosen as the primary key. Integrity constraints ensure data accuracy: Entity Integrity dictates the primary key cannot be NULL, and Referential Integrity ensures a foreign key points to an existing row in a related table.
SQL Language Subsets
SQL is divided into three main parts: 1) DDL (Data Definition Language) for defining structures via CREATE, ALTER, and DROP; 2) DML (Data Manipulation Language) for managing data via INSERT, UPDATE, SELECT, and DELETE; and 3) DCL (Data Control Language) for managing access rights and database security.