DATABASE SYSTEM CONCEPTS (LOOSELEAF)
DATABASE SYSTEM CONCEPTS (LOOSELEAF)
7th Edition
ISBN: 9781260515046
Author: SILBERSCHATZ
Publisher: MCG
Expert Solution & Answer
Book Icon
Chapter 5, Problem 5PE

Explanation of Solution

Trigger on an insert of record into section table:

Create trigger check_instructor_insert_in_section after insertion section referencing new table as inserted for each statement when inserted.course_id, inserted.sec_id, inserted.semester, inserted.year, inserted.item_slot_id in (select course_id, sec_id, semester, year, time_slot_id, count (distinct building, room_no)

from instructor natural join teaches natural join section group by (ID, name, sec_id, semester, year, time_slot_id) having count (building, room_no) > 1)

begin

    rollback

end;

Explanation:

  • The prefix inserted is added to the set of tables with newly inserted records.
  • Initially, define the trigger, which comes into action when an insertion into section table takes place.
  • Call the “tigger check_instructor_insert_in_section”.
  • Use “after insert” on clause to specify the trigger comes into effect after an insert takes place into section table.
  • Use the new name for account table as inserted and put it as such in referencing new table as clause.
  • Use the select query in for each statement insert into when clause. This query will get all those instructors, which violate the given constraint.
    • First, perform a natural join of instructor, teaches and section table.
    • Count the number of distinct buildings and room numbers from the join using a group by clause on “ID”, “name”, “sec_id”, “semester”, “year”, and “time_slot_id” attributes.
  • Select those instructor, section details from t query for which the above-calculated count is more than 1.
  • Finally, use the “begin” and “end” block to specify that any transaction, which violates this constraint, will be rolled back...

Blurred answer
Students have asked these similar questions
I would like to get help to resolve the following case
Last Chance Securities The IT director opened the department staff meeting today by saying, "I've got some good news and some bad news. The good news is that management approved the payroll system project this morning. The new system will reduce clerical time and errors, improve morale in the payroll department, and avoid possible fines and penalties for noncompliance. The bad news is that the system must be installed by January 1st in order to meet new federal reporting rules, all expenses from now on must be approved in advance, the system should have a modular design if possible, and the vice president of finance would like to announce the new system in a year-end report if it is ready by mid-December." Tasks 1. Why is it important to define the project scope? How would you define the scope of the payroll project in this case? 2. Review each constraint and identify its characteristics: present versus future, internal versus exter- nal, and mandatory versus desirable. 3. What…
2. Signed Integers Unsigned binary numbers work for natural numbers, but many calculations use negative numbers as well. To deal with this, a number of different methods have been used to represent signed numbers, but we will focus on two's complement, as it is the standard solution for representing signed integers. 2.1 Two's complement • Most significant bit has a negative value, all others are positive. So, the value of an n-digit -2 two's complement number can be written as: Σ2 2¹ di 2n-1 dn • Otherwise exactly the same as unsigned integers. i=0 - • A neat trick for flipping the sign of a two's complement number: flip all the bits (0 becomes 1, or 1 becomes 0) and then add 1 to the least significant bit. • Addition is exactly the same as with an unsigned number. 2.2 Exercises For questions 1-3, answer each one for the case of a two's complement number and an unsigned number, indicating if it cannot be answered with a specific representation. 1. (15 pts) What is the largest integer…
Knowledge Booster
Background pattern image
Similar questions
SEE MORE QUESTIONS
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education