Understanding Relational Databases: Part 1

Databases are the backbone of any modern application, and knowing how to manage and manipulate them is essential for developers, analysts, and anyone working with data. In this blog post, we will explore the key concepts behind relational databases, focusing on how they are structured, how to create and modify them, and how SQL commands work to manage data.

What is a Relational Database?
Basic Structure of a Database
Types of SQL Commands
Creating and Modifying Databases and Tables
Alter Table Commands
Constraints in MySQL
Referential Actions for Foreign Keys
Conclusion

What is a Relational Database?

A relational database is a way to store and organize data in a structured format, using tables. Each table has rows and columns, where rows represent individual records and columns store data about those records. For example, in a university database, we might have tables for students, courses, and enrollments, where each table holds information related to its respective category.

Basic Structure of a Database

A database consists of several components:

Database Server refers to the hardware or software system that physically stores and manages the database.
DBMS is a software application that helps you create, manage, and interact with databases.(example : MySQL, PostgreSQL, Oracle, and SQL Server)
Databases: These are containers that hold all the tables and data.
Tables: Organized collections of data within a database.

Types of SQL Commands

SQL (Structured Query Language) is used to interact with relational databases. There are four main types of SQL commands:

DDL (Data Definition Language): These commands define the database structure.
Examples: CREATE, ALTER, DROP, DELETE.
DML (Data Manipulation Language): These commands allow us to manipulate the data inside tables.
Examples: INSERT, UPDATE, DELETE, SELECT.
DCL (Data Control Language): These commands control access to data.
Examples: GRANT, REVOKE.
TCL (Transaction Control Language): These commands manage database transactions.
Examples: COMMIT, ROLLBACK.

Creating and Modifying Databases and Tables

Creating a Database

To create a new database, you can use the CREATE DATABASE command.
Example:

CREATE DATABASE IF NOT EXISTS university_db;

Creating a Table

Tables are created to store data in a structured way. Here’s how to create a students table:

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), password VARCHAR(255) );

Dropping a Table

To remove a table from a database, you can use the DROP TABLE command.
Example:

DROP TABLE IF EXISTS students;

ALTER TABLE Commands

ALTER TABLE is used to modify an existing table:

Add Columns
Example:ALTER TABLE customers ADD COLUMN surname VARCHAR(100);
Delete Columns
Example:ALTER TABLE customers DROP COLUMN surname;
Modify Columns
Example:ALTER TABLE customers MODIFY COLUMN age INT;
Add Constraints
Example:ALTER TABLE customers ADD CONSTRAINT age_check CHECK (age > 18);
Drop Constraints
Example:ALTER TABLE customers DROP CONSTRAINT age_check;

Constraints in MySQL

Constraints are rules we define on columns to ensure data integrity. These rules make sure that the data entered into the table is accurate and consistent.

Common Constraints

1. UNIQUE Constraint

Ensures all values in a column are unique.

Method 1:

CREATE TABLE users ( user_id INT NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL );

Method 2:

CREATE TABLE users ( user_id INT NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, CONSTRAINT users_email_unique UNIQUE (email) );

Significance of Method 2:

Naming Constraints: In Method 2, you can explicitly name your constraint (e.g., users_email_unique). This is useful when you want to reference the constraint later, such as for deletion or modification. It also makes your database schema easier to understand and maintain.
Flexibility for Complex Constraints: Method 2 makes it easier to define more complex constraints, such as multi-column unique constraints, without cluttering the column definition.

2. NOT NULL Constraint

Ensures a column cannot have a NULL value.

Example:

CREATE TABLE students ( student_id INT NOT NULL, name VARCHAR(255) NOT NULL );

3. PRIMARY KEY Constraint

Uniquely identifies each row in a table.Automatically includes NOT NULL and UNIQUE.

Method 1:

CREATE TABLE users ( user_id INT NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL );

Method 2:

CREATE TABLE users ( user_id INT NOT NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, CONSTRAINT users_pk PRIMARY KEY (user_id) );

Significance of Method 2:

Composite Primary Key: If you need a composite primary key (using multiple columns), Method 2 is more convenient. For example, if you need the combination of user_id and email to be unique, you can define it like this:

CONSTRAINT users_pk PRIMARY KEY (user_id, email)

Naming: By explicitly naming the primary key (users_pk), it becomes easier to identify and reference the constraint later. For example, if you need to drop or modify the primary key constraint, you can do so by referring to users_pk.

4. FOREIGN KEY Constraint

Creates a relationship between two tables by referencing a primary key in another table.

Method 1:

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Method 2:

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT orders_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Significance of Method 2:

Naming Foreign Key Constraints: Method 2 allows you to give a meaningful name to the foreign key constraint, such as orders_fk. This is particularly useful when managing large schemas with multiple foreign keys, as it becomes easy to identify and modify the constraint when needed.
Referential Actions: When defining actions like ON DELETE CASCADE, ON UPDATE CASCADE, or ON DELETE SET NULL, it’s easier to apply them using Method 2 because you can do it with the constraint’s name. For example:

CONSTRAINT orders_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE

5. CHECK Constraint

Ensures that data meets a specific condition.

Method 1:

CREATE TABLE voters ( voter_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT CHECK (age > 18 AND age < 100) );

Method 2:

CREATE TABLE voters ( voter_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT, CONSTRAINT voters_age_check CHECK (age > 18 AND age < 100) );

Significance of Method 2:

Naming Constraints: In Method 2, you can name the check constraint (e.g., voters_age_check), which is beneficial for clarity and future reference. If you need to modify or delete the constraint, it’s easier to refer to by name.
Modularity: Defining constraints in Method 2 allows you to manage constraints separately from column definitions. This helps with maintaining cleaner table definitions, especially when dealing with complex constraints.

6. DEFAULT Constraint

Assigns a default value if no value is provided.

Method 1:

CREATE TABLE tickets ( ticket_id INT PRIMARY KEY, name VARCHAR(100), travel_date DATETIME DEFAULT CURRENT_TIMESTAMP );

Method 2:

CREATE TABLE tickets ( ticket_id INT PRIMARY KEY, name VARCHAR(100), travel_date DATETIME, CONSTRAINT tickets_travel_date_default DEFAULT CURRENT_TIMESTAMP FOR travel_date );

Significance of Method 2:

Named Default Constraints: By naming the default constraint (tickets_travel_date_default), it is easier to manage and modify it later, especially in larger databases with multiple default values.
Better Control: Naming the constraint makes it easier to control and make changes without altering the column definition directly.

7. AUTO INCREMENT Constraint

Automatically generates a unique number when a new record is inserted.

Example:

CREATE TABLE students ( student_id INT NOT NULL, email VARCHAR(255) UNIQUE );

Referential Actions for Foreign Keys

When defining foreign keys, we can set referential actions to control what happens when data in the referenced table is updated or deleted.

Referential Actions

1. RESTRICT

RESTRICT prevents any update or delete operation on the parent table if there are any corresponding rows in the child table. This action ensures that data in the child table remains intact unless all related records are removed from the child table first.

Example:

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT ON UPDATE RESTRICT );

How it works:

If you try to delete or update a record in the customers table that is referenced in the orders table, the operation will be restricted. In other words, MySQL will prevent the deletion or update unless there are no matching records in the orders table.

Use Case:

RESTRICT is useful when you want to make sure that a record in the parent table cannot be deleted or updated if any dependent rows exist in the child table. For example, if you want to prevent a customer from being deleted if they have any active orders

2. CASCADE

CASCADE means that when a record in the parent table is updated or deleted, the change will automatically apply to the related records in the child table. This helps maintain consistency across the tables.

Example:

How it works:

If a record in the customers table is deleted or updated, the corresponding records in the orders table will automatically be deleted or updated as well.
For example, if a customer’s record is deleted, all their related orders in the orders table will also be deleted.

Use Case:

CASCADE is helpful when you want to ensure that changes in the parent table automatically propagate to the child table, maintaining data integrity. For example, if a customer is deleted, you might want to automatically remove all their orders.

3. SET NULL

SET NULL sets the foreign key column in the child table to NULL when the corresponding record in the parent table is deleted or updated. This action is useful when you want to preserve the child records but indicate that they no longer have a valid reference.

Example:

How it works:

If a record in the customers table is deleted or updated, the customer_id field in the related orders table will be set to NULL.
This keeps the order records in the orders table but removes the association with the customer.

Use Case:

SET NULL is useful when you want to preserve the child record but acknowledge that the parent record is no longer valid. For example, if a customer is deleted but you still want to keep the order history, you can set the customer_id in the orders table to NULL.

4. SET DEFAULT

SET DEFAULT sets the foreign key column in the child table to its default value when the corresponding record in the parent table is deleted or updated. The default value is predefined and can be set when creating the table.

Example:

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT DEFAULT 0, -- Default customer_id set to 0 order_date DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT );

How it works:

If a record in the customers table is deleted or updated, the customer_id in the orders table will be set to the default value (0 in this case).

Use Case:

SET DEFAULT is useful when you want to set a predefined value (e.g., 0 or some other identifier) for the foreign key when the referenced parent record is deleted or updated. This can help maintain data integrity when there is a valid fallback value.

Conclusion

In this blog, we’ve covered the basics of relational databases, including their structure, common SQL commands, and key concepts like constraints and referential actions. Understanding how to create and manage databases and tables is crucial for working with data efficiently.

But this is just the beginning! In the next part of our series, we will dive deeper into Data Manipulation Language (DML). Stay tuned as we explore how to insert, update, delete, and query data, the next essential steps for managing and interacting with your database.

More to come in Part 2!