Unit-1. Introduction of Relational model
Introduction to the Relational Model
The relational model is a fundamental concept in database management systems (DBMS). Introduced by Edgar F. Codd in 1970, the relational model organizes data into tables, called relations, making it easier to understand, query, and manage information.
Key Concepts of the Relational Model
1. Relation (Table)
A relation is a two-dimensional structure, often visualized as a table with rows and columns.
Each relation represents a specific entity or concept.
2. Tuple (Row)
A tuple is a single record or instance in a relation. It represents a single entity’s data.
Example: In an “Employee” table, each row corresponds to an individual employee.
3. Attribute (Column)
An attribute represents a property or characteristic of the entity.
Example: In an “Employee” table, attributes might include `EmployeeID`, `Name`, and `Department`
4. Domain
A domain is the set of permissible values for a given attribute.
Example: For the “Age” attribute, the domain might be integers ranging from 18 to 65.
5. Primary Key
A primary key uniquely identifies each tuple in a relation.
Example: `EmployeeID` in the “Employee” table could serve as the primary key.
6. Foreign Key
A foreign key is an attribute in one relation that refers to the primary key of another relation.
This establishes a relationship between two tables.
Characteristics of the Relational Model
Data Independence: The relational model separates the physical storage of data from its logical structure, enabling flexibility in data management.
Structured Query Language (SQL): SQL is the standard language used to interact with relational databases, allowing for data retrieval, insertion, updating, and deletion.
Simplicity: The tabular representation of data makes it easy to understand and use.
Normalization: This process reduces data redundancy and improves data integrity by organizing data into multiple related tables.
Advantages of the Relational Model
Flexibility: Data can be queried and manipulated easily.
Data Integrity: Constraints like primary keys and foreign keys ensure accuracy and consistency.
Scalability: Relational databases can handle large amounts of data efficiently.
Standardization: Widely adopted and supported by major DBMS like MySQL, Oracle, and PostgreSQL.
Applications of the Relational Model
Banking and Finance: To manage customer accounts and transactions..
Healthcare: For storing patient records and treatment histories.
Retail: To maintain inventory and sales data.
Education: For managing student information and academic records.
1.1 Codd’s Rules
Codd’s rules, proposed by Dr. Edgar F. Codd in 1985, serve as a benchmark for evaluating whether a database management system (DBMS) is truly relational.
Codd originally defined 12 rules, including a rule 0, which collectively outline the characteristics a system must exhibit to qualify as a relational database.
These rules ensure data integrity, independence, and consistency in relational database management systems (RDBMS).
Rule 0: Foundation Rule :
For a system to qualify as a relational database, it must manage data entirely through its relational capabilities.
The system must use relational techniques for data storage, retrieval, and manipulation without relying on external tools or approaches.
Rule 1: Information Rule :
All information in the database is represented in a single logical way—as values in tables.
Data is stored in tables, with each table consisting of rows (tuples) and columns (attributes).
The database uses these tables as the sole method to represent both the data and its metadata (e.g., table names, column names).
Rule 2: Guaranteed Access Rule
Every data element is accessible by a combination of table name, primary key, and column name.
This rule ensures that every piece of information in the database can be uniquely identified and accessed.
Rule 3: Systematic Treatment of Null Values
Null values are uniformly supported for representing missing or inapplicable data.
Null values must be distinctly handled, separate from zero or an empty string.
Nulls indicate that a value is either unknown, missing, or irrelevant.
Rule 4: Active Online Catalog
The database must have a self-describing nature, storing metadata within the database itself and making it accessible via query.
Metadata (such as table structures, relationships, and constraints) is stored in a format that users can query using the same methods as data.
Rule 5: Comprehensive Data Sub-Language Rule
A single language, such as SQL, must support all tasks, including:
Data Definition Language (DDL) for creating and modifying schemas.
Data Manipulation Language (DML) for querying and updating data.
Data Control Language (DCL) for managing access permissions.
Rule 6: View Updating Rule
Any view that is theoretically updatable must be updatable through the system.
A view (virtual table) provides a filtered or transformed perspective of data.
If a view logically supports updates, the database must allow users to perform updates on the view, which will reflect on the base table.
Rule 7: High-Level Insert, Update, and Delete
The database must support set-level operations for modifying data.
Users should be able to insert, update, or delete multiple rows simultaneously using a single command, rather than operating on rows individually.
Rule 8: Physical Data Independence
Changes to the physical storage structure should not affect how data is accessed at the logical level.
For example, reorganizing data on disk, adding indexes, or changing storage devices should not require modifications to user queries or applications.
Rule 9: Logical Data Independence
Changes to the logical structure (schema) of the database should not affect existing applications.
For instance, adding a new column to a table should not disrupt existing queries that do not use that column.
Rule 10: Integrity Independence
Integrity constraints must be defined in the database and not in application programs.
Constraints such as primary keys, foreign keys, and check conditions should be enforced by the database itself, ensuring data integrity regardless of the application interacting with the database.
Rule 11: Distribution Independence
The database should function as if it were centralized, even if the data is distributed across multiple locations.
Users and applications should not need to know whether the data is stored locally or across various servers.
Rule 12: Non-Subversion Rule
No low-level operation should bypass the integrity rules defined in the database.
If the system provides alternative access methods (e.g., direct file manipulation), they must not compromise the integrity constraints or relational principles.
Relational Algebra Operation.
Relational Algebra Operations are : Select, Project, Union, Intersection, and Rename
Relational algebra provides a collection of algebraic operation that operate on relations (or tables) and it gives output result in the form of tables.
Relational Algebra is a procedural query language used to manipulate and retrieve data from relational databases.
It consists of a set of operations that take one or more relations as input and produce a new relation as output.
These operations are the foundation for query processing in relational databases.
1. Select Operation (σ) :
Definition : The Select operation is used to retrieve rows (tuples) from a relation (table) that satisfy a specific condition.
Notation: σ condition(Relation)
Functionality: Filters data by applying a condition to the rows of a relation.
Example:
Let
Employeebe a table with attributesEmpID,Name, andDepartment. To retrieve employees in the “IT” department:σDepartment=′IT′(Employee)
2. Project Operation (π):
Definition: The Project operation selects specific columns (attributes) from a relation, eliminating duplicates.
Notation: π attribute1,attribute2,…(Relation)
Functionality: Reduces the number of columns in the output while preserving distinct tuples.
Example:
From the Employee table, to retrieve only the Name and Department columns:
3. Union Operation (∪)
Definition: The Union operation combines tuples from two relations and removes duplicates.
Notation: Relation1∪ Relation2
Conditions for Use: Both relations must have the same number of attributes. The attributes in both relations must have the same domain.
Example:
If
R1andR2are two tables with the same schema:R1 ∪ R2
Combines all tuples from both relations without repetition.
4. Intersection Operation (∩)
Definition: The Intersection operation returns tuples that are present in both relations.
Notation:
Relation1 ∩ Relation2
Conditions for Use: Both relations must have the same number of attributes.
The attributes in both relations must have the same domain.
Example:
If
R1andR2are two tables with the same schema:R1 ∩ R2
Retrieves tuples common to both relations.
5. Rename Operation (ρ)
Definition: The Rename operation allows a relation to be given a new name or its attributes to be renamed.
Notation: ρ NewName (Relation)
Functionality: Useful when working with multiple relations that require aliasing to avoid ambiguity.
Example:
To rename the relation
EmployeetoStaff:ρ Staff (Employee)
To rename attributes:
ρ Staff (EmpID,EmpName,Dept) (Employee)
Summary of Operations
| Operation | Symbol | Purpose |
|---|---|---|
| 1. Select | σ | Filters rows based on a condition. . |
| 2. Project | π | Selects specific columns. |
| 3. Union | ∪ | Combines tuples from two relations. |
| 4. Intersection | ∩ | Finds common tuples in two relations. . |
| 5.Rename | ρ | Renames relations or attributes. |
Comparison of COMMIT, SAVEPOINT, and ROLLBACK
Applications of Relational Algebra Operations
Database Querying: These operations form the core of SQL query processing.
Data Transformation: Used to shape data into desired formats for analysis.
Intermediate Steps: Simplify complex queries by breaking them into smaller, manageable operations.
Transaction control language
Transaction Control Language (TCL): Commit, Savepoint, and Rollback
- Transaction Control Language (TCL) is a subset of SQL commands used to manage transactions in a database.
- Transactions are sequences of operations that are executed as a single unit of work, ensuring consistency and reliability in the database.
- TCL commands are used to maintain the integrity of data by controlling the completion or reversal of transactions.
Key Concepts in Transactions
1. Transaction:
A logical unit of work that includes one or more SQL statements. It follows the ACID properties:
Atomicity: All operations are completed, or none are.
Consistency: Ensures the database transitions from one valid state to another.
Isolation: Transactions are independent of one another.
Durability: Once committed, changes are permanent.
2. TCL Commands:
COMMIT: Permanently saves the changes made by a transaction.
SAVEPOINT: Creates a temporary point within a transaction to which you can roll back.
ROLLBACK: Undoes changes made during the transaction, reverting the database to its previous state.
1. COMMIT Command
Purpose : The COMMIT command is used to make all changes made by a transaction permanent in the database. Once committed, the changes cannot be undone.
Syntax: COMMIT;
Features:
Saves all modifications made during the transaction.
Releases any locks held by the transaction.
Ensures durability in the database.
Example :
BEGIN TRANSACTION;
INSERT INTO Employees (EmpID, Name, Department) VALUES (101, ‘John’, ‘IT’);
UPDATE Employees SET Department = ‘HR’ WHERE EmpID = 102;
COMMIT;
In this example, the changes made by the INSERT and UPDATE statements are permanently saved to the database.
2. SAVEPOINT Command
Purpose: The SAVEPOINT command allows you to create a point within a transaction to which you can roll back later if needed. This is useful for dividing a transaction into smaller parts.
Syntax: SAVEPOINT savepoint_name;
Features:
Temporarily saves the state of the database at a specific point.
Multiple savepoints can be created within a single transaction.
Does not commit the transaction.
Example:
BEGIN TRANSACTION;
INSERT INTO Employees (EmpID, Name, Department) VALUES (103, ‘Alice’, ‘Finance’);
SAVEPOINT sp1;
UPDATE Employees SET Department = ‘Marketing’ WHERE EmpID = 104;
SAVEPOINT sp2;
DELETE FROM Employees WHERE EmpID = 105;
ROLLBACK TO sp1; — Reverts changes made after sp1.
COMMIT;
In this example:
sp1andsp2are savepoints.Rolling back to
sp1undoes changes made after the first savepoint but keeps earlier changes.
3. ROLLBACK Command :
Purpose: The ROLLBACK command undoes all changes made by the current transaction or to a specific savepoint.
Syntax: ROLLBACK;
Features:
Restores the database to its state before the transaction began or to a specific savepoint.
Releases any locks held by the transaction.
Ensures data consistency by discarding unwanted changes.
Example:
BEGIN TRANSACTION; INSERT INTO Employees (EmpID, Name, Department) VALUES (106, ‘Bob’, ‘Legal’); DELETE FROM Employees WHERE EmpID = 107; ROLLBACK;
In this example
the database is reverted to its state before the transaction began, and neither the
INSERTnorDELETEoperation is applied.
| Command | Functionality | Purpose | Use Case |
|---|---|---|---|
| 1. COMMIT | Permanently saves changes. | Entire transaction. | To finalize a transaction. |
| 2. SAVEPOINT | Marks a temporary point in a transaction for rollback. | Specific point within a transaction. | To divide a transaction into parts. |
| 3. ROLLBACK | Reverts changes to the beginning of the transaction or a savepoint. | Entire transaction or specific savepoint. | To undo unwanted changes. |
Practical Use of TCL Commands
TCL commands are essential for ensuring the consistency and reliability of database operations. They allow developers and administrators to:
Finalize transactions with COMMIT.
Experiment with changes and selectively undo them using SAVEPOINT and ROLLBACK.
Maintain database integrity by preventing unintended updates.
Data Control Language (DCL):
Data Control Language (DCL): GRANT and REVOKE
Data Control Language (DCL) is a subset of SQL used to manage permissions and access rights to the database objects, such as tables, views, and procedures.
DCL ensures that the right users have the appropriate level of access to the database, maintaining data security and integrity.
Key DCL Commands: GRANT and REVOKE
GRANT: Provides specific privileges to users or roles.
REVOKE: Removes previously granted privileges from users or roles.
1. GRANT Command
- Purpose: The GRANT command is used to give permissions to users or roles for accessing or manipulating database objects. These privileges can be given for various operations, such as selecting, inserting, updating, or deleting data.
- Syntax: GRANT privilege_name ON object_name TO user_or_role [WITH GRANT OPTION];
Key Components:
privilege_name: The specific permission(s) to be granted (e.g., SELECT, INSERT, UPDATE, DELETE).object_name: The database object (e.g., table, view) to which the permission applies.user_or_role: The recipient of the permissions, which can be a user or a role.WITH GRANT OPTION: Allows the recipient to further grant the same permissions to others.
Example:
- GRANT SELECT, INSERT ON Employees TO Alice;
- GRANT SELECT ON Employees TO Bob WITH GRANT OPTION;
2. REVOKE Command
Purpose: The REVOKE command is used to withdraw permissions that were previously granted to a user or role.
Syntax: REVOKE privilege_name ON object_name FROM user_or_role;
Key Components:
privilege_name: The specific permission(s) to be revoked.object_name: The database object from which the permission is being revoked.user_or_role: The user or role losing the permissions.
Example:
REVOKE INSERT ON Employees FROM Alice;
REVOKE SELECT ON Employees FROM Bob;
In this case, Bob loses the SELECT permission on the Employees table, and if he had granted this permission to others (via WITH GRANT OPTION), they lose it too.
Types of Privileges :
1. Object Privileges:
SELECT: Allows reading data from a table or view.
INSERT: Permits adding new rows to a table.
UPDATE: Allows modifying existing data.
DELETE: Enables deleting rows from a table.
EXECUTE: Grants the ability to run stored procedures or functions.
2. System Privileges:
Allow administrative tasks, such as creating users, granting roles, or managing schemas.
Importance of DCL Commands
Data Security: Ensures only authorized users can access or manipulate sensitive data.
Access Management: Helps in defining clear roles and responsibilities for database users.
Accountability: By restricting permissions, it becomes easier to audit database activities.
Flexible Permissions: Administrators can fine-tune access levels based on organizational needs.
Fun & Easy to follow
Works on all devices
Your own Pace
Super Affordable
Popular Videos
UX for Teams
Learn the basics and a bit beyond to improve your backend dev skills.
Chris Matthews
Designer
SEO & Instagram
Learn the basics and a bit beyond to improve your backend dev skills.
Chris Matthews
Designer