DB2 Finals Session 2 Ex1 Solution

/* Part 1 */
CREATE INDEX boat_reservation_index ON Reservation(
    bid     ASC
)

--------------------------------------------------------------------

/* Part 2 */ /* Trigger */
CREATE TRIGGER reservations_trigger ON Reservation FOR INSERT
AS
BEGIN
    DECLARE @boatID INT;

    SELECT @boatID = i.bid FROM inserted i;

    UPDATE Boats
    SET nbreserves = (nbreserves + 1)
    WHERE bid = @boatID;

END

--------------------------------------------------------------------

/* Part 3 */
CREATE FUNCTION nbEmployeeReservation
    (@empID INT)
RETURNS
    INT
AS
BEGIN
    DECLARE @COUNT INT;

    SELECT @COUNT = COUNT(eid)
    FROM Reservation r
    WHERE r.eid = @empID;

    RETURN @COUNT;
END

-------------------------------------------------------------------

/* Part 4 */
CREATE TRIGGER checkBoatVacancy ON Reservation INSTEAD OF INSERT
AS
BEGIN
    DECLARE @boatID INT;
    DECLARE @reservationDate DATE;
    DECLARE @COUNT INT;

    SELECT @boatID = i.bid FROM inserted i;
    SELECT @reservationDate = i.DAY FROM inserted i;

    IF EXISTS (
        SELECT *
        FROM Reservation r
        WHERE r.bid = @boatID AND r.DAY = @reservationDate
        )
    BEGIN
        print 'Boat taken at this date, reservation failed';
        ROLLBACK TRANSACTION;
    END
END

-------------------------------------------------------------------

/* Part 5 */
CREATE VIEW ViewBookDays AS
    SELECT ename, bname, day_rate
    FROM Boats AS b
    INNER JOIN Reservation AS r
    ON b.bid = r.bid
    INNER JOIN Employees AS e
    ON e.eid = r.eid;

-------------------------------------------------------------------

/* Part 6 */
DECLARE @boat INT = 1; /* or 2800 in exam */

BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM Reservation
        WHERE bid = @boat;

        DELETE FROM Boats
        WHERE bid = @boat;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    print 'Error deleting boat';
    ROLLBACK TRANSACTION
END CATCH

-------------------------------------------------------------------

/* Part 7 */
CREATE PROC createEmpAccount
    @EmpID INT
AS
BEGIN
    DECLARE @EmpUsername VARCHAR(256) = 'Emp' + CONVERT(VARCHAR(256),@EmpID);
    DECLARE @createUser VARCHAR(MAX);
    DECLARE @createView VARCHAR(MAX);
    DECLARE @grantQuery VARCHAR(MAX);

    SET @createUser = 'CREATE USER ' + @EmpUsername + ' WITH PASSWORD = ''' + CONVERT(VARCHAR(256),@EmpID) + '''';
    EXEC(@createUser);

    SET @createView = 'CREATE VIEW ' + @EmpUsername + 'Bookings AS
                        SELECT * FROM Reservation WHERE eid = ' + CONVERT(VARCHAR(256),@EmpID);
    EXEC(@createView);

    SET @grantQuery = 'GRANT UPDATE ON ' + @EmpUsername + 'Bookings TO ' + @EmpUsername;
    EXEC(@grantQuery);

    SET @grantQuery = 'GRANT INSERT ON Reservation TO ' + @EmpUsername;
    EXEC(@grantQuery);

END

SET NOCOUNT ON
DECLARE @ID INT;
DECLARE cur CURSOR FOR
SELECT eid FROM Employees
OPEN cur
    IF(@@CURSOR_ROWS < 0)
    BEGIN
        FETCH NEXT FROM cur INTO @ID

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC createEmpAccount @ID;

            FETCH NEXT FROM cur INTO @ID
        END
    END
CLOSE cur
DEALLOCATE cur
SET NOCOUNT OFF