🛠️

Relational Model-
The Relational Model represents data and their relationships through a
collection of tables.
Each table also known as a relation consists of rows and columns.
Every column has a unique name and corresponds to a specific attribute,
while each row contains a set of related data values representing a real-world
entity or relationship.

This model is part of the record-based models which structure data in fixed-
format records each belonging to a particular type with a defined set of
attributes.

E.F. Codd introduced the Relational Model to organize data as relations or

tables

Relational Model- 1
 What is the Relational Model?
The relational model represents how data is stored in Relational Databases. A
relational database consists of a collection of tables each of which is assigned
a unique name. Consider a relation STUDENT with
attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table.

Table STUDENT

Relational Model- 2
 Key Terms
Attribute: Each column in a Table. Attributes are the properties which
define a relation.. e.g. ROLL_NO, NAME, ADDRESS.

Relation Schema: It is the logical blueprint of the relation i.e., it describes

the design and the structure of the relation. It contains the table name, its
attributes, and their types:

Tuple: Each row in the relation is known as a tuple. The above relation
contains 4 tuples one of which is shown as:

1 RAM DELHI 9455123451 18

Relation Instance: It is the collection of records present in the relation at a

given time.

Degree: The total number of attributes which in the relation is called the
degree of the relation.

The STUDENT relation defined above has degree 5.

Cardinality: Total number of rows/Tuples present in the Table.

The STUDENT relation defined above has cardinality 4.

Column: The column represents the set of values for a specific attribut

NULL Values: The value which is not known or unavailable is called a NULL
value. It is represented by NULL. e.g. PHONE of STUDENT having ROLL_NO
4 is NULL.

Relation Key-
It is an attribute or a group of attributes that can be used to uniquely identify an
entity in a table or to determine the relationship between two tables. Relation
keys can be of 6 different types:

1. Candidate Key

2. Super Key

3. Composite Key

4. Primary Key

5. Alternate Key

Relational Model- 3
 6. Foreign Key

📌 Relational Model Notation

1️⃣ Relation Schema Notation
Relation Schema (Matlab ek table ka structure) ko R(A1, A2, …, An) se
denote kiya jata hai.

Example:

STUDENT(ID, Name, Age, Course)

Yahaan STUDENT relation ka naam hai, aur (ID, Name, Age, Course) uske
attributes hain.

2️⃣ Symbols & Their Meaning

Symbol Meaning Example

Q, R, S Relation (Table) ka naam STUDENT , COURSE

Relation states (Tables ke current r(STUDENT) (Student table ka

q, r, s
tuples ka set) current data)

t, u, v Tuples (Ek row ya record) t = (101, "Ali", 21, "DBMS")

Tuple ka specific attribute access

[Link] or t[Ai] t[Name] ya [Link]
karna

3️⃣ Example Explanation

Agar STUDENT table ho:

+-----+--------+-----+-------+
| ID | Name | Age | Course|
+-----+--------+-----+-------+
| 101 | Ali | 21 | DBMS |
| 102 | Aisha | 22 | OS |
+-----+--------+-----+-------+

Toh:

Relation Schema: STUDENT(ID, Name, Age, Course)

Relation Name: STUDENT

Relational Model- 4
 Relation State: r(STUDENT) (Ye actual data ka set hai jo abhi table me stored
hai)

Tuple Representation: t = <101, "Ali", 21, "DBMS">

Accessing Specific Value:

t[Name] = "Ali"

[Link] = 21

4️⃣ Attribute Qualification Using Dot Notation

Kabhi kabhi same attribute naam different tables me ho sakta hai, toh dot
notation use hoti hai:

[Link]

[Link]

[Link]

Characteristics of the Relational Model

Data Representation: Data is organized in tables (relations), with rows
(tuples) representing records and columns (attributes) representing data
fields.

Atomic Values: Each attribute in a table contains atomic values, meaning no

multi-valued or nested data is allowed in a single cell.

Yeh ensure karta hai ki ek cell ke andar sirf ek hi value ho, multiple
values nahi ho sakti.

Unique Keys: Every table has a primary key to uniquely identify each
record, ensuring no duplicate rows.

Attribute Domain: Each attribute has a defined domain, specifying the valid
data types and constraints for the values it can hold.

Domain ka matlab hota hai valid values ka set jo kisi attribute me store
ho sakti hain. Har attribute ka apna ek fixed data type hota hai jo
batata hai ki us column me kaunse type ki values allowed hain.

Tuples as Rows: Rows in a table, called tuples, represent individual records

or instances of real-world entities or relationships.

Relational Model- 5
 Relation Schema: A table’s structure is defined by its schema, which
specifies the table name, attributes, and their domains.

Data Independence: The model ensures logical and physical data

independence, allowing changes in the database schema without affecting
the application layer.

Integrity Constraints

The model enforces rules like:

Domain constraints: Attribute values must match the specified domain.

Entity integrity: No primary key can have NULL values.

Referential integrity: Foreign keys must match primary keys in the

referenced table or be NULL.

Relational Operations: Supports operations like selection, projection, join,

union, and intersection, enabling powerful data retrieval manipulation.

Data Consistency: Ensures data consistency through constraints, reducing

redundancy and anomalies.

Set-Based Representation: Tables in the relational model are treated as

sets, and operations follow mathematical set theory principles.

Constraints in Relational Model

While designing the Relational Model, we define some conditions which must
hold for data present in the database are called Constraints.
These constraints are checked before performing any operation (insertion,
deletion, and updation ) in the database. If there is a violation of any of the
constraints, the operation will fail.

Domain Constraints (Attribute Constraint)

Domain Constraints ensure that the value of each attribute A in a tuple must be
an atomic value derived from its specified domain, dom(A). Domains are
defined by the data types associated with the attributes. Common data types
include:

Numeric types: Includes integers (short, regular, and long) for whole
numbers and real numbers (float, double-precision) for decimal values,
allowing precise calculations.

Relational Model- 6
 Character types: Consists of fixed-length (CHAR) and variable-
length (VARCHAR, TEXT) strings for storing text data of various sizes.

Boolean values: Stores true or false values, often used for flags or
conditional checks in databases.

Specialized types: Includes types

for date (DATE), time (TIME), timestamp (TIMESTAMP),
and money (MONEY), used for precise handling of time-related and
financial data.

Key Integrity
Every relation in the database should have at least one set of attributes that
defines a tuple uniquely. Those set of attributes is called keys.
e.g.; ROLL_NO in STUDENT is key. No two students can have the same roll
number. So a key has two properties:

It should be unique for all tuples.

It can’t have NULL values.

Referential Integrity Constraints

Referential Integrity constraints in DBMS are based on the concept of Foreign
Keys. A foreign key is an important attribute of a relation which should be
referred to in other relationships. Referential integrity constraint state happens
where relation refers to a key attribute of a different or same relation. However,
that key element must exist in the table.

Relational Model- 7
 🔑 Super Key, Candidate Key, and Composite
Agar table ke andar ek ya ek se zyada attributes (columns) milke kisi bhi row
(tuple) ko uniquely identify kar sakein, toh unko super key bolte hain.
Super keys ka ek proper subset hota hai jo best unique identifier banta hai,
usko Candidate Key bolte hain.
Agar Candidate Key ek hi attribute se bani ho toh simple primary key hoti hai,
lekin agar multiple attributes ka combination ho toh Composite Key kehlati
hai.

Relational Model- 8
 Anomalies in Relational Model
When we notice any unexpected behavior while working with the relational
databases, there may be a presence of too much redundancy in the data stored
in the database. This can cause anomalies in the DBMS and it can be of various
types such as:
An anomaly is an irregularity or something which deviates from the expected or
normal state. When designing databases, we identify three types of
anomalies: Insert, Update, and Delete.

Insertion Anomaly
it is the inability to insert data in the database due to the absence of other data.

For example: Suppose we are dividing the whole class into groups for a project
and the GroupNumber attribute is defined so that null values are not allowed. If
a new student is admitted to the class but not immediately assigned to a group
then this student can't be inserted into the database.
Example-

For example, in the above table if a new student named Nanda has joined the
college and he has no department affiliation as the club allows intake of
students only from second year. Then we can't insert the data of Nanda into
the table since the stu_club field cannot accept null values.

Delete Anomaly

Relational Model- 9
 It is the accidental loss of data in the database upon deletion of any other data
element.
For example: Suppose, we have an employee relation that contains the details
of the employee along with the department they are working in. Now, if a
department has one employee working in it and we remove the information of
this employee from the table, there will be a loss of data related to the
department also. This can lead to data inconsistency.

Suppose, for instance, the college at some point closes the club crypto, then
deleting the rows that contain s_club as crypto would also delete the
information of student Mukesh since he belongs only to this department.

Modification/Update Anomalies
It is the data inconsistency that arises from data redundancy and partial
updation of data in the database. For example: Suppose, while updating the
data into the database duplicate entries were entered. Now, if the user does not
realize that the data is stored redundantly after updation, there will be data
inconsistency in the database.

NOTE-All these anomalies can lead to unexpected behavior and

inconvenience for the user. These anomalies can be removed with the help of
a process known as normalization.

Relational Model- 10
 Codd Rules in DBMS-
Edgar F. Codd, the creator of the relational model proposed 13 rules known
as Codd Rules that state:
For a database to be considered as a perfect relational database, it must follow
the following rules:
Agar koi
DBMS in rules ko follow karta hai, tabhi usko "Relational DBMS" kaha ja sakta
hai.
📌Codd ke
12 rules officially define kiye gaye the, lekin agar Rule 0 ko include karein, toh
total 13 ho jaate hain

🛠 Codd's 13 Rules for RDBMS

🔹 Rule 0 se start hota hai jo ek foundational rule hai, aur Rule 1 se 12 tak
functional rules hain.

Rule 0: Foundation Rule (Basic Rule)

For any system that is advertised as, or claimed to be, a relational data base
management system, that system must be able to manage data bases entirely
through its relational capabilities.
"Agar koi system apne aap ko Relational DBMS bolta hai, toh usko pure
relational capabilities ke through database manage karna aana chahiye.
🔹 Matlab
koi bhi DBMS sirf naam ka relational nahi ho sakta, usko saare relational
principles follow karne chahiye jaise:

Data tabular format me hona chahiye.

Relational integrity constraints (Primary Key, Foreign Key, etc.) maintain

hone chahiye.

Relational operations (SELECT, INSERT, DELETE, UPDATE, JOIN, etc.) fully

supported hone chahiye.

🚀 Example:
Agar koi database system sirf tables use karta hai but wo relational operations
(JOIN, FOREIGN KEYS, CONSTRAINTS) support nahi karta, toh wo "Relational

Relational Model- 11
 DBMS" nahi keh sakta apne aap ko!

Yeh Rule 0 sabse important hai kyunki yahi guarantee karta hai ki ek DBMS
actually "relational" hai ya nahi! 😎🔥
Rule 1: Information Rule
The data stored in a database, may it be user data or metadata, must be a value
of some table cell. Everything in a database must be stored in a table format.
"Database me jo bhi data store hoga, chahe wo user data ho ya metadata
(database ka structure, constraints, etc.), sab table ke cells me store hona
chahiye."
🔹 Matlab:
Saara data tabular format me store hoga.

Koi bhi extra storage method (file system, pointers, arrays) use nahi hoga.

Metadata bhi tables ke andar store hoga, alag se kisi hidden storage me
nahi.

🚀 Example:
✔ Relational DBMS me:
Agar aap MySQL ya PostgreSQL use kar rahe ho, toh table
"INFORMATION_SCHEMA" ya "pg_catalog" me metadata bhi ek table ke
format me stored hota hai.
❌ Non-Relational DBMS me:
Agar ek system files ya objects me data store kare bina tables use kiye, toh
wo Relational DBMS nahi hoga.

👉 Codd ka yeh pehla rule confirm karta hai ki ek true relational database me
sab kuch tables ke andar hi store hoga! 🔥😎

Rule 2 : Guaranteed Access Rule

Every single data element (value) is guaranteed to be accessible logically with a
combination of table-name, primary-key (row value), and attribute-name
(column value). No other means, such as pointers, can be used to access data.

Matlab har ek data element (value) ko access karne ka sirf ek structured

tarika hoga, jo table-name, primary key, aur attribute-name ke combination se
hoga. Koi pointers ya memory addresses use nahi honge!

Relational Model- 12
 🧐 Iska Matlab Kya Hai?
Agar relational database ko follow karna hai, toh:

✔ Har ek data item ka ek unique identifier hoga.

✔ Hum sirf table structure ke through data access karenge (pointer ya kisi
aur shortcut se nahi).

✅ Example (Sahi Tarika - Relational Model)

Emp_ID Name Salary

101 Ali 50,000

102 Aisha 60,000

Agar mujhe Ali ka salary access karna hai, toh:

Table Name: Employee

Primary Key: Emp_ID = 101

Attribute Name: Salary

Query:

SELECT Salary FROM Employee WHERE Emp_ID = 101;

Output: 50,000

Rule 3: Systematic Treatment of Null Values

The NULL values in a database must be given a systematic and uniform
treatment. This is a very important rule because a NULL can be interpreted as
one the following − data is missing, data is not known, or data is not applicable.
Matlab, database me NULL values ka ek systematic aur consistent treatment
hona chahiye. NULL ka matlab alag-alag situations me alag ho sakta hai,
jaise:

✔ Data missing hai (kisi ne input nahi diya)

✔ Data pata nahi hai (unknown)
✔ Data applicable hi nahi hai (not applicable)
Agar database NULL values ko achhe se handle nahi karega, toh data
inconsistency ya errors ho sakte hain! 😵

Relational Model- 13
 🧐 Example
Emp_ID Name Salary Bonus Department

101 Ali 50,000 5,000 HR

102 Aisha 60,000 NULL Sales

103 Ahmed NULL 3,000 IT

104 Sana 70,000 NULL NULL

🔹 Aisha ka Bonus NULL hai → iska matlab ho sakta hai ki bonus diya hi nahi
gaya ya bonus ka data missing hai.

🔹 Ahmed ka Salary NULL hai → iska matlab ho sakta hai ki uska salary data
available nahi hai ya uska salary process nahi hua.
🔹 Sana ka Department NULL hai → ho sakta hai ki woh kisi particular
department se belong nahi karti!

✅ Sahi Tarika (Consistent Handling of NULL Values)

Database management system ko NULL values ko proper tarike se handle
karna chahiye, jaise:

✔ Default Values ya Placeholders dene ka option hona chahiye.

✔ Queries likhte time NULL ka sahi use hona chahiye.
✔ Aggregation functions (SUM, AVG) me NULL ko dhyan se handle karna
chahiye!

Example Query: NULL ko handle karna (COALESCE function)

SELECT Name, COALESCE(Salary, 0) AS Salary FROM Employee;

Yeh query agar NULL salary values ko detect karegi, toh unko 0 set kar degi,
taki calculations me dikkat na aaye!

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online
catalog, known as data dictionary, which can be accessed by authorized users.
Users can use the same query language to access the catalog which they use
to access the database itself.

Relational Model- 14
 Matlab, database ka pura structure (schema, tables, constraints, data types,
etc.) ek online catalog me store hona chahiye, jise data dictionary bhi kehte
hain.

Aur users is catalog ko access kar sakein wahi SQL ya query language use
karke jo woh database access karne ke liye use karte hain.

🧐 Data Dictionary Kya Hota Hai?

Ek special table ya metadata store, jisme database ka pura structure ka
description hota hai.
Isme store hoti hain:

✔ Tables ka naam aur structure

✔ Columns ka data type
✔ Primary & Foreign Keys
✔ Indexes, Constraints, Triggers, Procedures
✔ Users aur Permissions ka record
Matlab, database ke khud ke structure ki bhi information database me hi store
honi chahiye!

Explanation: Database ka catalog ek table ki tarah hona chahiye, jisme

metadata (table structure, columns, etc.) store ho.

📝 Example
Agar ek database me Employee table hai, toh uska structure data dictionary me
kuch is tarah store hoga:

Table Name Column Name Data Type Constraint

Employee Emp_ID INT Primary Key

Employee Name VARCHAR NOT NULL

Employee Salary DECIMAL NULL Allowed

Employee Dept_ID INT Foreign Key

Yeh metadata ek online catalog me store hoga, jisse authorized users query
karke dekh sakte hain.

Rule 5: The Comprehensive Data Sublanguage Rule

Relational Model- 15
 A relational system may support several languages and various modes of
terminal use (for example, the fill-in-the-blanks mode). However, there must be
at least one language whose statements are expressible, per some well-
defined syntax, as character strings and that is comprehensive in supporting all
of the following items:

Data definition.

View definition.

Data manipulation (interactive and by program).

Integrity constraints.

Authorization.

Transaction boundaries (begin, commit and rollback).

Matlab, database ko sirf ek structured query language (SQL ya koi aur query
language) ke through hi access karna chahiye. Agar database ko bina kisi
structured query language ke access kiya ja sakta hai (jaise direct file access
ya pointers se), toh yeh relational model ka violation hoga ❌.
Rule 6: View Updating Rule
All views in a relational database must be updatable. If a view can be derived
from a table, it should be possible to update the base table through that view.

Explanation: Agar kisi view ko kisi table se derive kiya gaya hai, toh us view ko
update karne par base table ko bhi update hona chahiye.

Example: Agar hum student view bana rahe hain jo ID aur Name columns
dikhata hai, toh agar hum view ko update karte hain, toh base table bhi update
ho jayega.

Rule 7: High-Level Insert, Update, and Delete

The capability of handling a base relation or a derived relation as a single
operand applies not only to the retrieval of data but also to the insertion, update
and deletion of data.
Explanation: Database ko aise design karna chahiye ki insert, update aur
delete operations ek saath multiple rows pe execute ho sakein.

Example: SQL mein hum ek query se multiple rows ko update kar sakte hain,
jaise:

Relational Model- 16
 UPDATE STUDENT SET Age = 20 WHERE Course = 'DBMS' .

Rule 8: Physical Data Independence

Changes in the physical storage of data should not affect the logical structure
or operations on data.
Explanation: Agar physical storage (jaise hard drive, indexing methods)
change hota hai, toh usse logical data ya query execution par koi impact nahi
hona chahiye.

Example: Agar data ko different disk location pe store kiya gaya ho, tab bhi
query ka result wahi rahega.

Rule 9: Logical Data Independence

Changes in the logical structure of data (such as adding or removing attributes)
should not affect application programs.

Explanation: Agar hum logical structure (jaise columns ko add ya remove

karna) change karte hain, toh applications ko reprogramming nahi karni
chahiye.
Example: Agar hum STUDENT table mein new column add karte hain, toh
applications ko us column ke bina bhi work kar paana chahiye.

Rule 10: Integrity Independence

A database must be independent of the application that uses it. All its integrity
constraints can be independently modified without the need of any change in
the application. This rule makes a database independent of the front-end
application and its interface.
Integrity constraints (jaise primary key, foreign key, unique constraints, check
constraints, etc.) ko database ke andar hi define karna chahiye, na ki
application code me.

Matlab constraints database ke part hone chahiye, taaki unhe modify kar
sakein bina application code change kiye.

Rule 11: Distribution Independence

The end-user must not be able to see that the data is distributed over various
locations. Users should always get the impression that the data is located at
one site only. This rule has been regarded as the foundation of distributed
database systems

Relational Model- 17
 Agar database multiple locations pe distributed hai, toh bhi user ko yeh
dikhna nahi chahiye.

User ko hamesha aisa lagna chahiye ki data ek hi jagah pe hai, chahe woh
internally multiple servers pe distribute ho.

Rule 12: Non-Subversion Rule

If a system supports lower-level access to data (such as through a procedural
language), it must not bypass the integrity constraints imposed by the relational
model.

Agar ek database high-level query language (like SQL) provide karta hai aur
low-level access bhi possible hai (jaise ke file system ya pointers), toh low-
level interface security rules ko bypass nahi kar sakti.
Matlab agar koi user ya program low-level access ka use kare, toh bhi wo
security ya integrity constraints tod na sake.

1️⃣ Database ko unauthorized access se bachana.

2️⃣ High-level aur low-level access ke rules consistent rakhna.
3️⃣ Security aur integrity constraints ko ensure karna.
Codd’s 12 rules were established to specify a set of criteria that could be used
to determine if a DBMS could be considered relational or not. If a DBMS
complied with all of Codd’s rules, then it could be considered a relational DBMS
(i.e. RDBMS). Otherwise it couldn’t.

Relational Model- 18