UNIT - II

INTEGRITY RULES. (OR)

ENTITY INTEGRITY AND REFERENTIAL INTEGRITY

Relational database integrity rules are very important to good database

design. Many (RDBMSs enforce integrity rules automatically. Our application
design implements entity and referential integrity rules mentioned below:
Entity integrity:
Entity integrity is an integrity rule which states that every table must have a
primary key and that the column or columns chosen to be the primary key should
be unique and not null.
Eg: CUS_NUM in CUSTOMER Table cannot be null and and should not be
repeated.
Referential integrity:
Referential integrity is the relational property that each foreign key
value in a table exists as a primary key in the referenced table
otherwise null.

Example:

Integrity constraints in DBMS (Database Management System) are rules enforced on

database relations (tables) to ensure the accuracy and consistency of the data. These
constraints help maintain data integrity and prevent invalid or inconsistent data from being
inserted, updated, or deleted.

Different Types of Integrity Constraints that are enforced with SQL are listed below

1
 1. Domain Constraint

Ensures that values in a column belong to a predefined domain (data type, range,
format).

Example:

CREATE TABLE Student (

RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Age INT CHECK (Age BETWEEN 18 AND 25)
);
Here, Age must be between 18 and 25.

Entity Integrity Constraint

 Ensures that every table has a primary key and that key cannot be NULL.
 Example:

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL
);
EmpID uniquely identifies each employee and cannot be NULL.

Referential Integrity Constraint

 Ensures foreign key values in a table refer to valid primary key values in another
table.
 Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Here, CustomerID in Orders must match an existing CustomerID in Customers.

Key Constraint (Uniqueness Constraint)

 Ensures that certain columns contain unique values.

 Example:

CREATE TABLE Users (

UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
Each Email must be unique

CHECK Constraint

 Ensures that values in a column meet a specific condition.

 Example:

2
 CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10,2) CHECK (Price > 0)
);
The Price must always be greater than 0.

NOT NULL Constraint

 Ensures that a column cannot have NULL values.

 Example:

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL
);
Name must always have a value.

DEFAULT Constraint

 Assigns a default value if no value is provided.

 Example:

CREATE TABLE Students (

ID INT PRIMARY KEY,
Status VARCHAR(10) DEFAULT 'Active'
);

RELATIONAL KEYS.
The relational keys in DBMS are explained below.
Primary key
An attribute or combination of attributes that have unique
identification property is called primary key. It cannot contain null
values and repeated values.
Eg. STU_NUM in STUDENT Table is the primary key.
Composite primary key
Not every relation will have single-attribute primary key. There can
be a possibility that some combination of attributes have the
unique identification property. These attributes as a group is called
composite primary key.
Candidate key
In a relation, there can be more than one attribute combination
possessing the unique identification property. These combinations,
which can act as primary key, are called candidate keys.

EmpNo SocSecurityNo Name Age

1011 2364236 Harry 21

1012 1002365 Sympson 19

3
 1013 1056300 Larry 24

Table having “EmpNo” and “SocSecurityNo” as candidate keys

Secondary key An attribute (or combination of attributes) used
strictly for data retrieval purposes.
Foreign key An attribute (or combination of attributes) in one
table whose values must either match the primary key in another
table or be null.
Recursive Foreign key: It is an attribute (or combination of
attributes) in one table which refers to the values of primary key
in same table.
Super key:
An attribute or combination of attributes that have unique
identification property is called super key. Any superset of key is
super key.

Types of SQL commands.

SQL commands are categorized into different types based on their functionality. The main
categories include:

1. DDL (Data Definition Language)

2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)
5. DQL (Data Query Language)

DDL Commands and Examples

Comman
Description Example
d
Creates a new database, table, or other CREATE TABLE Students (ID INT
CREATE
objects. PRIMARY KEY, Name VARCHAR(50));
Modifies an existing database object (e.g., ALTER TABLE Students ADD COLUMN
ALTER
adding/removing columns). Age INT;
DROP Deletes a database or table permanently. DROP TABLE Students;
Removes all records from a table but keeps
TRUNCATE TRUNCATE TABLE Students;
the structure.
RENAME TABLE Students TO
RENAME Renames a table or column.
Learners;

DML (Data Manipulation Language)

DML commands are used to insert, update, delete, and retrieve data from database
tables.

DML Commands and Examples

Comman
Description Example
d
INSERT INTO Students (ID, Name, Age) VALUES
INSERT Adds new records into a table.
(1, 'John', 20);
UPDATE Modifies existing records in a UPDATE Students SET Age = 21 WHERE ID = 1;

4
Comman
Description Example
d
table.
Removes specific records from a
DELETE DELETE FROM Students WHERE ID = 1;
table.

DCL (Data Control Language)

DCL commands are used to control access to data within the database.

DCL Commands and Examples

Comman
Description Example
d
Provides specific privileges to GRANT SELECT, INSERT ON Students TO
GRANT
users. 'user1'@'localhost';
Removes specific privileges from REVOKE INSERT ON Students FROM
REVOKE
users. 'user1'@'localhost';

TCL (Transaction Control Language)

TCL commands manage database transactions to ensure data consistency.

TCL Commands and Examples

Command Description Example
Saves all changes made during the
COMMIT COMMIT;
transaction.
Reverts changes made during a
ROLLBACK ROLLBACK;
transaction.
SAVEPOIN SAVEPOINT
Creates a savepoint within a transaction.
T sp1;

DQL (Data Query Language)

DQL is used for retrieving data from a database.

DQL Command and Example

Comman
Description Example
d
Retrieves data from a SELECT * FROM
SELECT
table. Students;

ER-to-Relational Mapping Algorithm (Logical database design)

Logical database design is the process of organizing data into tables that reflect a business's
operations. It's a key step in creating efficient and organized databases

Consider the following ER diagram.

5
Step 1: Mapping of Regular Entity Types

Each regular (strong) entity type in the ER model is transformed into a relation that includes
all its simple attributes. A primary key is selected, and composite keys are represented as
multiple attributes. Example: The EMPLOYEE, DEPARTMENT, and PROJECT entities in the
COMPANY database are converted into corresponding relations with SSN, DNUMBER, and
PNUMBER as their primary keys.

Step 2: Mapping of Weak Entity Types

Weak entities rely on strong entities and do not have sufficient attributes to form a primary
key. They are mapped into a relation with all their attributes and a foreign key referencing
the owner entity. The primary key is a combination of the foreign key and the partial key of
the weak entity. Example: The DEPENDENT entity is mapped, where ESSN (foreign key)
references the EMPLOYEE relation.

Step 3: Mapping of Binary 1:1 Relationship Types

A binary one-to-one (1:1) relationship is mapped using three approaches:

 Foreign Key Approach: One entity's primary key is included as a foreign key in the
other.
 Merged Relation Approach: The two entities and their relationship are merged into
a single relation if both participations are total.
 Cross-Reference Table: A new relation is created to reference both entities.
Example: The MANAGES relationship is mapped by choosing DEPARTMENT as the
entity that stores the foreign key.

Step 4: Mapping of Binary 1:N Relationship Types

6
For a one-to-many (1:N) relationship, the relation representing the entity at the N-side
includes a foreign key referencing the other entity’s primary key. Simple attributes of the
relationship are added to the relation. Example: The WORKS_FOR relationship is mapped by
including DNUMBER as a foreign key (DNO) in the EMPLOYEE relation.

Step 5: Mapping of Binary M:N Relationship Types

A many-to-many (M:N) relationship is mapped into a new relation that stores foreign keys
from the participating entities. The combination of these foreign keys serves as the primary
key. Example: The WORKS_ON relation is created with ESSN and PNO as foreign keys
referencing EMPLOYEE and PROJECT, respectively.

Step 6: Mapping of Multivalued Attributes

Multivalued attributes require a separate relation containing the attribute and a foreign key
referencing the entity. The combination of both forms the primary key. Example: The
DEPT_LOCATIONS relation is created with DLOCATION (multivalued attribute) and DNUMBER
(foreign key referencing DEPARTMENT) as its primary key.

The relational schema for the above ER model is given below:

Step 7: Mapping of N-ary Relationship Types

For relationships involving more than two entities (n-ary, where n>2), a separate relation is
created. This relation includes foreign keys referencing all participating entities and any
attributes of the relationship. Example: The SUPPLY relationship is mapped into the SUPPLY
relation with SNAME, PARTNO, and PROJNAME as foreign keys forming the primary key.

Example for mapping n-ary relationships. (No. of entities are 3, so it is ternary relationship).

7
Summary of Mapping Constructs and Constraints

The following table highlights the correspondences between ER and relational models:

ER Model Relational Model

Entity type Entity relation
1:1 or 1:N
Foreign key
relationship
Relationship relation with two foreign
M:N relationship
keys
Relationship relation with n foreign
N-ary relationship
keys
Simple attribute Attribute
Composite attribute Set of simple attributes
Multivalued attribute Separate relation and foreign key
Value set Domain
Key attribute Primary or secondary key

8
Views
A view is a virtual or derived relation: a relation that does not
necessarily exist in its own right, but may be dynamically derived from
one or more base relations. Thus, an external model can consist of both
base (conceptual- level) relations and views derived from the base
relations.
Base relation
A named relation corresponding to an entity in the conceptual schema,
whose tuples are physically stored in the database.
We can define a view in terms of base relations: The dynamic result of
one or more relational operations operating on the base relations to
produce another relation. A view is a virtual relation that does not
necessarily exist in the database but can be produced upon request by
a particular user, at the time of request.
A view is a relation that appears to the user to exist, can be
manipulated as if it were a base relation, but does not necessarily exist
in storage in the sense that the base relations do. The contents of a
view are defined as a query on one or more base relations. Any
operations on the view are automatically translated into operations on
the relations from which it is derived. Views are dynamic, meaning that
changes made to the base relations that affect the view are
immediately reflected in the view. When users make permitted changes
to the view, these changes are made to the underlying relations
Purpose of Views
The view mechanism is desirable for several reasons:
 It provides a powerful and flexible security mechanism by hiding
parts of the database from certain users. Users are not aware of
the existence of any attributes or tuples that are missing from
the view.
 It permits users to access data in a way that is customized to
their needs, so that the same data can be seen by different
users in different ways, at the same time.
 It can simplify complex operations on the base relations. For
example, if a view is defined as a combination (join) of two
relations users may now perform more simple operations on the
view, which will be translated by the DBMS into equivalent
operations on the join.
A view should be designed to support the external model that the user
finds familiar. For example:
 A user might need Branch tuples that contain the names of
managers as well as the other attributes already in Branch. This
view is created by combining the Branch relation with a restricted
form of the Staff relation where the staff position is “Manager.”
 Some members of staff should see Staff tuples without the salary
attribute.
 Attributes may be renamed or the order of attributes changed.
For example, the user accustomed to calling the branchNo
attribute of branches by the full name Branch Number may see
that column heading.

9
  Some members of staff should see property records only for those
properties that they manage.
Updating Views
All updates to a base relation should be immediately reflected in all
views that reference that base relation. Similarly, if a view is updated, then
the underlying base relation should reflect the change. However, there are
restrictions on the types of modification that can be made through views.
We summarize here the conditions under which most systems
determine whether an update is allowed through a view:
 Updates are allowed through a view defined using a simple query
involving a
 Single base relation and containing either the primary key or a
candidate key of the base relation.
 Updates are not allowed through views involving multiple base
relations. Updates are not allowed through views involving
aggregation or grouping operations.
In Relational Database Management Systems (RDBMS), you can destroy
(drop) or alter tables and views using SQL commands.
Destroying (Dropping) Tables and Views

 DROP TABLE: Permanently deletes a table along with its structure and all data.

DROP TABLE table_name;

Eg. DROP TABLE Students;

DROP VIEW: Permanently deletes a view but does not affect the underlying tables.

DROP VIEW view_name;

Eg. DROP VIEW StudentView;

Altering Tables and Views

Altering Tables

Add a new column

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE Students ADD Email VARCHAR(100);

Modify an existing column

ALTER TABLE table_name MODIFY column_name new_datatype;

ALTER TABLE Students MODIFY age BIGINT;

Rename a column:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

ALTER TABLE Students RENAME COLUMN FullName TO StudentName;

10
 Delete a column:

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE Students DROP COLUMN Email;

Altering Views

 Modify a view definition (by recreating it using CREATE OR REPLACE):

CREATE OR REPLACE VIEW view_name AS SELECT column1, column2 FROM table_name

WHERE condition;

CREATE VIEW StudentView AS SELECT StudentID, StudentName FROM Students WHERE age
> 18;

Rename a view (in some databases like MySQL and PostgreSQL):

ALTER VIEW old_view_name RENAME TO new_view_name;

ALTER VIEW StudentView RENAME TO AdultStudents;

Relational Algebra Operations

Selection (σ)

Notation:

Description:
o Selects rows (tuples) that satisfy a given condition.
o Filters data from a relation (table).
Example:
Find students older than 18 from the Students table.

SELECT * FROM Students WHERE Age > 18;

Projection (π)

Notation:

Description:

Selects columns (attributes) from a relation.

11
Example:

Get only StudentID and StudentName from the Students table.

SELECT StudentID, StudentName FROM Students;

Cartesian Product (×)

 Notation: R×S
 Description:
o Combines every tuple of relation R with every tuple of relation S.
o Produces a large result set.
 Example:
Students×Courses
Combine Students and Courses tables.
SELECT * FROM Students, Courses;
Union (∪)

Notation: R∪S

Description:

o Combines tuples from two relations with duplicate elimination.

o Both relations must have the same schema.

Example:
Get a list of all student names from Students2023 and Students2024 tables.

SELECT StudentName FROM Students2023

UNION
SELECT StudentName FROM Students2024;
Set Difference (−)
Notation: R−S

Description:
Returns tuples from R that are not present in S.
Example:
Find students enrolled in 2023 but not in 2024.

SELECT StudentName FROM Students2023

EXCEPT
SELECT StudentName FROM Students2024;

Intersection (∩)

 Notation: R∩S
 Description:
o Returns tuples common to both relations.
12
  Example:
Get students enrolled in both 2023 and 2024.

SELECT StudentName FROM Students2023

INTERSECT
SELECT StudentName FROM Students2024;
Rename (ρ)
Notation:

Description:
Renames a relation.
Example:
Rename the Students relation to Scholars.

SELECT * FROM Students AS Scholars;

Advanced Relational Algebra Operations:

SELECT * FROM Students NATURAL JOIN Enrollments;

SELECT * FROM Students, Courses WHERE Students.Age > 18;

13
14
Equivalent SQL query: SELECT * FROM Students WHERE Age > 18;

Equivalent SQL query:

SELECT * FROM Students WHERE StudentID

IN (SELECT StudentID FROM Enrollment WHERE Course = 'Mathematics');

Equivalent SQL query:

SELECT StudentID FROM Enrollment GROUP BY StudentID
HAVING COUNT(DISTINCT CourseID) = (SELECT COUNT(*) FROM Courses);

15
 SELECT Name, Age FROM Students WHERE Age > 18;

SELECT StudentID FROM Enrollment WHERE Course = 'Mathematics';

SELECT Name FROM Students WHERE NOT EXISTS (SELECT * FROM Courses
WHERE CourseID NOT IN (SELECT CourseID
FROM Enrollment WHERE Enrollment.StudentID = Students.StudentID));

16