Integrity Rules in DBMS
In DBMS systems, integrity rules, which take a prime place, are designed to
ensure that the quality of data is always high, with no inconsistencies or
errors.
What are Integrity Rules?
Integrity rules normally represent the pre-existing conditions, or constraints,
of data stored in the database that will ensure the data is valid and
consistent.
These rules, defining permissible values, relationships, and data operations
enclosed in the database system, ensure the accuracy and reliability of the
data to be used for its purposes.
Types of Integrity Rules
Entity Integrity: Also called business key integrity that each row in a
table must contain some unique data, which is known as the business
key.
Through this verification, a primary key will be ensured of non-duplication,
which also implies that no null values are allowed in the primary key
column, thus uniquely identifying each instance of an entity in the table.
Referential Integrity: Referential integrity guarantees the consistent pass
of key reference constraints between data through main table and foreign
keys.
This rule means that for every foreign key value in a child table, that value
has to possess the same primary key value in the parent table. This is the
only way to maintain foreign keys and make father child relationships
work properly.
Domain Integrity: Verifying domain validity means finding out if the value
lies within the allowed range for a particular attribute's column in the data
table.
It implements data integrity by specifying data formats, ranges, and
constraints, for example, check constraints and limitations, that ensure
that all officially recognized data are kept.
User-defined Integrity: This is an enterprise-specific set of integrity rules
that would be established by different organizations depending on their
business edges and the rules ascribed to sensitive data.
These rules can be the user-defined ones like business rules or validation
criteria or can even be the custom constraints that are different from
organization to organization.
RELATIONAL SET OPERATORS
Relational set operators in databases allow combining, comparing, and
manipulating data from multiple tables. The main relational set operators
are UNION, INTERSECT, and EXCEPT (also known as MINUS). These
operators work on relations (tables) with compatible structures, meaning they
have the same number of columns and corresponding columns have
compatible data types.
1. UNION:
The UNION set operators in SQL are used to combine the results of two or more SQL SELECT
queries. It is important to note that no duplicate entries are present in the result obtained from
UNION.
Example:
Accounts:
Emp_id Name Working_status
1 Saurabh WFH
2 Akash Hybrid
3 Ayush Office
4 Vaishnavi WFH
5 Neha Office
Sales:
Emp_id Name Working_status
6 Vikas WFH
2 Akash Hybrid
15 Vasu Office
14 Pradeep WFH
21 Vivek Office
Output:
Emp_id Name Working_status
1 Saurabh WFH
2 Akash Hybrid
3 Ayush Office
4 Vaishnavi WFH
5 Neha Office
6 Vikas WFH
15 Vasu Office
14 Pradeep WFH
21 Vivek Office
2.UNION ALL Operator
The UNION ALL set operators in SQL function similarly to the UNION set operators in
SQL. Thus, the UNION ALL operator is also used to combine results from two or more
SELECT queries. The only difference between the UNION and UNION ALL operators is
that the UNION ALL does not remove duplicate elements, unlike the UNION operator.
SELECT * FROM Accounts
UNION ALL
SELECT * FROM Sales;
Output:
Emp_id Name Working_status
1 Saurabh WFH
2 Akash Hybrid
3 Ayush Office
4 Vaishnavi WFH
5 Neha Office
6 Vikas WFH
2 Akash Hybrid
15 Vasu Office
14 Pradeep WFH
21 Vivek Office
3.INTERSECT Operator
The INTERSECT set operators in SQL are also used to combine the results of two
SELECT queries, but the combination procedure is different. The INTERSECT operator
returns only those records which are common between both the outputs that are being
combined.
SELECT * FROM Accounts
INTERSECT
SELECT * FROM Sales;
Output:
Emp_id Name Working_status
2 Akash Hybrid
4.MINUS/EXCEPT Operator
The MINUS/EXCEPT operator returns the list of records that are present in the first
table and not in the second table. Databases like SQLite, SQL Server, and PostgreSQL
support the EXCEPT set operators in SQL. The MINUS operator is supported only by
Oracle databases.
SELECT * FROM Accounts
EXCEPT
SELECT * FROM Sales;
Output:
Emp_id Name Working_status
1 Saurabh WFH
3 Ayush Office
4 Vaishnavi WFH
5 Neha Office
