MODULE - 2

Relational Model
Introduction to Relational Model
The Relational Model is a fundamental concept in Database Management Systems (DBMS) that
organizes data into tables, also known as relations. This model simplifies data storage, retrieval,
and management by using rows and columns.

• It was proposed by E.F. Codd in 1970

• It is used in modern Relational Database Management Systems (RDBMS)

Characteristics of Relational Model

• Data is represented in rows and columns called relations.

• Data is stored in tables having relationships between them called the Relational model.

• The relational model supports the operations like Data definition, Data manipulation, and
Transaction management.

• Each column has a distinct name and they are representing attributes.
• Each row represents a single entity.

Components of Relational Model

❑ Relation (Table): A set of tuples (rows) with attributes (columns).

❑ Tuple (Row): Each row in the relation is known as a tuple.

❑ Attribute (Column): A property or field of a relation.

❑ Domain: The set of possible values an attribute can take.

❑ Schema: The structure of a relation, including attribute names and types.

❑ Instance: The actual data present in a relation at a specific time.

Relational Model Constraints

Relational model constraints are rules applied to a relational database to maintain data integrity,
consistency, and accuracy by restricting the values that can be stored in relations (tables).
❑ Domain Constraint – Ensures that values in an attribute belong to a specific domain (data
type, range, or format).

Example : If we assign the datatype of attribute SRN as int, we can’t give it values other than
int datatype.

❑ Key Constraint or Uniqueness Constraints – It ensures that each row in a table is

uniquely identified using a Primary Key, which must be unique and not null.

Example: The EID column is a Primary Key, ensuring each entry is unique and not null.

❑ Entity Integrity Constraint – The Entity Integrity Constraint ensures that each table
(relation) must have a Primary Key, and that Primary Key values cannot be NULL.

Example: The given table demonstrates the Entity Integrity Constraint, which ensures
that every table has a Primary Key, and that key cannot have NULL values.

Here, the EID column serves as the Primary Key, uniquely identifying each row while
ensuring that no record has a NULL or duplicate EID, maintaining the integrity of the entity.
 ❑ Referential Integrity Constraint – It ensures a Foreign Key refers to a valid Primary Key in
another table.

Example: The given tables demonstrate the Referential Integrity Constraint, which
ensures that a foreign key in one table refers to a valid primary key in another table.

Here, the Dno (Department Number) in the Employee table is a Foreign Key, which
references the Dno (Primary Key) in the Department table. This ensures that an
employee's department exists in the Department table

Relational database schemas

• A Relational Model Schema represents the logical structure of a relational database,
defining how tables (relations) are organized and how they relate to each other.
• The schema includes:
▪ Tables (Relations) in the database
▪ Columns (Attributes) and their Data Types
▪ Constraints like Primary Key, Foreign Key, etc.

Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE,

and AGE shown in the table

Table STUDENT

Key Terms

• Attribute: Attributes are the properties that define an entity. e.g. ROLL_N0(1,2,3,4)
 • Relation Schema: A relation schema defines the structure of the relation and represents
the name of the relation with its attributes. e.g. STUDENT (ROLL_NO, NAME, ADDRESS,
PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1
relation it is called Relational Schema.
• Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples.

• Relation Instance: The set of tuples of a relation at a particular instance of time is called
a relation instance. It can change whenever there is an insertion, deletion or update in the
database.

• Degree: The number of attributes in the relation is known as the degree of the relation. The
STUDENT relation defined above has degree 5.

• Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT

relation defined above has cardinality 4.

• 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.

• Primary Key: It is used to uniquely identify the records. It is a combination of not null and
unique. One table should have only one primary key. The STUDENT table has ROLL_NO
as primary key.

Relational Schema Diagram for University Database

Student (S_id, Sname, DOB, Phone, D_id, C_id)

Department(D_id, Dname)

Courses(C_id, Cname, F_id)

Faculty(F_id, Fname, Di_d)

STUDENT
S_id Sname DOB Phone D_id C_id

DEPARTMENT
D_id Dname

Courses
C_id Cname F_id

Faculty
F_id Fname D_id
Update Operations
• Insert Operation:- The INSERT operation is used to insert new records in a table.

Syntax:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:

Inserting specific columns:

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME) VALUES (105, 'Emma’, 'Watson’);

Inserting all columns:

INSERT INTO EMPLOYEE VALUES (106, 'John', 'Doe', 55000), (107, ‘Tina', ‘Roy', 50000);

• Delete Operation:- The DELETE operation is used to delete existing records in a table.
Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM EMPLOYEE WHERE SSN = 103;

• Update Operation:- The UPDATE statement is used to modify the existing records in a
table.

Syntax:
UPDATE table_name

SET column1 = value1, column2 = value2 ……..

WHERE condition;
Example:

UPDATE EMPLOYEE SET SALARY = 60000 WHERE SSN = 102;