Introduction to Database Systems Lectures 4-6 PDF

Introduction to Database Systems  Three-Schema Architecture 19  Three-Schema Architecture ▸Proposed to support DBMS characteristics of: ▹Self-describing nature of DBMS ▹Program-data independence. ▹Support of multiple views of the data. ▸Not e...

Introduction to Database Systems  Three-Schema Architecture 19  Three-Schema Architecture ▸Proposed to support DBMS characteristics of: ▹Self-describing nature of DBMS ▹Program-data independence. ▹Support of multiple views of the data. ▸Not explicitly used in commercial DBMS products, but has been useful in explaining database system organization 20 Figure9. Levels of DBS Architecture  Three-Schema Architecture External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual level. Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. Internal schema at the internal level to describe physical storage structures and access paths. Typically uses a 21 physical data model. Figure10. Three-Schema Architecture  Three-Schema Architecture ▸Mappings among schema levels are needed to transform requests and data. ▹Programs refer to an external schema are mapped )i.e., translated) 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 5  Data Independence 6  Data Independence ▸Three-schema architecture is used to achieve data independence ▹changing schema at one level without changing the schema at the next higher level 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. 7  Data Independence Logical Data Independence: The capacity to change the conceptual schema without having to change the external schemas and their application programs. E.g., view should not be changed if the base tables are changed. 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 whenever certain file structures are reorganized or 25 new indexes are created to improve database performance.  DBMS Language and Interfaces 9  DBMS Languages ▸Data Definition Language )DDL) is used by the DBA and database designers to specify the conceptual schema of a database. ▹In many DBMSs, the DDL is also used to define internal and external schemas (views(. ▸Storage Definition Language )SDL) is typically realized via DBMS commands provided to the DBA and database designers ▸View Definition Language (VDL) are used to define internal and external schemas. 10  DBMS Languages ▸Data Manipulation Language (DML) is used to specify database retrievals and updates. ▹Retrieval of information ▹Insertion of new information ▹Deletion and/or modification of information stored in the DB ▸Two main types DMLs ▹Low level (procedural ) DML: DML commands (data sublanguage) can be embedded in a general-purpose programming language (host language), such as C or an Assembly Language. ▹High-level (nonprocedural) DML: stand-alone DML commands can be applied directly (called a query language(. 11  DBMS Languages ▸High Level or Non-procedural Languages: e.g., SQL, are set-oriented and specify what data to retrieve than how to retrieve. Also called declarative languages. ▸Low Level or Procedural Languages: record-at-a-time; they specify how to retrieve data and include constructs such as looping. 12  DBMS Interfaces ▸Stand-alone query language interfaces. ▸Programmer interfaces for embedding DML in programming languages: ▹Pre-compiler Approach ▹Procedure (Subroutine) Call Approach ▸User-friendly interfaces: ▹Menu-based, popular for browsing on the web ▹Forms-based, designed for naïve users ▹Graphics-based (Point and Click, Drag and Drop etc(. ▹Natural language: requests in written English 30 ▹Combinations of the above  Other DBMS Interfaces ▸Speech as Input and Output ▸Web Browser as an interface ▸Parametric interfaces (e.g., bank tellers) using function keys. ▸Interfaces for the DBA: ▹Creating accounts, granting authorizations ▹Setting system parameters ▹Changing schemas or access path 14  Database System Utilities and Tools 15  Database System Utilities ▸To perform certain functions such as: ▹Loading data stored in files into a database. Includes data conversion tools. ▹Backing up the database periodically on tape. ▹Reorganizing database file structures. ▹Report generation utilities. ▹Performance monitoring utilities. ▹Other functions, such as sorting, user monitoring, data compression, etc. 16  Other Tools ▸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. ▸Application Development Environments and CASE (computer-aided software engineering) tools: ▹Examples – Power builder (Sybase), Builder (Borland( 17  Centralized and Client/Server Architecture 18  Centralized DBMS Architecture ▸Centralized DBMS: combines everything into single system including: ▹DBMS software, ▹hardware, ▹application programs and ▹user interface processing software. 36 Figure11. A Physical Centralized DBMS Architecture  Basic Client-Server Architectures The client/server architecture was developed to deal with computer environment in which many PCs, workstation, file server, etc. A client in this framework is typically a user machine that provides user interface capabilities and local processing A server is a system containing both hardware and software that can provide services to the. client machines Figure12. A Basic Client-Server Architectures 37  Client-Server DBMS Architectures ▸The DBMS client/server architecture consists of: ▹Specialized Servers with Specialized functions, such as: File Servers Printer Servers Web Servers E-mail Servers ▹Clients ▹DBMS Server 38  Clients Provide appropriate interfaces and a client-version of the system to access and utilize the server resources. Connected to the servers via some form of a network (e.g., LAN: local area network, wireless network, etc(. 39 Figure13. A Basic Client-Server Architectures  DBMS Server ▸Provides database query and transaction services to the clients Figure14. DBMS Server ▸Sometimes called query and transaction servers 23  Two Tier Client-Server Architecture ▸User Interface Programs and Application Programs run on the client side ▸Interface called ODBC (Open Database Connectivity) provides an Application program interface (API) allow client-side programs to call the DBMS. ▸Most DBMS vendors provide ODBC drivers. 24  Two Tier Client-Server Architecture ▸Divided into two parts: 2 Data Tier: consists of the database, 1 where requests are processed, and the Client Tier: where requests are sent data are sent back to the client. 42 from the clients to the database.  Two Tier Client-Server Architecture Figure12. Logical Two-Tier Client/Server Figure13. Physical Two-Tier Client/Server DBMS Architecture DBMS Architecture 43  Two Tier Client-Server Architecture ▸A client program may connect to several DBMSs. ▸Other variations of clients are possible: e.g., in some DBMSs, more functionality is transferred to clients including: ▹data dictionary functions, and ▹optimization and recovery across multiple servers, etc. ▸In such situations the server may be called the Data Server. 27  Three Tier Client-Server Architecture ▸The emergence of the Web changed the roles of client and server, leading to the three-tier architecture which is common for Web applications. Figure14. Logical Three-Tier Client/Server DBMS Architecture 28  Three Tier Client-Server Architecture ▸Intermediate Layer or middle layer is sometimes called Application Server or Web Server: ▹stores the web connectivity software and the rules and business logic (constraints) part of the application used to access the right amount of data from the database server ▹acts like a conduit for sending partially processed data between the database server and the client. ▸Additional Features- Security: ▹encrypt the data at the server before transmission ▹decrypt data at the client 29  Two Tier vs. Three Tier Architecture Two Tier Client-Server Three Tier Client-Server Architecture Architecture 30  Classification of DBMSs 31  Classification of DBMSs ▸Based on the data model used: ▹Traditional: Relational, Network, Hierarchical. ▹Emerging: Object-oriented, Object-relational. ▸Other classifications: ▹Single-user (typically used with micro-computers) vs. multi-user )most DBMSs(. ▹Centralized )uses a single computer with one database) vs. distributed (uses multiple computers, multiple databases( 32  Classification of DBMSs ▸Distributed Database Systems are 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. Figure 15 is a typical example of distributed database system, in which communication channel is used to communicate with the different locations and every system has its own memory and database. 50 Figure15. Distributed Database System  Variations of Distributed Environments: all the sites use identical different sites have different operating DBMS and operating systems systems, DBMS products and data models independent in nature and integrated Each database is independent together so that they function as a that functions on its own. single database system Data is distributed across the database systems employ a central homogeneous nodes and a central DBMS coordinating module through which 51 co-ordinates data updates across the sites the databases are accessed