Exercícios de Procedures, triggers e Views em Banco de dados

Exercises involving Views, Procedures, Functions and Triggers

Consider the instance implemented and populated in SQL in the last activity for the LIBRARY

relational database schema in the next page, which is used to keep track of books, borrowers, and

book loans. Referential integrity constraints are shown as directed arcs in the figure.

Implement the following exercises using both PostgreSQL (PL/pgSQL) and Oracle Database

(PL/SQL) a sequence of SQL instructions to perform the following actions granting that no stored

data is lost or damaged. Submit a file with the script for PostgreSQL and another file for the

Oracle’s script, clearly delimiting the exercises in each script.

1. Create a materialized view month_borrowers that shows the data (card_no, name, address, and

phone) from the borrowers who had (or have) more than one loan whose length (i.e., the difference

between the date out and the due date) is greater than or equal to 30 days. The view should also

show the loan length, the book title and the branch name of these loans. Besides, perform updates to

the base tables, eventually run statements to make the DBMS update the view, and show the state of

the up-to-date materialized view.

2. Assume that after having deployed the database and loaded it with data, the library manager

decided to store book copies individually. From now on, the database should store an id, the

acquisition date, and the current conditions (fine, good, fair, or poor) for each copy. Book loans

should refer to a specific book copy. The attribute no_of_copies should no longer be stored in the

database. However, existing applications should "see" the database as if the schema had not been

updated for backward compatibility.

• Create a temporary table to save the current number of copies of each book in each branch.

• Implement the commands to perform the necessary change to the database schema.

• Write a query or a function that returns/shows the inconsistencies between the number of

copies of a book in a branch in the updated schema and the respective number of copies in

the temporary table. That is, identify the cases these numbers do not match.

• Insert book copies into the updated schema until there is no more such an inconsistency.

Commit the changes and drop the temporary table.

• Create a view with the same name as the old table (book_copies), showing the same content.

That is, an application could interact with the view as it was the old table.

◦ A delete from the view should trigger the deletion of the tuples corresponding to the

book copies.

◦ An update on the view to attributes book_id or branch_id should be automatically

redirected to the base tables.

◦ An update on the view reducing the number of copies cannot be accepted.

◦ An insert into the view or an update on the view increasing the number of copies should

trigger the insertion of book copy tuples such that the number of copies matches the

value provided to the update statement. The new tuples' id should be set using a

sequence,