Database Management Systems (DBMS)

• A Database Management System (DBMS) holds information about a specific enterprise.
• The DBMS comprises interrelated data and a suite of programs to access this data.
• The DBMS provides a user-friendly and efficient environment.

Database Applications

• Transactions in banking
• Reservations and schedules in airlines
• Registration and grades in universities
• Customer, product, and purchase data in sales
• Order tracking and customized recommendations in online retail
• Production, inventory, orders, and supply chain data in manufacturing
• Employee records, salaries, and tax deductions in human resources.
• Databases can be very large
• Databases are used in all aspects of modern life.

University Database Example

• The university database can:
  • Add new students, instructors and courses.
  • Register students for courses and generate class rosters.
  • Assign grades, compute grade point averages (GPA), and generate transcripts.
• Database applications were built directly on top of file systems in early days.

Drawbacks of File Systems

• Data redundancy and inconsistency due to multiple file formats and duplicated information.
• Difficulty in accessing data, requiring new programs for each task.
• Data isolation that occurs with multiple files and formats.
• Integrity constraints become buried in program code.
• It's hard to add new constraints or change existing ones
• Failures may lead to inconsistent database states with partial updates.
• The transferring of funds should either complete, or not happen at all
• Concurrent access is needed for performance
• Uncontrolled concurrent access can lead to inconsistencies.
• Security issues arise when its hard to restrict user access to some, but not all data.
• Modern database systems provide solutions to these problems.

Levels of Abstraction

• Physical Level: describes how a record (e.g., instructor) is stored.
• Logical Level: describes the data stored in the database and the relationships among the data.
• View Level: used by application programs to hide details of data types and restrict access to sensitive information

Instances and Schemas

• They are also similar to types and variables in programming languages.
• Logical Schema: the overall logical structure of the database.
  • e.g. A database consists of info about bank customers, accounts, and relationships
  • It's analogous to type information for variables.
• Physical Schema: the overall physical structure of the database.
• Instance: the actual content of the database at a specific time.
  • It's analogous to the value of a variable.
• Physical Data Independence: the ability to modify the physical schema without affecting the logical schema.
  • Applications depend on the logical schema.
  • Well-defined interfaces prevent changes in one part from seriously influencing others.

Data Models

• Data models provide a collection of tools for describing data, data relationships, semantics, and constraints.
• Relational model
• Entity-Relationship data model (mainly for database design)
• Object-based data models (Object-oriented and Object-relational)
• Semistructured data model (XML)
• Other older models like network and hierarchical models

Relational Model

• Data is stored in tables.
• Each table has columns and rows.

Data Definition Language (DDL)

• A notation for defining the database schema.
• DDL compiler generates table templates that are stored in a data dictionary.
• Data dictionary contains metadata (data about data) like database schema, integrity constraints, primary keys, and authorization details.
• Creating a table can be done with create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

Data Manipulation Language (DML)

• Used to access and manipulate data organized by a data model.
• It's also known as a query language.
• Includes:
  • Pure languages used for proving properties and optimization
    • Relational Algebra
    • Tuple relational calculus
    • Domain relational calculus
  • Commercial languages used in commercial systems; SQL is the most prominent.

SQL

• A widely used commercial language but is not Turing-complete.
• It is usually embedded in a higher-level language to compute complex functions.
• Can be accessed through language extensions or application program interfaces, such as ODBC/JDBC.

Database Design

• It includes designing the general structure of the database.
• Logical Design: Deciding on the database schema.
• Requires one to find a "good" collection of relation schemas.
  • Determining what attributes to record, and how relation schemas should be structured and attributes be distributed
• Physical Design: Deciding on the physical layout of the database.

Design Approaches

• A methodology is needed to ensure relations are "good.".
• Key methods are:
  • The Entity-Relationship Model presented diagrammatically in an entity-relationship diagram(Chapter 7)
  • Normalization Theory, which formalizes the identification of bad designs and testing for them (Chapter 8).

Object-Relational Data Models

• Extends the relational data model with object orientation and constructs for new data types.
• Attributes can have complex types, including nested relations.
• Preserves relational foundations and offers upward compatibility with existing languages.

XML

• Defined by the WWW Consortium (W3C).
• Originally intended for document markup, it allows custom tags and nested structures for data exchange.
• Widely used for data interchange, with parsing, browsing, and querying tools available.

Database Engine

• Consists of a storage manager, query processing, and transaction manager.

Storage Management

• Storage manager provides the interface between low-level data and application programs and queries.
• It handles interaction with the OS file manager, efficient data storage, retrieval, and updates.
• Storage management involves storage access, file organization, indexing and hashing.

Query Processing

• Query processing has three phases, those being:
  • Parsing and translation
  • Optimization
  • Evaluation

Query Processing (Cont.)

• There are alternative ways of evaluating a given query using equivalent expressions and different algorithms.
• Cost differences between query evaluation methods can be significant.
• Estimating operational costs relies on statistical information about relations maintained by the database.
• it's important to estimate statistics for intermediate results to determine the cost of complex expressions.

Transaction Management

• Manages system failures and concurrent updates.
• A transaction is a series of operations performing a single logical function.
• The transaction-management component ensures the database remains consistent despite failures.
• The Concurrency-control manager oversees interactions to maintain database consistency.

Database Users and Administrators

• Naive users: Tellers, agents, web users
• Application programmers
• Sophisticated users: Analysts
• Database administrators

Database Architecture

• Heavily influenced by the underlying computer system.
• Architectures include centralized, client-server, parallel, and distributed systems.

History of Database Systems

• 1950s-1960s: Data processing used magnetic tapes with sequential access and punched cards for input.
• Late 1960s-1970s: Hard disks allowed direct data access, network, and hierarchical data models became widespread.
  • Ted Codd defines the relational model, with IBM and UC Berkeley developing prototypes.
• 1980s: Relational prototypes led to commercial systems with SQL as the industrial standard; parallel and object-oriented database systems emerged.
• 1990s: Large decision support and data-mining applications.
  • Large multi-terabyte data warehouses and the emergence of Web commerce
• Early 2000s: XML and XQuery standards, automated database administration.
• Later 2000s: Giant data storage systems like Google BigTable, Yahoo PNuts, and Amazon products.