Fundamentals of Database Systems

Fundamentals of Database Systems: Core Concepts Explained

Introduction

Database systems are the backbone of modern applications, from web services to enterprise software. Understanding the fundamental concepts—transaction properties, data types, referential integrity, query processing, and entity‑relationship modeling—provides a solid foundation for designing robust, scalable, and secure databases. This course expands on the key ideas tested in a typical quiz, offering detailed explanations, practical examples, and best‑practice tips that are both educational and SEO‑friendly.

1. Transaction Properties: The ACID Model

Database transactions must satisfy four essential properties, collectively known as ACID:

• Atomicity: All operations in a transaction succeed or none do.
• Consistency: The database moves from one valid state to another, preserving all defined rules.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction commits, its effects survive system crashes or power failures.

When a system crash occurs, the Durability property guarantees that committed changes are permanently stored, typically via write‑ahead logs and checkpoint mechanisms. Understanding durability helps developers design recovery strategies and choose appropriate storage engines (e.g., InnoDB for MySQL).

2. Storing Unicode Characters in SQL Server

Unicode support is crucial for multilingual applications. In Microsoft SQL Server, the NVARCHAR data type stores Unicode characters, allowing you to handle languages such as Vietnamese, Chinese, or Arabic without data loss. Unlike VARCHAR, which uses a single‑byte code page, NVARCHAR uses two bytes per character (UTF‑16), ensuring accurate representation of international text.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Address NVARCHAR(200)   -- Supports Vietnamese characters
);

When designing schemas, always prefer NVARCHAR (or NCHAR) for columns that may contain non‑ASCII characters.

3. Referential Integrity and Foreign Key Actions

Foreign keys enforce relationships between tables, preserving data consistency. SQL provides several referential actions that dictate what happens when a referenced row is deleted or updated:

• ON DELETE RESTRICT: Prevents deletion of a parent row if child rows exist.
• ON DELETE CASCADE: Automatically deletes child rows when the parent row is removed.
• ON DELETE SET NULL: Sets the foreign key column in child rows to NULL when the parent row is deleted.
• ON DELETE NO ACTION: Similar to RESTRICT but defers the check until the end of the statement.

The ON DELETE SET NULL action is especially useful for optional relationships where the child record can exist without a parent.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
        ON DELETE SET NULL
);

4. Query Execution Order: The Role of the WHERE Clause

SQL query processing follows a logical order that differs from the written syntax. After the FROM clause determines the source tables and joins, the WHERE clause is evaluated next to filter rows before any grouping or aggregation occurs.

Understanding this order helps you write efficient queries and avoid logical errors. For instance, placing a condition that references an alias created in the SELECT list will cause an error because the alias is not yet defined when WHERE runs.

Logical Query Processing Order:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

5. Entity‑Relationship (ER) Modeling: Representing Weak Entities

ER diagrams visualize database structure. A weak entity cannot be uniquely identified by its own attributes alone; it relies on a relationship with an owner (strong) entity. In diagrammatic notation, a weak entity is depicted with a double rectangle and its identifying relationship with a double diamond. Additionally, a double line connects the weak entity to its owner, indicating a total participation constraint.

This visual cue signals that the weak entity’s primary key includes the primary key of its owner, ensuring referential integrity.

Example: Consider an OrderItem entity that depends on Order. The OrderItem primary key might be (OrderID, ItemNumber), where OrderID comes from the owning Order entity.

6. Modifying Table Structure: Adding a DATE Column

Altering an existing table to add a new column uses the ALTER TABLE statement. The correct syntax for adding a DATE column named NgaySinh (Vietnamese for "Date of Birth") to the NHANVIEN table is:

ALTER TABLE NHANVIEN ADD NgaySinh DATE;

Other options like UPDATE or INSERT modify data, not schema, while MODIFY is not a standard SQL keyword for adding columns.

7. Candidate Keys and Primary Keys

In a relation schema (A, B, C) where A is designated as the primary key, A alone uniquely identifies each tuple. Therefore, the set {A} is a candidate key. A candidate key is any minimal set of attributes that can serve as a primary key. Since A already satisfies uniqueness and minimality, no additional attributes are needed.

Understanding candidate keys is essential for normalization, ensuring that each table has a clear, non‑redundant identifier.

8. Selecting Distinct Rows

When a query may return duplicate rows, the DISTINCT keyword eliminates redundancy, returning only unique combinations of the selected columns. This is different from GROUP BY, which aggregates rows, and from HAVING, which filters aggregated results.

Example:

SELECT DISTINCT Country FROM Customers;

The above query lists each country represented in the Customers table exactly once.

9. Putting It All Together: A Mini‑Project

To reinforce the concepts, create a small employee management database that incorporates the topics covered:

1. Define tables with appropriate data types:

   CREATE TABLE Departments (
           DeptID INT PRIMARY KEY,
           DeptName NVARCHAR(100)
       );
   
       CREATE TABLE Employees (
           EmpID INT PRIMARY KEY,
           FirstName NVARCHAR(50),
           LastName NVARCHAR(50),
           BirthDate DATE,
           DeptID INT,
           CONSTRAINT FK_Employees_Departments FOREIGN KEY (DeptID)
               REFERENCES Departments(DeptID)
               ON DELETE SET NULL
       );
   

2. Insert sample data using Unicode characters:

   INSERT INTO Departments VALUES (1, N'Kỹ thuật'), (2, N'Nhân sự');
   INSERT INTO Employees VALUES (101, N'Nguyễn', N'Văn A', '1990-05-12', 1);
   INSERT INTO Employees VALUES (102, N'李', N'小明', '1985-11-23', 2);
   

3. Run a query that demonstrates WHERE and DISTINCT:

   SELECT DISTINCT DeptName FROM Employees e
   JOIN Departments d ON e.DeptID = d.DeptID
   WHERE e.BirthDate > '1988-01-01';
   

4. Test transaction durability:

   BEGIN TRANSACTION;
   UPDATE Employees SET DeptID = NULL WHERE EmpID = 101;
   COMMIT;  -- After this point, the change persists even if the server crashes.
   

By completing this mini‑project, you will have applied data types, foreign key actions, query ordering, and transaction concepts in a realistic scenario.

Conclusion

Mastering the fundamentals of database systems—ACID properties, Unicode handling, referential integrity, query execution order, ER modeling, schema alteration, candidate keys, and distinct selection—equips you to design efficient, reliable, and internationalized databases. These concepts are not only essential for academic exams but also for real‑world development, where data integrity and performance are paramount.

Further Reading and Resources

• SQL Server Transaction Log Architecture
• Unicode Character Sets in MySQL
• Understanding Foreign Key Constraints
• ER Diagram Basics