Relational Model in DBMS

The Relational Model organizes data using tables (relations) consisting of rows and
columns. Each column represents a specific attribute with a unique name, while each row holds
data about a real-world entity or relationship. As a record-based model, it stores data in fixed-
format records with defined attributes.

Introduced by E.F. Codd, the relational model transforms conceptual designs from ER
diagrams into implementable structures. These structures are used in relational database systems
like Oracle SQL and MySQL.

What is the Relational Model?

The relational model represents how data is stored and managed in Relational Databases.
Data is organized into tables, each known as a relation, consisting of rows (tuples) and columns
(attributes). Each row represents an entity or record, and each column represents a particular
attribute of that entity. A relational database consists of a collection of tables each of which is
assigned a unique name.
For example, consider a relation STUDENT with
attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table.
Key Terms in the Relational Model
1. Attribute: Attributes are the properties that define an entity.
● Example: ROLL_NO, NAME, ADDRESS etc.
2. Relation Schema: A relation schema defines the structure of the relation and represents the
name of the relation with its attributes.
● Example: 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.
3. Tuple: A Tuple represents a row in a relation. Each tuple contains a set of attribute values that
describe a particular entity.
● Example: (1, RAM, DELHI, 9455123451, 18) is a tuple in the STUDENT table.
4. 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.
5. Degree: The number of attributes in the relation is known as the degree of the relation.
● Example: The STUDENT relation has a degree of 5, as it has 5 attributes.
6. Cardinality: The number of tuples in a relation is known as cardinality.
● Example: The STUDENT relation defined above has cardinality 4.
7. Column: The column represents the set of values for a particular attribute.
● Example: The column ROLL_NO is extracted from the relation STUDENT.
8. NULL Values: The value which is not known or unavailable is called a NULL value. It is
represented by NULL.
● Example: PHONE of STUDENT having ROLL_NO 4 is NULL.
Types of Keys in the Relational Model
1. Primary Key:
A Primary Key uniquely identifies each tuple in a relation. It must contain unique values and
cannot have NULL values. Example: ROLL_NO in the STUDENT table is the primary key.
2. Candidate Key
A Candidate Key is a set of attributes that can uniquely identify a tuple in a relation. There can
be multiple candidate keys, and one of them is chosen as the primary key.
3. Super Key
A Super Key is a set of attributes that can uniquely identify a tuple. It may contain extra
attributes that are not necessary for uniqueness.
4. Foreign Key
A Foreign Key is an attribute in one relation that refers to the primary key of another relation. It
establishes relationships between tables. Example: BRANCH_CODE in the STUDENT table is
a foreign key that refers to the primary key BRANCH_CODE in the BRANCH table.
5. Composite Key
A Composite Key is formed by combining two or more attributes to uniquely identify a
tuple. Example: A combination of FIRST_NAME and LAST_NAME could be a composite key
if no one in the database shares the same full name.
Relational Model Notation
● Relation schema R of degree n is denoted by by R(A1, A2, ...,An).
● Uppercase letters Q, R, S denote relation names.
● Lowercase letters q, r, s denote relation states.
● Letters t, u, v denote tuples.
● In general, the name of a relation schema such as STUDENT also indicates the current set of
tuples in that relation.
● An attribute A can be qualified with the relation name R to which it belongs by using the dot
notation R.A for example, STUDENT.Name or STUDENT.Age.
● An n-tuple t in a relation r(R) is represented as t=<v1,v2,...,vn> where vi is the value
corresponding to the attribute Ai. The value vi for attribute Ai in tuple t can be accessed using
t[Ai] or t.Ai.
Characteristics of the Relational Model
1. Data Representation: Data is organized in tables (relations), with rows (tuples) representing
records and columns (attributes) representing data fields.
2. Atomic Values: Each attribute in a table contains atomic values, meaning no multi-valued or
nested data is allowed in a single cell.
3. Unique Keys: Every table has a primary key to uniquely identify each record, ensuring no
duplicate rows.
4. Attribute Domain: Each attribute has a defined domain, specifying the valid data types and
constraints for the values it can hold.
5. Tuples as Rows: Rows in a table, called tuples, represent individual records or instances of
real-world entities or relationships.
6. Relation Schema: A table’s structure is defined by its schema, which specifies the table name,
attributes, and their domains.
7. Data Independence: The model ensures logical and physical data independence, allowing
changes in the database schema without affecting the application layer.
8. Integrity Constraints: The model enforces rules like:
9. Domain constraints: Attribute values must match the specified domain.
10. Entity integrity: No primary key can have NULL values.
11. Referential integrity: Foreign keys must match primary keys in the referenced table or be
NULL.
12. Relational Operations: Supports operations like selection, projection, join, union, and
intersection, enabling powerful data retrieval manipulation.
13. Data Consistency: Ensures data consistency through constraints, reducing redundancy and
anomalies.
14. 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.
1. Domain Constraints
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.
● 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.
2. 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.
3. Referential Integrity Constraints
When one attribute of a relation can only take values from another attribute of the same relation
or any other relation, it is called referential integrity. Let us suppose we have 2 relations
Table STUDENT

BRANCH_CO
ROLL_NO NAME ADDRESS PHONE AGE DE

1 RAM DELHI 9455123451 18 CS

2 RAMESH GURGAON 9652431543 18 CS

3 SUJIT ROHTAK 9156253131 20 ECE

4 SURESH DELHI 18 IT

Table BRANCH
BRANCH_CODE BRANCH_NAME

CS COMPUTER SCIENCE

IT INFORMATION TECHNOLOGY

ELECTRONICS AND COMMUNICATION

ECE
ENGINEERING
 CV CIVIL ENGINEERING

Explanation: BRANCH_CODE of STUDENT can only take the values which are present in
BRANCH_CODE of BRANCH which is called referential integrity constraint. The relation
which is referencing another relation is called REFERENCING RELATION (STUDENT in this
case) and the relation to which other relations refer is called REFERENCED RELATION
(BRANCH in this case).

Anomalies in the Relational Model

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.
1. Insertion Anomaly in Referencing Relation
We can’t insert a row in REFERENCING RELATION if referencing attribute’s value is
not present in the referenced attribute value. e.g.; Insertion of a student with BRANCH_CODE
‘ME’ in STUDENT relation will result in an error because ‘ME’ is not present in
BRANCH_CODE of BRANCH.
2. Deletion/ Updation Anomaly in Referenced Relation:
We can’t delete or update a row from REFERENCED RELATION if the value of
REFERENCED ATTRIBUTE is used in the value of REFERENCING ATTRIBUTE.
Example: If we try to delete a tuple from BRANCH having BRANCH_CODE ‘CS’, it will
result in an error because ‘CS’ is referenced by BRANCH_CODE of STUDENT, but if we try to
delete the row from BRANCH with BRANCH_CODE CV, it will be deleted as the value is not
been used by referencing relation. It can be handled by the following method:
3. On Delete Cascade
It will delete the tuples from REFERENCING RELATION if the value used by
REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION. e.g.; if we delete a
row from BRANCH with BRANCH_CODE ‘CS’, the rows in STUDENT relation with
BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted.
4. On Update Cascade
It will update the REFERENCING ATTRIBUTE in REFERENCING RELATION if the
attribute value used by REFERENCING ATTRIBUTE is updated in REFERENCED
RELATION. e.g., if we update a row from BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the
rows in STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be
updated with BRANCH_CODE ‘CSE’.
5. Super Keys
Any set of attributes that allows us to identify unique rows (tuples) in a given relationship
is known as super keys. Out of these super keys, we can always choose a proper subset among
these that can be used as a primary key. Such keys are known as Candidate keys. If there is a
combination of two or more attributes that are being used as the primary key then we call it a
Composite key.
Codd Rules in Relational Model
E.F. Codd, the creator of the relational model, proposed 12 rules (known as Codd’s 12
Rules) that define what constitutes a relational database system. These rules emphasize the
importance of data independence, consistency, and structure.
Key Codd’s Rules:
● Rule 1: The information rule – All information in a relational database is represented
logically in tables (relations).
● Rule 2: The guaranteed access rule – Every data element is accessible by using a combination
of table name, primary key, and attribute name.
● Rule 5: The powerful language rule – A relational DBMS should have a comprehensive
language capable of expressing all relational queries.
Advantages of the Relational Model
1. Simple model: Relational Model is simple and easy to use in comparison to other languages.
2. Flexible: Relational Model is more flexible than any other relational model present.
3. Secure: Relational Model is more secure than any other relational model.
4. Data Accuracy: Data is more accurate in the relational data model.
5. Data Integrity: The integrity of the data is maintained in the relational model.
6. Operations can be Applied Easily: It is better to perform operations in the relational model.
Disadvantages of the Relational Model
1. Performance: The relational model can experience performance issues with very large
databases.
2. Complexity for Complex Data: The model struggles with hierarchical or complex data
relationships, which might be better handled with other models like the Graph or Document
model.
3. Normalization Overhead: Extensive use of normalization can result in complex queries and
slower performance.
A Logical View of Data

Relational database model enables logical representation of the data and its relationships
Logical simplicity yields simple and effective database design methodologies Facilitated by the
creation of data relationships based on a logical construct called a relation.

Keys:

What are Keys in DBMS?

KEYS in DBMS is an attribute or set of attributes which helps you to identify a

row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help
you uniquely identify a row in a table by a combination of one or more columns in that table.
Key is also helpful for finding unique record or row from the table. Database key is also helpful
for finding unique record or row from the table.

Example:

Employee ID FirstName LastName

11 Andrew Johnson

22 Tom Wood
 33 Alex Hale

In the above-given example, employee ID is a primary key because it uniquely

identifies an employee record. In this table, no other employee can have the same employee
ID.

Why we need a Key?

Here are some reasons for using sql key in the DBMS system.

● Keys help you to identify any row of data in a table. In a real-world application, a table could
contain thousands of records. Moreover, the records could be duplicated. Keys in RDBMS
ensure that you can uniquely identify a table record despite these challenges.
● Allows you to establish a relationship between and identify the relation between tables
● Help you to enforce identity and integrity in the relationship.

Types of Keys in DBMS (Database Management System)

There are mainly Eight different types of Keys in DBMS and each key has it’s different
functionality:

1. Super Key
2. Primary Key
3. Candidate Key
4. Alternate Key
5. Foreign Key
6. Compound Key
7. Composite Key
8. Surrogate Key

Let’s look at each of the keys in DBMS with example:

● Super Key – A super key is a group of single or multiple keys which identifies rows in a table.
● Primary Key – is a column or group of columns in a table that uniquely identify every row in
that table.
● Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a
super key with no repeated attributes.
● Alternate Key – is a column or group of columns in a table that uniquely identify every row in
that table.
● Foreign Key – is a column that creates a relationship between two tables. The purpose of
Foreign keys is to maintain data integrity and allow navigation between two different instances
of an entity.
● Compound Key – has two or more attributes that allow you to uniquely recognize a specific
record. It is possible that each column may not be unique by itself within the database.
● Composite Key – is a combination of two or more columns that uniquely identify rows in a
table. The combination of columns guarantees uniqueness, though individual uniqueness is not
guaranteed.
● Surrogate Key – An artificial key which aims to uniquely identify each record is called a
surrogate key. These kind of key are unique because they are created when you don’t have any
natural primary key.

What is the Super key?

A superkey is a group of single or multiple keys which identifies rows in a table. A Super
key may have additional attributes that are not needed for unique identification.

Example:

EmpSSN EmpNum Empname

9812345098 AB05 Shown

9876512345 AB06 Roslyn

199937890 AB07 James

 In the above-given example, EmpSSN and EmpNum name are superkeys.

What is a Primary Key?

PRIMARY KEY in DBMS is a column or group of columns in a table that uniquely
identify every row in that table. The Primary Key can’t be a duplicate meaning the same value
can’t appear more than once in the table. A table cannot have more than one primary key.
Rules for defining Primary key:

● Two rows can’t have the same primary key value

● It must for every row to have a primary key value.
● The primary key field cannot be null.
● The value in a primary key column can never be modified or updated if any foreign key refers to
that primary key.

Example:

In the following example, StudID is a Primary Key.

StudID Roll No First Name LastName Email

1 11 Tom Price abc@gmail.com

2 12 Nick Wright xyz@gmail.com

3 13 Dana Natan mno@yahoo.com

What is the Alternate key?

ALTERNATE KEYS is a column or group of columns in a table that uniquely identify
every row in that table. A table can have multiple choices for a primary key but only one can be
set as the primary key. All the keys which are not primary key are called an Alternate Key.

Example:
 In this table, StudID, Roll No, Email are qualified to become a primary key. But since StudID is
the primary key, Roll No, Email becomes the alternative key.

StudID Roll No First Name LastName Email

1 11 Tom Price abc@gmail.com

2 12 Nick Wright xyz@gmail.com

3 13 Dana Natan mno@yahoo.com

What is a Candidate Key?

CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples in a table.
Candidate Key is a super key with no repeated attributes. The Primary key should be selected
from the candidate keys. Every table must have at least a single candidate key. A table can have
multiple candidate keys but only a single primary key.
Properties of Candidate key:

● It must contain unique values

● Candidate key in SQL may have multiple attributes
● Must not contain null values
● It should contain minimum fields to ensure uniqueness
● Uniquely identify each record in a table

Candidate key Example: In the given table Stud ID, Roll No, and email are candidate
keys which help us to uniquely identify the student record in the table.

StudID Roll No First Name LastName Email

1 11 Tom Price abc@gmail.com

2 12 Nick Wright xyz@gmail.com

3 13 Dana Natan mno@yahoo.com

 Candidate Key in DBMS

What is the Foreign key?

FOREIGN KEY is a column that creates a relationship between two tables. The purpose
of Foreign keys is to maintain data integrity and allow navigation between two different
instances of an entity. It acts as a cross-reference between two tables as it references the primary
key of another table.
Example:

DeptCode DeptName

001 Science

002 English

005 Computer

Teacher ID Fname Lname

B002 David Warner

B017 Sara Joseph

 B009 Mike Brunton

In this key in dbms example, we have two table, teach and department in a school. However,
there is no way to see which search work in which department.

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a
relationship between the two tables.

Teacher ID DeptCode Fname Lname

B002 002 David Warner

B017 002 Sara Joseph

B009 001 Mike Brunton

This concept is also known as Referential Integrity.

What is the Compound key?

COMPOUND KEY has two or more attributes that allow you to uniquely recognize a
specific record. It is possible that each column may not be unique by itself within the database.
However, when combined with the other column or columns the combination of composite keys
become unique. The purpose of the compound key in database is to uniquely identify each record
in the table.
Example:

OrderNo ProductID Product Name Quantity

B005 JAP102459 Mouse 5

B005 DKT321573 USB 10

B005 OMG446789 LCD Monitor 20

 B004 DKT321573 USB 15

B002 OMG446789 Laser Printer 3

In this example, OrderNo and ProductID can’t be a primary key as it does not uniquely
identify a record. However, a compound key of Order ID and Product ID could be used as it
uniquely identified each record.

What is the Composite key?

COMPOSITE KEY is a combination of two or more columns that uniquely identify
rows in a table. The combination of columns guarantees uniqueness, though individually
uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.
The difference between compound and the composite key is that any part of the
compound key can be a foreign key, but the composite key may or maybe not a part of the
foreign key.

What is a Surrogate key?

SURROGATE KEYS is An artificial key which aims to uniquely identify each record is
called a surrogate key. This kind of partial key in dbms is unique because it is created when you
don’t have any natural primary key. They do not lend any meaning to the data in the table.
Surrogate key in DBMS is usually an integer. A surrogate key is a value generated right before
the record is inserted into a table.

Fname Lastname Start Time End Time

Smith 09:00 18:00

Anne

Jack Francis 08:00 17:00

Anna McLean 11:00 20:00

Shown Willam 14:00 23:00

Above, given example, shown shift timings of the different employee. In this example, a
surrogate key is needed to uniquely identify each employee.
 Surrogate keys in sql are allowed when

● No property has the parameter of the primary key.

● In the table when the primary key is too big or complicated.

Difference Between Primary key & Foreign key

Following is the main difference between primary key and foreign key:

Primary Key Foreign Key

Helps you to uniquely identify a record in the It is a field in the table that is the primary key of
table. another table.

Primary Key never accept null values. A foreign key may accept multiple null values.

Primary key is a clustered index and data in the A foreign key cannot automatically create an index,
DBMS table are physically organized in the clustered or non-clustered. However, you can
sequence of the clustered index. manually create an index on the foreign key.

You can have the single Primary key in a table. You can have multiple foreign keys in a table.

INTEGRITY RULES:

Integrity constraints:

Integrity constraints in a database management system (DBMS) are rules that help to
keep the data in the database accurate, consistent, and reliable. Every time there is an insertion,
deletion, or update of data in the database, it is the responsibility of these integrity constraints to
maintain the integrity of the data.
‍
Integrity constraints are a set of rules that ensure all the information stored in the
database follows defined formats, relationships, and conditions. Integrity constraints help prevent
accidental data errors, but protecting against unauthorised users is handled by security features
like access control.

Why are integrity constraints important?

Integrity constraints play an essential role in DBMS. There are various reasons why integrity
constraints is crucial. Let’s see
‍
 ● Data stays accurate: Just imagine entering data in the wrong format. With integrity
constraints, errors get flagged immediately. Without these rules, we might end up with
incorrect information. This saves lots of time and possibe headaches later.

● Data consistent: For example, if one table references another table constraints make sure
that the relationships stay valid. Reduce the chances of having conflicts or duplicate
records in the data entries.

● Data reliability: By preventing invalid data entries we trust that the data we have holds
only valid information. This is important for decision-making and reporting.

● Data validation: They enforce specific rules for data entry guaranteeing every piece of
information meets our standards. This means our database remains a depe ndable
resource.
What are Integrity Constraints ?
Integrity constraints in a Database Management System (DBMS) are rules that help keep
the data in a database accurate, consistent and reliable. They act like a set of guidelines that
ensure all the information stored in the database follows specific standards.
For example:
● Making sure every customer has a valid email address.
● Ensuring that an order in the database is always linked to an existing customer.
These rules prevent mistakes, such as adding incomplete or incorrect data, and make sure the
database is secure and well-organized. In simple terms, integrity constraints keep the database
neat, correct and trustworthy
Types of Integrity Constraints

NOT NULL PRIMARY KEY

CONSTRAINT CONSTRAINT

CHECK UNIQUE KEY

CONSTRAINT CONSTRAINT
Domain Constraints
Domain constraints are a type of integrity constraint that ensure the values stored in a
column (or attribute) of a database are valid and within a specific range or domain. In simple
terms, they define what type of data is allowed in a column and restrict invalid data entry. The
data type of domain include string, char, time, integer, date, currency etc. The value of the
attribute must be available in comparable domains.
Example:

Student_Id Name Semester Age

21CSE100 Aniket Kumar 6th 20

21CSE101 Shashwat Dubey 7th 21

21CSE102 Manvendra Sharma 8th 22

21CSE103 Ashmit Dubey 5th 20

This table demonstrates domain constraints in action by enforcing rules for each column:
1. Student_Id: Must be unique and follow a specific format like 21CSE###. No duplicates or
invalid formats allowed.
2. Name: Accepts only valid text (no numbers) and cannot be left empty (NOT NULL
constraint).
3. Semester: Allows specific values like 5th, 6th, etc., and ensures valid input (e.g., no 10th if
not permitted).
4. Age: Must be an integer within a reasonable range (e.g., 18-30) and cannot contain invalid
data like negative numbers or text.
Types of Domain Constraints
● NOT NULL Constraint
● CHECK Constraint
Why Domain Constraints Are Important ?
● They prevent invalid or inconsistent data from entering the database.
● They ensure the database is reliable and follows predefined business rules.
● They make the database easier to manage and maintain by reducing errors.
Example:
Let, the not-null constraint be specified on the "Semester" attribute in the relation/table given
below, then the data entry of 4th tuple will violate this integrity constraint, because the
"Semester" attribute in this tuple contains null value. To make this database instance a legal
instance, its entry must not be allowed by database management system.

Name Semester Age

21CSE1001 Sonali Rao 5th 20

21CSE1012 Anjali Gupta 5th 21

21CSE1023 Aastha Singh 5th 22

21CSE1034 Ayushi Singh NULL 20