***** PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!! ***** PL
ID: 3918534 • Letter: #
Question
*****PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!!
*****PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!!
*****PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!!
*****PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!!
*****PLEASE ANSWER ONLY QUESTION E USING SQL STANDARD!!!!!!!!!!!!!!!!!
Exercise 7.5.3: Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The database schema is from the battleships example of Exercise 2.4.3. Classes (class, type, country, numGuns, bore, displacement) Ships (name, class, launched) Battles (name, date) Outcomes(ship, battle, result) a) When a new class is inserted into Classes, also insert a ship with the b) When a new class is inserted with a displacement greater than 35,000 ! c) If a tuple is inserted into Outcomes, check that the ship and battle are name of that class and a NULL launch date. tons, allow the insertion, but change the displacement to 35,000. listed in Ships and Battles, respectively, and if not, insert tuples into one or both of these relations, with NULL components where necessary of Ships, check that no country has more than 20 ships. fought in a battle that was at a later date than another battle in which 1 d) When there is an insertion into Ships or an update of the class attribute !! e) Check, under all circumstances that could cause a violation, that no ship that ship was sunk.Explanation / Answer
Working in MS SQL Server.
Query No. 3 Answers c & e both.
That is a INSTEAD OF trigger on Outcomes table which checks for both the conditions mentioned in c & e.
-- 1
GO
CREATE TRIGGER addship
ON Classes AFTER INSERT
AS
Insert Into Ships(name, class)
select class,class from inserted;
-- 2
GO
CREATE TRIGGER checkDisplacement
ON Classes INSTEAD OF INSERT
AS
IF((Select displacement from inserted) > 35000)
BEGIN
INSERT INTO Classes (class,[type],country,numGuns,bore,displacement)
SELECT class,[type],country,numGuns,bore,35000 FROM inserted
END
ELSE
BEGIN
INSERT INTO Classes (class,[type],country,numGuns,bore,displacement)
SELECT class,[type],country,numGuns,bore,displacement FROM inserted
END
-- 3
GO
CREATE TRIGGER checkOutcome
ON Outcomes INSTEAD OF INSERT
AS
Declare @SunkDate date;
Declare @Fought date;
Select @Fought = b.[date]
From Battles b INNER JOIN inserted i
on b.name = i.battle;
Select @SunkDate = Min(b.[date])
From Outcomes o INNER JOIN inserted i
on o.ship = i.ship
INNER JOIN Battles b
on o.battle = b.name
where o.result = 'sunk';
-- Insert only if not sunk before this battle.
IF(@SunkDate IS NULL OR @SunkDate > @Fought)
BEGIN
IF Not Exists (Select TOP 1 1 From Ships s INNER JOIN inserted i
on s.name = i.ship)
BEGIN
Insert Into Ships (name) Select ship from inserted;
END
IF Not Exists (Select TOP 1 1 From Battles b INNER JOIN inserted i
on b.name = i.battle)
BEGIN
Insert Into Battles(name) Select battle from inserted;
END
Insert Into Outcomes(ship,battle,result)
Select ship,battle,result From inserted;
END
-- 4
GO
CREATE TRIGGER checkCount
ON Ships INSTEAD OF INSERT, UPDATE
AS
IF((Select COUNT(*)
From inserted i INNER JOIN Classes c
on i.class = c.class
INNER JOIN Classes cc
on c.country = cc.country
INNER JOIN Ships s
on cc.class = s.class) < 20)
BEGIN
Insert Into Ships(name, class, launched)
Select name,class,launched from inserted;
END
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.