Chapter 2 Database Environment PDF

Chapter 2 Database Environment Transparencies © Pearson Education Limited 1995, 2005 1 Chapter 2 - Objectives  Purpose of three-level database architecture.  Contents of external, conceptual, and internal levels.  Purpose of external/conceptual...

Chapter 2 Database Environment Transparencies © Pearson Education Limited 1995, 2005 1 Chapter 2 - Objectives  Purpose of three-level database architecture.  Contents of external, conceptual, and internal levels.  Purpose of external/conceptual and conceptual/internal mappings.  Meaning of logical and physical data independence.  Distinction between DDL and DML.  A classification of data models. 2 © Pearson Education Limited 1995, 2005 Chapter 2 - Objectives  Purpose/importance of conceptual modeling.  Typical functions and services a DBMS should provide.  Function and importance of system catalog.  Software components of a DBMS.  Meaning of client–server architecture and advantages of this type of architecture for a DBMS.  Function and uses of Transaction Processing Monitors. 3 © Pearson Education Limited 1995, 2005 Objectives of Three-Level Architecture  All users should be able to access same data.  A user’s view is immune to changes made in other views.  Users should not need to know physical database storage details. 4 © Pearson Education Limited 1995, 2005 Objectives of Three-Level Architecture  DBA should be able to change database storage structures without affecting the users’ views.  Internal structure of database should be unaffected by changes to physical aspects of storage.  DBA should be able to change conceptual structure of database without affecting all users. 5 © Pearson Education Limited 1995, 2005 ANSI-SPARC Three-Level Architecture 6 © Pearson Education Limited 1995, 2005 ANSI-SPARC Three-Level Architecture  External Level – Users’ view of the database. – Describes that part of database that is relevant to a particular user.  Conceptual Level – Community view of the database. – Describes what data is stored in database and relationships among the data. 7 © Pearson Education Limited 1995, 2005 ANSI-SPARC Three-Level Architecture  Internal Level – Physical representation of the database on the computer. – Describes how the data is stored in the database. 8 © Pearson Education Limited 1995, 2005 Differences between Three Levels of ANSI- SPARC Architecture 9 © Pearson Education Limited 1995, 2005 Data Independence  Logical Data Independence – Refers to immunity of external schemas to changes in conceptual schema. – Conceptual schema changes (e.g. addition/removal of entities). – Should not require changes to external schema or rewrites of application programs. 10 © Pearson Education Limited 1995, 2005 Data Independence  Physical Data Independence – Refers to immunity of conceptual schema to changes in the internal schema. – Internal schema changes (e.g. using different file organizations, storage structures/devices). – Should not require change to conceptual or external schemas. 11 © Pearson Education Limited 1995, 2005 Data Independence and the ANSI-SPARC Three-Level Architecture 12 © Pearson Education Limited 1995, 2005 Database Languages  Data Definition Language (DDL) – Allows the DBA or user to describe and name entities, attributes, and relationships required for the application – plus any associated integrity and security constraints. 13 © Pearson Education Limited 1995, 2005 Database Languages  Data Manipulation Language (DML) – Provides basic data manipulation operations on data held in the database.  Procedural DML – allows user to tell system exactly how to manipulate data.  Non-Procedural DML – allows user to state what data is needed rather than how it is to be retrieved.  Fourth Generation Languages (4GLs) 14 © Pearson Education Limited 1995, 2005 Data Model Integrated collection of concepts for describing data, relationships between data, and constraints on the data in an organization.  Data Model comprises: – a structural part; – a manipulative part; – possibly a set of integrity rules. 15 © Pearson Education Limited 1995, 2005 Data Model  Purpose – To represent data in an understandable way.  Categories of data models include: – Object-based – Record-based – Physical. 16 © Pearson Education Limited 1995, 2005 Data Models  Object-Based Data Models – Entity-Relationship – Semantic – Functional – Object-Oriented.  Record-Based Data Models – Relational Data Model – Network Data Model – Hierarchical Data Model.  Physical Data Models 17 © Pearson Education Limited 1995, 2005 Relational Data Model 18 © Pearson Education Limited 1995, 2005 Network Data Model 19 © Pearson Education Limited 1995, 2005 Hierarchical Data Model 20 © Pearson Education Limited 1995, 2005 Conceptual Modeling  Conceptual schema is the core of a system supporting all user views.  Should be complete and accurate representation of an organization’s data requirements.  Conceptual modeling is process of developing a model of information use that is independent of implementation details.  Result is a conceptual data model. 21 © Pearson Education Limited 1995, 2005 Functions of a DBMS  Data Storage, Retrieval, and Update.  A User-Accessible Catalog.  Transaction Support.  Concurrency Control Services.  Recovery Services. 22 © Pearson Education Limited 1995, 2005 Functions of a DBMS  Authorization Services.  Support for Data Communication.  Integrity Services.  Services to Promote Data Independence.  Utility Services. 23 © Pearson Education Limited 1995, 2005 System Catalog  Repository of information (metadata) describing the data in the database.  One of the fundamental components of DBMS.  Typically stores: – names, types, and sizes of data items; – constraints on the data; – names of authorized users; – data items accessible by a user and the type of access; – usage statistics. 24 © Pearson Education Limited 1995, 2005 Components of a DBMS 25 © Pearson Education Limited 1995, 2005 Components of Database Manager (DM) 26 © Pearson Education Limited 1995, 2005 Multi-User DBMS Architectures  Teleprocessing  File-server  Client-server 27 © Pearson Education Limited 1995, 2005 Teleprocessing  Traditional architecture.  Single mainframe with a number of terminals attached.  Trend is now towards downsizing. 28 © Pearson Education Limited 1995, 2005 File-Server  File-server is connected to several workstations across a network.  Database resides on file-server.  DBMS and applications run on each workstation.  Disadvantages include: – Significant network traffic. – Copy of DBMS on each workstation. – Concurrency, recovery and integrity control more complex. 29 © Pearson Education Limited 1995, 2005 File-Server Architecture 30 © Pearson Education Limited 1995, 2005 Traditional Two-Tier Client-Server  Client (tier 1) manages user interface and runs applications.  Server (tier 2) holds database and DBMS.  Advantages include: – wider access to existing databases; – increased performance; – possible reduction in hardware costs; – reduction in communication costs; – increased consistency. 31 © Pearson Education Limited 1995, 2005 Traditional Two-Tier Client-Server 32 © Pearson Education Limited 1995, 2005 Traditional Two-Tier Client-Server 33 © Pearson Education Limited 1995, 2005 Three-Tier Client-Server  Client side presented two problems preventing true scalability: – ‘Fat’ client, requiring considerable resources on client’s computer to run effectively. – Significant client side administration overhead.  By 1995, three layers proposed, each potentially running on a different platform. 34 © Pearson Education Limited 1995, 2005 Three-Tier Client-Server  Advantages: – ‘Thin’ client, requiring less expensive hardware. – Application maintenance centralized. – Easier to modify or replace one tier without affecting others. – Separating business logic from database functions makes it easier to implement load balancing. – Maps quite naturally to Web environment. 35 © Pearson Education Limited 1995, 2005 Three-Tier Client-Server 36 © Pearson Education Limited 1995, 2005 Transaction Processing Monitors  Program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for Online Transaction Processing (OLTP). 37 © Pearson Education Limited 1995, 2005 TPM as middle tier of 3-tier client-server 38 © Pearson Education Limited 1995, 2005