Database Connectivity (Frontend + Middleware + Backend)

Database Connectivity:

Write a program to implement MySQL/Oracle database connectivity with any front-end language to implement Database navigation operations (add, delete, edit etc.)

Technical Stack used:

1.Frontend (Client Side): 

A.HTML: To create structure of web pages.

B.CSS: To Style web element on web pages. (colors, background, button)

C. JavaScript: for interactivity, form validation purpose we use it.

Why Frontend required?

Ans:

1.Provides the User Interface (UI) for client to enter data (CRUD) and view results.

2.Sends form data to the backend via HTTP requests (like POST /add).

3.Displays responses from the server.

2.Backend (Server Side):

A.Node.js: JavaScript runtime to run server code outside the browser.

B.Express.js: Web framework for Node.js that makes it easier to handle routes (/add, /update, /delete, /users).

Why Backend required?

Ans:

1. Processes requests from the frontend (like when a form is submitted).

2. Talks to the database (MySQL) to perform CRUD operations.

3. Sends a response (success message or data) back to the client.

3.Database (Data Layer):

A. MySQL: (installed locally, accessed via MySQL Workbench for management).

Why Backend required?

Ans:

1. Stores persistent data (student info like: id, name, email, age).

2. Allows Insert, Select, Update, Delete queries.

3. Ensures data integrity and supports multiple users accessing it.

4.Middleware:

A. body-parser (or express.urlencoded() in newer Express versions).

B. Express static middleware (app.use(express.static("public"))).

Why Middleware required?

Ans:

1. body-parser: extracts form data from POST requests so you can use req.body.

2. express.static: serves static files (HTML, CSS, images) from the public folder.

3. Middleware acts like a "middle layer" that processes requests before they reach your route handlers.

Software Installation

1. Node.js

We need this to run your backend (server.js)

Download: https://nodejs.org & install it on your local machine.

Check installation is done successfully. npm -v or node -v on cmd

2. Express.js

A web framework for Node.js.

Installed inside your project folder: npm install express

3. MySQL Server

To store and manage your database.

Download: https://dev.mysql.com/downloads/mysql/

Runs in the background as a database service.

4. MySQL Workbench (GUI for MySQL)

Helps you create database, tables, and run queries without writing CLI commands.

Download: https://dev.mysql.com/downloads/workbench/

5. MySQL Node.js Driver (mysql2)

Required for Node.js to connect to MySQL.

Installed via npm: npm install mysql2

6. body-parser (middleware for form data)

Parses data from HTML forms.

Install via npm: npm install body-parser

7. VS Code (Visual Studio Code)

Best IDE for editing server.js, HTML, and CSS files.

Download: https://code.visualstudio.com/

Folder Structure of Project

Before this nodejs and Microsoft workbench must be installed on your machine:

1. download nodejs from internet for windows os

2. install it and check it is installed or not on cmd prompt: npm -v or node -v

3. database: Goto MySQL community downloads and install MySQL workbench

4. create database (bsiotr) and tables (tebstudent) in it.

Implementation of database Connectivity Project steps:

1. open vscode go to Terminal - New Terminal

c:\Project> mkdir dbconproject

c:\Project> cd dbconproject

c:\Project\dbconproject> npm init -y

c:\Project\dbconproject> npm install express mysql2 body-parser

2. Once install all folder structure gets created like this

dbconproject

-node_modules (Folder here installed all node packages)

-public (Folder here all frontend files: HTML, CSS and js)

-- images (Folder to store images used)

--index.html

--add.html

--update.html

--delete.html

-package-lock.json

-package.json (this file for dependencies and script, manages express, mysql2, body-parser)

-server.js (this file for backend server) (your Node.js backend connects to MySQL and handles CRUD)

3. first we want to check database connection is proper or not. second, we want to check server is listening on port 5000 or not.

so, in server.js you have to add below code.

4. Now it's time to check database connection is established or not and also server is listening on port 5000 or not.

go to the Terminal - New Terminal and type command to run the code > node server.js

it means that your server is listening on port:5000 and your database connected.

5. Now we want to perform CRUD operation on database through front end.

5.1 Now we want to Insert / Create record into the database table. so, we have to make changes in three files

1. index.html 

2. server.js

3.add.html

1. index.html

it will display home page with message included in h1 tag and one button Add user. index.html from this file you have to call add.html file to fill user input.

2. add.html

add.html in this file you have to create form to accept input from user so that we can submit it to the database.

3. server.js

server.js in this file we already created database connection in step no 3 but to handle this HTTP post request we need to write following code in server.js

Explanation of code:

app.post(...): This defines a POST route in Express.

.

The route is /add. So, when a client sends a POST request to http://yourserver/add, this function will run.

.

(req, res) => {...}: This is the callback function that handles the request (req) and sends a response (res).

req.body: Contains the data sent from the client (usually through an HTML form or API request in JSON).

{name, email, age}: This is object destructuring in JavaScript.

It means → get name, email, and age directly from req.body.

res.send("user added") This function send response to client 

Database Trigger (All Types: Row level and Statement level triggers, Before and After

 

Database Trigger (All Types: Row level and Statement level triggers, Before and After Triggers)

Problem Statement:

Write a database trigger on Library table. The System should keep track of the records that are being updated or deleted. The old value of updated or deleted records should be added in Library_Audit table.

Note: Instructor will Frame the problem statement for writing PL/SQL block for all types of Triggers in line with above statement.

"Write a database trigger on the Library table that maintains an audit log. Whenever a record is updated or deleted, the old values of that record should be inserted into the Library_Audit table. The trigger should differentiate between update and delete operations and store additional details like the type of operation and the timestamp."

Let's Study First What is Trigger? Why do we need Trigger? How to implement Trigger?

Note: Instructor will Frame the problem statement for writing PL/SQL block for all types of Triggers in line with above statement.

"Write a database trigger on the Library table that maintains an audit log. Whenever a record is updated or deleted, the old values of that record should be inserted into the Library_Audit table. The trigger should differentiate between update and delete operations and store additional details like the type of operation and the timestamp."

let's understand what we want to do here in this.

You have a Library table in a database.

Whenever a record in this table is UPDATED or DELETED, you don’t want to lose the old information.

So, you need to:

Write a database trigger that automatically captures the old values before they are changed or removed.

Insert those old values into another table called Library_Audit (this act like a log/history table).

This way:

If someone updates a book’s details → the previous details are saved in Library_Audit.

If someone deletes a book record → the deleted record is also saved in Library_Audit.

But why this is needed?

Audit trail: Helps to keep track of who changed or removed data.

Data recovery: If someone deletes a row by mistake, you still have the backup in Library_Audit.

Accountability: Ensures transparency in changes to critical data.

Let's start the Implementation part:

Since we want all types of triggers, we’ll cover:

1. Row-level trigger

2. Statement-level trigger

3. BEFORE trigger

4. AFTER trigger

5. INSTEAD OF trigger (used for views)

First, we have to create two tables 1. Library and 2. Library_Audit common for all types of triggers.

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:

Problem Statement:

Cursors: (All types: Implicit, Explicit, Cursor FOR Loop, Parameterized Cursor)

Write a PL/SQL block of code using parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall. If the data in the first table already exist in the second table, then that data should be skipped.

Note: Instructor will frame the problem statement for writing PL/SQL block using all types of Cursors in line with above statement.

Algorithm:

Let's start the Implementation of above problem statement as follows:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.

5. Third try to implement Cursor FOR Loop example.

6. Forth try to implement Parameterized Cursor that will merge the data available in the newly created table N_RollCall with the data available in the table O_RollCall.

6.1 Apply the logic: If the data in the first table already exist in the second table, then that data should be skipped.

Implementation:

1. We need to create two tables N_RollCall (Rno, Sname, Per) and O_RollCall (Rno, Sname, Per) with same attributes.

2. Insert the 3-4 records inside the O_RollCall table after that Insert 4-8 records inside the N_RollCall table out of which 3 records keep same as it is inside the O_RollCall tables.

Here, if we observe carefully in N_RollCall table two records 101 and 103 already present inside the O_RollCall table. so, by skipping these two records we want to merge remaining two records 102 and 104 inside the O_RollCall table.

3. First try to understand what Implicit Cursor is and how it works?

4. Second try to implement Explicit Cursor.

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