Transaction processing system consists of a TP monitor, databases, and transactions: System Requirements: High; Availability, Reliability, Throughput, low response time, long lifetime, security

Normalisation: • A normal form (NF) is a condition on a schema (guaranteeing certain desirable properties — relating to redundancy). • 1NF: ≈ the definition of the Relational Model (relation = set of tuples; tuple = series of atomic values) • 2NF: scientific accident, not useful. • 3NF: always preserves FD (4NF & BCNF sometimes don’t) (zie definitie hier rechts ) • 4NF: verderop beschreven • BCNF (Boyce-Codd Normal Form): the schema has no unavoidable or trivial FDs,

that is, in each FD in the schema, the lefths is a superkey or includes the righths. Hence, if a schema is in BCNF, then there is no redundancy-caused-by-FDs.

• Definition: X Y is trivial if: Y isSubset X (alle “elementen” uit Y zitten ook in X). X Y is unavoidable if: X is superkey (or key). (private terminology)

Denormalisation: • Normalisation helps maintain consistency (no anomalies) and save space, but performance might suffer because data is distributed over multiple rel’ns. • Tradeoff: judiciously introduce redundancy to improve performance. • Example. Recall the BCNF schema for the student registration system:

Student(StudId,Name,...), Transcript(StudId,...,Grade), . . . Alas, “getting names and grades” needs a (costly!) join. Adding Name to Transcript introduces redundancy and eliminates the join: Student(StudId,Name,...), Transcript(StudId,Name,...,Grade), . .

• With many queries and few updates, added redundancy improves performance.

Decomposition: • r isSubset r1 UNION r2 is always true

r1 UNION r2 isSubset may sometimes fail. • Theorem. The decomposition is lossless (“always r = r1 UNION r2”) iff

the common attributes form a key for either of the components: Y X or Y Z is in F+.

C D betekent C isSubset D Betekent isIn Axiom = vanzelfsprekendheid

De aanduiding covering betekent: altijd is een unilid ook prof of student of beide (nooit null). De aanduiding disjunct betekent: nooit is een unilid zowel prof als ook student ((=XOR)(disjunct kan ook null zijn)).

Give, per student, his avg grade from INF courses, provided all those grades > 5. Order the students by their avg grade, in descending order (highest grade first). { m:Movies | “m is gemaakt na 1998” @ m.name, m.rating } { m:Movies | m.year > 1998 @ m.name, m.rating } SELECT m.name, m.rating FROM Movies m WHERE m.year > ‘1998’;

ERD= Entity relation diagram RM= Relations Model

VOOR ELKE ENTITEITSTYPE ÉN RELATIE EEN

(x :TXT | “schrijver heeft een recente film geschreven” en “alle recente films van deze schrijver zijn door tenminste één ervaren regisseur geregisseerd” @ schrijver ) (x :TXT | (m: Movies, w: Writers, p: Persons | m.year > 1999 AND m.movie_id = w.movie_id AND w.person_= p.person_id @ p.name) en “alle recente films van deze schrijver zijn door tenminste één ervaren regisseur geregisseerd” @ p.name ) {x :TXT | (m: Movies, w: Writers, p: Persons | m.year > 1999 AND m.movie_id = w.movie_id AND w.person_= p.person_id @ p.name) en FOR ALL (m1: Movies | m1.year > 1999 AND m1.movie_id = w.movie_id @ EXISTS (d: Director, d1: Director | d.movie_id = m1.movie_id AND d.person_id = d1.person_id AND d1.movie_id != d.movie_id)) @ p.name } {p Persons | (m: Movies, w: Writers, p: Persons | m.year > 1999 AND m.movie_id = w.movie_id AND w.person_id = p.person_id @ p.name) en NOT EXISTS (m1: Movies | m1.year > 1999 AND m1.movie_id = w.movie_id @ NOT EXISTS (d: Director, d1: Director | d.movie_id = m1.movie_id AND d.person_id = d1.person_id AND d1.movie_id != d.movie_id)) @ p.name } SELECT DISTINCT p.name FROM Movies m, Writers w, Persons p WHERE m.year > 1999 AND m.movie_id = w.movie_id AND w.person_id = p.person_id AND NOT EXISTS (

SELECT m.movie_id FROM Movies m1 WHERE m1.year > 1999 AND m1.movie_id = w.movie_id AND NOT EXISTS (