Database Systems Lec 2

Database System Architecture

Schema

  • Definition: A plan of the database describing the entities and attributes, and specifying relationships among them.

  • Includes the definition of the database name, record type, and the components that make up those records.

  • Instance Variation: The values fitted into the format change from instance to instance.

Example of a Schema

  • STUDENT

    • Name

    • Student_number

    • Class

    • Major

  • COURSE

    • Course_name

    • Course_number

    • Credit_hours

    • Department

  • PREREQUISITE

    • Course_number

    • Prerequisite_number

  • SECTION

    • Section_identifier

    • Course_number

    • Semester

    • Year

    • Instructor

  • GRADE_REPORT

    • Student_number

    • Section_identifier

    • Grade

  • Figure 2.1: Schema diagram for the database in Figure 1.2.

Subschema

  • Definition: A subset of the schema that inherits the same properties as the schema.

  • Represents a plan for a view and refers to the application programmer’s view of data item types and record types.

  • Allows users to view only the part of the database that is of interest.

  • Each application programmer can have a different view of the data.

  • The Database Administrator (DBA) ensures the subschema requested is derivable from the schema.

Instance

  • Definition: The state of the database or a snapshot at any point in time.

  • An instance consists of filled data item values based on the schema framework.

  • Example:

    • STUDENT

      • Smith, 17, 1, CS

      • Brown, 2, 8, CS

    • COURSE

      • Intro to Computer Science, CS1310, 4, CS

      • Data Structures, CS3320, 4, CS

      • Discrete Mathematics, MATH2410, 3, MATH

      • Database, CS3380, 3, CS

    • SECTION

      • 85, MATH2410, Fall, 04, King

Database Architecture

  • Database applications are typically partitioned into:

    • Two Tier Architecture

    • Three Tier Architecture

Two Tier Architecture

  • Definition: Application is divided into components residing at the client machine and database functionality at the server machine via query language statements.

  • Uses application program interface standards for interaction between client and server.

  • Illustrative Example:

    • Client: Database application

    • Server: Database system

Three Tier Architecture

  • Definition: Client acts as a front end and communicates with an application server, which in turn communicates with the database system.

  • Business logic is embedded in the application server.

  • Illustrative Structure:

    • CLIENT

      • GUI

    • APPLICATION SERVER

    • DATABASE SYSTEM

Three-Level ANSI-SPARC Database Architecture

  • Consists of three levels:

    • Internal Level

    • Conceptual Level

    • External Level

Three Views of the Data

  • User Views: Logical external views representing specific data to users.

    • Example Views:

      • CUST-NAME, CUST-CITY (User View #1)

      • CUSTOMER (Conceptual View)

      • STORED-CUST (Internal View)

External Level

  • Represents the user’s view of the database; the highest level of data abstraction.

  • Describes parts of the database relevant to users; may have different representations of the same data.

  • Allows customization of data access at the user level.

Conceptual Level

  • Middle level of three-tier architecture, containing the logical structure of the entire database as seen by the DBA.

  • Describes what data is stored and the relationships among them.

Internal Level

  • Lowest level of abstraction; describes how data is stored in the database.

  • Involves data structure, file structures, and access methods used by the database.

Advantages of Three-Tier Architecture

  • Separates physical data representation from user views.

  • Allows each user to access the same data but have different customized views.

  • The internal structure of the database is unaffected by physical storage changes.

Data Independence

  • Characterizes the ability to change schema at one level without affecting the next higher level.

  • Two types:

    • Physical Data Independence: Changes in internal schema do not affect conceptual or external schemas.

    • Logical Data Independence: Changes in conceptual schema must be possible without changing external schemas.

Mapping

  • Mechanism that relates data at one level to its format at the other levels

  • DBMS manages the mapping between internal, conceptual, and external schemas.

Database System Environment

  • Components define and regulate the collection, storage, management, and use of data within a database environment:

    • Data

    • Hardware

    • Software

    • Users

Database Language

  • DBMS must provide a variety of languages for users to express database queries and updates:

    • Data Definition Language (DDL)

    • Data Manipulation Language (DML)

    • Data Storage Definition Language (DSDL)

    • View Definition Language (VDL)

    • Fourth-Generation Language (4GL)

Data Definition Language (DDL)

  • Used to specify the database conceptual schema using definitions, describing entities, attributes, and relationships.

Data Manipulation Language (DML)

  • Provides operations for basic data manipulation tasks such as retrieving, adding, deleting, or modifying records within the database.

Data Storage Definition Language (DSDL)

  • Specifies the internal schema and defines mappings, storage structures, and access methods.

View Definition Language (VDL)

  • Specifies the user’s view and their mappings to the conceptual schema.

Fourth-Generation Language (4GL)

  • A compact, efficient, and non-procedural programming language designed to improve DBMS productivity.

References

  • Database Systems Concepts by S K Singh

  • Database System Concepts by Silberschatz, Korth, Sudarshan

  • Database Management Systems by Ramakrishnan and Gehrke

  • Course material from Duke University and MCS Fall 2009.

robot