What is Cursor? Why do we need cursor? Example.

 

Q. What is a Cursor?

Ans:

A cursor is a pointer/handle to the result set of a query in PL/SQL.

It lets us process rows one by one (row-by-row iteration).

Think of it like:

👉 SQL gives you a whole table of results.

👉 A cursor lets you pick each row, work with it, then move to the next.

Q. Why Do We Need Cursors?

Ans:

SQL by itself works in sets (all rows at once).

Example:

UPDATE students SET grade='A' WHERE marks > 90.

→ These updates all qualifying rows in one go. 

But PL/SQL is procedural. 

Sometimes you need to:

1. Fetch one row, process it, make a decision

2. Then move to next row, apply a different logic

👉 In such cases, we require cursors.

Example Without Cursor (Problem Case)

Suppose we have a table Students (roll_no, name, marks)

Now we want to give grades like:

Marks ≥ 90 → Grade 'A'

Marks 75–89 → Grade 'B'

Marks < 75 → Grade 'C'

We cannot write one simple SQL UPDATE because logic is conditional per row.

Notes in Detail:

How to find candidate key before Normalization?

Finding candidate keys is the foundation before we normalize a relation (2NF, 3NF, BCNF)

Q. What is a Candidate Key?

Ans: A candidate key is a minimal set of attributes that can uniquely identify every tuple (row) in a relation. "Minimal" means: if you remove any attribute from it, it will no longer uniquely identify.

Q. What are the steps to find candidate key?

Steps to Find Candidate Keys

1. List all attributes in the relation.

2. List given Functional Dependencies (FDs).

3. Find closure of attributes (denoted as X⁺ = all attributes X can determine).

4. If X⁺ = all attributes in relation → X is a superkey.

5. Check minimality: if no proper subset of X is a superkey → X is a candidate key.

Let's try to understand this with simple example

Example:

Given: 

Relation with attributes and its functional dependencies are given here in case study.

Relation R (A, B, C, D, E)

Functional Dependencies (FDs):

A → B

B → C

CD → E

E → A

Now apply above steps to find out Candidate Key from the above relation.

Step 1: Check closures

A⁺ = {A, B, C} (from A→B, then B→C) → not all attributes.

B⁺ = {B, C} → not all.

C⁺ = {C} → not all.

D⁺ = {D} → not all.

E⁺ = {E, A, B, C} (E→A, then A→B, B→C) → missing D → not all.

Step 2: Try combinations

AE⁺ = {A, B, C, E} → still missing D.

AD⁺ = {A, B, C, D} → missing E.

ED⁺ = {E, A, B, C, D} → this gives all attributes → superkey.

Step 3: Check minimality

From ED, 

Remove E: D⁺ = {D} → not all.

Remove D: E⁺ = {E, A, B, C} → not all.

→ So, ED is minimal → Candidate Key.

Step 4: Verify if there are others

Try CD⁺ = {C, D, E, A, B} → also gives all → Candidate Key.

So, candidate keys are:

{ED}, {CD}

Q. Why Candidate Key helps in Normalization?

Ans:

Candidate keys help us identify prime attributes (those that are part of any key).

In higher normalization (2NF, 3NF, BCNF), we check FDs based on candidate keys and prime attributes

PL / SQL Unnamed PL SQL code block Assignment

Problem Statement:

Unnamed PL/SQL code block: Use of Control structure and Exception handling is

mandatory.

Suggested Problem statement:

Consider Tables:

1. Borrower (Roll_no, Name, DateofIssue, NameofBook, Status)

2. Fine (Roll_no,Date,Amt)

 Accept Roll_no and NameofBook from user.

 Check the number of days (from date of issue).

 If days are between 15 to 30 then fine amount will be Rs 5per day.

 If no. of days>30, per day fine will be Rs 50 per day and for days less than 30, Rs. 5 per

day.

 After submitting the book, status will change from I to R.

 If condition of fine is true, then details will be stored into fine table.

 Also handles the exception by named exception handler or user define exception handler.

OR

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area

Now the first question comes to our mind is how to implement this code? 

1. Which Software I have to use for implementation, if I don't have that software how to install it in my computer so that I can implement the same.

Ans: To implement this assignment we need following installation or setup

1. Choose Operating System: Windows

2. Open below link in new tab inside the browser to download free oracle database setup

https://www.oracle.com/database/free/get-started/?source=ubi-lightbox

3. Windows appear like this here you have to click on "Link to Download"

4. Once the file downloaded successfully you have to unzip or extract it in some where then go to the folder and double click on Setup file of type Application.

5. Select the destination folder or by default it shows the installation path keep it same as it is and click on Install button.

6. It will ask for password keep it simple like 123456 and just click on next button.

7. In between it will ask for java update just click on Allow button. after that it will take 5 to 6 minutes for installation.

8. If your installation done successfully following message appears on the screen.

Now required software installation is done. what next?

2. How do we use this software for implementation of above PL/ SQL assignment.

2.1 To connect the install database, first open your command prompt in Run as Administrator mode.

       and type following command 

       > sqlplus / as sysdab

2.2 In the above command prompt if we carefully observe then we come to know that we have successfully connected the Oracle free pluggable database container CDS$ROOT. as a system database administrator so we have all rights which administrator have.

if you want to see tables from the database you have to write following query:

SQL > select table_name from user_tables;

2.3 Now if you are having a knowledge of PL/SQL code then you can directly write code in the following manner inside the command prompt.

2.4 First basic PL/SQL unnamed code for understanding.

2.5 How to use for(...) loop inside the PL/SQL code block simple demo for the same. Here we have used DML insert command it means first of all we have to create table circle_data with two columns radius and area.

2.6 Complete code as per problem statement is as follows.

Write a PL/SQL code block to calculate the area of a circle for a value of radius varying from 5 to

9. Store the radius and the corresponding values of calculated area in an empty table named areas,

consisting of two columns, radius and area

 

Assignment No 5 PL SQL Create Procedure for Student Categorization

 

Database Transaction Management: Introduction, ACID, Schedule

 Introduction to Database Transaction, Transaction states, ACID properties, Concept of Schedule, Serial Schedule 

Normalization: Algorithms for Decomposition, 2NF, 3NF, BCNF