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.