24AD3391-Database Design and Management

UNIT II

RELATIONAL MODEL AND SQL

Syllabus:

Relational Model Concepts and Integrity Constraints - Mapping ER and EER Models to
Relational Schema - SQL: Data Definition Language (DDL) and Data Manipulation Language
(DML) - Views and Indexes in SQL - SQL Programming (Stored Procedures, Triggers, Cursors)

Definitions:

Relational Database:
A type of database that organizes data into tables (known as relations), where each table
contains rows and columns. Relationships can be established between tables using keys.
Eg. A library database where one table stores book details and another stores member
information.
Table:
A table is a collection of data organized in rows and columns. Each table represents one
entity type (like students, courses, employees, etc.).
Student_ID Name Department Marks
101 John CSE 92
102 Asha IT 88

Attribute:
An attribute is a column in a table. It represents a specific property of the entity. Each
attribute has a name and a data type.
Eg) In the above table, Name, Department, and Marks are attributes.

Tuple:
A tuple is a row in a table. It represents a single, complete record or entry in the relation.
Eg) Row | 101 | John | CSE | 92 | is a tuple representing one student.
Schema:
A schema defines the structure of a table or an entire database. It includes table names,
attribute names, and their data types.
Example:
For the student table:
Student(Student_ID: INT, Name: VARCHAR, Department: VARCHAR, Marks: INT)
2.1 RELATIONAL MODEL CONCEPTS AND INTEGRITY CONSTRAINTS

1
 24AD3391-Database Design and Management

Relational Model (RM) represents the database as a collection of relations. A relation is

nothing but a table of values. Every row in the table represents a collection of related data values.
These rows in the table denote a real-world entity or relationship.
Relational Model Concepts in DBMS:
 Attribute: Each column in a Table. Attributes are the properties which define a relation.
e.g., Student_Rollno, NAME,etc.
 Tables – In the Relational model the, relations are saved in the table format. It is stored
along with its entities. A table has two properties rows and columns. Rows represent
records and columns represent attributes.
 Tuple – It is nothing but a single row of a table, which contains a single record.
 Relation Schema: A relation schema represents the name of the relation with its
attributes.
 Degree: The total number of attributes which in the relation is called the degree of the
relation.
 Cardinality: Total number of rows present in the Table.
 Column: The column represents the set of values for a specific attribute.
 Relation instance – Relation instance is a finite set of tuples in the RDBMS system.
Relation instances never have duplicate tuples.
 Relation key – Every row has one, two or multiple attributes, which is called relation
key.
 Attribute domain – Every attribute has some pre-defined value and scope which is
known as attribute domain

INTEGRITY CONSTRAINTS
Integrity constraints are a set of rules used in DBMS to ensure that the data in a
database is accurate, consistent and reliable. These rules helps in maintaining the quality of
data by ensuring that the processes like adding, updating or deleting information do not harm
the integrity of the database. Integrity constraints also define how different parts of the
database are connected and ensure that these relationships remain valid. They act like a set of
guidelines that ensure all the information stored in the database follows specific standards.

2
 24AD3391-Database Design and Management

Types of Integrity Constraint:

Integrity Constraint

Domain Constraint Entity Integrity Referential Integrity Key Constraint

Constraint Constraint

Domain Constraint:

 Domain constraints can be defined as the definition of a valid set of values for an
attribute.
 The data type of domain includes string, character, integer, time, date, currency, etc. The
value of the attribute must be available in the corresponding domain.

Example

ID NAME SEMESTER AGE

1000 Tom 1st 17
1001 John 2nd 24
1002 Leonardo 3rd 21
1003 Kate 5th 19
1004 Morgan 8th A

Not allowed, because age is an integer attribute

Entity integrity constraints:

 The entity integrity constraint states that primary key value can't be null.
 This is because the primary key value is used to identify individual rows in relation and if
the primary key has a null value, then we can't identify those rows.
 A table can contain a null value other than the primary key field.
Example:
EMP_ID EMP_NAME SALARY
Not allowed, as 123 Tom 30000
primary Key cannot be 142 John 60000
NULL value Leonardo 20000

3
 24AD3391-Database Design and Management

Referential Integrity Constraints:

 A referential integrity constraint is specified between two tables.
 In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary
Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be
available in Table 2.
Example:
EMP_NAME NAME AGE D_NO
1 Jack 20 11
2 Harry 40 24 Not allowed as D_No 18 is not defined
3 John 27 18 as a primary key of table 2 and in table 1.
4 David 38 13 D_No is a foreign key Defined

Relationships

Primary Key D_No Location

11 Mumbai
24 Delhi
13 Noida

Key constraints:

 Keys are the entity set that is used to identify an entity within its entity set uniquely.
 An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.

Example:

ID NAME SEMESTER AGE

1000 Tom 1st 17
1001 John 2nd 24
1002 Leonardo 3rd 21
1003 Kate 5th 19
1002 Morgan 8th 22

Not allowed, because all row must be unique

2.2 MAPPING ER AND EER MODELS TO RELATIONAL SCHEMA
The process of database design often begins with an Entity–Relationship (ER) model,
which visually represents data, its attributes, and the relationships between entities. However, for
implementation in a relational database, the ER model must be converted into the relational
model. This conversion involves mapping each ER construct - such as entities, relationships,

4
 24AD3391-Database Design and Management

weak entities, and specialization/generalization - into relational tables with appropriate primary
keys, foreign keys, and constraints. Understanding this mapping ensures that the logical design
preserves the integrity, constraints, and semantics of the original ER model while making it
ready for practical SQL-based implementation.
2.2.1 Mapping of Entity Set to Relationship
 An entity set is mapped to a relation in a straightforward way.
 Each attribute of entity set becomes an attribute of the table.
 The primary key attribute of entity set becomes an entity of the table.
 For example - Consider following ER diagram.

EName

Emp_Id
Salary

Employee

The Converted Employee table is as follows:

EmpID EName Salary

201 Poonam 30000
202 Ashwini 35000
203 Sharda 40000
The SQL statement:

CREATE TABLE Employee (

EmpID CHAR(11),
EName CHAR(30),
Salary INTEGER,
PRIMARY KEY (EmpID)
);
2.2.2 Mapping Relationship Sets(without Constraints) to Tables
 Create a table for the relationship set.
 Add all primary keys of the participating entity sets as fields of the table.
 Add a field for each attribute of the relationship.
 Declare a primary key using all key fields from the entity sets.

5
 24AD3391-Database Design and Management

 Declare foreign key constraints for all these fields from the entity sets. For example -
Consider following ER model

The converted Table format is as follows,

EmpID DeptID EName Salary DeptName Building

E001 D001 Arjun Kumar 50000 Human Resources A1
E002 D002 Priya Sharma 62000 Finance B2
E003 D001 Ravi Menon 48000 Human Resources A1
E004 D003 Sneha Iyer 75000 IT C3
E005 D002 Manoj Pillai 54000 Finance B2
The SQL Statement:

CREATE TABLE Works_In (

EmpID CHAR(11),
DeptID CHAR(11),
EName CHAR(30),
Salary INTEGER,
DeptName CHAR(20),
Building CHAR(10),
PRIMARY KEY (EmpID, DeptID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
2.2.3 Mapping Relationship Sets( With Constraints) to Tables
 If a relationship set involves n entity sets and some m of them are linked via arrows in the
ER diagram, the key for anyone of these m entity sets constitutes a key for the relation to
which the relationship set is mapped.
 Hence we have m candidate keys, and one of these should be designated as the primary
key.
 There are two approaches used to convert a relationship sets with key constraints into
table.
Approach 1

6
 24AD3391-Database Design and Management

 By this approach the relationship associated with more than one entities is separately
represented using a table. For example - Consider following ER diagram. Each Dept has
at most one manager, according to the key constraint on Manages.

Here the constraint is each department has at the most one manager to manage it.
Hence no two tuples can have same DeptID. Hence there can be a separate table named Manages
with DeptID as Primary Key. The table can be defined using following SQL statement.

Department table
DeptID DName Budget
1 HR 500000
2 IT 1000000
3 Finance 800000

Employee Table
EmpID EName Salary
E101 Alice 70000
E102 Bob 90000
E103 Carol 75000
Manages
DeptID (PK) EmpID Since
10 E101 2020-04-10
20 E105 2019-07-01

Approach 2:
 In this approach, it is preferred to translate a relationship set with key constraints.
 It is a superior approach because; it avoids creating a distinct table for the relationship
set.
 The idea is to include the information about the relationship set in the table corresponding
to the entity set with the key, taking advantage of the key constraint.
 This approach eliminates the need for a separate Manages relation, and queries asking for
a department's manager can be answered without combining information from two
relations.
 The only drawback to this approach is that space could be wasted if several departments
have no managers.

7
 24AD3391-Database Design and Management

 The following SQL statement, defining a Dep_Mgr relation that captures the information
in both Departments and Manages, illustrates the second approach to translating
relationship sets with key constraints :
Dep_Mgr

DeptID DName Budget EmpID Since

1 HR 500000 E101 2020-01-15
2 IT 1000000 E102 2019-06-01
3 Finance 800000 E103 2021-03-20

2.2.4 Mapping Weak Entity Sets to Relational Mapping

A weak entity can be identified uniquely only by considering the primary key of another (owner)
entity. Following steps are used for mapping Weka Entity Set to Relational Mapping
 Create a table for the weak entity set.
 Make each attribute of the weak entity set a field of the table.
 Add fields for the primary key attributes of the identifying owner.
 Declare a foreign key constraint on these identifying owner fields.
 Instruct the system to automatically delete any tuples in the table for which there are no
owners
For example

Buildings table
Bldg_No Address
101 Main Campus Road
102 Science Block St

Department table
Bldg_No DeptID DeptName
101 1 HR
101 2 Finance
102 1 Physics

8
 24AD3391-Database Design and Management

2.2.2b Mapping of Specialization / Generalization (EER Construct) to Relational Mapping

The specialialization/Generalization relationship(Enhanced ER Construct) can be
mapped to database tables(relations) using three methods. To demonstrate the methods, we will
take the – InventoryItem, Book, DVD.
Specialization/Generalization is an Enhanced ER (EER) concept used to represent an
"is-a" relationship between a higher-level entity (superclass) and one or more lower-level entities
(subclasses).

 Generalization: Combining common features of multiple entities into a single

superclass.
 Specialization: Dividing an entity into sub-entities based on specific attributes.

In the above Example,

InventoryItem is a superclass, while Book and DVD are subclasses.

Method 1: Create separate tables for all entities

 One table is created for the superclass with its own attributes.
 One table is created for each subclass with only the attributes specific to that subclass.
 A foreign key links each subclass table to the superclass table.
1. Super class Table: (Inventory Item)

ID Price
101 500
102 300
103 200
2. Subclass table for Book

9
 24AD3391-Database Design and Management

ID Publisher
101 Penguin Books
103 HarperCollins
3. Subclass table for DVD

ID Manufacturer
102 Sony Pictures

Method 2: Only subclasses are mapped to tables

In this method:
 No table for the superclass is created.
 Attributes of the superclass are duplicated into each subclass table.
Example
Book Table
ID Price Publisher
B01 500 Penguin
B02 350 HarperCollins

DVD Table
ID Price Manufacturer
D01 800 Sony Pictures
D02 600 Warner Bros

Method 3: Only the superclass is mapped to a table

In this method:
 One single table stores both superclass and subclass attributes.
 Attributes not applicable to some entities will remain NULL.
Example
ID Price Publisher Manufacturer
B01 500 Penguin NULL
D01 800 NULL Sony Pictures

2.3 SQL: DATA DEFINITION LANGUAGE (DDL)

 DDL stands for Data Definition Language.
 It is a language used for defining and modifying the data and its structure.
 It is used to build and modify the structure of your tables and other objects in the
database.
DDL commands are as follows,
a) CREATE
b) DROP
c) ALTER
d) RENAME

10
 24AD3391-Database Design and Management

e) TRUNCATE
DDL has pre-defined syntax for describing the data.
CREATE COMMAND
 CREATE command is used for creating objects in the database. It creates a new table.
Syntax: CREATE TABLE <table_name> ( column_name1 datatype, column_name2 datatype, . .
column_name_n datatype );
Example:
CREATE TABLE employee
(
empid INT,
ename CHAR,
age INT,
city CHAR(25),
phone_no VARCHAR(20) );
DROP COMMAND
 DROP command allows removing entire database objects from the database.
 It removes entire data structure from the database.
 It deletes a table, index or view.
Syntax:
DROP TABLE <table_name>;
Or
DROP DATABASE <database_name>;
Example:
DROP TABLE employee;
Or
DROP DATABASE employees;
ALTER COMMAND
 An ALTER command allows to alter or modify the structure of the database.
 It modifies an existing database object.
 Using this command, you can add additional column, drop existing column and even
change the data type of columns.
Syntax:
ALTER TABLE <table_name> ADD <column_name datatype>;
Or
ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name>;
Or
ALTER TABLE <table_name> DROP COLUMN <column_name>;
Example
ALTER TABLE employee ADD (address varchar2(50));
Or

11
 24AD3391-Database Design and Management

ALTER TABLE employee CHANGE (phone_no) (contact_no);

Or
ALTER TABLE employee DROP COLUMN age;
To view the changed structure of table, use 'DESCRIBE' command.
RENAME COMMAND
 RENAME command is used to rename an object.
 It renames a database table.
Syntax:
RENAME TABLE <old_name> TO <new_name>;
Example:
RENAME TABLE emp TO employee;
TRUNCATE COMMAND
 TRUNCATE command is used to delete all the rows from the table permanently.
 It removes all the records from a table, including all spaces allocated for the records.
 This command is same as DELETE command, but TRUNCATE command does not
generate any rollback data.
Syntax:
TRUNCATE TABLE <table_name>;
Example:
TRUNCATE TABLE employee;
2.4 DATA MANIPULATION LANGUAGE (DML)
 DML commands are used to modify the database. It is responsible for all form of changes
in the database.
 The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback.
Here are some commands that come under DML
a) INSERT
b) UPDATE
c) DELETE
INSERT
 The INSERT statement is a SQL query. It is used to insert data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME
(col1, col2, col3,. ... col N)
VALUES (value1, value2, value3 ... valueN);
For example:
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");
UPDATE
 This command is used to update or modify the value of a column in the table.
Syntax:

12
 24AD3391-Database Design and Management

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [W HERE

CONDITION]
Example:
UPDATE students
SET User_Name = 'Sonoo'
WHERE Student_Id = '3';
DELETE
 It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
Example:
DELETE FROM javatpoint WHERE Author="Sonoo";
2.5 VIEWS AND INDEXES IN SQL
VIEWS
A view is a virtual table based on the result of an SQL query. It stores no data itself; it
displays data from one or more real tables. Always shows up-to-date data when queried.
Example with Student Details table
Suppose we have:
CREATE TABLE Student_Details (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(30),
CGPA DECIMAL(3,2),
Email VARCHAR(50)
);
Creating a view for high-performing students
CREATE VIEW HighCGPA_Students AS
SELECT StudentID, Name, Department, CGPA
FROM Student_Details
WHERE CGPA >= 8.0;
Using the view
SELECT * FROM HighCGPA_Students;

13
 24AD3391-Database Design and Management

INDEXES in SQL
An index is like a book’s index - it helps SQL find rows faster without scanning the
entire table. Indexes improve read performance but can slightly slow down inserts/updates
because they must also update the index.
Example with Student Details table
Creating an index on CGPA
CREATE INDEX idx_cgpa
ON Student_Details(CGPA);
2.6 SQL PROGRAMMING (STORED PROCEDURES, TRIGGERS, CURSORS)
2.6.1 Stored Procedures
 A stored procedure is a collection of one or more SQL statements stored and compiled
inside the database for repeated use.
 Instead of writing the same query or logic again and again, we can create it once as a
procedure and call it whenever needed.
 Stored procedures can accept input parameters, return output parameters, and include
control-flow statements such as loops and conditions. Because they are precompiled, they
typically run faster than sending raw SQL from an application each time.
 They also improve security by allowing restricted database access - users can run the
procedure without having direct permission on the underlying tables.
a) Creating stored procedure with and without parameters
b) Altering stored procedures
c) View the stored procedure
d) Dropping the stored procedure

Creating stored procedure without parameters

Syntax:
CREATE PROCEDURE ProcedureName
AS
BEGIN
-- SQL statements go here
SELECT * FROM table_name;
END;
Example:
CREATE PROCEDURE ShowAllEmployees
AS
BEGIN
SELECT * FROM Employee;
END;

14
 24AD3391-Database Design and Management

Execution:
EXEC ShowAllEmployees;
Output:
EMP_ID Emp_Firstname Emp_Lastname Emp_Gender Emp_Salary

1 Raj Darji Male 65000

1 Karnav Sonavane Male 65000

2 Rincy Patel Female 50000

2 Krupali Raj Female 50000

3 Ajay Chariya Male 75000
3 Asif Hussian Male 75000
3 gourav gupta Male 75000
Normal SQL Query:
SELECT Emp_Firstname, Emp_Lastname, Emp_Gender FROM Employee;
Converted to Stored Procedure:
CREATE PROCEDURE smce
AS
BEGIN
SELECT Emp_Firstname, Emp_Lastname, Emp_Gender
FROM Employee;
END;
Execution:
EXEC smce;
Emp_Firstname Emp_Lastname Emp_Gender

Raj Darji Male

Karnav Sonavane Male

Rincy Patel Female

Krupali Raj Female

Ajay Chariya Male
Asif Hussian Male
gourav gupta Male

15
 24AD3391-Database Design and Management

Creating stored procedure with parameters

Syntax:
CREATE PROCEDURE ProcedureName
@ParameterName DataType
AS
BEGIN
SELECT * FROM table_name
WHERE ColumnName = @ParameterName;
END;
 Parameters make procedures flexible.
 We can pass different values each time we execute.
 Example: Pass Employee ID or Gender to filter employees.

Example
CREATE PROCEDURE employeedata
@EMP_ID INT,
@EMP_GENDER VARCHAR(50)
AS
BEGIN
SELECT Emp_Firstname, Emp_Lastname, Emp_Gender
FROM Employee
WHERE EMP_ID = @EMP_ID AND Emp_Gender = @EMP_GENDER;
END;
Execution:
EXEC employeedata 1, 'Male';
Emp_Firstname Emp_Lastname Emp_Gender
Raj Darji Male
Kamav Sonavane Male
Altering stored procedures

Syntax:
ALTER PROCEDURE ProcedureName
AS
BEGIN
-- Modified SQL statements
END;
Example:
ALTER PROCEDURE SMCE
AS
BEGIN
SELECT Emp_Firstname, Emp_Lastname, Emp_Gender

16
 24AD3391-Database Design and Management

FROM Employee
ORDER BY Emp_Firstname;
END
To Execute:
EXEC SMCE;
Emp_Firstname Emp_Lastname Emp_Gender
Ajay Chariya Male
Asif Hussain Male
Gourav Gupta Male
Kamav Sonavane Male
Krupali Raj Female
Raj Darji Male
Rincy Patel Female
View the stored procedure

Syntax:
sp_helptext 'ProcedureName';
Example:
sp_helptext 'employeedata';
Dropping the stored procedure

Syntax:
DROP PROCEDURE ProcedureName;

Example:
DROP PROCEDURE smce;

2.6.2 Triggers
 A trigger is a special stored program in the database that is executed automatically when
a specific event occurs on a table or view.
 The event can be an INSERT, UPDATE, or DELETE operation. Triggers can run either
before or after the event, allowing you to enforce business rules, validate data, or
maintain audit logs without manual intervention.
 Types of triggers
o DML Trigger (Insert, Update, Delete)
o DDL Trigger (Create, Alter, Drop)
o LOGON Trigger (User Session)
Virtual Tables
 Inserted
 Deleted
Inserted Deleted
Stores new
Insert -
inserted records

17
 24AD3391-Database Design and Management

Stores old
Stores new
Update values for
updated records
updated records
Stores deleted
Delete -
records
SELECT * FROM employees
Employee_ID FIRYST_NAME LAST_NAME SALARY MANAGER_ID DEPARTMENT_ID
100 Shan King 24000 100 90
101 Nareen Karthik 17000 101 90
102 Lee Ann 18000 102 90
103 Alex Herald 6000 103 60
104 Bruce lee 4800 103 60
105 Diana Raj 4200 103 60
106 David Austin 8000 102 80
107 Louis Lamp 9000 102 80
The employees table is the main table, and the employees_audit table is used to record
changes whenever an update or delete operation happens on it. The audit table has three fields:
Employee_ID to store the affected employee, Operation to indicate whether it was an UPDATE
or DELETE, and Updated_Date to record the exact time of the change. By creating triggers on
the employees table, any modification or deletion will automatically insert a record into the
employees_audit table, ensuring that all changes are tracked for auditing purposes.
CREATE TABLE employees_audit
(
Employee_ID int,
Operation varchar(10),
UpdatedDate Datetime,
);
Trigger
CREATE TRIGGER trg_emp_audit
ON employees
AFTER INSERT
AS
BEGIN
INSERT INTO employees_audit
SELECT EMPLOYEE_ID,’INS’, GETDATE()
FROM inserted // Virtual table
END
// after creating this trigger lets insert a new value in the employees table.
INSERT INTO employees
VALUES(207,’sam’ , ‘Nichol’ , 2500,122,50);

18
 24AD3391-Database Design and Management

Execution:
SELECT * FROM employees_audit;

Employee_ID Operation Updated_Date

207 INS 2025-08-17 10:45:12

Alter
ALTER TRIGGER trg_emp_audit
ON employees
AFTER INSERT , DELETE
AS
BEGIN
INSERT INTO employees_audit
SELECT EMPLOYEE_ID,’INS’, GETDATE()
FROM inserted // Virtual table
UNION ALL
SELECT EMPLOYEE_ID,’DEL’,GETDATE()
FROM deleted //Virtual table
END

Let’s delete one data from the employee table.

Delete from employee where EMPLOYEE_ID = 107;

Employee_ID Operation Updated_Date

107 DEL 2025-08-17 11:05:33

Table name ‘employee_copy’

Employee_ID FIRST_NAME LAST_NAME SALARY MANAGER_ID DEPARTMENT_ID EndDate Active
100 Shan King 24000 100 90 NULL 1
101 Nareen Karthik 17000 101 90 NULL 1
102 Lee Ann 18000 102 90 NULL 1
103 Alex Herald 6000 103 60 NULL 1
104 Bruce Lee 4800 103 60 NULL 1
105 Diana Raj 4200 103 60 NULL 1
106 David Austin 8000 102 80 NULL 1
107 Louis Lamp 9000 102 80 NULL 1

CREATE TRIGGER trg_employee_delete

ON employee_copy

19
 24AD3391-Database Design and Management

INSTEAD OF DELETE
AS
BEGIN
UPDATE employee_copy
SET Active = 0,
EndDate = GETDATE()
WHERE Employee_ID IN (SELECT Employee_ID FROM deleted);
END;
// Delete one record for employee_copy table
DELETE FROM employee_copy WHERE EMPLOYEE_ID = 107
SELECT * FROM employees_copy;
Employee_ID FIRST_NAME LAST_NAME SALARY MANAGER_ID DEPARTMENT_ID EndDate Active
100 Shan King 24000 100 90 NULL 1
101 Nareen Karthik 17000 101 90 NULL 1
102 Lee Ann 18000 102 90 NULL 1
103 Alex Herald 6000 103 60 NULL 1
104 Bruce Lee 4800 103 60 NULL 1
105 Diana Raj 4200 103 60 NULL 1
106 David Austin 8000 102 80 NULL 1
2025-08-17
107 Louis Lamp 9000 102 80 0
11:55:00

Advantages:
 Triggers are just like stored procedures and easy to code.
 Automate tasks and reduce manual effort.
 Ensures data integrity.
 Virtual tables like inserted and deleted are very useful.
 Can invoke stored procedure and functions inside a trigger
Disadvantages:
 Triggers add complexity and cause additional overhead.
 Impacts performance
 Difficult to locate and invisible to client.
 Disabling triggers may cause inconsistency.
2.6.3 Cursors
 A cursor is a database object that lets you retrieve and process query results row-by-row
rather than all at once.
 This is useful when you need to perform operations on each row individually using
procedural logic.
 A cursor works like a pointer to the result set of a query—you can open it, fetch rows into
variables, process them, and then close it.

20
 24AD3391-Database Design and Management

 While they offer precise control, cursors are generally slower than set-based SQL
operations, so they are used only when row-by-row processing is necessary.
Syntax
DECLARE cursor_name CURSOR FOR
SELECT_statement;

OPEN cursor_name;

FETCH cursor_name INTO variable_list;

CLOSE cursor_name;
Example
Let the table name be ‘SalesData’
Here we are going to write the cursor for calculating the running total
Running Total column just grows row by row by adding the current row’s value to everything
that came before it.
ID Value Running Total
1 10 NULL
2 20 NULL
3 15 NULL
4 25 NULL
5 30 NULL

DECLARE @ID INT, @Value INT, @Total INT = 0;

-- Define the cursor

DECLARE RunningTotalCursor CURSOR FOR
SELECT ID, Value FROM SalesData ORDER BY ID;

-- Open the cursor

OPEN RunningTotalCursor;

-- Fetch the first row

FETCH NEXT FROM RunningTotalCursor INTO @ID, @Value;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Add the value to cumulative total

21
 24AD3391-Database Design and Management

SET @Total = @Total + @Value;

-- Update the RunningTotal column in the table

UPDATE SalesData
SET RunningTotal = @Total
WHERE ID = @ID;

-- Move to next row

FETCH NEXT FROM RunningTotalCursor INTO @ID, @Value;
END;

-- Close and release cursor

CLOSE RunningTotalCursor;
DEALLOCATE RunningTotalCursor;

Output After Running the Cursor

ID Value Running Total
1 10 10
2 20 30
3 15 45
4 25 70
5 30 100

Advantages:
 Row level processing
 Data Navigation (Fetch Next, Prior, First, Last)
 Complex Query handling.
Disadvantages:
 Performance issue
 Resource Consumption
 Complexity of code
 Locking of data

22
 24AD3391-Database Design and Management

QUESTION BANK
2 Marks

1. Define a relational database with one example.

A relational database stores data in tables (relations) with rows (tuples) and
columns (attributes). Example: Employee(EmpID, Name, Salary).
2. What is a tuple in a relational table?
A tuple is a single row in a table representing a set of related values.
Example: (101, "John", 50000).
3. Differentiate between schema and relation instance.
a. Schema: Structure of the database (table name, attributes).
b. Instance: Actual data present in the table at a given time.
4. What is cardinality in the relational model?
The number of tuples (rows) in a relation (table).
5. State the entity integrity constraint.
A primary key value must be unique and cannot be NULL for any tuple.
6. Give an example of a domain constraint.
In Employee(Salary INTEGER), the Salary attribute must have only integer
values.
7. What is a primary key? Give one example.
A primary key uniquely identifies each tuple in a relation. Example: EmpID in the
Employee table.
8. Define referential integrity constraint with an example.
Ensures that a foreign key in one table matches a primary key in another.
Example: DeptID in Employee must exist in Department table.
9. What is the purpose of foreign keys in a relational schema?
To establish and enforce a link between data in two tables.
10. Write the SQL command to create an Employee table with EmpID as the primary
key.
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(30),

23
 24AD3391-Database Design and Management

Salary INT
);
11. What does the TRUNCATE command do in SQL?
Removes all rows from a table but keeps its structure for future use.
12. Write the SQL syntax to rename a table in a database.
ALTER TABLE old_name RENAME TO new_name;
13. What is a view in SQL?
A virtual table based on the result of a SQL query, which doesn’t store data itself.
14. Give one advantage of using indexes in SQL.
Improves the speed of data retrieval operations.
15. Define a stored procedure.
A precompiled set of SQL statements stored in the database and executed on
demand.
16. What is the difference between a trigger and a stored procedure?
 Trigger: Executes automatically on specific events.
 Stored Procedure: Executes only when explicitly called.
17. What is a cursor in SQL programming?
A database object that allows row-by-row processing of query results.
18. Give an example of mapping a weak entity set to a relational table.
For a weak entity Dependent, include owner key:
CREATE TABLE Dependent (
EmpID INT,
DepName VARCHAR(30),
PRIMARY KEY(EmpID, DepName),
FOREIGN KEY(EmpID) REFERENCES Employee
);
19. Name the three methods for mapping specialization/generalization to relational
schema.
 Multiple tables for all entities.
 Tables for only subclasses (duplicate superclass attributes).
 Single table for the superclass (nulls for unused attributes).

24
 24AD3391-Database Design and Management

20. What is the function of the WITH CHECK OPTION in views?

Ensures that all modifications through the view satisfy its defining condition.

PART – B (13 Marks)

1. Explain relational model concepts with suitable examples.
2. Describe the types of integrity constraints in detail with examples.
3. Explain the steps to map ER models to relational schema with suitable examples.
4. Write and explain the SQL commands for CREATE, DROP, ALTER, RENAME, and
TRUNCATE.
5. Explain Data Manipulation Language (DML) commands with examples.
6. Describe mapping of relationship sets without constraints to tables with example SQL.
7. Explain mapping of relationship sets with constraints using both approaches with
examples.
8. Explain the creation, updating, and dropping of views in SQL using the CUSTOMERS
table example.
9. Describe the syntax and example of stored procedures, triggers, and cursors in SQL.
10. Explain mapping of specialization/generalization in EER to relational tables using all
three methods.
PART – C (15 Marks)
1. With a neat diagram, explain mapping of all ER and EER constructs to relational schema,
including entities, relationships, weak entities, and specialization/generalization.
2. Explain integrity constraints in detail and write SQL statements to enforce domain, entity,
and referential integrity in given sample tables.
3. Discuss DDL and DML in SQL with syntax, examples, and output for each command.
4. With examples, explain in detail views and indexes in SQL, including creation, updating,
and deletion of views.
5. Write and explain the SQL programming constructs - stored procedures, triggers, and
cursors - with syntax, examples, and use cases.

*********

25