Summary – CS482

• Chapter 1: Overview of DB and Transactions – Database vs Transaction

– Features of TP system

– Decision support system: online analytic processing (OLAP) vs online transaction processing (OLTP)

• Chapter 2: The Big Picture

– SQL (Simple)

– ACID properties

• Chapter 3: The Relational Data Model

– Relational data model

– SQL - Data Definition Language:

∗ create table, domain, assertion, etc.

∗ integrity constraint (primary key, foreign key, constraint, assertion, etc.)

∗ reactive constraints

• Chapter 4: Conceptual Modelling of DBs with ER Diagrams

– Conceptual design (ER approach) (distinguish between entity and relationship)

– Translation of ER diagram to relational database schema (algorithm: entity, relationship, keys)

• Chapter 5: Relational algebra and SQL

– The basic operators and derived operators

– SQL Query language

– Modifying relation instances in SQL

• Chapter 6: Database Design with the Relational Normalization Theory

– Redundancy and decomposition

– Functional dependencies

– Lossless and lossy decomposition

– Dependencies preserving decomposition

– Normal form ∗ BCNF (Boyce-Code Normal form)

∗ Third normal form

– Algorithm for BCNF-decomposition

– Multi-valued dependency, 4th normal form, decomposing to 4NF

• Chapter 7: Triggers and active database

– Trigger, how to create, how to use, problem with trigger

• Chapter 8: Using SQL in an application

– stored procedure

– JDBC