COMP3205 DB CH2 (FL24).ppt

Chapter 2 Overview of Database Languages and Architectures Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 2 Outline  Data Models, Schemas, and Instances  Three-Schema Architecture and Data Independence  Datab...

Chapter 2 Overview of Database Languages and Architectures Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 2 Outline  Data Models, Schemas, and Instances  Three-Schema Architecture and Data Independence  Database Languages and Interfaces  The Database System Environment  Centralized and Client/Server Architectures for DBMSs  Classification of Database Management Systems Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database System Concepts and Architecture  Basic client/server DBMS architecture  Client module Handling user interaction  Server module Provides user interface Handles data storage, access and search Database Architecture: Refers to the design of a database system, including how data is stored, managed, and accessed, as well as the interactions between the database, users, and other systems. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Models, Schemas, and Instances  Data abstraction  Hide details of data organization and storage  Highlighting of the essential features for an improved understanding of data  Different users can find data at their preferred level of detail Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Models,Data Schemas, and type, relationships, Instancesand(cont'd.) constraints that apply to the data  Data model  Is a Collection of concepts that describe the structure of a database  Provides means to achieve data abstraction  Basic operations Specify retrievals and updates on the database  Dynamic aspect or behavior of a database application Allows the database designer to specify a set of valid operations allowed on database objects (e.g. user-defined operation: COMPUTE_GPA can be applied to Student object) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Categories of Data Models Based on concepts used to describe DB structure  High-level or conceptual data models  Close to the way many users perceive End user data  Representational data models  Easily understood by end users  Also similar to how data organized in computer storage  Low-level or physical data models  Describe the details of how data is stored Computer specialiston computer storage media Copyright © 2011 Ramez Elmasri and Shamkant Navathe Categories of Data Models (cont'd.) Conceptual data model concepts: Entity  Represents a real-world object or concept (e.g. a student) Attribute  Represents some property of interest  Further describes an entity (e.g. student name) Relationship among two or more entities  Represents an association among the entities (e.g. relation between an employee and a project)  Entity-Relationship model Copyright © 2011 Ramez Elmasri and Shamkant Navathe Categories of Data Models (cont'd.)  Relational data model  Used most frequently in traditional commercial DBMSs (e.g. network model)  Object data model  New family of higher-level implementation data models that are closer to conceptual data models (frequently used in software engineering domain) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Categories of Data Models (cont'd.)  Physical data models  Describe how data is stored as files in the computer (record formats, records ordering, and access path)  Access path Structure that makes the search for particular database records efficient  Index Example of an access path Allows direct access to data using an index term or a keyword (e.g. index at the end of the book) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Categories of Data Models (cont'd.)  Physical data models Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schemas, Instances, and Database Aspects: -Data type of data items State -Relationships  Database schema -Constraints (e.g. prerequisite for Specified during design phase and not changed frequently  majoring in CS) Description of a database  Schema diagram Display structure of record but not instance of  Displays selected (some) aspects of schema records  Schema construct  Each object in the schema (e.g. student/course)  Database instance  Refer to individual records (actual students info.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schemas, Instances, and Database State (cont'd.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schemas, Instances, and Database State (cont'd.)  Database state or snapshot  Data in database at a particular moment in time  When Define a new database  Specify database schema to the DBMS  empty state (no data)  Initial state  Populated or loaded with the initial data  Valid state  Satisfies the structure and constraints specified in the schema Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schemas, Instances, and Database State (cont'd.)  Schema evolution  Changes applied to schema as application requirements change. This changes the database current state.  Example: adding the Date_of_birth to the STUDENT schema Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Schema Architecture and Data Independence  Three-Schema Architecture was proposed to achieve main characteristics of DBMS:  Self-describing nature (by storing schema)  Data, program, operation independence  Support Multi-user views Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Schema Architecture and Data Independence (cont'd.) Goal: Separate physical storage from user applications Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Schema Architecture and Data Independence  Internal level  Describes physical storage structure of the database  Conceptual level  Describes structure of the whole database for a community of users; focus on describing entities, data types, relationships, user operations and constraints  External or view level  Describes part of the database that a particular user group is interested in while hiding the rest of the data Mapping: is the process of transforming requests and results between levels Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Independence  Definition: Capacity to change the schema at one level of a database system without effecting the schema at the next higher level (notice: the mapping between levels is changed) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Independence  Capacity to change the schema at one level of a database system without effecting the schema at the next higher level (notice: the mapping between levels is changed)  Types:  Logical Capacity of changing conceptual scheme and not external schemas (e.g. adding/removing record type or data item) Application programs reference to external schema must work as before the changes  Physical Capacity of changing internal scheme and not conceptual schema (e.g. creating additional access structure) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Languages  Data definition language (DDL) One language is used to define both conceptual and internal schemas by the DBA and database designers  Storage definition language (SDL) Specifies the internal schema (control indexing and mapping)  View definition language (VDL) Specifies user views/mappings to conceptual schema (identifying external-level)  Data manipulation language (DML) Allows retrieval, insertion, deletion, modification  Comprehensive database language  Combination of above (e.g. SQL, relational database language)  Use more than a language to define the conceptual/external levels Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Languages (cont'd.) Types of data manipulation languages (DML): Casual end users use a high-level query language to specify their requests, programmers use the DML in its embedded form. naive users, there usually are user-friendly interfaces for interacting with the database; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS user-friendly Interfaces  Menu-based interfaces for Web clients or browsing (e.g. Web-based user interface)  Forms-based interfaces  Graphical user interfaces (mixed of menus and forms)  Natural language interfaces (use dictionary/search engines)  Speech input and output (e.g. telephone directory)  Interfaces for parametric users (e.g. bank tellers)  Interfaces for the DBA (multi-tasks commands) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Database System Environment  DBMS component modules  Buffer management  Stored data manager  DDL compiler  Interactive query interface Query compiler validate the query syntax correctness Query optimizer concerned with re-ordering of operations, eliminating redundancies, use of correct algorithm/indexes during execution  Pre-compiler: extract DML commands from application program written in host programming language  DML Compiler: compilation into object codes Copyright © 2011 Ramez Elmasri and Shamkant Navathe The Database System Environment (cont'd.)  DBMS component modules  Runtime database processor Execute privileged commands, executable query plans and canned transactions with runtime parameters, update system catalog with statistics  System catalog  Concurrency control system Integrated into work of runtime processor for  Backup and recovery system transaction management Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Client server application server Database server Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database System Utilities  Loading  Load existing data files into a database  Conversion tools helps transferring data between multiple DBMS  Backup  Creates a backup copy of the database Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database System Utilities (cont'd.)  Database storage reorganization  Reorganize a set of database files into different file organizations  Create new access paths to improve query performance  Performance monitoring  Monitors database usage and provides statistics to the DBA for making decisions such as need for new index to improve performance Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database System Utilities (cont'd.)  Other utilities for:  Sorting files  Handling data compression  Monitoring users access Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Tools, Application Environments, and Communications Facilities  CASE (Computer Aided Software Engineering) Tools  For designing database  Data dictionary (data repository) system (info. Respository)  Stores design decisions, usage standards, application program descriptions, and user information. It has wider information than DBMS catalog. It mainly accessed by users  for large organization  Application development environments  Includes facilities for developing DB, including database design, GUI development, querying and updating  Communications software  Allows users at locations remote from database system site to access the database from distance via workstations or personal computers Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Centralized and Client/Server Architectures for DBMSs  Centralized DBMSs Architecture  Past: All DBMS functionality, application program execution, and user interface processing carried out on one machine  Gradually, DBMS systems make use of processing power at the user side, and consequently the client/server DBMS is developed Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Basic Client/Server Architectures  Specialized Servers with specific functionalities  File server Maintains the files of the client machines.  Printer server Connected to various printers; all print requests by the clients are forwarded to this machine  Web servers or e-mail servers  Resources provided by specialized servers is accessible by many client machines Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Basic Client/Server Architectures (cont'd.)  Client machines  Provide user with: Appropriate interfaces to use specialized servers and local processing power to run local applications Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Basic Client/Server Architectures (cont'd.)  Client  User machine that provides user interface capabilities and local processing  Server  System containing both hardware and software  Provides services to the client machines Such as file access, printing, archiving, or database access Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Two-Tier Client/Server Architectures for DBMSs  The software components are distributed among two systems (client and server)  Server handles  Query and transaction functionality related to SQL processing, data storage, local concurrency control, buffering  Client handles  User interface programs and application programs, data dictionary functions Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Two-Tier Client/Server Architectures (cont'd.)  Open Database Connectivity (ODBC)  Provides application programming interface (API)  Allows client-side programs to call the DBMS Both client and server machines must have the necessary software installed  Example: JDBC  Allows Java client programs to access one or more DBMSs through a standard interface Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Two-Tier Client/Server Architectures (cont'd.)  DBMS interacts with programming language compilers, global query optimization, concurrency control, and recovery across multiple servers (client and server interaction).  The exact distribution of functions vary from system to another. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Three-Tier and n-Tier Architectures for Web Applications  Application server or Web server  Adds intermediate layer between client and the database server  Runs application programs and stores business rules  Provides security by checking client’s qualification before applying a request Copyright © 2011 Ramez Elmasri and Shamkant Navathe  (a) Three – tires: Use interface  application rules  data access Accept client request Process data Process request further and filter and sends DB to present to queries to DB user in GUI server format Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Display info to user and Handles intermediate allows data rules/constraints before passing entry data Act as web server: retrieve and format query results into dynamic web pages for web browser view in client side. Data management services Copyright © 2011 Ramez Elmasri and Shamkant Navathe N-tier Divide the layers between the user and the stored data further into finer components Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems (at a time) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems (cont'd.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems (cont'd.) Example of hierarchical data model Copyright © 2011 Ramez Elmasri and Shamkant Navathe Example of XML DB Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems (cont'd.)  Number of sites Centralized: data stored in a single computer site but support multiple users Distributed(DDBMS): actual DB and DBMS software distributed over many sites Homogeneous: use the same DBMS software at all the sites Heterogeneous: use different DBMS software at each site Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems (cont'd.)  Cost Open source (free) Different types of licensing  Types of access path options for storing files  Purpose:  General-purpose  Special-purpose: DBMS built for specific application; require major changes if used for other applications. (e.g. some airline reservation systems – “online transaction processing” category) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Summary  Concepts used in database systems  Main categories of data models  Types of languages supported by DMBSs  Interfaces provided by the DBMS  DBMS classification criteria:  Data model, number of users, number of sties, access paths, cost Copyright © 2011 Ramez Elmasri and Shamkant Navathe