Database CH1 & CH2

Database

A collection of related data.

Data

Known facts that can be recorded and have an implicit meaning.

Mini-world

Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university.

Database Management System (DBMS)

A software package/system to facilitate the creation and maintenance of a computerized database.

Database System

The DBMS software together with the data itself. Sometimes, the applications are also included.

Traditional Applications

Numeric and Textual Databases.

More Recent Database Applications

Multimedia Databases; Geographic Information Systems (GIS); Biological and Genome Databases; Data Warehouses; Mobile databases; Real-time and Active Databases.

Big Data Storage Systems

Systems involving large clusters of distributed computers.

NOSQL

Not Only SQL.

Cloud

Huge data centers using thousands of machines.

Implicit Properties of a Database

Represents some aspect of the real world; logically coherent collection of data with some inherent meaning; designed, build and populated with data for a specific purpose.

Define a Database

Define a particular database in terms of its data types, structures, and constraints.

Construct or Load

Construct or Load the initial database contents on a secondary storage medium.

Retrieval

Querying, generating reports.

Modification

Insertions, deletions and updates to database content.

Query

Accesses different parts of data and formulates the result of a request.

Transaction

May read some data and “update” certain values or generate new data and store that in the database.

Additional DBMS Functionality

Protection or Security measures; “Active” processing; Presentation and Visualization of data; Maintenance of the database and associated programs.

UNIVERSITY Mini-world Entities

STUDENTs, COURSEs, SECTIONs, DEPARTMENTs, INSTRUCTORs.

UNIVERSITY Mini-world Relationships

SECTIONs are of specific COURSEs; STUDENTs take SECTIONs; COURSEs have prerequisite COURSEs; INSTRUCTORs teach SECTIONs; COURSEs are offered by DEPARTMENTs; STUDENTs major in DEPARTMENTs.

Main Traits of the Database Approach

Self-describing nature of a database system; insulation between programs and data and data abstraction; support of multiple views of the data; sharing of data and multiuser transaction processing.

Self-describing Nature of a Database System

A DBMS catalog stores the description of a particular database, such as data structures, types, and constraints.

Meta-data

The description of a particular database stored in the DBMS catalog.

Program-data Independence

Allows changing data structures and storage organization without having to change the DBMS access programs.

Data Abstraction

A data model is used to hide storage details and present the users with a conceptual view of the database.

Multiple Views of Data

Each user may see a different view of the database, which describes only the data of interest to that user.

Concurrency Control

Guarantees that each transaction is correctly executed or aborted.

Recovery Subsystem

Ensures each completed transaction has its effect permanently recorded in the database.

OLTP

Online Transaction Processing. A major part of database applications that allows hundreds of concurrent transactions to execute per second.

Actors on the Scene

Those who actually use and control database content and those who design, develop and maintain database applications.

Workers Behind the Scene

Those who design and develop DBMS software and related tools and computer systems operators.

Database Administrators

Responsible for authorizing access to the database, coordinating and monitoring its use, acquiring software and hardware resources, controlling its use, and monitoring efficiency of operations.

Database Designers

Responsible to define the content, structure, constraints, and functions or transactions against the database.

Casual End Users

Access database occasionally when needed.

Naïve or Parametric End Users

Use previously well-defined functions in the form of “canned transactions” against the database.

Sophisticated End Users

Business analysts, scientists, engineers, and others thoroughly familiar with the system capabilities.

Stand-alone End Users

Mostly maintain personal databases using ready-to-use packaged applications.

System Analysts

Understand the user requirements of naïve and sophisticated users and design applications including canned transactions to meet those requirements.

Application Programmers

Implement the specifications developed by analysts and test and debug them before deployment.

Business Analysts

Analyze vast amounts of business data and real-time data (“Big Data”) for better decision making related to planning, advertising, marketing, etc.

System Designers and Implementors

Design and implement DBMS packages in the form of modules and interfaces and test and debug them.

Tool Developers

Design and implement software systems called tools for modeling and designing databases, performance monitoring, prototyping, test data generation, user interface creation, simulation, etc.

Operators and Maintenance Personnel

Manage the actual running and maintenance of the database system hardware and software environment.

Advantages of the Database Approach

Controlling redundancy; sharing data; restricting unauthorized access; persistent storage; storage structures for efficient query processing; query optimization; backup and recovery; multiple interfaces; representing complex relationships; enforcing integrity constraints; drawing inferences and actions from stored data.

Potential for Enforcing Standards

Standards refer to data item names, display formats, screens, report structures, meta-data, Web page layouts, etc.

Reduced Application Development Time

Incremental time to add each new application is reduced.

Flexibility to Change Data Structures

Database structure may evolve as new requirements are defined.

Availability of Current Information

Extremely important for online transaction systems such as shopping, airline, hotel, and car reservations.

Economies of Scale

Wasteful overlap of resources and personnel can be avoided by consolidating data and applications across departments.

Hierarchical and Network Models

Introduced in the mid-1960s and dominated during the seventies.

Relational Model

Originally introduced in 1970. Relational DBMS products emerged in the early 1980s.

OODBMSs

Object-Oriented Database Management Systems introduced in the late 1980s and early 1990s.

ORDBMSs

Object-relational DBMSs created as relational DBMSs incorporated object database concepts.

HTML

Hypertext markup language.

XML

eXtended Markup Language.

When a DBMS May Be Unnecessary

If the database and applications are simple, well defined, and not expected to change; stringent real-time requirements may not be met due to overhead; multiple-user access is not required; or a general purpose DBMS may not fit in available storage.

Data Model

A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints that the database should obey.

Data Model Constructs

Used to define the database structure.

Data Model Constraints

Specify some restrictions on valid data; these constraints must be enforced at all times.

Data Model Operations

Used for specifying database retrievals and updates by referring to the constructs of the data model.

Basic Model Operations

Generic insert, delete, update.

User-defined Operations

Operations such as compute_student_gpa and update_inventory.

Conceptual Data Models

Provide concepts that are close to the way many users perceive data. Also called entity-based or object-based data models.

Physical Data Models

Provide concepts that describe details of how data is stored in the computer.

Implementation Data Models

Provide concepts that fall between conceptual and physical data models and are used by many commercial DBMS implementations.

Self-Describing Data Models

Combine the description of data with the data values. Examples include XML, key-value stores and some NOSQL systems.

Database Schema

The description of a database. Includes descriptions of the database structure, data types, and constraints on the database.

Schema Diagram

An illustrative display of most aspects of a database schema.

Schema Construct

A component of the schema or an object within the schema, e.g., STUDENT, COURSE.

Database State

The actual data stored in a database at a particular moment in time.

Database Instance

Another name for database state. Also called occurrence or snapshot.

Initial Database State

The database state when it is initially loaded into the system.

Valid State

A state that satisfies the structure and constraints of the database.

Intension

Another name for schema.

Extension

Another name for state.

Three-Schema Architecture

Proposed to support program-data independence and multiple views of the data.

Internal Schema

Describes physical storage structures and access paths, such as indexes. Typically uses a physical data model.

Conceptual Schema

Describes the structure and constraints for the whole database for a community of users.

External Schemas

Describe the various user views.

Schema Mappings

Needed to transform requests and data among schema levels.

Logical Data Independence

The capacity to change the conceptual schema without having to change the external schemas and their associated application programs.

Physical Data Independence

The capacity to change the internal schema without having to change the conceptual schema.

DDL

Data Definition Language.

Data Definition Language

Used by the DBA and database designers to specify the conceptual schema of a database.

SDL

Storage Definition Language.

VDL

View Definition Language.

DML

Data Manipulation Language.

Data Manipulation Language

Used to specify database retrievals and updates.

Host Language

A general-purpose programming language in which DML commands can be embedded, such as COBOL, C, C++, or Java.

Query Language

Stand-alone DML commands applied directly.

High-Level or Non-procedural Language

Set-oriented and specifies what data to retrieve rather than how to retrieve it. Also called a declarative language.

Declarative Language

Another name for a high-level or non-procedural language.

Low-Level or Procedural Language

Retrieves data one record-at-a-time; looping is needed to retrieve multiple records along with positioning pointers.

DBMS Interfaces

Stand-alone query language interfaces; programmer interfaces; user-friendly interfaces; mobile interfaces.

Embedded Approach

Examples include embedded SQL for C and C++ and SQLJ for Java.

Procedure Call Approach

Examples include JDBC for Java and ODBC for other programming languages as APIs.

ODBC

Open Database Connectivity.

JDBC

Programming interface for Java database access.

API

Application Programming Interface.

PL/SQL

A programming language based on SQL used by ORACLE that incorporates SQL and its data types as integral components.