Create VIEW view2 AS

SELECT artist.idArtist AS id,

concat(FirstName, ' ', LastName) AS nome,

DateDeceased-DateofBirth as vita,

(SELECT Count(picture.idPicture) FROM picture

WHERE Artist.idArtist = Picture.ArtistidArtist) AS opera

FROM Artist;

trigger is a stored program that is executed by the DBMS whenever a specific event occurs

Provides default changes

Enforces data constraints especially when interrelations are involved

Implements referential integrity actions

CREATE TRIGGER RecordChange

BEFORE UPDATE ON Artist

FOR EACH ROW
BEGIN

IF OLD.DateDeceased != NEW.DateDeceased

THEN

INSERT INTO audit(Changed, oldValue, Artist_idArtist)

VALUES(Now(), OLD.DateDeceased, OLD.idArtist);

END IF;

END; 

CREATE TRIGGER CheckAge

BEFORE INSERT ON Artist

FOR EACH ROW
IF NEW.DateDeceased < NEW.DateOfBirth

THEN CALL FAIL('DateOfBirth is after DateDeceased');

END IF; 

CREATE PROCEDURE countBooks(OUT result INT)

BEGIN

SET result = (SELECT COUNT(*) FROM Book);

END;

Are called by value and are read only to the body of the code

CREATE PROCEDURE booksByAuthor

(in authorName VARCHAR(45))

BEGIN

SELECT Book.title FROM

Book, Author, Book_has_author

WHERE idBook=Book_idBook

AND idAuthor=Author_idAuthor

AND Author.name = authorName;

END;

Benefits of Stored procedures

• Decrease in network traffic by putting more work on the server
• Security is increased becuase the administrator can allow use of stored procedures without granting permission on the underlying tables
• Encourage reuse of code
• Can be used to provide a database encapsulation layer that hides details of the database form the business code.

• Redundancy: Information repeated unnecessarity
• Update: We could cahnge information in one place but not another
• Insert: We could enter data inconsistently
• Delete: If a set of values becomes empty, we lose the facts associated with that set of values

Property that states that if two tuples agree on a set of attributes {A1, ... An} they must also agree on other set of attributes {B1, ... Bm}

The set of {A1, ..} is called the determinant and the sent {B1, ...} is the dependent

Given a set of attributes {A1, ... , An} and a set of Functional Dependencies S, the closure of {A1, ... , An} under the set S of FDs is the set of attributes B such that every relation that satisfies all of the FDs in S also satisfies {A1, ... , An} -> B

The closure of {A1, ... , An} is denoted by {A1, ... , An}⁺