Databases summary lecture slides

Introduction and Overview

- A database is a collection of data with
● certain logical structure
● specific semantics
● specific group of users
- A database management system allows to
● create, modify and manipulate database
● query data
● support persistent storage of large amounts of data
● enable durability and recovery from failure
● control access to data by many users in parallel
- Motivation for DBMS
● data independence
● avoidance of duplication
● high-level declarative query languages
- imperative languages
- declarative languages
- SQL is a declarative data manipulation language. The user
describes conditions the requested data is required to
fulful.
● well-defined data models & data integrity constraints
● concurrent access (ACID properties)
● persistent storage, safety and high availability
● scalability
● security
- ANSI SPARC Architecture
● View level
● Logical level
● Physical level
- Database schema : structure of the database (relations + constraints)
- Database instance: actual content (state) of the database at some
moment
- A transaction is a collection of operations that performs a single logical
function in a database application.




Functional Dependencies and Database Schema Normalisation

- Functional Dependencies are a generalization of keys, central part of
relational database design theory.
- Bad database design if schema contains relations that store data
redundantly and information is intermixed

, - Normalization algorithms can construct good relation schemas from a set
of attributes and a set of FD’s. When ER model is well designed ->
automatically in BCNF.
- First Normal Form: requires that all table entries are atomic
- The goal of database normalisation is to turn FD’s into keys
- Implication of FD’s : only interested in a representative FD set. Armstrong
Axioms:
● Reflexivity
● Augmentation
● Transitivity
● (Cover of an FD/attribute)
- Minimal key: Attributes/keys that imply all attributes
- Determinants: Minimal key for a given set of attributes (so not all)
- Consequences of Bad DB Design
● Update anomalies
● Insertion anomalies
● Deletion anomalies
- BCNF
● To check if something is in BCNF:
- The FD is trivial (rechterkant is deel van linkerkant)
- De linkerkant contains minimal key. Dus als {A,B} een
minimal key is, is {B} niet in BCNF. Als {B} een minimal key
is, is {A,B} wel in BCNF.
● BCNF is slightly more restrictive than 3NF.
● Anomalies do not occur
- 3NF
● Assume that every FD has only a single attribute on the right-hand
side
● To check if something is in 3NF:
- The FD is trivial (rechterkant is deel van linkerkant)
- De linkerkant contains a minimal key.
- De rechterkant is a minimal key. (of zit in een minimal key!)
dus C,D -> A. {A,B} minimal key dus in 3NF
● If a relation is in BCNF, it is automatically in 3NF.
● 3NF is the standard facto in the industry.
- Canonical Set of Functional Dependencies
● Make the right- hand side singular
● Minimize left-hand side
● Remove implied FD’s
- BCNF Synthesis Algorithm
- 3NF Synthesis Algorithm