Assertions and Triggering
Assertion: An assertion is a statement in SQL that ensures a certain condition will always exist in the database. Assertions are similar to column and table constraints except that they are specified separately from table definitions. NOT NULL is an example of a column constraint and a compound foreign key is an example of a table constraint because the compound cannot be declared with column constraints. Assertion is defined independently from any table. It is activated on a modification of any table that is mentioned in the assertion. The components of assertion includes a constraint name, followed by CHECK and followed by condition. The query result must be empty. If the query result is not empty then it is known that the assertion has been violated. A trigger is a statement that the system executes automatically as a side effect of a modification to the database. In order to design a trigger we must meet two requirements: Specification is required when a trigger is to be executed. The specification is broken up into two things. One is event that causes the trigger to be checked and the other is a condition that must be satisfied for the execution of a trigger to proceed. Specify the actions that are to be taken when the trigger executes. These above requirements are also referred to as the event-condition-action models of triggers. The database stores triggers as if they were regular data. This way they are persistent and are accessible to all database operations. Once a trigger is entered into the database, the database system takes on the responsibility of executing it whenever the event occurs and the condition is satisfied. A good use for a trigger would be, for instance, if you own a warehouse and you sell out of a particular item, to re-order that item and automatically generate the order invoice. So, triggers are considered to be very useful for automating things in your database. There are three parts of a trigger and they are: Event that activates the trigger insertion, deletion or update of the database. Condition (tests whether the trigger should run) a Boolean statement or a query. Action (what happens if the trigger runs) wide variety of options.
Summary
Assertion: An assertion is a statement in SQL that ensures a certain condition will always exist in the database. Assertions are similar to column and table constraints except that they are specified separately from table definitions. NOT NULL is an example of a column constraint and a compound foreign key is an example of a table constraint because the compound cannot be declared with column constraints. Assertion is defined independently from any table. It is activated on a modification of any table that is mentioned in the assertion. The components of assertion includes a constraint name, followed by CHECK and followed by condition. The query result must be empty. If the query result is not empty then it is known that the assertion has been violated. A trigger is a statement that the system executes automatically as a side effect of a modification to the database. In order to design a trigger we must meet two requirements: Specification is required when a trigger is to be executed. The specification is broken up into two things. One is event that causes the trigger to be checked and the other is a condition that must be satisfied for the execution of a trigger to proceed. Specify the actions that are to be taken when the trigger executes. These above requirements are also referred to as the event-condition-action models of triggers. The database stores triggers as if they were regular data. This way they are persistent and are accessible to all database operations. Once a trigger is entered into the database, the database system takes on the responsibility of executing it whenever the event occurs and the condition is satisfied. A good use for a trigger would be, for instance, if you own a warehouse and you sell out of a particular item, to re-order that item and automatically generate the order invoice. So, triggers are considered to be very useful for automating things in your database. There are three parts of a trigger and they are: Event that activates the trigger insertion, deletion or update of the database. Condition (tests whether the trigger should run) a Boolean statement or a query. Action (what happens if the trigger runs) wide variety of options.
Things to Remember
- An assertion is a statement in SQL that ensures a certain condition will always exist in the database. Assertions are similar to column and table constraints except that they are specified separately from table definitions.
- NOT NULL is an example of a column constraint and a compound foreign key is an example of a table constraint because the compound cannot be declared with column constraints.
- Assertion is defined independently from any table. It is activated on a modification of any table that is mentioned in the assertion. The components of assertion includes a constraint name, followed by check and followed by condition.
- A trigger is a statement that the system executes automatically as a side effect of a modification to the database.
- In order to design a trigger we must meet two requirements:
Specification is required when a trigger is to be executed. The specification is broken up into two things. One is event that causes the trigger to be checked and the other is a condition that must be satisfied for the execution of a trigger to proceed.
Specify the actions that are to be taken when the trigger executes. - There are three parts of a trigger and they are:
Event that activates the trigger insertion, deletion or update of the database.
Condition (tests whether the trigger should run) a Boolean statement or a query.
Action (what happens if the trigger runs) wide variety of options.
MCQs
No MCQs found.
Subjective Questions
Q1:
What are the physiological changes in musculo skeletal system ?
Type: Short Difficulty: Easy
<li>Due to the effect of the progesterone and relaxin increases relaxation of ligament, muscles, reaching maximum affect during last week of pregnancy. The relaxation allows the pelvis to increase its capacity to accommodate the fetal presenting part at the end of the pregnancy and labor.</li>
<li>All joints loosen allowing the coccyx to be displaced backward. Rolling gait is due to unstable pelvic joint.</li>
<li>Increased mobility of pelvic joint due to softening of the ligament and increased lardosis during later months of pregnancy causes a backache and waddling gait.</li>
<li>The daily calcium requirement during pregnancy is high (average 1-1.5 gm). Depletion of calcium in diet result in mobilization of calcium from the bone, which causes osteomalacia or osteoporosis or both.</li>
</ol>
Q2:
What are the physiological changes that occurs in metabolism during pregnancy ?
Type: Short Difficulty: Easy
<li>Total metabolism is increased due to the needs of the growing uterus and the fetus. Basal metabolic rate is increased to be an extent of 30% higher than that of the average of the non-pregnant women.</li>
<li>During a normal pregnancy, about 1000 gm of protein is stored (450 gm in a fetus, 450 gm in a uterus, other are in a breast, plasma protein, and hemoglobin). Conversion of amino acid to urea is suppressed.</li>
<li>Carbohydrate metabolism: Normal pregnancy show fasting, hypoglycemia, and postprandial hyperglycemia leads to a diabetic type state in a non diabetic woman, which reverse after delivery.</li>
</ol>
Videos
No videos found.
Assertions and Triggering
Assertions
An assertion is a statement in SQL that ensures a certain condition will always exist in the database. Assertions are similar to column and table constraints except that they are specified separately from table definitions. NOT NULL is an example of a column constraint and a compound foreign key is an example of a table constraint because the compound cannot be declared with column constraints. Assertion is defined independently from any table. It is activated on a modification of any table that is mentioned in the assertion. The components of assertion include:
- A constraint name
- Followed by CHECK
- Followed by condition
The query result must be empty. If the query result is not empty then it is known that the assertion has been violated. For example: The salary of an employee must not be greater than the salary of a manager of the department that the employee works for:
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXIST (SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE SALARY> M.SALARY AND
E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))
Number of boats + number of sailors is greater than 100
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100);
Triggering
A trigger is a statement that the system executes automatically as a side effect of a modification to the database. In order to design a trigger we must meet two requirements:
- Specification is required when a trigger is to be executed. The specification is broken up into two things. One is event that causes the trigger to be checked and the other is a condition that must be satisfied for the execution of a trigger to proceed.
- Specify the actions that are to be taken when the trigger executes.
These above requirements are also referred to as the event-condition-action models of triggers. The database stores triggers as if they were regular data. This way they are persistent and are accessible to all database operations. Once a trigger is entered into the database, the database system takes on the responsibility of executing it whenever the event occurs and the condition is satisfied. A good use for a trigger would be, for instance, if you own a warehouse and you sell out of a particular item, to re-order that item and automatically generate the order invoice. So, triggers are considered to be very useful for automating things in your database. There are three parts of a trigger and they are:
- Event that activates the trigger insertion, deletion or update of the database.
- Condition (tests whether the trigger should run) a Boolean statement or a query.
- Action (what happens if the trigger runs) wide variety of options.
References:
- H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
- A.K.Majumdar and p, Bhattacharaya,"Database Management Systems",Tata McGraw Hill,India,2004
- F.Korth, Henry. Database System Concepts. 6th edition.
Lesson
Database Constraints and Normalization
Subject
Computer Engineering
Grade
Engineering
Recent Notes
No recent notes.
Related Notes
No related notes.