Database System Concepts and Architecture PDF

Chapter 2 Database System Concepts and Architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Models  Data Model:  A set of concepts to describe the structure of a database, the operations for man...

Chapter 2 Database System Concepts and Architecture Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Data Models  Data Model:  A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints that the database should obey.  Data Model Structure and Constraints:  Constructs are used to define the database structure  Constructs typically include elements (and their data types) as well as groups of elements (e.g. entity, record, table), and relationships among such groups  Constraints specify some restrictions on valid data; these constraints must be enforced at all times Slide 2- 2 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Models (continued)  Data Model Operations:  These operations are used for specifying database retrievals and updates by referring to the constructs of the data model.  Operations on the data model may include basic model operations (e.g. generic insert, delete, update) and user-defined operations (e.g. compute_student_gpa, update_inventory) Slide 2- 3 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Categories of Data Models  Conceptual (high-level, semantic) data models:  Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models.)  Physical (low-level, internal) data models:  Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals  Implementation (representational) data models:  Provide concepts that fall between the above two, used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems). Slide 2- 4 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Models, Schemas, and Instances  Data abstraction  Suppression (hiding )of details of data organization and storage  Highlighting of the essential( main) features for an improved understanding of data Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Categories of Data Models (cont'd.)  Entity  Represents a real-world object or concept  Attribute  Represents some property of interest  Further describes an entity  Relationship among two or more entities  Represents an association among the entities  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  Object data model  New family of higher-level implementation data models  Closer to conceptual data models 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  Access path Structure that makes the search for particular database records efficient(active)  Index Example of an access path Allows direct access to data using an index term or a keyword Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Schemas, Instances(case), and Database State  Database schema  Description of a database  Includes descriptions of the database structure, data types, and the constraints on the database  Schema diagram  Displays selected aspects of schema  Schema construct  Each object in the schema  Database state or snapshot  Data in database at a particular moment in time 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.)  Define a new database  Specify database schema to the DBMS  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  Database Schema vs. Database State (continued)  Distinction  The database schema changes very infrequently.  The database state changes every time the database is updated.  Schema is also called intension (building).  State is also called extension 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  External or view level  Describes part of the database that a particular user group is interested in Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Schema Architecture and Data Independence (cont'd.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Schema Architecture  Mappings among schema levels are needed to transform requests and data.  Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution.  Data extracted from the internal DBMS level is reformatted to match the user’s external view (e.g. formatting the results of an SQL query for display in a Web page) Slide 2- 15 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Independence  Logical Data Independence:  The capacity to change the conceptual schema without having to change the external schemas and their associated application programs.  Physical Data Independence:  The capacity to change the internal schema without having to change the conceptual schema.  For example, the internal schema may be changed when certain( specific) file structures are reorganized or new indexes are created to improve database performance Slide 2- 16 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Data Independence (continued)  When a schema at a lower level is changed, only the mappings between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence.  The higher-level schemas themselves are unchanged.  Hence, the application programs need not be changed since they refer to the external schemas. Slide 2- 17 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Languages  Data definition language (DDL) Defines both schemas (DBA, DB designer)  Storage definition language (SDL) Specifies the internal schema  View definition language (VDL) Specifies user views/mappings to conceptual schema (External Level)  Data manipulation language (DML) Allows retrieval, insertion, deletion, modification Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Languages (cont'd.)  High Level or Non-procedural Language:  For example, the SQL relational language  Are “set”-oriented and specify what data to retrieve rather than how to retrieve it.  Also called declarative languages.  Low Level or Procedural Language:  Retrieve data one record-at-a-time;  Constructs such as looping are needed to retrieve multiple records, along with positioning pointers Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Interfaces  Menu-based interfaces for Web clients or browsing  Forms-based interfaces  Graphical user interfaces  Natural language interfaces  Speech input and output  Interfaces for parametric users  Interfaces for the DBA Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Database System Environment  DBMS component modules  Buffer management Schedule disk read/write, because this has a considerable effect on performance. Reducing disk read/write improves performance considerably.  Stored data manager controls access to DBMS information that is stored on disk, whether it is part of the database or the catalog. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Database System Environment (cont'd.) Interactive query interface-Interface that casual users and persons with occasional need for information from the database interact.  query compiler that compiles queries into an internal form. Queries are parsed and validated for correctness of the query syntax, the names of files and data elements, and so on  Query optimizer concerned( interested) with the rearrangement and possible reordering of operations, elimination of redundancies, and use of correct algorithms and indexes during execution. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Database System Environment (cont'd.)  Precompiler 1-extracts DML commands from an application program written in a host programming language. 2-These commands are sent to the DML compiler for compilation into object code for database access.3- The rest of the program is sent to the host language compiler. The object codes for the DML commands and the rest of the program are linked, forming a canned transaction whose executable code includes calls to the runtime database processor. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The Database System Environment (cont'd.)  DBMS component modules  Runtime database processor executes: (1) the privileged commands, (2) the executable query plans, and (3) the canned transactions with runtime parameters.  System catalog  Concurrency control system  Backup and recovery system Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Database System Utilities  Loading  Load existing data files  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  Performance monitoring  Monitors database usage and provides statistics to the DBA Copyright © 2011 Ramez Elmasri and Shamkant Navathe Tools, Application Environments, and Communications Facilities  Data dictionary / repository:  Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc.  Active data dictionary is accessed by DBMS software and users/DBA.  Passive data dictionary is accessed by users/DBA only Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Centralized and Client/Server Architectures for DBMSs  Centralized DBMSs Architecture  Combines everything into single system including- DBMS software, hardware, application programs, and user interface processing software.  User can still connect through a remote terminal – however, all processing is done at centralized site Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Basic Client/Server Architectures  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 Copyright © 2011 Ramez Elmasri and Shamkant Navathe Basic Client/Server Architectures (cont'd.)  Client machines  Provide appropriate interfaces through a client software module to access and utilize the various server resources.  Clients may be diskless machines or PCs or Workstations with disks with only the client software installed.  Connected to the servers via some form of a network.  (LAN: local area network, wireless network, etc.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  DBMS Server  Provides database query and transaction services to the clients  Relational DBMS servers are often called SQL servers, query servers, or transaction servers  Applications running on clients utilize an Application Program Interface (API) to access server databases via standard interface such as:  ODBC: Open Database Connectivity standard  JDBC: for Java programming access  Client and server must install appropriate client module and server module software for ODBC or JDBC Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Two-Tier Client/Server Architectures for DBMSs  A client program may connect to several DBMSs, sometimes called the data sources.  In general, data sources can be files or other non- DBMS software that manages data.  Other variations of clients are possible: e.g., in some object DBMSs, more functionality is transferred to clients including data dictionary functions, optimization and recovery across multiple servers, etc. 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  JDBC  Allows Java client programs to access one or more DBMSs through a standard interface Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Three-Tier and n-Tier Architectures for Web Applications  Common for Web applications  Intermediate Layer called Application Server or Web Server:  Stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server  Acts like a conduit for sending partially processed data between the database server and the client.  Three-tier Architecture Can Enhance Security:  Database server only accessible via middle tier  Clients cannot directly access database server Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Classification of Database Management Systems  Based on the data model used  Traditional: Relational, Network, Hierarchical.  Emerging: Object-oriented, Object-relational.  Other classifications  Single-user (typically used with personal computers) vs. multi-user (most DBMSs).  Centralized (uses a single computer with one database) vs. distributed (uses multiple computers, multiple databases) Copyright © 2011 Ramez Elmasri and Shamkant Navathe Variations of Distributed DBMSs (DDBMSs)  Homogeneous DDBMS  Heterogeneous DDBMS  Federated or Multidatabase Systems  Distributed Database Systems have now come to be known as client-server based database systems because:  They do not support a totally distributed environment, but rather a set of database servers supporting a set of clients. Slide 2- 40 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Cost considerations for DBMSs  Cost Range: from free open-source systems to configurations costing millions of dollars  Examples of free relational DBMSs: MySQL, PostgreSQL, others  Commercial DBMS offer additional specialized modules, e.g. time-series module, spatial data module, document module, XML module  These offer additional specialized functionality when purchased separately  Sometimes called cartridges (e.g., in Oracle) or blades  Different licensing options: site license, maximum number of concurrent users (seat license), single user, etc. Slide 2- 41 Copyright © 2011 Ramez Elmasri and Shamkant Navathe