RDBMS: Relational Design, SQL, Normalization and Transactions

1. DBMS and RDBMS Basics

A Database Management System (DBMS) is software that stores, organizes, retrieves, updates, and protects data. An RDBMS is a DBMS that implements the relational model, where data is represented through relations, commonly displayed as tables.

In a college database, student details, courses, departments, and enrolments can be stored separately and connected using keys. This avoids repeating the same facts in every row.

Important relational terms

2. Three-Level Database Architecture

The ANSI/SPARC three-schema architecture separates what users see, the logical database design, and physical storage details. This separation supports data independence.

• Logical data independence: changes to the conceptual schema should require minimal changes to external views.
• Physical data independence: changes to storage structures or indexes should not require changes to the logical schema or application queries.

3. Keys, Constraints, and NULL

Keys identify rows and define relationships. Constraints enforce rules that help maintain accurate and consistent data.

Types of keys

• Super key: one or more attributes that uniquely identify a row; it may contain unnecessary attributes.
• Candidate key: a minimal super key; no attribute can be removed without losing uniqueness.
• Primary key: the candidate key selected to identify rows. It must be unique and not NULL.
• Alternate key: a candidate key not chosen as the primary key.
• Composite key: a key made from two or more columns.
• Foreign key: one or more columns that reference a candidate key, usually a primary or unique key, in another table.

Constraints

CREATE TABLE Department (
    DepartmentID INTEGER PRIMARY KEY,
    DepartmentName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    DepartmentID INTEGER,
    CONSTRAINT fk_student_department
        FOREIGN KEY (DepartmentID)
        REFERENCES Department(DepartmentID)
);

In this example, StudentID is the primary key. The DepartmentID foreign key can contain NULL because it was not declared NOT NULL; otherwise, any non-NULL value must reference an existing department.

NULL is not zero or an empty string

NULL represents an unknown, missing, or inapplicable value. Use IS NULL or IS NOT NULL in SQL, not = NULL.

SELECT StudentName
FROM Student
WHERE DepartmentID IS NULL;

4. SQL Categories and Queries

SQL stands for Structured Query Language. SQL standards and DBMS products differ in some syntax and behavior, but the following categories are widely used for teaching and discussion.

DELETE, TRUNCATE, and DROP

SELECT, grouping, and filtering

SELECT DepartmentID, COUNT(*) AS TotalStudents
FROM Student
WHERE DepartmentID IS NOT NULL
GROUP BY DepartmentID
HAVING COUNT(*) >= 10
ORDER BY TotalStudents DESC;

This query finds departments with at least ten students and orders the result from the largest count to the smallest.

Logical processing order

Although SELECT appears first in written SQL, a simplified logical evaluation order is:

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → OFFSET / FETCH or LIMIT

• WHERE: filters individual rows before grouping.
• GROUP BY: creates groups for aggregate functions.
• HAVING: filters groups after aggregation.
• ORDER BY: sorts the final result.

Use parameters for user-supplied values

Do not build SQL by joining raw user input into a query string. Use the parameter-binding mechanism provided by the database driver or framework.

SELECT StudentName, DepartmentID
FROM Student
WHERE StudentID = ?;

The placeholder syntax differs between database libraries. The important rule is that the value is bound separately from the SQL text, helping prevent SQL injection and quoting mistakes.

5. Transactions and ACID Properties

A transaction is a logical unit of work. For example, a bank transfer should either debit one account and credit the other successfully, or leave both balances unchanged.

BEGIN;

UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 101;

UPDATE Account
SET Balance = Balance + 500
WHERE AccountID = 202;

COMMIT;

Exact transaction syntax and automatic-commit behavior vary by DBMS and client library. In real systems, also verify that the debit succeeds and that the account has sufficient funds.

6. Normalization and Functional Dependencies

Normalization is a design process that places each fact in an appropriate table. Its purpose is to reduce unnecessary duplication and avoid insertion, update, and deletion anomalies without making a design unnecessarily complex.

Common anomalies

• Insertion anomaly: a fact cannot be recorded without also recording an unrelated fact.
• Update anomaly: one fact is repeated in many rows and must be changed repeatedly.
• Deletion anomaly: deleting one row unintentionally removes information about another entity.

Functional dependency

A functional dependency X → Y means that a value of X determines one value of Y. For example, if each DepartmentID has one DepartmentName, then:

DepartmentID → DepartmentName

3NF example

Suppose a table stores StudentID, StudentName, DepartmentID, DepartmentName. If DepartmentID → DepartmentName, then the department name is repeated for every student in that department.

Student(StudentID, StudentName, DepartmentID)
Department(DepartmentID, DepartmentName)

This separates student facts from department facts. A correct normalization decision depends on the actual keys and functional dependencies, not simply on splitting every table into smaller tables.

7. ER Modelling and Relationships

The Entity-Relationship (ER) model represents requirements before tables are implemented. It identifies entities, attributes, keys, relationships, and business rules.

ER-model concepts

• Entity: a distinguishable thing about which data is stored, such as Student, Course, Employee, or Book.
• Attribute: a property of an entity, such as StudentName or DateOfBirth.
• Strong entity: an entity with its own identifying key.
• Weak entity: an entity whose identity or existence depends on another entity.
• Composite attribute: an attribute with meaningful parts, such as Address.
• Multivalued attribute: an attribute that may have multiple values, such as phone numbers.
• Derived attribute: a value calculated from other stored data, such as age calculated from date of birth.

Cardinality and participation

• One-to-one (1:1): each entity occurrence is associated with at most one occurrence on the other side.
• One-to-many (1:N): one department can have many students, while each student is assigned to at most one department under that business rule.
• Many-to-many (M:N): one student can enrol in many courses and one course can have many students.
• Optionality: identifies whether participation in a relationship is required or optional.

Implementing a many-to-many relationship

CREATE TABLE Enrollment (
    StudentID INTEGER NOT NULL,
    CourseID INTEGER NOT NULL,
    EnrolledOn DATE NOT NULL,

    PRIMARY KEY (StudentID, CourseID),

    FOREIGN KEY (StudentID)
        REFERENCES Student(StudentID),

    FOREIGN KEY (CourseID)
        REFERENCES Course(CourseID)
);

The Enrollment table is a junction table. Its composite primary key prevents the same student-course pair from being recorded twice.

8. SQL Joins

Joins combine related data from separate tables. They are essential because a normalized database stores different kinds of facts in separate relations.

SELECT s.StudentName, d.DepartmentName
FROM Student AS s
INNER JOIN Department AS d
    ON s.DepartmentID = d.DepartmentID;

9. Indexes, Security, and Backup Practice

Indexes

An index is a data structure that can speed up some searches, joins, sorts, and lookups. Indexes are helpful on columns frequently used in WHERE, JOIN, or suitable ORDER BY operations, but they also consume storage and can slow inserts, updates, and deletes.

Security and permissions

• Grant users only the permissions they genuinely need: the principle of least privilege.
• Use parameterized queries for untrusted input.
• Store credentials securely and avoid hard-coding them in application source code.
• Use separate accounts for administrators, applications, and read-only reporting where appropriate.
• Review access rights and audit important changes.

Backups and recovery

• Maintain regular backups appropriate to the importance of the data.
• Keep copies separate from the production system where practical.
• Test restoration procedures; an untested backup is not a reliable recovery plan.
• Use transaction logs and recovery tools provided by the selected DBMS when available.

10. Quick Revision and Practice Questions

Practice questions

1. What is the difference between a primary key and a foreign key?
   Answer: A primary key identifies rows in its own table. A foreign key refers to a key in another table and helps maintain referential integrity.
2. What is the difference between WHERE and HAVING?
   Answer: WHERE filters rows before grouping, while HAVING filters groups after aggregation.
3. Can a table have more than one primary key?
   Answer: A table has one primary-key constraint, but that primary key can be composite and contain more than one column.
4. Why is the Optimal page-replacement algorithm not relevant to SQL?
   Answer: It is an operating-system memory-management concept, not an RDBMS normalization or SQL concept.
5. Why are parameterized queries important?
   Answer: They bind values separately from SQL text, helping prevent SQL injection and quoting errors.