Welcome to My Blog

Father of RDBMS is Dr. Edgar F.Codd, he introduced the concept of RDBMS published in 1970, as he proposed storing of the data in tables (rows & columns) or tuples and attributes, introduced the technique of Normalization to eliminate data redundancy, so there will be Consistency and he also introduced 12 rules, as they are called as 12 Codd rules which defines an RDBMS, the modern Databases like Oracle, Microsoft SQL Server, MySQL , PostgreSQL and others follow these 12 rules.

Codd Rules of RDBMS

Understanding E.F. Codd's Vision for Relational Databases

Who was Edgar F. Codd and his impact on database design

The revolution of the relational model

Why the 12 rules were created

How these rules transformed data management

The Foundational Rules (Rules 1-4)

Rule 1: The Information Rule - data representation as values in tables

Rule 2: The Guaranteed Access Rule - locating data through table, column, and key

Rule 3: Systematic Treatment of Null Values

Rule 4: Dynamic Online Catalog - database schema in the same database format

Data Integrity Rules (Rules 5-7)

Rule 5: The Comprehensive Data Sublanguage Rule

Rule 6: The View Updating Rule

Rule 7: High-level Insert, Update, and Delete operations

Logical Data Independence (Rules 8-10)

Rule 8: Physical Data Independence

Rule 9: Logical Data Independence

Rule 10: Integrity Independence - constraints stored in catalog

Distribution and Data Manipulation (Rules 11-12)

Rule 11: Distribution Independence - location transparency

Rule 12: The Nonsubversion Rule - preserving integrity

Detailed Explanation of the 12 Codd rules

1.Information Rule

1.1.Data are represented only one way as values within columns within rows.

1.2.Simple consistent and versatile

1.3.The basic requirement of the relational model.

2.Guaranteed access Rule

2.1.Every value can be accessed by providing table name, Colum name and key

2.2.All data are uniquely identified and accessible via this identity

Ex: SELECT Email FROM Students WHERE StudentID = 101;

3.Systematic treatment of null values

3.1.Separate handling of missing and / or non applicable data.

3.2.This is distinct to zero or empty strings.

3.3.Codd would further like several types of null to be handled.

Ex: In a Patients table, a BloodType entry left NULL indicates that the information was not provided, whereas an empty string ('') might imply a data entry error

4.Relation online catalog

4.1.Catalog can be queried by authorized users as part of the database

4.2.The catalog is part of the database.

Ex: SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'public';

5.Comprehensive data sublanguage

5.1.Used interactivity and embedded within programs

5.2.Supports data definition, data manipulation security, integrity constraints and transaction processing.

Ex: Data Definition: CREATE TABLE Students (...);

Data Manipulation: INSERT INTO Students VALUES (101, 'Alice', 2024);

Transactions: BEGIN; UPDATE ...; COMMIT;

6.View updating rule

6.1.All theoretically possible view updated should be possible.

6.2.Views are virtual tables they appear to behave as conventional tables except that they are built dynamically when the query is run. This means that a view is always up to date. It is not always theoretically possible to update views. odd himself ,did not completely understand this. One himself exists when a view relates to part of a table not including a candidate key. this means that potential updates would violate the entity integrity rule.

Ex: CREATE VIEW ActiveStudents AS SELECT * FROM Students WHERE Status = 'Active';

7.High-Level insert, update and delete

7.1.Must support set-at-a-time updates i.e transactions

7.2.Many rows may be updated with this single statement.

Ex: UPDATE Students SET Status = 'Graduated' WHERE Year = 2024;

8.Physical data independence

8.1.Physical layer of the architecture is mapped onto the logical layer.

8.2.Users and programs are not dependent on the physical structure of the database.

8.3.Physical layer implementation is dependent on the DBMS

9.Logical data independence

Users and programs are independent of the logical structure of the database i.e the logical structure of the data can involve minimal impact on the programs.

EX: Adding a MiddleName column to the Students table does not break applications that only reference FirstName and LastName. The RDBMS handles schema evolution transparently, ensuring backward compatibility

10.Integrity Independence

10.1.Integrity constraints are to be stored in the catalog not the programs.

10.2.Alterations to integrity constraints should not affect application programs

10.3.This simplifies the programs

10.4.It is not always to do this.

Ex: ALTER TABLE Students ADD CONSTRAINT AgeCheck CHECK (Age >= 18);

11.Distribution Independence

11.1Applications should still work in a distributed database[DDB].

Ex: A distributed Orders table split across servers in North America and Europe should allow queries like SELECT * FROM Orders; to return consolidated results without application-level adjustments.

12.Nonsubversion rule

12.1.If there is a record –at-a-time interface, security and integrity of the database must not be violated.

12.2.There should be no backdoor to by pass the security imposed by the DBMS.

What is Database?

A structured or semi-structured or unstructured collection of data is stored electronically. We have different tools for example: Excel, Google Sheets, Microsoft SQL Server, Oracle, PostgreSQL, MySQL, MongoDB, etc., These are Database Management Systems and they are also called as Relational Database Management System, except MongoDB which is a NoSQL Tool

DBMS vs RDBMS

1.Data Structure: In Database Management Systems we have Unstructured or Semi-Structured data.

In Relational Database Management Systems, Only Structured data in an Table format, with rows and columns.

2.Relationships: In DBMS there is no support

In RDBMS full support with relationships like Constraints( Not Null, Unique, Primary Key, Foreign Key, Check, Default)

3.Data Integrity: In DBMS there are no constraints and there is Limited data integrity here.

In RDBMs, there is a strong data integrity.

4.Normalization: In DBMS, there is no Normalization, In RDBMS It supports different Normalization types and it often requires normalization

5.Scalability: DBMS is suitable for small-scale applications and smaller organizations, Whereas RDBMS suitable for larger-scale applications and organizations.

6.Concurrency Control: in DBMS it is limited, Robust Concurrency management in RDBMS.

7.Examples: XML databases, file systems are the DBMS, Microsoft SQL Server, Oracle, MySQL, PostgreSQL are RDBMS applications.

ACID properties are the main principles of database transactions, Full form of ACID stands for Atomicity, Consistency, Isolation and Durability

1. Atomicity: Any transaction in a database table is treated as a single unit of work, though all operations in it are successfully completed or none of them are applied to the database. It prevents partial updates, so that the failed transaction has no impact. Example: In any money transactions between two accounts , the debit and credit operations must be successful, if any one fails other will not occur.

2. Consistency: Any modifications done to the database should always be kept, this ensures the integrity of the data in RDBMS, as Data Integrity is essential for transactions in order to assure consistency in the database earlier and later in the transactions. Example: If any transaction violates a constraint, such as exceeding an account's credit limit, then the transaction is rolled back to maintain the database consistency state.

3. Isolation: Transactions are executed independently and they don't interfere with any other transaction. Every transaction should execute if it is the only one transaction running and preventing the other, which doesn't allows data inconsistencies For example: one transaction is buying an item from the inventory, mean while other transaction is making the inventory to zero, and once the second transaction is updated, then Transaction one cannot buy the item.

4. Durability: If any transaction is committed, then the changes are permanent, if their is a system failure due to any reason(Power outage ,crash), System can recover the committed transaction.

   Sub-Sets of SQL

   1. Data Definition Language(DDL): It is used to define, modify and manage the structure of the database objects such as tables, indexes and schemas.

   example to create database Objects.

   create table employees

   (employeeid int primary key,

   name varchar(50),

   position varchar(50),

   salary decimal(10,2),

   gender char(1)

   )

Alter: Modifies the structure of existing objects

Alter table employees add date_of_joining date

Drop : removes database objects permanently

drop table employees

Truncate: it removes all records from any table without logging individual row deletions.

truncate table employees

2.Data Manipulation Language( DML): It handles manipulation of data within the database objects

Insert: We can add new rows or records to a table

INSERT INTO Employees (EmployeeID, Name, Position, Salary) VALUES (1, 'Ranjit', 'Manager', 75000);

Update: it modifies the existing records in a table

UPDATE Employees

SET Salary = 80000

WHERE EmployeeID = 1

Delete: We can remove specific row or record from a table.

delete from employees where employeeid = 1

3.Data Control Language(DCL): We can control access(insert, update, delete, select) and permissions to database objects

grant select , insert on employees to user1

Revoke: we can remove access and permissions of users to the database objects

revoke insert on employees from user1

4.Transaction Control Language: It manages transactions to ensure data consistency and integrity.

To Start a transaction

Begin transaction

Commit: to save all changes made in the current transaction

commit

Rollback: to undo the changes made in the current transaction

rollback

Savepoint: It sets a point within a transaction to which a rollback can be performed.

savepoint saveTran

5.Data Query Language(DQL): using SELECT we can query and fetch data from tables and views.

select name, position, salary from employees where salary > 5000

Summary:

DDL -- Define database structure -- Create, Alter, Drop, Truncate

DML-- Manipulate data within tables -- Insert, Update, Delete

DCL -- Manage access and permissions -- Grant, Revoke

TCL -- Handles transactions and data integrity -- begin, commit, rollback, savepoint

DQL -- Query and retrieve data -- Select

1.Information Rule

àData are represented only one way as values within columns within rows.

àSimple consistent and versatile

àThe basic requirement of the relational model.

2.Guaranteed access Rule

àEvery value can be accessed by providing tablename,columname and key

àAll data are uniquely identified and accessible via this identity.