Database

• Collection of interrelated data
• Represents a subset of the real world
• Logically coherent with inherent meaning
• Intended group of users and applictions

DBMS (Database Management Systems)

• Collection of general-purpose software that allows us to define, create and manipulate a database.

Database system

• Combination between a database and a DBMS.

File based approach

• Only data is stored in a file
• Structure is defined within the applications
• Waste of storage due to redundancy
• Data inconsistency (every changed entry in one file needs to be changed in the other files)
• Strong data-application dependancy (change in definition = change in all applications)
• Difficult to integrate various apps (high difficulty and cost)
• Only one user/application per file

Database-oriented approach

• DBMS contains both data and description of database structure
• The catalog stores all this metadata
• Changes in the structure just require a catalaog change, program-data independence
• Subsets of data can be defined
• Multiple users can access the same data at the same time = concurrency control

Elements of a DB system

• Data model
• Schemas and instances
• Three-schema architecture
• Catalog (data dictionary)
• DBMS languages

Data model

Collection of concepts that can be used to define the structure of the database

Data models: conceptual

• High-level concepts
• Representation of part of reality
• Close to how the user percieves the data

• (E)ER, OOModels (UML)

Data models: implementation

• Concepts that may be understood by end users
• Not too far removed from physical data organization
• Some details of data storage are hidden (but can be implemented in a direct way)

• Hierarchical model, network model, relational model, ODMG model

Data models: physical

Schemas, instances and database state

• Database schema: description of a database, specified during design, not expected to change frequently (= inf. stored in catalog)
• Database state: the data contained in the database at a particular moment (= current set of instances)

Three-schema Architecture: External/user view

• Each external schema describes the part of the database that a particular group is interested in and hides the rest of the DB
• High-level data model or an implementation data model can be used

Three-schema Architecture: Conceptual schema

• Specifies objects, characteristics of objects, relationships between objects, integrity rules and object behaviour.
• Hides physical storage details. Concentrates on describing entities, data types, relationships and constraints.
• A high-level data model or an implementation data model can be used.

Three-schema Architecture: Internal schema

Catalog: data dictionary

• System database with metadata
• Contains definition of:
• Conceptual schema
• External view/user views
• Physical schema

DBMS Languages: DDL

• Data Definition Language
• Used by the Database Administrator (DBA)
• Defines conceptual, internal and physical schemas

DBMS Languages: DML

Advantages of DB design

• Data and functional independence
• Databese modeling
• Managing data redundancy
• Specifying integrity rules
• Data security
• Backup and recovery facilities
• Performance utilities

Data independence: physical

• Changes to data storage specifications
• No need to change applications
• No need to change conceptual schema
• DBMS provides interfaces between conceptual and physical data models

• New acces paths, indices, different storage media

Data independence: logical

•  Changes in the conceptual schema
• No need to change software applications
• DBMS provides interfaces between conceptual schema and external views

• Adding new objects, new characteristics of objects

Functional independence

• Function
• Interface (signature): name of the function and its arguments
• Implementation (method): specifies how the function should be executed
• Implementation can change without impact on software applications
• Information hiding

Database modeling

• Data model specifies objects, characteristics of objects, relationships between objects, integrity rules and functions.
• Should provide formal and perfect mapping of the real world.

• Hierarchical, CODASYL, (E)ER, relationa, OOModel...

Managing data redundancy

• Redundant data may be positive
• Increase in performance
•            Use of distributed environments

DBMS manages redundancy

• Synchronization
• Consistency

Comparison to file-based redundancy

• Guarantees correctness
• No user intervention required
• More efficient
• Errors unlikely

Integrity rules

• Determine correctness of data
• Syntactical errors
• Semantical errors
• Integrity rules are stored
• Embedded in applications (file based aproach to DBManagement)
• Specified as part of conceptual schema and stored in catalog (database approach)

Enforcement of Integrity Rules

• By applications accessing the files
• By the DBMS whenever it is updated

• IR can be sensitive to simultaneous usage of data in a distributed environment

Data security issues

• User access can be restricted to diferent levels, reading and writing
• Data access can be managed via accounts and passwords for users or groups
• Each personal account has its own authorization rules, stored in the catalog

Backup and recovery facilities

• Loss of data
• Hardware or network errors
• Bugs in system software or applications
• Backup facilities perform full or incremental backups
• Restoring the system to a previous state
• Recovery facilities allow to restore data after loss or damage
• Recreate the state of the system prior to the failure

Performance utilities

• Part of the job of the DBA

• Distributing data storage
• Tuning indices to allow faster queries
• Tuning queries to improve performance
• Optimizing buffer management