Relational Database Model and Structured Query Language (SQL)

Introduction to the Relational Model, Relational Schema, Keys, Integrity Constraints, and Relational Algebra

The relational model lies at the very heart of database management systems (DBMS). Since its introduction by E. F. Codd in 1970, the relational model has become the most widely used data model in both academic study and industry practice. Its concepts form the foundation of modern relational databases such as MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

1. The Relational Model

The relational model is a way of structuring and organizing data into tables, known as relations. Each relation is essentially a collection of data organized into rows and columns.

Relation (Table): Represents a set of data items that share the same attributes.
Tuple (Row): Represents a single record in the table.
Attribute (Column): Represents a property or characteristic of the data.

For example, consider a table called Student:

Here:

The table Student is the relation.
Each row (e.g., (101, Anjali, 20, B.Sc CS)) is a tuple.
Each column (StudentID, Name, Age, Course) is an attribute.

2. Relational Schema

A relational schema describes the structure of a relation in a formal way. It defines the relation’s name, attributes, and their respective data types.

Formally:

RelationName(Attribute1, Attribute2, Attribute3, ..., AttributeN)

For the Student table:

Student(StudentID: INT, Name: VARCHAR, Age: INT, Course: VARCHAR)

The relational schema also shows how different relations (tables) are connected.

3. Tuples in the Relational Model

A tuple is a single row in a table that represents one instance of the entity being modeled.

For example, (102, Ramesh, 21, B.Com) is a tuple in the Student relation.

Characteristics of tuples:

Each tuple is unique (no duplicate rows in a relation).
The order of tuples does not matter; relational databases treat relations as sets of tuples.
The values in each attribute must follow the domain defined for that attribute.

4. Concept of Keys in the Relational Model

Keys are crucial in uniquely identifying and establishing relationships among tuples. Let’s explore the different types:

a. Primary Key

A primary key is a unique identifier for each tuple in a relation.
It must be unique and cannot contain NULL values.
Example: In the Student table, StudentID can serve as the primary key because each student has a unique ID.

b. Foreign Key

A foreign key is an attribute in one relation that refers to the primary key in another relation.
It establishes relationships between tables.
Example: Suppose we have another table called Enrollment:

EnrollmentID StudentID CourseCode

E01 101 CS101

E02 102 COM101

E03 103 CS101

Here, StudentID in the Enrollment table is a foreign key that refers to the StudentID in the Student table.

c. Candidate Key

A candidate key is an attribute (or set of attributes) that can uniquely identify a tuple in a relation.
A relation may have multiple candidate keys, but only one is chosen as the primary key.
Example: In the Student table, both StudentID and Name+Course (if combined) might uniquely identify a record, so they are candidate keys.

d. Super Key

A super key is any set of attributes that can uniquely identify a tuple.
All candidate keys are super keys, but not all super keys are candidate keys (because candidate keys are minimal).
Example: In the Student table, {StudentID, Name} is a super key, even though StudentID alone is sufficient.

5. Integrity Constraints in the Relational Model

Integrity constraints ensure the accuracy and consistency of data in the database. They act as rules that data must follow.

a. Domain Constraint

Ensures that the values of an attribute must come from a predefined domain (set of permissible values).
Example: In the Student table, Age must be an integer within the range of 15–30.

b. Entity Integrity Constraint

Ensures that the primary key of a relation cannot be NULL.
This guarantees that each tuple can be uniquely identified.
Example: In the Student table, StudentID must always have a value.

c. Referential Integrity Constraint

Ensures that the foreign key value in one relation must match a primary key value in another relation (or be NULL).
Example: In the Enrollment table, StudentID must exist in the Student table.

d. Key Constraint

Ensures that all primary key values are unique in a relation.
Example: Two students cannot share the same StudentID.

6. Relational Algebra

Relational Algebra is a formal system of operations on relations. It provides a foundation for query languages like SQL and is essential for manipulating and retrieving data from relational databases.

Relational algebra uses operators that take one or two relations as input and produce a new relation as output.

Basic Relational Algebra Operations

1. Selection (σ)

Filters tuples based on a condition.
Example: σ Age > 20 (Student) → Retrieves students older than 20.

2. Projection (π)

Extracts specific attributes (columns).
Example: π Name, Course (Student) → Retrieves only names and courses.

3. Union (∪)

Combines tuples from two relations, eliminating duplicates.
Example: Student_CS ∪ Student_BA

4. Set Difference (−)

Returns tuples present in one relation but not in the other.
Example: Student_CS − Student_BA

5. Cartesian Product (×)

Combines each tuple of one relation with every tuple of another.
Example: Student × Course

6. Rename (ρ)

Renames the relation or its attributes for clarity.

Advanced Relational Algebra Operations

Join (⨝)

Combines related tuples from two relations based on a common attribute.

Example: Student ⨝ Student.StudentID = Enrollment.StudentID Enrollment

Intersection (∩)

Returns tuples that are common to both relations.

Division (÷)

Finds tuples in one relation that are associated with all tuples in another.

7. Importance of Relational Algebra

Relational algebra is not only theoretical but also practical:

It forms the foundation of SQL query design.
Provides a mathematical approach to data manipulation.
Ensures that operations on data are well-defined and consistent.

8. Real-World Example

Let’s consider a real-world scenario of a university database:

Relations:

Student(StudentID, Name, Age, Course)
Enrollment(EnrollmentID, StudentID, CourseCode)
Course(CourseCode, CourseName, Instructor)

Queries using Relational Algebra:

Find names of students enrolled in CS101:

π Name (σ CourseCode = 'CS101' (Student ⨝ Enrollment))

Find all students older than 20:

σ Age > 20 (Student)

Find the courses taught by instructor "Dr. Sharma":

π CourseName (σ Instructor = 'Dr. Sharma' (Course))

Introduction to SQL: Foundation, Importance, Data Types, Constraints, and Commands

Structured Query Language (SQL) is the backbone of relational databases. It provides a standardized way to store, manage, and retrieve data efficiently. Whether you are a student learning database fundamentals, a software developer, or a data analyst, SQL remains a crucial skill. This blog post will give you a comprehensive introduction to SQL, covering its importance in databases, SQL data types and constraints, and the major categories of SQL commands: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

1. What is SQL?

Structured Query Language (SQL) is a domain-specific language used to interact with relational databases. It allows users to create, read, update, and delete (CRUD) data while ensuring accuracy and consistency. Initially developed at IBM in the 1970s, SQL has since become the standard language supported by most relational database management systems (RDBMS), such as MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.

SQL is both powerful and user-friendly. Unlike programming languages such as Java or Python, SQL is declarative: instead of writing code to describe how to perform a task, you state what you want, and the database engine figures out the most efficient way to achieve it. For example, if you want all employees earning more than ₹50,000, you simply write a SELECT query, and the system retrieves the relevant rows without you needing to explain step-by-step logic.

2. Importance of SQL in Databases

SQL plays a central role in database management for several reasons:

a. Universal Standard

SQL is standardized by ANSI (American National Standards Institute) and ISO (International Organization for Standardization). This makes SQL highly portable across platforms, though some RDBMS include proprietary extensions.

b. Efficient Data Retrieval

SQL allows querying vast datasets using simple commands. Operations like filtering (WHERE), aggregation (GROUP BY), and joining tables (JOIN) provide incredible power to extract insights from data.

c. Data Integrity

With the help of constraints such as primary keys, foreign keys, and unique constraints, SQL ensures that the data remains consistent and reliable.

d. Security and Access Control

SQL provides fine-grained access control through roles and permissions, ensuring sensitive data is protected.

e. Transaction Management

SQL enables reliable transaction processing using commands like COMMIT and ROLLBACK, ensuring data accuracy even in the event of errors or failures.

f. Industry Relevance

SQL is the most in-demand database skill across industries—finance, healthcare, retail, logistics, and technology all rely heavily on relational databases and SQL.

3. SQL Data Types

Data types define the kind of data that can be stored in each column of a table. SQL provides a variety of types grouped into broad categories:

a. Numeric Data Types

INT / INTEGER: Whole numbers, e.g., 25, 1000.
SMALLINT / BIGINT: Smaller or larger integers.
DECIMAL / NUMERIC: Fixed-point numbers, useful for financial data.
FLOAT / REAL / DOUBLE PRECISION: Approximate numeric values with decimals.

b. Character/String Data Types

CHAR(n): Fixed-length character strings.
VARCHAR(n): Variable-length strings.
TEXT / CLOB: Large text data.

c. Date and Time Data Types

DATE: Stores date values (YYYY-MM-DD).
TIME: Stores time of day.
DATETIME / TIMESTAMP: Stores both date and time, often with time zones.

d. Boolean

BOOLEAN: Stores TRUE, FALSE, or NULL.

e. Binary Data Types

BLOB (Binary Large Object): Used to store images, videos, or binary files.

4. SQL Constraints

Constraints ensure that the data entered into a table meets certain rules, preserving integrity and accuracy.

a. NOT NULL Constraint

Ensures that a column cannot contain NULL values.

CREATE TABLE Employees (

EmpID INT NOT NULL,

Name VARCHAR(50) NOT NULL

);

b. UNIQUE Constraint

Guarantees that all values in a column are unique.

ALTER TABLE Employees ADD CONSTRAINT unique_email UNIQUE (Email);

c. PRIMARY KEY

A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table.

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

d. FOREIGN KEY

Links a column to the primary key of another table, ensuring referential integrity.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

e. CHECK Constraint

Restricts the values entered into a column.

ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (Salary > 0);

f. DEFAULT Constraint

Provides a default value for a column when none is specified.

ALTER TABLE Employees ADD CONSTRAINT def_country DEFAULT 'India' FOR Country;

5. SQL Commands

SQL commands are divided into categories based on their function. Together, these commands allow comprehensive control over database design, data manipulation, and security.

5.1 Data Definition Language (DDL)

DDL commands define and manage the database schema (tables, indexes, views).

a. CREATE

Creates a new table or database.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

Email VARCHAR(50) UNIQUE,

Salary DECIMAL(10,2),

DeptID INT

);

b. ALTER

Modifies an existing table’s structure.

ALTER TABLE Employees ADD Phone VARCHAR(15);

c. DROP

Deletes a table or database permanently.

DROP TABLE Employees;

d. TRUNCATE

Removes all rows from a table but retains its structure.

TRUNCATE TABLE Employees;

5.2 Data Manipulation Language (DML)

DML commands are used to query and modify data.

a. SELECT

Retrieves data from one or more tables.

SELECT Name, Salary FROM Employees WHERE Salary > 50000;

b. INSERT

Adds new rows to a table.

INSERT INTO Employees (EmpID, Name, Email, Salary, DeptID)

VALUES (101, 'Rahul Sharma', 'rahul@company.com', 60000, 1);

c. UPDATE

Modifies existing rows in a table.

UPDATE Employees SET Salary = 70000 WHERE EmpID = 101;

d. DELETE

Removes rows from a table.

DELETE FROM Employees WHERE Salary < 30000;

5.3 Data Control Language (DCL)

DCL commands control user access to the database.

a. GRANT

Gives privileges to users.

GRANT SELECT, INSERT ON Employees TO user1;

b. REVOKE

Removes previously granted privileges.

REVOKE INSERT ON Employees FROM user1;

5.4 Transaction Control Language (TCL)

TCL commands manage transactions, ensuring reliability.

a. COMMIT

Saves all changes made in the transaction.

COMMIT;

b. ROLLBACK

Undoes changes since the last commit.

ROLLBACK;

c. SAVEPOINT

Sets a point in a transaction to roll back to later.

SAVEPOINT sp1;

d. RELEASE SAVEPOINT

Removes a savepoint.

RELEASE SAVEPOINT sp1;

e. SET TRANSACTION

Specifies transaction properties like read/write.

SET TRANSACTION READ WRITE;

6. Advantages of SQL

Simplicity: Easy to learn and write.
Versatility: Works with small to massive datasets.
Portability: Runs on multiple platforms and RDBMS.
Performance: Optimized for efficient data access.
Scalability: Supports enterprise-level applications.
Integration: Works seamlessly with programming languages and data analysis tools.

7. Real-World Applications of SQL

SQL is deeply integrated into multiple industries. Let’s explore:

7.1 Banking 🏦

Managing accounts, customers, transactions, and fraud detection.

📊 Diagram: SQL in Banking

7.2 E-Commerce 🛒

Product management, order tracking, customer analytics.

📊 Diagram: SQL in E-commerce

7.3 Healthcare 🏥

Patient records, billing systems, and research databases.

📊 Diagram: SQL in Healthcare

7.4 Social Media 📱

User data, posts, comments, and real-time analytics.

📊 Diagram: SQL in Social Media

7.5 Education 🎓

Student records, grading systems, and research management.

📊 Diagram: SQL in Education

7.6 Summary of Applications 🌍

📊 Diagram: Real-World SQL Applications

Creating and Managing Tables in SQL, Basic SQL Queries, and Understanding Joins

Structured Query Language (SQL) is the backbone of relational databases. Whether you are a beginner starting your journey into database management or a professional looking to sharpen your skills, understanding how to create and manage tables, perform queries, and use joins effectively is essential.

How to create and manage tables in SQL.

Performing the basic SQL operations (SELECT, INSERT, UPDATE, DELETE).

Understanding joins (INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN).

1. Introduction to Tables in SQL

Tables are the building blocks of a relational database. They store data in a structured format with rows (tuples) and columns (attributes). Each table represents an entity such as customers, products, employees, or transactions.

For example:

Customer Table

CustomerID Name City Age

1 Ramesh Bhopal 25

2 Suresh Indore 30

3 Mahesh Jabalpur 22

Here, CustomerID is the primary key that uniquely identifies each customer.

2. Creating Tables in SQL

You use the CREATE TABLE statement to define a new table in a database.

Syntax:

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

...

);

Example: Creating a Customers table

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

City VARCHAR(50),

Age INT CHECK (Age > 0)

);

CustomerID: Integer, unique, primary key.

Name: Cannot be NULL.

City: Optional column.

Age: Must be greater than 0.

3. Modifying Tables

Tables may need to evolve as business requirements change. SQL provides commands to alter, drop, and truncate tables.

a) ALTER TABLE

Used to add, delete, or modify columns.

ALTER TABLE Customers ADD Email VARCHAR(100);

ALTER TABLE Customers DROP COLUMN Age;

ALTER TABLE Customers MODIFY City VARCHAR(100);

b) DROP TABLE

Permanently deletes a table and all its data.

DROP TABLE Customers;

⚠️ Use with caution as this action cannot be undone.

c) TRUNCATE TABLE

Deletes all rows but keeps the table structure intact.

TRUNCATE TABLE Customers;

4. Basic SQL Queries

The most important SQL operations are CRUD: Create, Read, Update, and Delete.

a) SELECT – Retrieving Data

The SELECT statement is used to query the database.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example:

SELECT Name, City FROM Customers WHERE Age > 25;

This retrieves the names and cities of customers older than 25.

b) INSERT – Adding New Records

The INSERT INTO statement is used to add new rows to a table.

Syntax:

INSERT INTO table_name (column1, column2, column3)

VALUES (value1, value2, value3);

Example:

INSERT INTO Customers (CustomerID, Name, City, Age)

VALUES (4, 'Amit', 'Delhi', 28);

c) UPDATE – Modifying Records

The UPDATE statement modifies existing rows.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

UPDATE Customers SET City = 'Mumbai' WHERE CustomerID = 2;

d) DELETE – Removing Records

The DELETE statement removes rows.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Customers WHERE CustomerID = 3;

⚠️ If you omit the WHERE clause, all rows will be deleted.

5. Understanding Joins in SQL

In relational databases, data is often spread across multiple tables. To combine data meaningfully, SQL provides JOIN operations.

a) INNER JOIN

Returns rows when there is a match in both tables.

Example:

Suppose we have two tables:

Customers Table

CustomerID Name City

1 Ramesh Bhopal

2 Suresh Indore

Orders Table

OrderID CustomerID Product

101 1 Laptop

102 2 Phone

103 3 Keyboard

Query:

SELECT Customers.Name, Orders.Product

FROM Customers

INNER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Result:

Name Product

Ramesh Laptop

Suresh Phone

b) LEFT JOIN

Returns all rows from the left table, and matched rows from the right table. Unmatched rows from the right table are NULL.

SELECT Customers.Name, Orders.Product

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Result:

Name Product

Ramesh Laptop

Suresh Phone

Mahesh NULL

c) RIGHT JOIN

Returns all rows from the right table and matched rows from the left.

SELECT Customers.Name, Orders.Product

FROM Customers

RIGHT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Result:

Name Product

Ramesh Laptop

Suresh Phone

NULL Keyboard

d) FULL OUTER JOIN

Returns all rows when there is a match in either table.

SELECT Customers.Name, Orders.Product

FROM Customers

FULL OUTER JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

Result:

Name Product

Ramesh Laptop

Suresh Phone

Mahesh NULL

NULL Keyboard

6. Best Practices for Writing SQL Queries

Use proper constraints: Always define primary keys, foreign keys, and checks.

Be cautious with DELETE and UPDATE: Always include a WHERE clause.

Use aliases for readability:

SELECT C.Name, O.Product

FROM Customers C

INNER JOIN Orders O

ON C.CustomerID = O.CustomerID;

Normalize data to reduce redundancy.

Index frequently queried columns for performance.

7. Real-World Use Cases

E-commerce: Managing customers, products, and orders.
Banking: Handling accounts, transactions, and loan records.
Healthcare: Storing patient and treatment data.
Education: Tracking students, courses, and grades.
Social Media: Managing users, posts, and interactions.

Hands-On Guide to SQL: From Relational Schema Design to Relational Algebra

Structured Query Language (SQL) lies at the heart of modern databases. While the theoretical foundations of relational models give us the framework, SQL provides the practical tools to create, manipulate, and query databases. In this blog, we will walk through five essential activities that will help students and professionals build a strong understanding of SQL:

Creating a relational schema for a sample database
Using SQL commands to create, insert, update, and delete records
Writing SQL queries with different types of joins
Applying integrity constraints to sample tables
Performing relational algebra operations on sample datasets

By the end of this guide, you will have both the practical skills and theoretical knowledge to work with relational databases effectively.

1. Creating a Relational Schema for a Sample Database

What is a Relational Schema?

A relational schema is the blueprint of a database. It defines the structure of tables, their attributes (columns), and the relationships among them.

Let’s design a simple schema for a Student Management System that tracks students, courses, and enrollments.

Relational Schema Design

We will create the following tables:

Students(StudentID, Name, Age, Department)
Courses(CourseID, CourseName, Credits)
Enrollments(EnrollmentID, StudentID, CourseID, Grade)

Explanation:

Each student has a unique StudentID.
Each course has a unique CourseID.
The Enrollments table represents a many-to-many relationship between students and courses.

2. Using SQL Commands to Create, Insert, Update, and Delete Records

a. Creating Tables (DDL - Data Definition Language)

-- Creating Students Table

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT,

Department VARCHAR(50)

);

-- Creating Courses Table

CREATE TABLE Courses (

CourseID INT PRIMARY KEY,

CourseName VARCHAR(100),

Credits INT

);

-- Creating Enrollments Table

CREATE TABLE Enrollments (

EnrollmentID INT PRIMARY KEY,

StudentID INT,

CourseID INT,

Grade CHAR(2),

FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

b. Inserting Records (DML - Data Manipulation Language)

-- Insert into Students

INSERT INTO Students (StudentID, Name, Age, Department)

VALUES (1, 'Amit Sharma', 20, 'Computer Science'),

(2, 'Neha Verma', 21, 'Mathematics'),

(3, 'Rahul Gupta', 19, 'Physics');

-- Insert into Courses

INSERT INTO Courses (CourseID, CourseName, Credits)

VALUES (101, 'Database Systems', 4),

(102, 'Linear Algebra', 3),

(103, 'Quantum Physics', 4);

-- Insert into Enrollments

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Grade)

VALUES (1, 1, 101, 'A'),

(2, 2, 102, 'B'),

(3, 3, 103, 'A'),

(4, 1, 102, 'C');

c. Updating Records

-- Update student’s department

UPDATE Students

SET Department = 'Data Science'

WHERE StudentID = 1;

-- Update grade in enrollments

UPDATE Enrollments

SET Grade = 'B+'

WHERE EnrollmentID = 4;

d. Deleting Records

-- Delete a course

DELETE FROM Courses

WHERE CourseID = 103;

-- Delete a student

DELETE FROM Students

WHERE StudentID = 3;

3. Writing SQL Queries with Different Types of Joins

Joins allow us to combine data from multiple tables.

a. INNER JOIN

Fetch students and the courses they are enrolled in:

SELECT Students.Name, Courses.CourseName, Enrollments.Grade

FROM Students

INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

b. LEFT JOIN

Fetch all students and their course enrollments (if any):

SELECT Students.Name, Courses.CourseName

FROM Students

LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

LEFT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

c. RIGHT JOIN

Fetch all courses and the students enrolled in them:

SELECT Students.Name, Courses.CourseName

FROM Students

RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

RIGHT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

d. FULL OUTER JOIN

(Not always supported in all SQL dialects, but conceptually combines LEFT and RIGHT joins.)

SELECT Students.Name, Courses.CourseName

FROM Students

FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

FULL OUTER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

4. Applying Integrity Constraints to Sample Tables

Integrity constraints ensure accuracy and consistency in databases.

a. Domain Constraint

Defines valid values for an attribute. Example: Age should be positive.

ALTER TABLE Students

ADD CONSTRAINT chk_age CHECK (Age > 0);

b. Entity Integrity Constraint

Ensures each table has a primary key.

ALTER TABLE Students

ADD CONSTRAINT pk_student PRIMARY KEY (StudentID);

c. Referential Integrity Constraint

Maintains relationships between tables using foreign keys.

ALTER TABLE Enrollments

ADD CONSTRAINT fk_student FOREIGN KEY (StudentID) REFERENCES Students(StudentID);

d. Key Constraints

Ensure uniqueness across attributes.

ALTER TABLE Courses

ADD CONSTRAINT uq_course UNIQUE (CourseName);

5. Performing Relational Algebra Operations on Sample Datasets

Relational algebra is the mathematical foundation of SQL. Let’s translate some SQL queries into relational algebra.

a. Selection (σ)

Find all students from the "Computer Science" department.

SQL:

SELECT * FROM Students WHERE Department = 'Computer Science';

Relational Algebra:

σ Department='Computer Science' (Students)

b. Projection (π)

Get only names of students.

SQL:

SELECT Name FROM Students;

Relational Algebra:

π Name (Students)

c. Union (∪)

List all student names and all course names.

SQL (if compatible types):

SELECT Name AS Entity FROM Students

UNION

SELECT CourseName AS Entity FROM Courses;

Relational Algebra:

π Name (Students) ∪ π CourseName (Courses)

d. Set Difference (−)

Find students who are not enrolled in any course.

SQL:

SELECT Name FROM Students

WHERE StudentID NOT IN (SELECT StudentID FROM Enrollments);

Relational Algebra:

π Name (Students) − π Name (Students ⨝ Enrollments)

e. Cartesian Product (×)

Pair all students with all courses.

SQL:

SELECT Students.Name, Courses.CourseName

FROM Students, Courses;

Relational Algebra:

Students × Courses

f. Join (⨝)

Find student names with their enrolled courses.

SQL:

SELECT Students.Name, Courses.CourseName

FROM Students

JOIN Enrollments ON Students.StudentID = Enrollments.StudentID

JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Relational Algebra:

Students ⨝ Enrollments ⨝ Courses

Technology with Vivek

Search This Blog