28 06 18ts

Page 1

DATA BASES 2 – JUNE 28TH, 2018 – TIME: 2H 15M PROF. DANIELE M. BRAGA, PROF. STEFANO PARABOSCHI A. Active Databases (9 p.) CHESSPOSITION(PieceId, CurRow, CurCol) PIECE(Id, Type, Color, InitRow, InitCol) MOVE(MoveNum, StartRow, StartCol, EndRow, EndCol, MovedPieceId) Table CHESSPOSITION describes the current position of pieces on the chessboard in a chess game (row, column). Table PIECE contains 32 tuples, each describing the type (King, Queen, Rook, Bishop, Knight, Pawn), color (White or Black), and initial position of each piece . Table MOVE describes the moves of pieces: moves are numbered incrementally starting from 1, and players alternate in the execution of moves , so that t he White player (controlling white pieces) plays the “odd” moves and the Black player plays the “even” ones. Attribute MovedPieceId refers to the Id of the piece being moved, and is always null when new moves are inserted. For simplicity, “special” moves (like castling, en-passant, and pawn promotion) are not considered here. By chess rules, when a piece p1 is moved into a square occupied by a piece p2 of the opposite color, p2 is “captured” and removed from the chessboard. Design a set of triggers that react to insertions of new moves, and assign the Id of the moved piece to attribute MovedPieceId (overwriting the initially null value). Consider a scenario in which the whole content of the MOVE table is inserted by a single statement and let triggers react to the initial “bulk” insertion and to updates to the MovedPieceId attribute, assigning a correct value to this attribute for all the tuples in the MOVE table. B. Recovery (6 p.) We have a system that manages checkpoints by using two records in the log. A StartCheckpoint record signals the start of the checkpoint, presenting the list of…