The Revolutionary Relational Data Model & E.F. Codd’s Groundbreaking Rules

In the previous article we discussed some of the data models like hierarchy, object, ER, there we left out with one data model that is Relational data model.

Brief History of the Relational Data Model

The relational data model was first introduced in 1970 by a computer scientist and a mathematician named Edgar Frank Codd, also called Ted Codd. He was working for IBM when he invented this relational model.

In this Relational Data model, he gave a simple way of storing data as well as retrieving data via rows and columns in a table. That is, Relational Data model was all about organizing data into tables with rows and columns.

This contribution of Ted Codd had produced a dramatic or a great change. Though he has made many other contributions to computer science, this invention of his was his biggest achievement.

The relational data model uses the concept of mathematical relations and theoretically, it is based on Set Theory. Any DBMS which follows relational model it becomes as RDBMS.

The first commercial implementations of the relational model were by Oracle, and slowly, even IBM started to implement the same. SQL/DS (Structured Query Language Data System) was IBM’s first commercial relational database management system (RDBMS).

The current popular RDBMS (Relational Database Management Systems) are:

• SQL Server and Access from Microsoft
• DB2 and Informix from IBM, etc.

SQL (Structured Query Language) became the standard language for these commercial RDBMS.

In the later chapters, we will be learning SQL query language in detail.

Basic Terminologies in RDBMS

As mentioned earlier, a relational database model organizes data in the form of tables.

In other words, a relational model represents data as a collection of tables.

Table Representation (Example)

Roll_Number	Name	Age	Course
101	Alice	20	DBMS
102	Bob	21	SQL
103	Charlie	22	Python

Here in RDBMS, a table is also called a relation.

So, we can say that a relational model represents data as a collection of relations.

In the above table,

• Student is the relation name (table name).
• Each row in the table is called a tuple.
• Each column (header) is called an attribute (field).

For example, in the student relation,

• We have two tuples (rows).
• We have four attributes (columns).

---

1. Creating a Table in SQL

To create the student table:

CREATE TABLE Student (
    Roll_Number INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Course VARCHAR(50)
);

2. Inserting Data into a Table

INSERT INTO Student (Roll_Number, Name, Age, Course) 
VALUES 
(101, 'Alice', 20, 'DBMS'),
(102, 'Bob', 21, 'SQL'),
(103, 'Charlie', 22, 'Python');

---

Domain in Relational Model

A Domain in mathematics is a set of possible values that you can input.

Similarly, in a relational database, a domain is a set of values that are allowed for an attribute. These values should be atomic (each value is indivisible).

For example,

• The attribute “Name” should have string values representing names of people.
• The attribute “Age” should have integer values between 20 and 70.

3. Domain Constraint Example

ALTER TABLE Student 
ADD CONSTRAINT AgeCheck CHECK (Age BETWEEN 18 AND 30);

The domain constraint ensures that only values between 18 and 30 are allowed for the Age attribute.

---

Relation Schema

A relation schema describes the structure of a relation (table).

A relation schema is made up of:

• Relation name (denoted by capital R)
• List of attributes (denoted as A1, A2, A3, etc.)

4. Relation Schema Representation

Student(Roll_Number: INT, Name: VARCHAR(50), Age: INT, Course: VARCHAR(50))

---

Degree (Arity) of a Relation

The degree (arity) of a relation is the number of attributes (columns) in a relation schema.

In the Student table, the number of attributes = 4.

---

Cardinality of a Relation

The cardinality of a relation is the number of tuples (rows) in a table.

For example, in the Student table, the number of tuples (rows) = 3.

---

Relational Database Schema

A relational database schema consists of multiple relation schemas and integrity constraints.

Example: A Company Database Schema consists of:

• Employee Table
• Department Table
• Project Table

5. Creating a Company Database Schema

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    DepartmentID INT
);

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50),
    ManagerID INT
);

CREATE TABLE Project (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

---

Relation State (Instance) at Different Time Intervals

A relation instance (state) is a set of tuples at a given moment in time.

At Time T1 (Before Deleting a Row)

Roll_Number	Name	Age	Course
101	Alice	20	DBMS
102	Bob	21	SQL
103	Charlie	22	Python

At Time T2 (After Deleting a Row)

sqlCopyEditDELETE FROM Student WHERE Roll_Number = 103;

Roll_Number	Name	Age	Course
101	Alice	20	DBMS
102	Bob	21	SQL

Thus, at Time T2, the relation instance has only two tuples instead of three.

---

Rules of EF Codd

1. The data inserted into a cell must be a single value.
   Correct Example:
   CREATE TABLE Student ( RollNo INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK (Age > 0) -- Ensuring a valid age );
   Incorrect Example:
   INSERT INTO Students (RollNo, Name, Age) VALUES (500, 'Jorge, Anandh', 20);
-- Here, 'John, Alex' is multiple values in one cell, which is incorrect.

Incorrect Table (❌ Wrong)	Common column	Correct Table (✅ Right)
Table Name: Student		Table Name: Students
RollNo	Name	Age
500	Jorge, Anandh	20

Issues in the Incorrect Table (Left Side)

• ❌ The Name column contains multiple values in one cell ('Jorge, Anandh'), which is not allowed in the relational model.

Fixes in the Correct Table (Right Side)

• ✅ Each cell contains a single atomic value, ensuring proper normalization.

---

2. According to EF Codd, we can store data in multiple tables, and if necessary, we can establish the connection between two or more tables using key attributes.
   
   Example of Multiple Tables with Relationships (Using Foreign Key)
   
   CREATE TABLE Employee ( eId INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DepartmentID INT ); CREATE TABLE Department ( DeptID INT PRIMARY KEY, DeptName VARCHAR(50) NOT NULL ); ALTER TABLE Employee ADD CONSTRAINT FK_Dept FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID);
3. The Employee table stores employee details.
4. The Department table stores department details.
5. The Foreign Key (DepartmentID) links both tables.

---

3. In RDBMS, every kind of information is stored in table format, including metadata.✅ Example:
   • Metadata is system-generated, like column names, data types, constraints, etc.We can retrieve metadata using SQL:
   SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employee';

---

4. Every data should get validated before entering into the cell by assigning:
   • Datatypes (Mandatory) and Constraints (Optional)
   Example Table with Datatypes & Constraints
   CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100) NOT NULL, Price DECIMAL(10,2) CHECK (Price > 0), Stock INT CHECK (Stock >= 0) );

---

Why Datatypes are Mandatory?

eId	Name	Salary	Phone Number
1	Karthik	40,000	1234567890
Reshma (Entered Name instead of eId)	Prudvi	Fifty thousand(it should be number not String)	1029384756
3	2 (Entered eId instead of Name)	-20000(Salary can’t be in negative)	56473829105
4	Gopla	30,000	100299388477 (Ph number should be 10 letters)

Correct Table with Proper Datatypes & Constraints

CREATE TABLE EmployeeDetails (
    eId INT PRIMARY KEY,        
    Name VARCHAR(50) NOT NULL,   
    Salary INT CHECK (Salary > 0),  
    Phone_Number CHAR(10) CHECK (LENGTH(Phone_Number) = 10) 
);

• Ensures that eId is a number.
• Ensures Salary is always positive.
• Ensures Phone Number is exactly 10 digits.

Conclusion

In this article, we have discussed the Relational Data Model, including its brief history and the contributions of E.F. Codd. We have also explored key terminologies in Relational Database Management Systems (RDBMS), the concept of domains in a Relational Data Model, and the structure of a Relational Database Schema. Understanding the Relational Data Model is essential for effectively designing and managing relational databases, ensuring data integrity, and optimizing database performance.