Introduction to Database Management Systems

Introduction

• Database Management Systems (DBMS) store information about an enterprise
• DBMS includes a collection of interrelated data
• DBMS provides a set of programs to access and manage stored data
• DBMS offer an environment that is convenient and efficient for users
• Modern database systems are complex software designed to manage large and intricate data collections
• Databases impact everyday life

Database Applications

• Enterprise information includes sales (customers, products, purchases), accounting (payments, receipts, assets), and human resources (employee information, salaries, taxes)
• Manufacturing manages production, inventories, orders, and supply chains
• Banking and finance involves customer data, accounts, loans, transactions, credit cards, and financial instrument sales
• Universities handle registration and grades
• Airlines manage reservations and schedules
• Telecommunications tracks calls, texts, data usage, generates bills, and manages prepaid card balances
• Web-based services include online retailers with order tracking and recommendations as well as online ads
• Document databases store documents
• Navigation systems maintain locations of interest and routes for roads and transit

University Database Example

• A university database contains information about students, instructors, and classes
• This database is used to illustrate database concepts
• Application program examples include adding new students/instructors/courses
• Application program examples also include registering students, generating class rosters, assigning grades, and computing GPAs

Purpose of Database Systems

• Early database applications were built on file systems, leading to data redundancy and inconsistency due to multiple file formats
• Accessing data in file systems was difficult, requiring new programs for each task
• Data isolation occurred due to multiple files/formats
• Integrity problems arose as constraints were buried in program code
• Adding or changing constraints was challenging
• File systems lacked atomicity of updates
• Failures could leave the database in an inconsistent state
• Concurrent access by multiple users could lead to inconsistencies
• Providing security in file systems was difficult
• Database systems offer solutions to all the above problems

View of Data

• A database system provides an abstract data view for users
• Data models are conceptual tools describing data, relationships, semantics, and constraints
• Data abstraction hides data structure complexity through abstraction levels

Data Models

• Data models are collections of tools that describe data, relationships, semantics, and constraints
• The Relational model is a type of data model
• The Entity-Relationship model is mainly for database design
• The Object-based data models are Object-oriented and Object-relational
• Semi-structured data model uses XML
• Older models include Network and Hierarchical models

Relational Model and Ted Codd

• All data is stored in tables
• Relational Model: Tabular data

Levels of Abstraction

• The Physical level describes how records (e.g., instructor) are stored
• The Logical level describes stored data/relationships
• The View level hides data type details and information for security

Instances and Schemas

• Schemas describes the structure of the data.
• Instances are the actual content (values) of the data at a specific point in time
• Instance is Analogous to variables in programming languages
• Logical Schema is the logical structure of the database; analogous to type info
• Physical schema is the overall physical structure of the database; analogous to the value of a variable

Physical Data Independence

• Physical Data Independence is the ability to modify the physical schema without changing the logical schema
• Applications depend on the logical schema
• Defined interfaces between levels/components are important to avoid changes from influencing others

Data Definition Language (DDL)

• DDL is a specification notation for defining the database schema
• A DDL compiler generates table templates, stored in a data dictionary
• Data dictionaries contain metadata
• Metadata includes database schema, integrity constraints, Primary keys (IDs for instructors), and authorization (access rights)

Data Manipulation Language (DML)

• DML is a language for accessing/updating data organized by a data model, also known as a query language
• Procedural DML requires users to specify what data is needed and how to get it
• Declarative DML requires users to specify what data is needed, but not how to get it
• Declarative DMLs are non-procedural and easier to use
• A query language is the portion of a DML that involves information retrieval

SQL Query Language

• SQL is nonprocedural
• Takes tables as input and returns a table
• Complex functions in SQL are embedded in higher-level languages
• Applications access databases through language extensions or APIs (ODBC/JDBC)

Database Access from Application Program

• Non-procedural languages like SQL aren't as powerful as Turing machines
• SQL doesn't support actions like user input, display output, or network communication
• Computations/actions must be written in a host language (C++, Java, Python)
• Host languages contain embedded SQL queries to access data
• Application programs interact with the database

Database Design

• Database Design is the process of designing the general structure of the database
• Logical Design involves deciding on the database schema
• Business decisions determine attributes to record
• Computer Science decisions determine relation schemas and attribute distribution
• Physical Design involves deciding on the physical layout

Database Engine

• A database system is split into modules for system responsibilities
• Functional components include storage manager, query processor, and transaction management

Storage Manager

• The storage manager provides an interface between low-level data and the application program
• It is responsible for interaction with the file manager and efficient data handling
• Storage manager components include authorization/integrity, transaction, file, and buffer managers

Storage Manager (Cont.)

• The storage manager uses data structures for implementation
• Data files store the database itself
• Data dictionaries store schema metadata
• Indices provide fast data access with pointers

Query Processor

• DDL interpreters record defintions in a data dictionary
• DML compilers translate DML statements to low-level steps and performs query optimization
• Query evaluation engine executes low-level instructions generated by the DML compiler

Query Processing Steps

• Parsing and translation
• Optimization
• Evaluation

Transaction Management

• A transaction is a collection of operations for a single logical function
• Transaction management ensures database consistency despite failures
• Concurrency control manages concurrent transactions to ensure database consistency

Database Architecture

• Centralized databases have one to few cores and shared memory
• Client-server architecture has one server machine doing work through behalf of multiple client macines.
• Parallel databases use many cores and shared memory
• Shared disk and shared nothing architectures
• Distributed databases have geographical distribution and schema/data heterogeneity

Database Applications

• Two-tier architecture applications invoke database system functionality on the server machine to reside at the client machine.
• In a Three-tier architecture, the client side acts as a front-end, without direct database calls
• The client communicates with an application server through a form interface, and the application server accesses data via the database

Database Users

• Multiple kinds: naive users (tellers, agents, and web users), application programmers, sophisticated users, and database administrators

Database Administrator

• DBAs have central control over the system and do a variety of things
• DBAs are in charge of schema definition, storage structure & access, schema & physical modification, granting authorization, and routine maintenance

History of Database Systems

• 1950s-60s: Data processing used magnetic tapes/punched cards
• Late 1960s/1970s: Hard disks are able to direct access data with network and hierarchical data models
• Ted Code defined relational data model
• Early 1980s: Research relational prototypes evolve to SQL standard
• 1990s: Large decision support and data-mining applications
• 2000s: Big data storage/analysis (Google, Yahoo, "NoSQL", MapReduce)
• 2010s: SQL reloaded with MapReduce, parallel databases, and multi-core memory