DBMS LAB SOLUTION

1. Install oracle/ MYSQL.

-- For MySQL installation on Ubuntu

sudo apt update

sudo apt install mysql-server

-- For MySQL installation on CentOS

sudo yum update

sudo yum install mysql-server

-- For Oracle Database installation, download the RPM package from Oracle's website and run:

sudo rpm -ivh oracle-database-ee-<version>.rpm

OR

To install Oracle Database or MySQL, here’s a simple step-by-step guide for both. Could you confirm
your operating system (Windows, macOS, Linux) so I can tailor the instructions?

✅ For MySQL Installation:

On Windows:

1. Download MySQL Installer from:

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

2. Run the installer and choose the "Developer Default" or "Server only" option.

3. Proceed through the setup:

o Choose MySQL Server version.

o Set the root password.

o Configure networking (default port 3306).

4. Complete the installation.

5. Verify installation:

o Open MySQL Command Line Client and log in with root.

On Linux (Ubuntu example):

bash
CopyEdit

sudo apt update

sudo apt install mysql-server

sudo systemctl start mysql

sudo mysql_secure_installation

Then login with:

bash

CopyEdit

sudo mysql -u root -p

✅ For Oracle Database Installation:

On Windows:

1. Download Oracle Database 21c/19c from:

https://www.oracle.com/database/technologies/

2. Extract the downloaded ZIP and run setup.exe.

3. Choose options like:

o Desktop class (for simple installation).

o Set password for SYS/SYSTEM users.

4. Wait for the installation to complete.

5. Use SQL*Plus or Oracle SQL Developer to connect.

On Linux (example using Oracle XE):

bash

CopyEdit

sudo apt update

sudo apt install alien

wget https://download.oracle.com/otn-pub/otn_software/db-express/21c/oracle-database-xe-21c-
1.0-1.ol7.x86_64.rpm

sudo alien -i oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm

sudo /etc/init.d/oracle-xe configure

2. Create Entity-Relationship Diagram using case tools.

Here's a simple guide on how to create an Entity-Relationship Diagram (ERD) using

CASE tools. CASE (Computer-Aided Software Engineering) tools help design and
document systems easily.

✅ Steps to Create ERD Using CASE Tools:

1️⃣ Choose a CASE Tool:
Here are some popular free and paid CASE tools for ERD creation:
Tool
Type Link
Name
Draw.io
(diagram Free https://app.diagrams.net/
s.net)
Lucidcha Freem
https://www.lucidchart.com/
rt ium
dbdiagra
Free https://dbdiagram.io/
m.io
MySQL
Workben Free https://dev.mysql.com/downloads/workbench/
ch
Oracle
SQL
https://www.oracle.com/database/sqldeveloper/technolo
Develope Free
gies/datamodeler/
r Data
Modeler

2️⃣ General Steps to Create ER Diagram:

Example: Using Draw.io
1. Open https://app.diagrams.net/.
2. Select "Blank Diagram".
3. Use the "Entity Relation" shape library (enable from "More Shapes").
4. Drag and drop:
o Entities (tables)
o Attributes (fields)
o Relationships (lines between entities)
5. Define:
o Primary keys (PK)
o Foreign keys (FK)
o Cardinality (1:1, 1:N, M:N)
6. Save your diagram in formats like PNG, PDF, or XML.

3️⃣ Example ERD Structure:

For a "Student - Course Enrollment System", you might have:
diff
CopyEdit
Entities:
- Student (StudentID, Name, Email)
- Course (CourseID, CourseName, Credits)
- Enrollment (EnrollmentID, StudentID, CourseID, DateEnrolled)
Relationships:
- Student (1) ---- (M) Enrollment
- Course (1) ---- (M) Enrollment
Your ERD will connect these entities with relationships indicating how they interact.

4️⃣ Export or Save:

Once done, you can export the diagram or generate SQL scripts (if the tool supports it).

OR

-- Create a database for the project

CREATE DATABASE project_db;

-- Use the created database

USE project_db;

-- Create tables for the Entity-Relationship Diagram

CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);

CREATE TABLE Orders (

OrderID INT AUTO_INCREMENT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Products (

ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);

CREATE TABLE OrderDetails (

OrderDetailID INT AUTO_INCREMENT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
3. Normalization.

What is Normalization?
Normalization is the process of organizing data in a database to eliminate
redundancy (duplicate data) and ensure data integrity. The goal is to split larger
tables into smaller, related tables and define relationships between them.

✅ Why Normalize?
 Remove duplicate data.
 Reduce data anomalies (insert, update, delete anomalies).
 Improve data consistency.
 Make databases more efficient and maintainable.

✅ Types of Normal Forms (NFs):

Normal Form Rule Purpose
No repeating groups or arrays.
1NF (First Structure the table
Every column should have
Normal Form) properly.
atomic (indivisible) values.
Must be in 1NF + no partial
2NF (Second dependency (non-key attributes Eliminate partial
Normal Form) depend on the whole primary dependencies.
key).
Must be in 2NF + no transitive
3NF (Third Remove transitive
dependency (non-key attributes
Normal Form) dependencies.
depend only on the primary key).
BCNF (Boyce- A stronger version of 3NF. Handle more
Codd Normal Every determinant must be a complex
Form) candidate key. dependency issues.

✅ Simple Example:
🎯 Unnormalized Table (UNF):
StudentID StudentName Course1 Course2
1 John Math Science
2 Mary History NULL

🎯 1NF (Remove repeating columns):

StudentID StudentName Course
1 John Math
1 John Science
2 Mary History

🎯 2NF (Remove partial dependencies):

Separate into two tables:
Students
StudentID StudentName
 StudentID StudentName
1 John
2 Mary
Enrollments
StudentID Course
1 Math
1 Science
2 History

🎯 3NF (Remove transitive dependencies):

If we had something like this in the Enrollments table: | StudentID | Course |
CourseFee | Then, if CourseFee depends on Course, move it to a new table:
Courses
Course CourseFee
Math $100
Science $120
History $80

✅ Summary:
 1NF ➡️ Remove repeating groups.
 2NF ➡️ Remove partial dependencies.
 3NF ➡️ Remove transitive dependencies.
 BCNF ➡️ Handle advanced anomalies.
4. Create cursor.

How to Create a CURSOR in MySQL

A CURSOR in MySQL is used to process each row returned by a query one at a
time. It's commonly used when you need to perform operations on each row
individually from a result set.

✅ Basic Syntax of a Cursor:

sql
CopyEdit
DECLARE cursor_name CURSOR FOR select_statement;
🔹 Full Example:
Suppose we have a table called students:
sql
CopyEdit
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
marks INT
);
Insert some sample data:
sql
CopyEdit
INSERT INTO students VALUES (1, 'John', 85), (2, 'Mary', 92), (3, 'Alex', 76);

✅ Cursor Example to display student names:

sql
CopyEdit
DELIMITER $$

CREATE PROCEDURE display_student_names()

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE student_name VARCHAR(50);

-- Declare the cursor

DECLARE student_cursor CURSOR FOR
SELECT name FROM students;

-- Declare the handler to handle end of data

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Open the cursor

OPEN student_cursor;

-- Loop through the cursor

read_loop: LOOP
FETCH student_cursor INTO student_name;
IF done THEN
 LEAVE read_loop;
END IF;
-- Do something with the fetched data
SELECT student_name;
END LOOP;

-- Close the cursor

CLOSE student_cursor;
END$$

DELIMITER ;

✅ Run the procedure:

sql
CopyEdit
CALL display_student_names();

✅ Explanation:
Keyword Purpose
DECLARE CURSOR Defines the cursor and its SELECT statement.
OPEN Opens the cursor and starts processing rows.
FETCH Retrieves the next row into a variable.
LOOP Processes each row.
LEAVE Exits the loop when there’s no more data.
CLOSE Releases the cursor.

✅ Notes:
 Cursors are usually used inside stored procedures.
 Avoid using cursors when set-based operations (like UPDATE or SELECT) can do
the job.
 Use cursors for row-by-row operations that can't be done with a simple query.
5. Create procedure and functions.

Sure! Could you please clarify what procedure and functions you want to create?
Specifically:
 Which database are you using? (e.g., MySQL, SQL Server, PostgreSQL, Oracle,
etc.)
 What is the purpose of the procedure and functions? (like inserting data, fetching
data, calculating something, etc.)
 Any table structure or sample data you'd like me to work with?
Here’s a basic example to show the difference between a procedure and a function in
SQL Server (for instance):

Example for SQL Server:

1️⃣ Stored Procedure (Insert Data Example):
sql
CopyEdit
CREATE PROCEDURE InsertEmployee
@Name NVARCHAR(50),
@Department NVARCHAR(50),
@Salary DECIMAL(10, 2)
AS
BEGIN
INSERT INTO Employees (Name, Department, Salary)
VALUES (@Name, @Department, @Salary);
END;
Usage:
sql
CopyEdit
EXEC InsertEmployee 'John Doe', 'Finance', 60000.00;

2️⃣ Function (Calculate Bonus Example):

sql
CopyEdit
CREATE FUNCTION GetBonus
(
@Salary DECIMAL(10, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @Bonus DECIMAL(10, 2);
SET @Bonus = @Salary * 0.10; -- 10% bonus
RETURN @Bonus;
END;
Usage:
sql
CopyEdit
SELECT dbo.GetBonus(60000.00) AS BonusAmount;
6. Create packages and triggers.

Great! Before I create the packages and triggers, could you confirm which database
you're using?
 Packages are typically in Oracle.
 Triggers exist in most databases like Oracle, MySQL, SQL Server, etc.
For now, I'll give you a basic example using Oracle SQL, where packages are
common. Let me know if you're using something else, and I can adjust!

✅ Example of an Oracle Package:

A package in Oracle is a group of related procedures, functions, variables, etc.
Step 1: Create Package Specification
sql
CopyEdit
CREATE OR REPLACE PACKAGE emp_package AS
PROCEDURE add_employee(
p_name IN VARCHAR2,
p_department IN VARCHAR2,
p_salary IN NUMBER
);

FUNCTION get_bonus(
p_salary IN NUMBER
) RETURN NUMBER;
END emp_package;
/
Step 2: Create Package Body
sql
CopyEdit
CREATE OR REPLACE PACKAGE BODY emp_package AS

PROCEDURE add_employee(
p_name IN VARCHAR2,
p_department IN VARCHAR2,
p_salary IN NUMBER
) IS
BEGIN
INSERT INTO employees (name, department, salary)
VALUES (p_name, p_department, p_salary);
END add_employee;

FUNCTION get_bonus(
p_salary IN NUMBER
) RETURN NUMBER IS
BEGIN
RETURN p_salary * 0.10; -- 10% bonus
END get_bonus;

END emp_package;
/
Usage:
sql
CopyEdit
BEGIN
emp_package.add_employee('Alice', 'HR', 50000);
END;
/

SELECT emp_package.get_bonus(50000) AS bonus FROM dual;

✅ Example of an Oracle Trigger:

Trigger to automatically update updated_at timestamp on record change:
sql
CopyEdit
CREATE OR REPLACE TRIGGER trg_update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.updated_at := SYSDATE;
END;
/
7. Design and implement payroll processing system.

Thanks for the clarity! Let's design a Payroll Processing System using SQL concepts
like tables, procedures, functions, packages, and triggers (I'll assume Oracle SQL
for now).
If you're using a different DB, I can adjust the code.

✅ Step-by-step Payroll Processing System Design:

🏗️ 1. Table Design
We'll create these key tables:
Employee Master Table
sql
CopyEdit
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
department VARCHAR2(50),
salary NUMBER(10, 2),
hire_date DATE,
status VARCHAR2(20),
updated_at DATE
);
Payroll Table
sql
CopyEdit
CREATE TABLE payroll (
payroll_id NUMBER PRIMARY KEY,
emp_id NUMBER REFERENCES employees(emp_id),
pay_period_start DATE,
pay_period_end DATE,
basic_salary NUMBER(10, 2),
bonus NUMBER(10, 2),
deductions NUMBER(10, 2),
net_pay NUMBER(10, 2),
processed_date DATE
);

🛠️ 2. Package for Payroll Processing

Package Specification
sql
CopyEdit
CREATE OR REPLACE PACKAGE payroll_pkg AS
PROCEDURE process_payroll(p_emp_id IN NUMBER);
FUNCTION calculate_bonus(p_salary IN NUMBER) RETURN NUMBER;
FUNCTION calculate_deductions(p_salary IN NUMBER) RETURN NUMBER;
END payroll_pkg;
/
Package Body
sql
CopyEdit
CREATE OR REPLACE PACKAGE BODY payroll_pkg AS

FUNCTION calculate_bonus(p_salary IN NUMBER) RETURN NUMBER IS

BEGIN
RETURN p_salary * 0.10; -- 10% bonus
END calculate_bonus;

FUNCTION calculate_deductions(p_salary IN NUMBER) RETURN NUMBER IS

BEGIN
RETURN p_salary * 0.05; -- 5% deductions
END calculate_deductions;

PROCEDURE process_payroll(p_emp_id IN NUMBER) IS

v_salary employees.salary%TYPE;
v_bonus NUMBER(10, 2);
v_deductions NUMBER(10, 2);
v_net_pay NUMBER(10, 2);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;

v_bonus := calculate_bonus(v_salary);
v_deductions := calculate_deductions(v_salary);
v_net_pay := v_salary + v_bonus - v_deductions;

INSERT INTO payroll (

payroll_id, emp_id, pay_period_start, pay_period_end,
basic_salary, bonus, deductions, net_pay, processed_date
) VALUES (
payroll_seq.NEXTVAL, p_emp_id, TRUNC(SYSDATE, 'MM'),
LAST_DAY(SYSDATE),
v_salary, v_bonus, v_deductions, v_net_pay, SYSDATE
);
END process_payroll;

END payroll_pkg;
/

🔔 3. Trigger to update timestamps

Automatically update the updated_at column in employees when details change:
sql
CopyEdit
CREATE OR REPLACE TRIGGER trg_emp_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.updated_at := SYSDATE;
END;
/
🚀 4. Usage Example
Process payroll for an employee:
sql
CopyEdit
BEGIN
payroll_pkg.process_payroll(101); -- Where 101 is the emp_id
END;
/
Check payroll:
sql
CopyEdit
SELECT * FROM payroll WHERE emp_id = 101;
8. Design and implement Library Information System.

Great! Here's a full design and implementation of a basic Library Information System
using SQL (Oracle) concepts like tables, procedures, functions, packages, and
triggers.
This system will manage books, members, loans, and returns.

✅ Step-by-step Library Information System Design:

🏗️ 1. Table Design
1️⃣ Book Master Table
sql
CopyEdit
CREATE TABLE books (
book_id NUMBER PRIMARY KEY,
title VARCHAR2(200),
author VARCHAR2(100),
publisher VARCHAR2(100),
total_copies NUMBER,
available_copies NUMBER
);
2️⃣ Member Table
sql
CopyEdit
CREATE TABLE members (
member_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
email VARCHAR2(100),
join_date DATE
);
3️⃣ Loan Table
sql
CopyEdit
CREATE TABLE loans (
loan_id NUMBER PRIMARY KEY,
book_id NUMBER REFERENCES books(book_id),
member_id NUMBER REFERENCES members(member_id),
issue_date DATE,
due_date DATE,
return_date DATE
);

🛠️ 2. Package for Library Operations

Package Specification
sql
CopyEdit
CREATE OR REPLACE PACKAGE library_pkg AS
PROCEDURE issue_book(p_book_id IN NUMBER, p_member_id IN NUMBER);
PROCEDURE return_book(p_loan_id IN NUMBER);
FUNCTION calculate_fine(p_due_date IN DATE, p_return_date IN DATE) RETURN
NUMBER;
END library_pkg;
/
Package Body
sql
CopyEdit
CREATE OR REPLACE PACKAGE BODY library_pkg AS

PROCEDURE issue_book(p_book_id IN NUMBER, p_member_id IN NUMBER) IS

v_available NUMBER;
BEGIN
SELECT available_copies INTO v_available FROM books WHERE book_id =
p_book_id;

IF v_available <= 0 THEN

RAISE_APPLICATION_ERROR(-20001, 'No available copies to issue.');
END IF;

INSERT INTO loans (

loan_id, book_id, member_id, issue_date, due_date
) VALUES (
loan_seq.NEXTVAL, p_book_id, p_member_id, SYSDATE, SYSDATE + 14
);

UPDATE books SET available_copies = available_copies - 1 WHERE book_id =

p_book_id;
END issue_book;

PROCEDURE return_book(p_loan_id IN NUMBER) IS

v_book_id NUMBER;
v_due_date DATE;
v_fine NUMBER;
BEGIN
SELECT book_id, due_date INTO v_book_id, v_due_date FROM loans WHERE
loan_id = p_loan_id;

UPDATE loans
SET return_date = SYSDATE
WHERE loan_id = p_loan_id;

UPDATE books
SET available_copies = available_copies + 1
WHERE book_id = v_book_id;

v_fine := calculate_fine(v_due_date, SYSDATE);

IF v_fine > 0 THEN
DBMS_OUTPUT.PUT_LINE('Fine to be paid: ' || v_fine);
END IF;
END return_book;

FUNCTION calculate_fine(p_due_date IN DATE, p_return_date IN DATE) RETURN

NUMBER IS
v_days_late NUMBER;
v_fine NUMBER;
 BEGIN
v_days_late := p_return_date - p_due_date;
IF v_days_late > 0 THEN
v_fine := v_days_late * 10; -- ₹10 per day late fee
ELSE
v_fine := 0;
END IF;
RETURN v_fine;
END calculate_fine;

END library_pkg;
/

🔔 3. Trigger to prevent negative copies

sql
CopyEdit
CREATE OR REPLACE TRIGGER trg_check_copies
BEFORE UPDATE ON books
FOR EACH ROW
BEGIN
IF :NEW.available_copies < 0 THEN
RAISE_APPLICATION_ERROR(-20002, 'Available copies cannot be negative.');
END IF;
END;
/

🚀 4. Usage Examples
Issue a Book:
sql
CopyEdit
BEGIN
library_pkg.issue_book(101, 1001); -- book_id, member_id
END;
/
Return a Book:
sql
CopyEdit
BEGIN
library_pkg.return_book(5001); -- loan_id
END;
/
Check Fine:
sql
CopyEdit
SELECT library_pkg.calculate_fine(DATE '2024-02-20', DATE '2024-03-05') AS fine
FROM dual;
9. Design and implement Student Information System.

Perfect! Here's a full design and implementation of a basic Student Information

System (SIS) using Oracle SQL concepts like tables, procedures, functions, packages,
and triggers.
This system will handle students, courses, enrollments, grades, and results.

✅ Student Information System (SIS) Design & Implementation:

🏗️ 1. Table Design
1️⃣ Student Table
sql
CopyEdit
CREATE TABLE students (
student_id NUMBER PRIMARY KEY,
full_name VARCHAR2(100),
email VARCHAR2(100),
dob DATE,
join_date DATE
);
2️⃣ Course Table
sql
CopyEdit
CREATE TABLE courses (
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(100),
credits NUMBER
);
3️⃣ Enrollment Table
sql
CopyEdit
CREATE TABLE enrollments (
enrollment_id NUMBER PRIMARY KEY,
student_id NUMBER REFERENCES students(student_id),
course_id NUMBER REFERENCES courses(course_id),
enroll_date DATE,
grade VARCHAR2(2)
);

🛠️ 2. Package for Enrollment and Grading

Package Specification
sql
CopyEdit
CREATE OR REPLACE PACKAGE student_pkg AS
PROCEDURE enroll_student(p_student_id IN NUMBER, p_course_id IN NUMBER);
PROCEDURE assign_grade(p_enrollment_id IN NUMBER, p_grade IN
VARCHAR2);
FUNCTION calculate_gpa(p_student_id IN NUMBER) RETURN NUMBER;
END student_pkg;
/
Package Body
sql
CopyEdit
CREATE OR REPLACE PACKAGE BODY student_pkg AS

PROCEDURE enroll_student(p_student_id IN NUMBER, p_course_id IN NUMBER)

IS
BEGIN
INSERT INTO enrollments (
enrollment_id, student_id, course_id, enroll_date
) VALUES (
enrollment_seq.NEXTVAL, p_student_id, p_course_id, SYSDATE
);
END enroll_student;

PROCEDURE assign_grade(p_enrollment_id IN NUMBER, p_grade IN

VARCHAR2) IS
BEGIN
UPDATE enrollments
SET grade = p_grade
WHERE enrollment_id = p_enrollment_id;
END assign_grade;

FUNCTION calculate_gpa(p_student_id IN NUMBER) RETURN NUMBER IS

v_gpa NUMBER := 0;
v_total_points NUMBER := 0;
v_total_courses NUMBER := 0;
BEGIN
FOR rec IN (
SELECT grade FROM enrollments
WHERE student_id = p_student_id
AND grade IS NOT NULL
) LOOP
CASE rec.grade
WHEN 'A' THEN v_total_points := v_total_points + 4;
WHEN 'B' THEN v_total_points := v_total_points + 3;
WHEN 'C' THEN v_total_points := v_total_points + 2;
WHEN 'D' THEN v_total_points := v_total_points + 1;
WHEN 'F' THEN v_total_points := v_total_points + 0;
END CASE;
v_total_courses := v_total_courses + 1;
END LOOP;

IF v_total_courses > 0 THEN

v_gpa := v_total_points / v_total_courses;
END IF;

RETURN v_gpa;
END calculate_gpa;

END student_pkg;
/

🔔 3. Trigger to prevent duplicate enrollments

sql
CopyEdit
CREATE OR REPLACE TRIGGER trg_prevent_duplicate_enrollment
BEFORE INSERT ON enrollments
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM enrollments
WHERE student_id = :NEW.student_id
AND course_id = :NEW.course_id;

IF v_count > 0 THEN

RAISE_APPLICATION_ERROR(-20003, 'Student already enrolled in this course.');
END IF;
END;
/

🚀 4. Usage Examples
Enroll a student:
sql
CopyEdit
BEGIN
student_pkg.enroll_student(1001, 501); -- student_id, course_id
END;
/
Assign a grade:
sql
CopyEdit
BEGIN
student_pkg.assign_grade(2001, 'A'); -- enrollment_id, grade
END;
/
Calculate GPA:
sql
CopyEdit
SELECT student_pkg.calculate_gpa(1001) AS gpa FROM dual;
10. Automatic Backup of Files and Recovery of Files

Here’s a practical design and implementation plan for Automatic Backup and
Recovery of Files, typically done using OS-level scripts (like Shell Script for
Linux/Unix or Batch/Powershell for Windows) alongside database backups if
needed.
Below, I’ll give a complete example for both backup and recovery mechanisms.

✅ Automatic Backup and Recovery System (File-level)

🏗️ 1. Key Components:
 Source Directory: Where original files are located.
 Backup Directory: Where copies of files are stored.
 Backup Script: Copies files automatically (daily/weekly).
 Recovery Script: Restores files from backup.
 Scheduler: Automates running of backups (like cron or Task Scheduler).

🛠️ 2. Backup Script Example (Linux Shell Script)

Daily Backup Script (backup.sh):
bash
CopyEdit
#!/bin/bash

# Define directories
SOURCE_DIR="/home/user/documents"
BACKUP_DIR="/home/user/backup"
DATE=$(date +"%Y-%m-%d_%H-%M-%S")
LOG_FILE="/home/user/backup/backup_log.txt"

# Create a dated backup directory

mkdir -p "$BACKUP_DIR/$DATE"

# Copy files
cp -r "$SOURCE_DIR/" "$BACKUP_DIR/$DATE"

# Log the backup

echo "Backup completed at $DATE" >> "$LOG_FILE"
Automate with cron (Linux):
bash
CopyEdit
# Edit cron jobs
crontab -e
Add this line to run backup every day at 1 AM:
arduino
CopyEdit
0 1 * * * /home/user/backup.sh

🔁 3. Recovery Script Example (Linux Shell Script)

Recover the latest backup (restore.sh):
bash
CopyEdit
#!/bin/bash

# Define directories
BACKUP_DIR="/home/user/backup"
RESTORE_DIR="/home/user/restore"

# Get the latest backup

LATEST_BACKUP=$(ls -td "$BACKUP_DIR"/*/ | head -1)

# Restore files
cp -r "$LATEST_BACKUP"* "$RESTORE_DIR/"

echo "Restored latest backup from $LATEST_BACKUP"

💡 4. Windows Example (Batch Script)

Backup Script (backup.bat):
batch
CopyEdit
@echo off
set SOURCE_DIR=C:\Users\User\Documents
set BACKUP_DIR=C:\Users\User\Backup
set DATE=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%

mkdir "%BACKUP_DIR%\%DATE%"
xcopy "%SOURCE_DIR%\*" "%BACKUP_DIR%\%DATE%\" /E /I /Y

echo Backup completed on %DATE%

Schedule with Windows Task Scheduler.

🧩 5. Database Backup (if needed)

For example, in Oracle:
sql
CopyEdit
EXPDP user/password DIRECTORY=backup_dir
DUMPFILE=backup_%DATE%.dmp LOGFILE=backup_%DATE%.log
For MySQL:
bash
CopyEdit
mysqldump -u root -p database_name > /backup/backup.sql

✅ Summary:
Feature Method
Backup Files Shell/Batch script with cron
Restore Files Copy from backup folders
Automation cron (Linux) / Task Scheduler (Windows)
Logging Logs in backup folder
Database Backup expdp, mysqldump, etc.
 11. Write SQL statements Using ORACLE /MYSQL:

a) Write basic SQL SELECT statements.

b) Restrict and sort data.
c) Display data from multiple tables.
d) Aggregate data using group function.
e) Manipulate data.
f) Create and manage tables.

Here’s a clear breakdown of SQL statements for each of the tasks you've mentioned,
written in a way that's compatible with Oracle and MySQL (with minor syntax
differences noted):

a) Basic SQL SELECT statements

sql
CopyEdit
-- Select all columns from the "employees" table
SELECT * FROM employees;

-- Select specific columns from the "employees" table

SELECT first_name, last_name, salary FROM employees;

b) Restrict and sort data

sql
CopyEdit
-- Select employees with salary greater than 50000 and sort by salary in descending
order
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

c) Display data from multiple tables

Using JOIN to display data from related tables (assuming "employees" and
"departments" tables):
sql
CopyEdit
-- Inner join to get employee names with their department names
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

d) Aggregate data using group function

sql
CopyEdit
-- Get the total salary, average salary, and number of employees in each department
SELECT department_id, COUNT(*) AS num_employees, SUM(salary) AS
total_salary, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
Using HAVING to filter groups:
sql
CopyEdit
-- Show departments with more than 5 employees
SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

e) Manipulate data
Insert:
sql
CopyEdit
INSERT INTO employees (employee_id, first_name, last_name, salary,
department_id)
VALUES (101, 'John', 'Doe', 60000, 10);
Update:
sql
CopyEdit
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10;
Delete:
sql
CopyEdit
DELETE FROM employees
WHERE employee_id = 101;

f) Create and manage tables

Create table:
sql
CopyEdit
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10,2),
department_id INT
);
Alter table:
sql
CopyEdit
-- Add a new column
ALTER TABLE employees
ADD hire_date DATE;
Drop table:
sql
CopyEdit
DROP TABLE employees;