Experiment No 17: Create and manage Views for faster access on relations.

 Experiment No 17: Create and manage Views for faster access on relations.

Result

The experiment was successfully performed. Views were created, updated, and dropped; sequences were generated; and indexes (simple and composite) were implemented to achieve faster data access and efficient database management.

---

a. What are synonyms, write its syntax and advantages?

👉 In Oracle, a synonym is an alias for a table, view, sequence, or other schema object.
⚠️ Note: MySQL does not support synonyms directly. Instead, in MySQL we often use Views as a workaround to achieve similar functionality.

Syntax in Oracle (not supported in MySQL):

CREATE SYNONYM synonym_name FOR object_name;

Alternative in MySQL: Use a View as a synonym.

CREATE VIEW emp_synonym AS
SELECT * FROM employees;

Advantages of synonyms (or view-based alternatives):

1. Simplify query writing by providing shorter names.

2. Provide location transparency (if objects move, synonym can still point to them).

3. Increase security by restricting direct access to base objects.

---

b. What is the difference between simple and composite index?

• Simple Index: Created on a single column of a table.

• Composite Index: Created on two or more columns together.

Example in MySQL:

-- Simple index on "name"
CREATE INDEX idx_name ON students(name);

-- Composite index on "name" and "roll_no"
CREATE INDEX idx_name_roll ON students(name, roll_no);

Key Difference:

• Simple index → speeds up search on one column.

• Composite index → speeds up search involving multiple columns in the same query.

---

c. What are the disadvantages of views?

1. Performance overhead – Views may slow down queries if based on complex joins.

2. No storage of data – They don’t store actual data, only query results.

3. Updatability limitations – Not all views are updatable (especially those with joins, group by, aggregate functions).

4. Dependency – If the base table is dropped or altered, the view becomes invalid.

---

d. Write the syntax to delete view (MySQL).

Syntax:

DROP VIEW view_name;

Example:

DROP VIEW emp_view;

---

Exercise:

Step 1: Sample Data Setup (for execution)

-- Create employee table
CREATE TABLE emp (
    emp_no INT PRIMARY KEY,
    e_name VARCHAR(50),
    salary DECIMAL(10,2),
    dept_no INT
);

-- Insert some sample Indian employee data
INSERT INTO emp VALUES
(101, 'Amit', 45000, 10),
(102, 'Priya', 52000, 20),
(103, 'Suresh', 60000, 10),
(104, 'Neha', 40000, 30),
(105, 'Rahul', 48000, 20);

-- Create department table
CREATE TABLE dept (
    dept_no INT PRIMARY KEY,
    location VARCHAR(50)
);

-- Insert department data
INSERT INTO dept VALUES
(10, 'Mumbai'),
(20, 'Pune'),
(30, 'Delhi');

---

i. Create view emp_view

CREATE VIEW emp_view AS
SELECT emp_no, e_name, salary FROM emp;

✅ Output: View created.

If you run:

SELECT * FROM emp_view;

You’ll see:

emp_no	e_name	salary
101	Amit	45000.00
102	Priya	52000.00
103	Suresh	60000.00
104	Neha	40000.00
105	Rahul	48000.00

---

ii. Update view

UPDATE emp_view SET e_name='Jay' WHERE emp_no=101;

✅ Output: Row updated in base table too.

Now:

SELECT * FROM emp_view WHERE emp_no=101;

emp_no	e_name	salary
101	Jay	45000.00

---

iii. Delete from view

DELETE FROM emp_view WHERE emp_no=105;

✅ Output: Row deleted from both emp_view and base table emp.

If you check:

SELECT * FROM emp_view;

Row with emp_no 105 (Rahul) is gone.

---

iv. Drop view

DROP VIEW emp_view;

✅ Output: View deleted.

---

v. Modify location of dept_no in dept_view

👉 First, create a dept view (since not defined yet).

CREATE VIEW dept_view AS
SELECT dept_no, location FROM dept;

Now modify:

UPDATE dept_view SET location='Bengaluru' WHERE dept_no=20;

✅ Output: Dept_no 20 location updated.

If you check:

SELECT * FROM dept_view;

dept_no	location
10	Mumbai
20	Bengaluru
30	Delhi

---

vi. Write output of following queries

(This is already shown above with each step).

---

vii. Create simple index

CREATE INDEX dept_simple_index ON dept(location);

✅ Output: Index created on location.

---

viii. Create composite index

CREATE INDEX dept_composite_index ON dept(dept_no, location);

✅ Output: Composite index created.

---

ix. Drop indexes

DROP INDEX dept_simple_index ON dept;
DROP INDEX dept_composite_index ON dept;

✅ Output: Both indexes dropped.

---

x. Create index raj on emp

CREATE INDEX raj ON emp(emp_no, e_name);

✅ Output: Index raj created on employee number + name.

---

Question 2 Write output of following queries.

Solution:

a. Create sequence emp_sequence

CREATE SEQUENCE emp_sequence
INCREMENT BY 2
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

✅ Output:

Sequence EMP_SEQUENCE created.

---

b. Alter sequence emp_sequence

ALTER SEQUENCE emp_sequence
INCREMENT BY 15
MAXVALUE 1000
CYCLE
CACHE 20;

✅ Output:

Sequence EMP_SEQUENCE altered.

---

c. Drop sequence emp_sequence

DROP SEQUENCE emp_sequence;

✅ Output:

Sequence EMP_SEQUENCE dropped.

---