Introduction to Database Systems

Introduction to Database Systems

• A database can be thought of as a repository or collection of data
• It stores, maintains, and presents data efficiently to applications and users
• Databases represent real-world aspects, contain related data, and are built for specific situations

Database Management System (DBMS)

• A DBMS is a tool for creating and managing large amounts of data efficiently over time
• It defines data types, structure, and constraints
• The process of constructing involves storing data into a storage medium
• Manipulation involves retrieving and updating data in storage
• Sharing allows multiple users to access data

Evolution of Database Systems

• Database technology has evolved through four generations
• Currently on the cusp of the fifth generation
• The first generation used file systems like ISAM and VSAM
• Second-generation systems were hierarchical, such as IMS and System 2000
• Third-generation systems used the network model (CODASYL), like IDS, TOTAL, ADABAS, IDMS
• The second and third generations enabled integrated database sharing among users
• The fourth generation introduced relational database technology to solve data independence issues using a declarative query approach
• The fifth generation aims for richer data models and facilities for broader applications beyond business data processing

History Landmarks

• 1950s-1960s: Magnetic disks are introduced for data storage, data reading from tapes and cards was sequential
• Late 1960s-1970s: Hard disks enabled direct data access, Codd's relational model paper emerged
• 1980s: Relational database technology was realized, Oracle, SQL/DB, DB2, and INGRES were introduced
• Late 1990s: The WWW and multimedia advancements push database systems further, object-oriented programming strained unified language

Database System Requirements

• Key factors include data sharing
• Consistency ensures data is stored, retrieved, and shared efficiently
• Concurrency allows multiple users to retrieve data simultaneously
• Performance requires reasonable response times
• Standard Adherence: Supports standard languages like SQL:
  • Data Definition Language (DDL) enables users to create and define database schemas
  • Data Manipulation Language (DML) enables users to query and manipulate data
• Security sets access permissions
• Reliability keeps stored data intact and ensures recovery from failures

Database System vs. File System

• File systems lack features of database systems
• Data redundancy and Inconsistency: Files and applications vary in format, leading to duplication
• Data Isolation: Related data is scattered across files
• Difficulty in Accessing Data: File systems lack responsive data-retrieval
• Integrity Issues: Maintaining constraints across files is difficult
• Atomicity Issues: Restoring consistent state after failures is crucial
• Concurrent Access Anomalies: Supervision of application is difficult
• Security Problems: Systems are vulnerable to security threats

Database System Architecture

• Centralized systems run on a single computer without interacting with others except for display purposes
• Client/Server architecture separates client and server processes
• Two-Tier Client/Server Architecture:
  • Client processes provide the user interface
  • Server processes interface with data storage
  • The logic is divided between the client and server
  • Fat clients handle user and business tiers, while thin clients handle only the user tier

Three-Tier Client/Server Architecture

• It consists of modularly separated tiers on three machines
• User Tier (GUI or Web Interface): presents the user interface
• Business Tier (Application Server or Web Server): incorporates business rules
• Data Tier (Data Server): comprises data storage
• Components and Functionalities of a Database System

Storage Manager

• An interface between low-level data and application programs
• Main components:
  • Authorization and Integrity Manager: checks user credentials and integrity constraints
  • Transaction Manager: preserves consistency and avoids conflicts
  • File Manager: manages disk storage allocation and data structure
  • Buffer Manager: fetches data from disk storage to main memory

Query Processor

• Handles queries and data modification requests
• Components:
  • DDL Interpreter (Compiler): processes DDL statements and records definitions in the data dictionary
  • DML Compiler: analyzes, translates, and optimizes DML statements
  • Query Evaluation Engine: executes low-level instructions generated by the DML compiler

Types of Data Models

• A data model is a conceptual tool for describing the database schema
• It includes data, relationships, semantics, and consistency constraints

Hierarchical Model

• Data is organized in a tree structure with parent-child relationships
• Record types are equivalent to tables, with records as rows
• It was popularized by IBM's Information Management System (IMS) DBMS

Network Model

• Network model is a many-to-many relationships data model
• The basic construct is the set construct, consisting of an owner record type, a set name, and a member record type.

Relational Model

• Based on E.F. Codd's relational algebra principles
• Data and relations are organized in tables with records and columns
• Properties of Relational Tables:
  • Values are atomic
  • Rows are unique
  • Column values are of the same kind
  • Sequence of Columns is insignificant
  • Sequence of Rows is insignificant
  • Each Column has a unique name

Implementation and Conceptual Data Models

• Network, hierarchical, and relational models are implementation data models
• High-level (conceptual) data models focus on user perception of data
• Entity-Relationship (E/R) Model:
  • Based on entities, attributes, and relationships
  • Entity: represents a real world object
  • Attribute: describes an entity
  • Relationship: is an association among entities
  • The E/R model is diagrammatical using rectangles, ellipses, diamonds, and lines

Object-Oriented Model

• Object-Oriented Programming (OOP) evolves into Object DBMS (ODBMS)
• High-level implementation data model
• Combination with the relational model leads to object-relational data model

Steps of Database Design

• Steps:
  • Requirements analysis specifies system requirements based on user input
  • Design specifies how the system addresses requirements
  • Implementation translates design specifications into a working system

Requirement Analysis

• Determines data, information, system components, and analysis functions
• Involves user interviews and formal specifications
• Considers:
  • User views
  • Data elements
  • Primary keys
  • Relationships
  • Operational requirements

Steps in Requirement Analysis

• Scope identification
• Metadata collection standards
• User view identification
• Data dictionary creation
• Volume and usage pattern identification
• Operational requirement identification
• Outputs include data and functional requirements

Design

• Conceptual Design: Synthesizes information from requirements analysis
• Implementation (Logical) Design: Transforms the conceptual data model into an internal schema for a DBMS
• Physical Design: Involves design of internal storage structures, record formats, access methods, and record blocking

Implementation

• Translates the implementation design into a database management system
• Involves entities, objects, relationships, and constraints