Knowlet

1. Relational Model

The Relational Model represents the database as a collection of relations. Informally, a relation looks like a table of values.

Definition: A relation is a mathematical concept defined as a subset of the Cartesian product of a list of domains. In database terms, it is a two-dimensional table consisting of rows and columns.

Key Concepts of the Relational Model

• Domain: The set of permitted values for each attribute.
• Attribute: The column headers or properties that describe a relation.
• Tuple: A single row in a relation, representing a single record.
• Relation Instance: A specific state of a relation at a particular point in time, consisting of a specific set of tuples.

Real-World Application: Managing customer information in an e-commerce platform where each customer is a row and attributes include Customer ID, Name, and Email.

2. Structure of Relational Databases

A relational database consists of multiple interconnected tables. The structural integrity is maintained using keys and integrity constraints.

Database Schema vs. Instance

Keys in Relational Databases

• Super Key: A set of one or more attributes that, taken collectively, allow us to uniquely identify a tuple in the relation.
• Candidate Key: A minimal super key. No proper subset of a candidate key can be a super key.
• Primary Key: A chosen candidate key used to uniquely identify tuples within a relation. It cannot contain null values.
• Foreign Key: An attribute or set of attributes in one relation that matches the primary key of another relation, establishing a relationship between them.

Exam-Oriented Note: Every primary key is a candidate key, but not every candidate key is a primary key. Always identify the minimum set of attributes for candidate keys.

3. Relational Algebra Operations

Relational Algebra is a procedural query language that takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries.

Fundamental Operations

• Selection (σ): Selects tuples that satisfy a given predicate. Example: σ(age > 20)(Student)
• Projection (π): Selects specific columns from a relation and eliminates duplicate rows. Example: π(name, age)(Student)
• Union (∪): Combines tuples from two compatible relations, removing duplicates.
• Set Difference (-): Finds tuples that are in the first relation but not in the second.
• Cartesian Product (×): Combines every tuple of one relation with every tuple of another relation.
• Rename (ρ): Renames a relation or its attributes.

Additional and Extended Operations

• Intersection (∩): Finds tuples that are present in both relations.
• Natural Join (⋈): Joins two relations based on common attributes, automatically eliminating duplicate columns.
• Assignment (←): Assigns intermediate query results to temporary relation variables.
• Outer Join: An extension of the join operation to deal with missing information. Includes Left Outer Join, Right Outer Join, and Full Outer Join.
• Aggregation: Computes aggregate functions like sum, average, maximum, minimum, and count using the grouping operator.

Common Mistake: Confusing Union compatibility. For Union, Intersection, and Set Difference operations, the relations must have the same set of attributes (same degree and compatible domains).

4. Views

A View is a virtual table based on the result-set of an SQL statement. Unlike ordinary tables, a view does not store data physically; it stores the query definition.

Characteristics of Views

• Provides data security by hiding sensitive columns or rows from users.
• Simplifies complex queries by encapsulating joins and aggregations into a single virtual table.
• Can be queried just like a normal table.

Real-World Application: Creating a view that shows only employee names and departments while hiding salary details from general staff.

5. DDL Statements in SQL

Data Definition Language (DDL) statements are used to define the database schema, structures, and constraints.

Common DDL Commands

• CREATE: Creates a new database, table, or view. Example: CREATE TABLE Student (ID INT, Name VARCHAR(50));
• ALTER: Modifies an existing database structure, such as adding a new column. Example: ALTER TABLE Student ADD Age INT;
• DROP: Deletes an entire table or database along with its structure and data. Example: DROP TABLE Student;
• TRUNCATE: Removes all records from a table, resetting the storage space without deleting the table structure. Example: TRUNCATE TABLE Student;

Exam-Oriented Note: Remember that DDL commands are auto-committed in most SQL databases, meaning changes cannot be rolled back easily.

6. DML Statements in SQL

Data Manipulation Language (DML) statements are used for managing data inside database tables.

Common DML Commands

• SELECT: Retrieves data from one or more tables. Example: SELECT * FROM Student;
• INSERT: Adds new rows of data to a table. Example: INSERT INTO Student VALUES (1, 'Alice');
• UPDATE: Modifies existing data within a table. Example: UPDATE Student SET Name = 'Bob' WHERE ID = 1;
• DELETE: Removes specific rows from a table based on a condition. Example: DELETE FROM Student WHERE ID = 1;

Important Distinction: DELETE removes rows one by one and can be rolled back if used within a transaction, whereas TRUNCATE is a DDL command that drops and recreates the table structure instantly.

7. Joins

JOINS are used to combine rows from two or more tables based on a related column between them.

Types of Joins

Practical Example: Joining an Employee table and a Department table using a Foreign Key (DeptID) to display employee names alongside their respective department names.