Databases conceptsIntroduction to database systems

Vitor Greati1

1Federal University of Rio Grande do Norte

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Preliminary concepts I

DataKnown facts that can be stored and have implicit meaning.

DatabaseA collection of related data with the following implicit properties:

I Represents a miniworld or Universe of Discourse (UoD);

I It is logically coherent and has inherent meaning;

I Its existence has specific purposes.

Databases have sources of data, interacts with real world eventsand have an audience interested in its contents.

Database Management System (DBMS)

Preliminary concepts II

Computerized general-purpose system that enables users to createand maintain databases, providing tools for the tasks of

I Defining through meta-data;

I Constructing the base in some media;

I Manipulating, for updating and queyring; and

I Sharing the base with multiple users and programs.

I Protecting from system (failures) and security (maliciousagents) problems;

I Maintaining, allowing the database evolution over time.

Database systems

Database together with a DBMS software.

Design phases of a database

Preliminary concepts III

1. Requirements specification and analysis, for knowing theminiworld and the potential audience;

2. Conceptual design, easily interpretable, modifiable andmaintainable;

3. Logical design, expressed in a data model available in someDBMS;

4. Physical design, concerned with the actual means of storingthe data.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

What about file processing? I

In file processing approach, users had to define and implement thefiles needed for a specific software as part of the applicationprogramming.

The database approach, the one we are interested in, presentsmain four advantages.

Self-describing nature

File processing stores the structure of the data in the applicationprograms, so they are work only with a specific set of definitionsand constraints.

Through meta-data, the database include information about itsown structure and constraints1.

Insulation between data and programs, with data abstraction

What about file processing? II

In file processing, if one file structure changes, every applicationprograms that access that file must change.

The DBMS approach provides program-data independence 2.This occurs because of data abstraction, which consists of aconceptual representation of data that hides storage andimplementation details. Data models are responsible for thisrepresentation.

Multiple views of data

A view is a subset of the database or a set of virtual data derivedfrom the database which is of interest of specific users orapplications. The database approach is strongly concerned withproviding multiple views of data for its users.

Sharing of data and multiuser transation processing

What about file processing? III

Concurrency control.

Transactions are executing programs or process that include oneor more databases access, such as reading or updating data. Theseoperations must obey to two main properties:

I Isolation: each transaction executes independently of othertransactions;

I Atomicity: all database operations in a transation must meexecuted, otherwise none is executed.

1In opposite to the NoSQL approach, which uses self-describing data.2For object-relational and object-oriented databases, we have also

program-operation independence.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

On the scene I

Database administratorsChief administrator: authorizes access, coordinate and monitordatabase usage, acquire software and hardware resources asneeded.

Database designers

People responsible for identifying the data to be stored and forchoosing appropriate structures to represent it, together withsuitable views according to the user requirements.

End users

On the scene II

Divided into four categories:

I Casual, accesses occasionally the database;

I Naive, which uses canned transactions (specific programmedand tested manipulations of the database);

I Sophisticated, like engineers, scientists and analysts;

I Standalone, which maintains personal databases.

System analysts and applications programmers

They are software engineers and developers.

Behind the scenes I

DBMS system designers and implementers

Tool developers

Operators and maintenance personnel

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Advantages of the DBMS approach I

Redundancy control

Redundancy is the repetition of the same data in the database.

Data normalization is the property of a database that guaranteesthe storage of each logical entity in only one place.

Sometimes, data normalization forces difficult queries, so acontrolled redundancy may be appropriate, in a process calleddenormalization.

Restricting unauthorized access

Providing persistent storage for program objects

Main common in object-based databases, which decreaseimpedance problems (incompatibily between program objects anddatabase structures).

Advantages of the DBMS approach II

Providing storage structures and search techniques for efficientquery processing

Efficiently executing queries and updates, through some specificstructures, like indexes; buffering or caching in the main memory;query processing and optimization modules.

Backup and recovery

Multiple users interfaces

Easiness of programming various kinds of GUI’s.

Representing complex relationships among data

Enforcing integrity constraints

Advantages of the DBMS approach III

I Referential constraints;

I Uniqueness constraints;

I Key constraints;

I Business rules;

I Inherent rules of the data model.

Permitting inferencing and actions using rules and triggers

Advantages of the DBMS approach IV

Inferences occur in deductive database systems through rules.

Triggers are actions that are executed with updates in thedatabase.

Stored procedures are invoked when certain conditions are met.

Active database systems provide active rules that can automaticallyinitiate actions when certain events and condition occur.

Additional implications of using the database approach

Advantages of the DBMS approach V

I Potential for enforcing standards;

I Reduced application delopment time;

I Flexibility;

I Availability of up-to-date information;

I Economies of scale.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

When not to use DBMS

Database systems provide some overhead costs due to resourcesthey provide, like generality, security, concurrency control, recoveryand integrity functions.

So, customized databases may be more appropriate when one ofthe following circumstances are met:

I Simple, well-defined databases, which won’t change at all;

I Stringent, real-time requirements and limited hardware;

I No multiple-user access data.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Concepts I

Data abstractionSupression of details of data organization and storage, and thehighlighting of the essential features for an improved understandingof data.

Data modelCollection of concepts that can be used to describe the structureof a database, in terms of types, relationships, constraints andbasic operations.

Data models perform data abstraction and occur in somecategories:

I High-level or conceptual, next to data understood by commonusers, which uses:

I Entities: real-world object or concept of the miniworld;I Attributes: properties of interest that further describes entities;I Relationships: associations among entities

Concepts II

A common conceptual model is the Entity-Relationship (ER)model.

I Representational or implementation, which presents data in asimilar way to which they are stored and organized in thecomputer, but still understandable by the users.

I Low-level or physical, which describes how data is stored inthe computer by representing information such as recordsformats, ordering and access paths, which are searchstructures that make the search for particupar databaserecords efficient. One example is indexing, which allows directaccess to data using an index term or a keyword.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Schemas, instances, database state I

Database schema or intensionThe description of the database, in which each object is a schemaconstruct.

The database schema can evolve as new requisites appear.

Schema diagram

A displayed schema, focusing in some specific aspects.

Database state or snapshot or extension

The data in the database at a particular moment in time.

When defined, a database is in the empty state. Then, the initialstate occurs when the database is first populated or loaded.

A valid state is a state that satisfies the structure and constraintsspecified in the schema.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Three-schema architecture(ANSI/SPARC) I

Internal levelHas an internal schema, which describes the physical storagestructure of the database.

Conceptual level

Has a conceptual schema, which describes the structure of thewhole database for a community of users. It focus on describingentities, data types, relationships, user operations and constraints.Uses conceptual schemas generally.

External or view levelIncludes a number of external schemas or user views. Usesrepresentational schemas commonly.

The process of transforming requests and results between levels arecalled mappings.

Data independence I

It is the capacity to change the schema at one level of a databasesystem without having to change the schema at the next higherlevel, but only the mappings. We can define two types of dataindependence:

Logical data independence

Capacity to change the conceptual schema without having tochange external schemas or application programs.

Physical data independence

Capacity to change the internal schema without having to changethe conceptual schema.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Database languages and interfaces I

Data definition language (DDL)

Language used to define the conceptual and internal schemas, indatabases with no clear separation between them; or only theconceptual schemas, when the separation is clear.

Storage definition language (SDL)

Used to specify the internal schema.

View definition language (VDL)

Used to specify user views and their mappings to the conceptualschema.

Data manipulation language (DML)

Used to manipulate the database. They can be high-level ordeclarative or set-at-a-time or set-oriented, like SQL, or low-levelor record-at-a-time, which must be embedded in a general-purposeprogramming language, the host language.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Database components I

Buffer management

Stored data manager

DLL compiler

Intective query interface

Has query compiler and query optimizer.

Precompiler

Runtime database processor

Database components II

System catalog

Concurrency control

Backup and recovery systems

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Database system utilities I

Loading

Backup

Databse storage reorganization

Performance monitoring

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Tools, application environments, communications facilitiesI

CASE tools

Data dictionary

Application development environments

Communications software

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Centralized I

Used mainframe computers to provide the main processing for allsystem functions, including user application programs, as well as allDBMS functions. This happened because before user machineswere just capable of showing things, not processing them.

Client/server architecture I

Specific servers can be implemented and client machines providethe user with the appropriate interfaces to utilize the servers. Theserver is a system containing both hardware and software that canprovide services to the client machines.

Two-layer architecture for DBMSs

The server is a query or transaction server; the interface programsand application programs can run on the client side. Thecommunication between these layers occurs by an API (ApplicationProgramming Interface) provided by the standard called OpenDatabase Connectivity (ODBC).

Three-tier and n-tier architecture for Web Applications

Has a web server in the middle tier, which holds the business logic.Other two layers are the client and database layers. It is alsopossible to divide the business layer into more layers, giving rise tothe n tiers.

Table of ContentsConcepts

File processing versus database approach

Actors

Advantages of the DBMS approach

Should we always use the DBMS approach?

Data models, schemas and instances

Schemas, instances, database state

The three-schema architecture

Database languages and interfaces

The database system environment

Database utilities

Tools, application environments, communications facilities

Centralized and Client/Server architectures for DBMSs

Database classification

Per data model I

I Relational;

I Object;

I hierarquical and network;

I native XML.

Per users number I

I monouser;

I multiuser.

Per local number I

I centralized;

I distributed (homogeneous, heterogeneous).

Per local number I

I centralized;

I distributed (homogeneous, heterogeneous).