Module-4 In SQL, write the usage of GROUP BY and

.ACID properties ANS: The following are the HAVING clauses with suitable examples.
ACID properties: Atomicity: A transaction is an ● GROUP BY: The GROUP BY clause is used to
atomic unit of processing. It should either be group rows that have the same values in
performed in its entirety or not performed at specified columns. It is often used with
all. Consistency preservation: A transaction aggregate functions (COUNT, SUM, AVG, etc.). ●
should be consistency preserving, meaning that HAVING: The HAVING clause is used to filter the
if it is completely executed from beginning to groups based on certain conditions, similar to
end without interference from other WHERE but for groups. Example: SELECT
transactions, it should take the database from DeptID, COUNT(EmpID) AS EmployeeCount
one consistent state to another. Isolation: A FROM Employees GROUP BY DeptID HAVING
transaction should appear as though it is being COUNT(EmpID) > 5; ● GROUP BY DeptID groups
executed in isolation from other transactions, the rows by department. ● HAVING
even though many transactions are executing COUNT(EmpID) > 5 filters the groups to only
concurrently. That is, the execution of a those departments with more than 5
transaction should not be interfered with by employees. In this query, we count the number
any other transactions executing concurrently. of employees in each department and return
Durability or permanency: The changes applied DBMS-Solution_2024-RENCITA only the
to the database by a committed transaction departments with more than 5 employees.
must persist in the database. These changes Assertions & Triggers with example
must not be lost because of any failure. ❖ The ASSERTIONS in SQL:An assertion is a database
atomicity property requires that a transaction is constraint that enforces a condition that must
executed to completion. It is the responsibility always be true for the entire database, not just
of the transaction recovery subsystem of a a single [Link] is part of standard [Link] to
DBMS to ensure atomicity. ❖ The preservation enforce complex rules involving multiple
of consistency is generally considered to be the [Link] data consistency and integrity.
responsibility of the programmers who write Syntax:CREATE ASSERTION assertion_name
the database programs and of the DBMS CHECK ( <condition> );
module that enforces integrity constraints. CREATE ASSERTION RatingCheck
What are the views in SQL? Explain with CHECK (
examples. Views in SQL are virtual tables based NOT EXISTS (
on the result of an SQL query. They do not store SELECT * FROM Sailors
data themselves but provide a way to look at WHERE rating > 10
and manipulate the result of a query as if it ));
were a table. Example: CREATE VIEW Triggers in SQL: A trigger is a set of SQL
HighSalaryEmployees AS SELECT EmpID, statements that automatically executes when a
EmpName, Salary FROM Employees WHERE specified event (INSERT, UPDATE, DELETE)
Salary > 50000; In this example, a view called occurs on a table. Follows the ECA model:Event
HighSalaryEmployees is created based on the – The operation that fires the trigger (e.g.,
result of a query that selects employees with a INSERT, UPDATE).Condition – A test that must
salary greater than 50,000. Once the view is be TRUE for the action to runAction – What
created, you can query it as if it were a table: happens when the condition is met.
DBMS-Solution_2024-RENCITA SELECT * FROM Syntax :CREATE TRIGGER trigger_name
HighSalaryEmployees; Benefits of Views: 1. AFTER INSERT ON table_name
Simplicity: They simplify complex queries by FOR EACH ROW
encapsulating them in a view. 2. Security: You BEGIN
can restrict access to certain data by allowing -- trigger actions
users to query a view instead of the base table. END;
3. Data Abstraction: Views provide an Ex: CREATE TABLE SailorLog (sid INT,sname
abstraction layer over the database schema. VARCHAR(50),action_time TIMESTAMP
DEFAULT CURRENT_TIMESTAMP );
 M-3 CREATE TRIGGER log_sailor_insert
Relational Database Design using ER-to- AFTER INSERT ON Sailors
Relational mapping. Step 1: For each regular FOR EACH ROW
(strong) entity type E in the ER schema, create BEGIN
a relation R that includes all the simple INSERT INTO SailorLog(sid, sname)
attributes of E. Step 2: For each weak entity VALUES ([Link], [Link]);
type W in the ER schema with owner entity END;
type E, create a relation R, and include all System Log in Database Transactions
simple attributes (or simple components of Purpose:To recover from failures (like system
composite attributes) of W as attributes. In crash, power failure, etc.), the database system
addition, include as foreign key attributes of R maintains a log known as the System Log.
the primary key attribute(s) of the relation(s) This log records all the actions performed by
that correspond to the owner entity type(s). transactions to help in restoring the consistent
Step 3: For each binary 1:1 relationship type R state of the [Link] Log Contains the
in the ER schema, identify the relations S and T Following Entries:
that correspond to the entity types 1. [start_transaction, T]:Indicates that
participating in R. Choose one of the relations, transaction T has started its execution.
say S, and include the primary key of T as a 2. [write_item, T, X, old_value, new_value]
foreign key in S. Include all the simple Transaction T modified the item X from
attributes of R as attributes of S. Step 4: For old_value to new_value.
each regular binary 1:N relationship type R 3. [read_item, T, X]Transaction T has read
identify the relation (N) relation S. the primary the value of data item X.
key of T as a foreign key of S. Simple attributes 4. [commit, T] Transaction T has
of R map to attributes of S. Step 5: For each completed successfully and its changes
binary M:N relationship type R, create a can be permanently saved to the
relation S. Include the primary keys of database.
participant relations as foreign keys in S. Their 5. [abort, T]transaction T has been
combination will be the primary key for S. aborted (possibly due to failure or
Simple attributes of R become attributes of S. error), and its actions may need to be
Step 6: For each multi-valued attribute A, undone.
create a new relation R. This relation will Why is the System Log Important?
include an attribute corresponding to A, plus • In case of system crash, the DBMS uses
the primary key K of the parent relation (entity the log to:
type or relationship type) as a foreign key in R. o Redo committed transactions
The primary key of R is the combination of A o Undo aborted or incomplete
and K. Step 7: For each n-ary relationship type transactions
R, where n>2, create a new relation S to • Ensures Atomicity and Durability (from
represent R. Include the primary keys of the the ACID properties)
relations participating in R as foreign keys in S. NATURAL JOIN:
Simple attributes of R map to attributes of A NATURAL JOIN is a special type of join where
[Link] primary key of S is a combination of all matching is done automatically based on
the foreign keys that reference the participants columns with the same name in both tables.
that have cardinality constraint > 1. For a Key Features:Automatically matches and joins
recursive relationship, we will need a new columns with the same [Link]
relation. duplicate columns in the [Link] as a
EQUIJOIN (Equi-Join): refined version of EQUIJOIN. Example:
An EQUIJOIN is a type of JOIN that combines Suppose:PROJECT(Dnum,Pname)
rows from two or more tables based on DEPARTMENT(Dnumber, Dname)
equality of specified [Link] Features: SELECT *
Uses = operator in the ON or WHERE FROM PROJECT
[Link] joined tables retain both matching NATURAL JOIN DEPT;
columns, which have identical valuesSELECT
[Link], [Link], [Link] DEPARTMENT
D JOIN EMPLOYEE E ON D.Mgr_ssn = [Link];