Views and Assertions

Views in MySQL

A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table, and the fields in a view are fields from one or more real tables in the database.

Creating a View

Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, department
FROM employees
WHERE department = 'Sales';

Using the View

You can query a view as you would a regular table:
SELECT * FROM employee_view;

Modifying a View

To modify an existing view, use the ALTER VIEW statement:

ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Dropping a View

To drop a view, use the DROP VIEW statement:
DROP VIEW view_name;

Example:

Let's create a scenario with a table, views, and some queries and operations based on them. We'll use a simple database for a company's employee management system.

SQL to Create the Table:

CREATE TABLE employees (

    employee_id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    department VARCHAR(50),

    salary DECIMAL(10, 2),

    hire_date DATE

);

Step 2: Insert Sample Data

Let's insert some sample data into the employees table.

SQL to Insert Sample Data:

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('John', 'Doe', 'Sales', 60000.00, '2020-01-15'),
('Jane', 'Smith', 'Marketing', 65000.00, '2019-03-22'),
('Emily', 'Johnson', 'Sales', 70000.00, '2018-11-05'),
('Michael', 'Brown', 'IT', 75000.00, '2017-09-18'),
('Jessica', 'Davis', 'HR', 68000.00, '2021-02-14');

Step 3: Create Views

Let's create two views:
  • A view to list all employees in the 'Sales' department.
  • A view to list all employees hired after a certain date.

SQL to Create Views:
Sales Department View:

CREATE VIEW sales_employees AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE department = 'Sales';

Employees Hired After a Certain Date:
CREATE VIEW recent_hires AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE hire_date > '2020-01-01';

Step 4: Query the Views

Now, let's write some queries to fetch data from these views.

SQL Queries:
Select All Employees in the Sales Department:
SELECT * FROM sales_employees;

Select All Employees Hired After January 1, 2020:
SELECT * FROM recent_hires;


Step 5: Perform Operations

Let's perform some operations based on these views.

Give a 10% Salary Raise to All Sales Employees:
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id IN (SELECT employee_id FROM sales_employees);

Delete All Employees Hired After January 1, 2020:
DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM recent_hires);


Step 6: Verify the Operations

After performing the operations, let's verify the changes.

SQL to Verify Changes:

Verify Salary Raise for Sales Employees:
SELECT * FROM sales_employees;

Verify Deletion of Recent Hires:
SELECT * FROM recent_hires;


In MySQL, you can use views to update the original table as long as the view satisfies certain conditions. For a view to be updatable, it must be a simple view that maps directly to a single base table without complex joins, subqueries, or aggregation.

Let's go through the process of updating the original employees table using the views we created earlier.

Updating the Original Table Using Views
Scenario 1: Update Salaries for Sales Employees

We have a view sales_employees which lists all employees in the Sales department. We can use this view to update the salaries of these employees.

View Definition:
CREATE VIEW sales_employees AS
SELECT employee_id, first_name, last_name, salary, hire_date
FROM employees
WHERE department = 'Sales';

Update Example: Let's give a 10% salary raise to all Sales employees.
UPDATE sales_employees
SET salary = salary * 1.10;

Explanation: This update will directly modify the salary field in the employees table for all employees where the department is 'Sales'.

Scenario 2: Delete Employees Hired After a Certain Date

We have a view recent_hires which lists all employees hired after January 1, 2020. We can use this view to delete these employees.

View Definition:

CREATE VIEW recent_hires AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE hire_date > '2020-01-01';

Delete Example: Let's delete all employees who were hired after January 1, 2020.
DELETE FROM recent_hires;

Explanation: This delete operation will remove the corresponding rows from the employees table for all employees who were hired after January 1, 2020.

Important Notes:

  1. Updatable Views: Not all views are updatable. A view is generally updatable if it:

    • Contains only one table.
    • Does not use DISTINCT.
    • Does not contain any aggregate functions (SUM, MIN, MAX, COUNT, etc.).
    • Does not use GROUP BY or HAVING.
    • Does not have a UNION or UNION ALL.
    • Does not contain subqueries in the SELECT clause or FROM clause.
    • Does not use JOIN.

  2. Inserting and Updating through Views: If the view is updatable, you can perform INSERT, UPDATE, and DELETE operations directly on the view, and these operations will affect the base table.

  3. Privileges: Ensure that the user performing the operations has the necessary privileges on both the view and the underlying table.


Summary

Using views to update the original table in MySQL can simplify certain operations and provide a level of abstraction. This approach works well as long as the views are simple and meet the criteria for being updatable. Here are the steps to update the original table using views:
  • Define an updatable view based on the original table.
  • Perform UPDATE or DELETE operations directly on the view.
  • Ensure the view meets the conditions for being updatable and the user has the necessary privileges.

********************************************************************

Assertions

In the context of databases, assertions are integrity constraints that enforce certain conditions on the data across multiple tables or the entire database. They are used to ensure that the data adheres to specific rules, maintaining the accuracy and consistency of the data. Assertions are typically implemented as part of the database schema and are enforced automatically by the database management system.

Example of Assertions

Suppose you have a scenario where you want to ensure that the total number of employees in the 'Sales' department does not exceed 100.

In some database systems (e.g., SQL Server, Oracle), you might use assertions like this:

Syntax (Hypothetical):

CREATE ASSERTION max_sales_employees CHECK ( (SELECT COUNT(*) FROM employees WHERE department = 'Sales') <= 100 );


However, MySQL does not directly support the CREATE ASSERTION statement. Instead, you can use triggers or stored procedures to enforce these types of constraints.

Simulating Assertions in MySQL with Triggers

Here’s how you might simulate the above assertion using a trigger in MySQL:

Step 1: Create the Table

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(50) );

Step 2: Create a Trigger to Enforce the Constraints

CREATE TRIGGER max_sales_employees_check BEFORE INSERT ON employees 
    FOR EACH ROW 
    BEGIN 
     DECLARE sales_count INT; 
     SELECT COUNT(*) INTO sales_count FROM employees WHERE department = 'Sales'; 
     IF NEW.department = 'Sales' AND sales_count >= 100 THEN SIGNAL SQLSTATE '45000' 
     SET MESSAGE_TEXT = 'Cannot add more than 100 employees to the Sales department'; 
     END IF; 
END;

This trigger will prevent the insertion of a new employee into the 'Sales' department if the total number of employees in that department is already 100.

Key Points
Assertions: General constraints that apply to multiple rows or tables, ensuring data integrity across the database.
Triggers: In MySQL, assertions can be simulated using triggers that enforce rules at the time of data modification (INSERT, UPDATE, DELETE).
Stored Procedures: Can also be used to enforce complex business rules and validations.

By using triggers and stored procedures, you can effectively implement and enforce complex integrity constraints and business rules in MySQL, achieving similar results to assertions in other database systems.


Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more