Introduction to Database System Concepts Notes

Database Management System (DBMS) Overview

  • A Database Management System (DBMS) consists of a collection of interrelated data and a set of programs to access that data.

  • The primary goal of a DBMS is to provide an environment that is both convenient and efficient for users to retrieve and store information.

  • Database applications are used to manage information about specific enterprises across various industries:

    • Banking: Managing all aspects of financial transactions.

    • Airlines: Handling reservations and flight schedules.

    • Universities: Managing student registration, grades, and course information.

    • Sales: Tracking customers, products, and purchases.

    • Online retailers: Managing order tracking and providing customized recommendations.

    • Manufacturing: Oversessing production, inventory, orders, and the supply chain.

    • Human resources: Maintaining employee records, salaries, and tax deductions.

  • Databases can be extremely large and currently touch nearly every aspect of modern life.

University Database Example

  • A university database system typically includes application programs designed to perform specific tasks:

    • Adding new students, instructors, and courses.

    • Registering students for specific courses and generating class rosters.

    • Assigning grades to students, computing grade point averages (GPA), and generating official transcripts.

  • In the earliest stages of computing, these database applications were built directly on top of file systems.

Drawbacks of Using File Systems for Data Storage

  • Data redundancy and inconsistency: Information may be duplicated in different files, and various file formats may be used, leading to inconsistencies where different copies of the same data do not match.

  • Difficulty in accessing data: Carrying out each new task often requires writing a new program from scratch.

  • Data isolation: Data is frequently scattered across multiple files in different formats, making it difficult to retrieve related information efficiently.

  • Integrity problems:

    • Integrity constraints, such as ensuring an account balance remains above 00, become buried in the program code instead of being stated explicitly.

    • It is difficult to add new constraints or modify existing ones when they are hard-coded into applications.

  • Atomicity of updates:

    • System failures can leave a database in an inconsistent state where only partial updates are completed.

    • Example: A transfer of funds between two accounts must either complete fully or not happen at all to ensure balance.

  • Concurrent access by multiple users:

    • While concurrent access is necessary for system performance, uncontrolled access can lead to inconsistencies.

    • Example: Two users reading a balance of 100100 and both attempting to withdraw 5050 at the exact same moment can result in an incorrect final balance if not managed.

  • Security problems: It is difficult to implement granular access control, ensuring a user can see some but not all of the stored data.

Levels of Data Abstraction

  • Physical level: The lowest level of abstraction that describes how a record, such as an instructor, is actually stored on storage media.

  • Logical level: Describes what data is stored in the database and what relationships exist among those data points.    

    • Example of a logical record for an instructor in code syntax:

      • type instructor=recordtype\,instructor = record

      • ID:string;ID : string;

      • name:string;name : string;

      • dept_name:string;dept\_name : string;

      • salary:integer;salary : integer;

      • end;end;

  • View level: The highest level of abstraction where application programs hide the complex details of data types. Views can also hide sensitive information, such as an employee's salary, to enhance security.

Instances and Schemas

  • The concepts of schemas and instances are analogous to types and variables in programming languages.

  • Logical Schema: Refers to the overall logical structure of the database.

    • Example: A database consisting of information about bank customers, accounts, and the relationships between them. This is analogous to the type information of a variable.

  • Physical Schema: Refers to the overall physical structure of the database.

  • Instance: The actual content of the database at a specific point in time, analogous to the current value of a variable.

  • Physical Data Independence: The ability to modify the physical schema without requiring changes to the logical schema.

    • Application programs depend on the logical schema.

    • Well-defined interfaces between levels ensure that changes in one part of the system do not necessitate changes in others.

Data Models

  • A data model is a collection of tools used to describe data, data relationships, data semantics, and data constraints.

  • Major data models include:

    • Relational model: Data is stored in various tables.

    • Entity-Relationship (E-R) data model: Primarily used for database design by modeling an enterprise as entities and relationships.

    • Object-based data models: Includes Object-oriented and Object-relational models.

    • Semistructured data model: Represented by XML (Extensible Markup Language).

    • Older models: Includes the Network model and the Hierarchical model.

The Relational Model and Sample Data

  • In a relational model, all data is organized into tables consisting of rows and columns.

  • Sample Instructor Table:

    • IDID: 22222, namename: Einstein, dept_namedept\_name: Physics, salarysalary: 95000

    • IDID: 12121, namename: Wu, dept_namedept\_name: Finance, salarysalary: 90000

    • IDID: 32343, namename: El Said, dept_namedept\_name: History, salarysalary: 60000

    • IDID: 45565, namename: Katz, dept_namedept\_name: Comp. Sci., salarysalary: 75000

    • IDID: 98345, namename: Kim, dept_namedept\_name: Elec. Eng., salarysalary: 80000

    • IDID: 76766, namename: Crick, dept_namedept\_name: Biology, salarysalary: 72000

    • IDID: 10101, namename: Srinivasan, dept_namedept\_name: Comp. Sci., salarysalary: 65000

    • IDID: 58583, namename: Califieri, dept_namedept\_name: History, salarysalary: 62000

    • IDID: 83821, namename: Brandt, dept_namedept\_name: Comp. Sci., salarysalary: 92000

    • IDID: 15151, namename: Mozart, dept_namedept\_name: Music, salarysalary: 40000

    • IDID: 33456, namename: Gold, dept_namedept\_name: Physics, salarysalary: 87000

    • IDID: 76543, namename: Singh, dept_namedept\_name: Finance, salarysalary: 80000

  • Sample Department Table:

    • dept_namedept\_name: Comp. Sci., buildingbuilding: Taylor, budgetbudget: 100000

    • dept_namedept\_name: Biology, buildingbuilding: Watson, budgetbudget: 90000

    • dept_namedept\_name: Elec. Eng., buildingbuilding: Taylor, budgetbudget: 85000

    • dept_namedept\_name: Music, buildingbuilding: Packard, budgetbudget: 80000

    • dept_namedept\_name: Finance, buildingbuilding: Painter, budgetbudget: 120000

    • dept_namedept\_name: History, buildingbuilding: Painter, budgetbudget: 50000

    • dept_namedept\_name: Physics, buildingbuilding: Watson, budgetbudget: 70000

Database Languages

  • Data Definition Language (DDL):

    • A notation for defining the database schema.

    • Example SQL: create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))create\,table\,instructor\,(ID\,char(5),\,name\,varchar(20),\,dept\_name\,varchar(20),\,salary\,numeric(8,2))

    • The DDL compiler generates table templates that are stored in a data dictionary.

    • Data dictionary: Contains metadata, which is data about the data.

    • Database schema components include integrity constraints, primary keys (e.g., IDID uniquely identifying an instructor), and authorization details regarding user access.

  • Data Manipulation Language (DML):

    • Used for accessing and manipulating data organized by the data model; also known as a query language.

    • Pure DMLs: Used for optimization and proving computational power. Examples include Relational Algebra, Tuple Relational Calculus, and Domain Relational Calculus.

    • Commercial DMLs: SQL is the most widely used commercial language.

SQL Characteristics and Usage

  • SQL is not a Turing machine equivalent language.

  • To perform complex computations, SQL is typically embedded in higher-level languages.

  • Application programs access databases through:

    • Language extensions that allow for embedded SQL.

    • Application Program Interfaces (APIs) such as ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) to send queries to the database.

Database Design

  • Logical Design: The process of deciding on the database schema, choosing a "good" collection of relation schemas.

    • Business decisions: Determining which attributes should be recorded in the database.

    • Computer Science decisions: Determining which relation schemas to use and how to distribute attributes among them.

  • Physical Design: Determining the physical layout of the database.

  • Design Methodologies:

    • Entity Relationship (E-R) Model: Modeling the enterprise as entities and relationships, visualized through entity-relationship diagrams.

    • Normalization Theory: A formal approach to identify and eliminate bad designs.

Other Data Models

  • Object-Relational Data Models:

    • Extends the relational model by adding object-oriented features.

    • Allows attributes of tuples to have complex types and non-atomic values, such as nested relations.

    • Preserves relational foundations and declarative access while increasing modeling power and maintaining upward compatibility with existing languages.

  • XML (Extensible Markup Language):

    • Defined by the World Wide Web Consortium (W3C).

    • Switched from being a document markup language to a standard for data exchange due to its ability to specify new tags and create nested structures.

    • Serves as the basis for modern data interchange formats and is supported by various parsing, browsing, and querying tools.

Database Engine Components

  • Storage Manager:

    • A module providing the interface between low-level data stored on disk and the application programs or queries submitted to the system.

    • Responsibilities include interacting with the OS file manager and ensuring efficient retrieval, storage, and updating of data.

    • Key issues managed: storage access, file organization, indexing, and hashing.

  • Query Processing:

    • Steps: 1. Parsing and Translation; 2. Optimization; 3. Evaluation.

    • There are multiple ways to evaluate a query (equivalent expressions and different algorithms).

    • The cost difference between different evaluation methods can be enormous.

    • Optimization depends on statistical information about relations maintained by the database to estimate the cost of operations and intermediate results.

  • Transaction Management:

    • A transaction is a collection of operations performing a single logical function.

    • Components ensure consistency (correctness) despite system failures (power outages, OS crashes) or transaction failures.

    • Concurrency-control manager: Regulates interactions between concurrent transactions to ensure database consistency.

Database System Internals and Users

  • Users are categorized into four types:

    • Naive users (tellers, agents, web users): Interact through application interfaces.

    • Application programmers: Write application programs and use compilers/linkers.

    • Sophisticated users (analysts): Use query tools to interact with the system.

    • Database administrators (DBAs): Use administration tools to manage the system.

  • System Internals Structure:

    • Query Processor: Includes the DML compiler/organizer, DDL interpreter, and the query evaluation engine.

    • Storage Manager: Includes the buffer manager, file manager, authorization and integrity manager, and transaction manager.

    • Disk Storage components: Data, indices, data dictionary, and statistical data.

Database Architectures

  • The architecture is influenced by the underlying computer system:

    • Centralized systems.

    • Client-server systems.

    • Parallel systems (multi-processor).

    • Distributed systems.

History of Database Systems

  • 1950s and early 1960s: Data processing relied on magnetic tapes (sequential access only) and punched cards for input.

  • Late 1960s and 1970s:

    • Hard disks allowed direct access to data.

    • Network and hierarchical models were widely used.

    • Ted Codd defined the relational data model (earning the ACM Turing Award).

    • IBM began the System R prototype; UC Berkeley began the Ingres prototype.

    • High-performance transaction processing emerged.

  • 1980s:

    • Research prototypes evolved into commercial relational systems; SQL became the industry standard.

    • Development of parallel and distributed systems.

    • Introduction of object-oriented database systems.

  • 1990s:

    • Growth of large decision support and data-mining applications.

    • Multi-terabyte data warehouses became common.

    • Emergence of Web commerce.

  • Early 2000s:

    • Standardization of XML and XQuery.

    • Implementation of automated database administration.

  • Later 2000s: Emergence of giant data storage systems such as Google BigTable, Yahoo PNuts, and Amazon systems.