Introduction

I’m not familiar with SQL but don’t want to spend time learning it. So let’s learn through practice.

Transactions

A transaction is the basic unit of concurrency control. A transaction is a sequence of operations that must either all be executed or none at all; it’s an indivisible work unit. For example, in bank transfers: debiting one account and crediting another account are two operations that must either both be executed or neither. Therefore, they should be considered as one transaction. Transactions are the unit by which databases maintain data consistency, ensuring data consistency at the end of each transaction.

In simple terms, a transaction is an atomic operation that cannot be divided.

Views

A view is a virtual table that doesn’t actually exist. It’s just a partial table extracted from the database to make it convenient for certain people to see. Views are defined by select queries—when you create a view, you’re actually executing a select statement in the database.

INSTEAD OF Triggers

• Definition of Triggers Triggers are actually a special type of stored procedure. The main function of triggers is to monitor user modifications to data. If a trigger is built for a data table, then when the data table is modified (insert, update, or delete), SQL Server automatically executes the trigger to take appropriate measures for the data modification, thereby ensuring data integrity and security.

INSTEAD OF triggers specify that the trigger is executed instead of the triggering SQL statement, thus replacing the operation of the triggering statement.

Stored Procedures

A stored procedure is a group of SQL statements in a database designed to accomplish a specific function. It’s stored in the database and doesn’t need to be recompiled when called again after the first compilation. Users execute it by specifying the name of the stored procedure and providing parameters (if the stored procedure has parameters). Stored procedures are important objects in databases.

SQL Relational Algebra

Five Basic Operations:

Union (∪), Difference (-), Cartesian Product (×), Projection (σ), Selection (π)

Four Combined Operations:

Intersection (∩), Join (equijoin), Natural Join, Division (÷)

Union includes records that are in both tables. Difference includes records that are in the first table but not in the second. Cartesian product is all one-to-one combinations of records from both tables. Projection is vertical selection, while selection is horizontal selection. Intersection is similar to union, and join connects through common attributes. Division is a complex operation.

Relational Schema

A relational schema is a description of a relation. As the real world changes over time, the relation in a relational schema will also change. However, many existing facts in the real world dictate that all possible relations in a relational schema must satisfy certain integrity constraints. These constraints are either expressed through limitations on the value range of attributes, such as employee age less than 65 (mandatory retirement at 65), or through the interconnection between attribute values (mainly reflected in whether values are equal). The relational schema should capture these integrity constraints.

Non-relational Databases

NoSQL (NoSQL = Not Only SQL) means “not just SQL.” It’s used for storing ultra-large-scale data.