Stored program that is executed whenever a specific event occurs.

BEFORE/AFTER INSERT/UPDATE/DELETE

• Provide default values
• Good for keeping a history of data without needing procedural code
• Validating business logic
• Security checking

• Advantages
• Help eliminate redundant code - instead of copying functionality in every client applciation, trigger stored once
• Improved security and integrity
• Disadvantages
• Performance overhead if trigger query too complex
• MyISAM tables produce a full table lock during the INSERT

CREATE TRIGGER HolidayChange

BEFORE UPDATE ON CustomerHoliday

FOR EACH ROW

BEGIN

IF OLD.StartDate != NEW.StartDate

THEN

INSERT INTO CHANGES (CustomerId, HolidayNo, NewDate, oldDate) VALUES (Old.CustomerNo, Old.HolidayNo, NEW.StartDate, Old.StartDate)

END IF

END

Procedures stored within the databaes

Parameter passing (by value) is possible - procedure can accept input and output parameters

• OUT – The parameter that will be returned, we can’t assume a value will be given at input.

• IN – A formal parameter for the procedure.

• INOUT – A formal parameter which will be returned by the procedure.

• Advantages
• Shared logic with different kind of applciations - encapsulate functionality
• Data validation and integrity
• Isolate users from access to the actual tables
• Provide a security mechanism - Users have access only to stored procedures
• Improve performance  - deduce traffic
• Avoid errors made by users through transparency (hiding functionality)
• Disadvantages
• Load one the database server
• Hard to migrate
• Might repeat logic with client appliction (Difficult to maintain)

DECLARE lim INT;

SET lim = (SELCT limit FROM table WHERE id = 10);

IF(expression)

THEN

SQL statement

ELSE

SQL statement

END IF;

The process of removing the functional dependencies that cause the problems

Normalisation is used to avoid or eliminate several type of anomalities:

• Redundancy
• Update
• Insert
• Delete

• Redundancy – information is repeated unnecessarily.

• Update – we change information about an entity in one place but not another.

•  

  Insert – we enter data inconsistently.

• Delete – If a set of values becomes empty we lose the facts associated with that set of values.

• On a relation R, if two tuples agree on a set of attributes {A1, ... , An} they must also agree on another set of attributes {B1, ... , Bm}
• {A1, ... , An} -> {B1, ... , Bm} Iff all values A are associated with at most one value Y.
• The Set {A1...} is called determinant and the set {B1, ...} is called independent

• Reflexive: Y ⊆ X =>X → Y
• Augmentation: X → Y => XZ → YZ
• Transitive: X → Y ⋀ Y → Z => X → Z
• Union: X → Y ⋀ X → Z  => X → YZ
• Decomposition: X → YZ => X → Y ⋀ X → Z
• Pseudo transitivity: A → B ⋀ BC → D => AC → D

A relation schema R is in Boyce-Codd Normal Form iff for every functional dependency X -> Y, at least one of the following holds:

• X -> Y is trivial (Y is a subset of X)
• X is a superkey for R

A relation with two attributes is always in BCNF

1. If there are violations for one X -> Y compute the following sets
• R1 = X+
• R2 = X Union (R - X+)
2. Determine FDs for R1 and R2
3. Apply the algorithm to decompose R1 and R2 is not in BCNF

1. Make a table with all the attributes as columns and a row for each of the decomposed relations
2. For each column in a row
1. If the attribute is in the current relation put the name of the attribute (A for example)
2. Otherwise put a uniquely numbered name of the attribute (A1 for example)
3. Force the funtional dependencies in the original relation on each row of the table
4. If after all dependencies are enforced there is a row with all attribute names without numbers, you have a lossless join

A relation schema is in 3NF iff for each functional dependency X -> Y one of the following is true:

• X -> Y is trivial
• X is a superkey
• Every attribute in Y-X is a member of some key

• Given a relation R and Functional dependencies FD
• Find a minimal basis for FD, say G (find redundant implications in FD)
• For each functional dependency X -> Y use (X, Y) as a new relation in the decomposition
• If none of the sets is a superkey add relations whose schema is a key of R to the decomposition