Introduction to Database Systems

Introduction to Database Systems

• This presentation covers database systems, focusing on three-schema architecture, data independence, DBMS languages, interfaces, and utilities.
• Different types of database architectures are covered, including centralized and client/server structures

Three-Schema Architecture

• This architecture supports DBMS characteristics, including self-describing nature, program-data independence, and the support of multiple data views.
• It's not widely used in commercial DBMS products, but helps in explaining database system organization.
• External schema describes different user views, using the same data model as the conceptual schema.
• Conceptual schema, at the conceptual level, describes the overall structure and constraints of the database for a community of users, using a conceptual or implementation data model.
• Internal schema describes physical storage structures and access paths, typically using a physical data model.
• Mappings between schema levels transform data and requests for execution.

Data Independence

• Three-schema architecture is used to achieve data independence, allowing schema changes at one level without affecting others.
• In DBMSs supporting data independence, only mappings between schemas (at the lower level) need alteration when a schema is changed.
• The change does not require changes to higher-level schemas or application programs, keeping them unaffected and easily maintained.
• Logical data independence refers to the capacity to modify the conceptual schema without affecting external schema and applications.
• Physical data independence refers to modifying internal schema without changing conceptual schema.

DBMS Languages

• Data Definition Language (DDL) defines the conceptual schema. Many DBMSs use DDL to define internal and external schemas (views).
• Storage Definition Language (SDL) controls DBMS commands, for DBA and database designers.
• View Definition Language (VDL) defines internal and external schemas.
• Data Manipulation Language (DML) handles database retrievals and updates, including: retrieval of information. insertion of new information. deletion and/or modification of information in the database.
• Two types of DML exist: Low level (procedural) and High level (non-procedural).
• High level (non-procedural) languages, such as SQL, define what to retrieve but not how to retrieve it.
• Low-level (procedural) languages, like record-at-a-time, describe how to retrieve data and include looping constructs.

DBMS Interfaces

• Stand-alone query language interfaces are used by programmers to embed DML in programming languages. Pre-compiler approaches and procedure/subroutine calls also fit here.
• User-friendly interfaces include menu options and forms for everyday usage, graphics, and natural languages.
• Other interfaces like speech input/output, web browsers, and parametric interfaces (e.g., bank teller systems) are also utilized.
• Interfaces relevant to the DBA include tasks like account creation, authorization, system parameter setup, and schema/access path changes.

Database System Utilities and Tools

• Database utilities perform functions like loading data from files, backing up databases on tapes, reorganizing file structures, generating reports, and monitoring performance.
• Other utilities include tasks like sorting, user monitoring, and data compression.
• Data dictionary/repository stores schema descriptions, design decisions, application details, user information, and usage standards.
• Some data dictionaries, active ones, are accessible to both DB software and the users, while others, passive ones, are accessible only to users and DBAs.
• Application development environments and CASE tools, such as Power Builder (Sybase) and Builder (Borland), aid in building applications.

Centralized and Client/Server Architecture

• Centralized database systems consolidate all components (software, hardware, application programs, user interfaces) into a single system.
• Client/server architecture separates client machines (user interfaces and applications) from a server system containing the database and software to deliver services to clients.
• Specialized servers (e.g., file, printer, web, email) provide specific functions. Each has its own role in the client-server architecture.

Two-Tier Client-Server Architecture

• Two-tier architecture separates the database system and user interface components of applications.
• User interface and application programs run on the client side, while data management and processing happen on the server (database) side.
• The ODBC (Open Database Connectivity) interface allows application programs to communicate directly with the DBMS(s).

Three-Tier Client-Server Architecture

• Three-tier architecture, common for web applications, involves a client, application server, and database server.
• The application server (or web server) acts as a middle layer, managing requests, handling business logic, and interacting with the database server to retrieve or process information and partially process data between clients and the database server.
• The presence of the application server (or web server) safeguards database security and data.

Classification of DBMSs

• DBMSs are classified based on data model (traditional like relational, network, hierarchical, or emerging like object-oriented, object-relational) and other criteria (single-user versus multi-user, centralized versus distributed).
• Distributed database systems are client-server based systems that connect through communication channels. These systems do not support a totally distributed environment.
• Separate sites of a distributed database system might be homogeneous (same DBMS and operating system) or heterogeneous (different systems). Distributed database systems might be autonomous, non-autonomous, federated, or multidatabase.