Database System Concepts and Architecture

Chapter 2

COSC 457

Sungchul Hong

The architecture of DBMS

• Early monolithic system

• Modular design– Client module– Server module

• Storage, access, search, and other functions

Data Models, Schemas, and Instances

• Data Model– A collection of concepts that can be used to

describe that structure of a database.– Structure of a database: data types,

relationships, and constraints – Basic operations

• Specifying retrievals and updates on the database

– Dynamic aspect (behavior): user defined operations.

Categories of Data Models

• High-level (conceptual) Data Models– They provide concepts that are close to the way many

users perceive data.

• Representational (Implementation) Data Models– Between two extremes

• Low-level (physical) Data Models– They provide concepts that describe the details of how

data is stored in the computer.

Conceptual Data Model

• Entities, attributes, and relationships– Entity represents a real-world object or concept.– An attribute represents some property of

interest that further describes an entity.– A relationship among two or more entities

represents an interaction among the entities.

• Entity-Relationship model.

Representational Data Models

• Relational data model

• Network models

• Hierarchical models

• Object data models

Hierarchical Model

• ADVANTAGES:• Hierarchical Model is simple to construct and operate on• Corresponds to a number of natural hierarchically organized

domains - e.g., assemblies in manufacturing, personnel organization in companies

• Language is simple; uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT etc.

• DISADVANTAGES:• Navigational and procedural nature of processing• Database is visualized as a linear arrangement of records• Little scope for "query optimization"

Network Model• ADVANTAGES:

• Network Model is able to model complex relationships and represents semantics of add/delete on the relationships.

• Can handle most situations for modeling using record types and relationship types.

• Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET etc. Programmers can do optimal navigation through the database.

• DISADVANTAGES:• Navigational and procedural nature of processing• Database contains a complex array of pointers that thread through a

set of records. Little scope for automated "query optimization”

Network Model

Relational Data Model

• Advantages– Strong Mathematical background– Query optimization is possible– 4th generation language (non-procedural)

• Disadvantage– Some (e.g. joint) Operations require a great

amount of resources.

Relational Data Model

Branch

Staff

WorkN

1

Physical Data Models

• How data is stored in the computer by representing information such as record formats, record orderings, and access paths.

Schemas, Instances, and Database State

• Schema (Meta data, intension)– The description of the database

• It is specified during database design.• It is not expected to change frequently.• Schema diagram: names of record types and data items

• Instances (extension of the schema)– The actual data in a database– It may change quite frequently.

• Database state: a snapshot, current set of instances• DBMS is responsible for ensuring that every state

of the database is a valid state.

Instance

Schema

DBMS Architecture and Data Independence

• The Three-Schema Architecture– Internal level– Conceptual level– External level

• Data Independence– Logical data independence– Physical data independence

The Three-Schema Architecture

• Internal level (internal schema)– Physical storage structure of the database.

• Conceptual level (conceptual schema)– The structure of the whole database for a community of

users.– Hides details

• External (view) level (external schemas or user views)– User’s view point of a database.

• Mappings: transforming requests and results between levels

Data Independence

• Logical data independence– Capacity to change the conceptual schema without

having to change external schemas or application programs.

• Physical data independence– Capacity to change the internal schema without having

to change to conceptual (or external) schema.

• Only mappings between two levels will be changed.

DBMS Languages

• Data Definition Language (DDL)• Storage Definition Language (SDL)• View Definition Language (VDL)• Data Manipulation Language (DML)• Comprehensive database language SQL

– Nonprocedural DML• Interactive (query language) or embedded (host/data)• Set-at-a-time, set-oriented, declarative (what)

– Procedural DML• Embedded in a general-purpose programming language.• Record-at-a-time,

SQL Example (DDL)

• CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL,

DNUMBER INTEGER NOT NULL,MGRSSN CHAR(9),MGRSTARTDATE CHAR(9),PRIMARY KEY (DNUMBER),UNIQUE (DNAME),FOREIGN KEY (MGRSSN) REFERENCES

EMPON DELETE SET DEFAULT ON UPDATE CASCADE );

SQL Example (DML)

• SELECT BDATE, ADDRESSFROM EMPLOYEEWHERE FNAME='John' AND

MINIT='B’ AND LNAME='Smith’;

Staff

staffNofName lName position sex DOB salary branchNo

SL21 John White Manager M Oct/1/45 30000 B005

SG37 Ann Beech Assistant F Nov/10/60 12000 B003

SG14 David Ford Supervisor M Mar/24/58 18000 B003

SA9 Mary Howe Assistant F 19/Feb/70 9000 B007

SG5 Susan Brand Manager F Jun/3/40 24000 B003

SL41 Julie Lee Assistant F Jun/13/65 9000 B005

SELECT staffNo, fName, lName, position, salaryFROM StaffWHERE salary > 10000;

DBMS Interfaces• Menu-Based Interfaces for Browsing.

– Browsing interface

• Forms-Based Interfaces– Forms specification languages

• Graphical User Interfaces• Natural Language Interface• Interfaces for Parametric Users

– Bank tellers

• Interfaces for the DBA: privileged commands– Creating accounts, setting system parameters, granting account

authorization, changing a schema, …

DBMS Component Modules• Operating systems• Stored Data Manager• DDL compiler

– Stores description of the schemas in the DBMS catalog.– Names of files, data items, storage details of each file, mapping in

formations among schemas, constraints

• Run-time database processor• Query compiler: interactive queries• Pre-compiler: DML command from an application program• DML compiler: compilation into object code.

try { rset= stmt.executeQuery("SELECT * from department"); ResultSetMetaData rsmd=rset.getMetaData(); while (rset.next()) { System.out.println(" " + rset.getString(1) + " " + rset.getString(2) + " " + rset.getString(3) +" "); } }

Embedded Code (Java)

Interactive SQL

• SQL> SELECT table_name FROM user_tables;

• TABLE_NAME

• ------------------------------

• DEPARTMENT

• STUDENT

Database Systems Utilities

• Loading– Convert non-database files into db files

• Backup• File reorganization

– organize a database file into a different file organization to improve performance

• Performance monitoring– Monitoring database usage.

• Sorting files, data compression, etc.

Tools, Application Environments, and Communication Facilities

• Expanded data dictionary system– Usage standards, application program

descriptions

• Application development environment

• Communications software

Classification of Database• Data model

– Relational data model, Object data model, Hierarchical, Network

• Number of users– Single-user (Access) , multi-user systems (Oracle)

• Number of sites– Centralized, distributed

• Costs• Types of Access path• Target area

– General purpose, special purpose (on-line transaction processing system)