4. Relational Databases

Relational Model

In relational databases, tables are interconnected, often by referencing each other’s IDs. For example, an employee table may reference a course table through a CourseID column.

Anomalies in Relational Databases

Anomalies are unexpected or problematic situations that can arise in a relational database. These are resolved by normalizing the database and splitting it into smaller, related tables.

Insert Anomaly

Occurs when you cannot insert data into a table due to structural dependencies.

Example:

• If a table contains both employee and course information, adding a new course requires adding an employee, which is illogical.

• Solution: Separate courses into their own table, and reference them using CourseID.

-- Separate tables
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    CourseID INT NULL REFERENCES Courses(CourseID)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    Title NVARCHAR(100)
);

Update Anomaly

Occurs when updating one piece of data requires multiple updates across the database.

Example:

• If the color "Gray" is renamed to "Light Gray," you need to update it in every row where it appears.

• Solution: Store the color details in a separate table, and reference the color by ColorID.

CREATE TABLE Cars (
    CarID INT PRIMARY KEY,
    Model NVARCHAR(50),
    ColorID INT REFERENCES Colors(ColorID)
);

CREATE TABLE Colors (
    ColorID INT PRIMARY KEY,
    ColorName NVARCHAR(50)
);

Delete Anomaly

Occurs when deleting a record inadvertently removes other necessary data.

Example:

• Deleting a student who is the only participant in a course also deletes the course record.

• Solution: Store courses in a separate table and use a foreign key to reference them.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50),
    CourseID INT REFERENCES Courses(CourseID)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    Title NVARCHAR(100)
)

Data Model Relationships

Relational databases use various types of relationships to define how data tables are connected.

One-to-One

A single row in table A is related to a single row in table B. This relationship is often used for unique data, such as a person and their passport.

CREATE TABLE People (
    PersonID INT PRIMARY KEY,
    Name NVARCHAR(50),
    PassportID INT UNIQUE REFERENCES Passports(PassportID)
);

CREATE TABLE Passports (
    PassportID INT PRIMARY KEY,
    PassportNumber NVARCHAR(20)
);

One-to-Many

A single row in table A is related to multiple rows in table B. For example, a class and its students.

CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    ClassName NVARCHAR(50)
);

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50),
    ClassID INT REFERENCES Classes(ClassID)
);

Many-to-Many

A row in table A can relate to multiple rows in table B and vice versa. For example, books and authors.

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title NVARCHAR(100)
);

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

CREATE TABLE BookAuthors (
    BookAuthorID INT PRIMARY KEY,
    BookID INT REFERENCES Books(BookID),
    AuthorID INT REFERENCES Authors(AuthorID)
);

Foreign Keys

Foreign keys link tables by referencing a column from another table. They ensure data consistency and enforce relationships between tables.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    PassportID INT NOT NULL REFERENCES Passports(PassportID)
);

Cartesian Product (Dekart Multiplication)

A Cartesian product occurs when every row in one table is paired with every row in another table. This is usually the result of a join operation without specifying a relationship condition.

SELECT *
FROM TableA, TableB;

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version