Chapter 2 Database System Concepts and Architecture

1. Chapter 2 Database System Concepts and Architecture Dr. Bernard Chen Ph.D. University of Central Arkansas Fall 2008

2. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • The database system environment • Centralized and client/server architecture

3. Categories of data models • High-level or Conceptual data models: Provide concept that are close to the way many users perceive data • Low-level or Physical data model: Provide concepts that describe the details of how data is stored in the computer

4. Conceptual data models • It uses concepts such as entities, attributes and relationships. • Entity represents a real-world object or concept, such as employee or project • Attribute represents some property of interest that further describes an entity, such as employee’s name or salary • Relation among two or more entities represents an association among two or more entitles

5. Example of a Relation

6. Schemas and Database State • In any data model, it is important to distinguish between the description of the data and database itself • The description of the database is called the database schema • A displayed Schema is called a schema diagram

7. University Database

8. Example of a Database Schema

9. Schemas and Database State • The data in the database at a particular moment in time is called a database state • The distinction between database schema and database state is very important • When we define a new database, we specify its database schema only to the DBMS • At this point, the corresponding database state is the empty state with no data • We get the initial state of the database when the database is first loaded • From then on, every time an update operation is applied to the database, we get another database state

10. Schemas and Database State • Valid State: a state that satisfies the structure and constrains specified in the schema. • The database schema changes very infrequently. • The database state changes every time the database is updated • Schema is also called intension. • State is also called extension.

11. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • The database system environment • Centralized and client/server architecture

12. Three-Schema Architecture • Three of four important characteristics of the database approach, listed in Ch.1 are: • Self-describing of a DB (Schema) • Insulation between programs and data • Support of multiple views of the data • Three-Schema Architecture : it was proposed to help achieve and visualize these characteristics

13. Three-Schema Architecture • Defines DBMS schemas at three levels: • Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). • Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. • External schemas at the external level to describe the various user views.

14. The three-schema architecture

15. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • The database system environment • Centralized and client/server architecture

16. DBMS Languages • The first step to create a database through DBMS is to specify conceptual and internal schemas for the database • Data Definition Language (DDL): is used by database designers to define schemas • Data Manipulation Language (DML) • View Definition Language (VDL): is to specify user views • In current DBMS, the preceding types of languages are usually not considered distinct languages

17. DBMS Programming Language Interfaces • Programmer interfaces for embedding DML in a programming languages: • Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for Java) • Procedure Call Approach: e.g. JDBC for Java, ODBC for other programming languages • Database Programming Language Approach: e.g. ORACLE has PL/SQL, a programming language based on SQL; language incorporates SQL and its data types as integral components

18. User-Friendly DBMS Interfaces • Menu-based, popular for browsing on the web (URSA) • Forms-based, designed for naïve users • Graphics-based • Natural language: requests in written English • Combinations of the above

19. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • The database system environment • Centralized and client/server architecture

20. DBMS Component Modules • A DBMS is a complex software system • The figure showed in next slide is divide into two halves. • The top half refers to the various users of the database system • The lower half shows the internals of the DBMS responsible for storage of data and processing of transactions

21. Typical DBMS Component Modules

22. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • The database system environment • Centralized and client/server architecture

23. Centralized DBMS Architecture • A centralized DBMS in which all the DBMS functionality, application program execution, and user interface processing were carried out on a single machine

24. A Physical Centralized Architecture

25. Basic Client/Server Architectures • The client/server architecture was developed to deal with computer environment in which a large number of PCs, workstation, file server… • 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.

26. Logical two-tier client server architecture

27. Two-tier Architecture • This is called two-tire architectures because the software components are distributed over two systems: client and server • The emergence of the Web changed the roles of client and server, leading to the three-tier architecture

28. Three-tier architecture • The intermediate layer or middle layer is sometimes called the application server or Web server • Three-tier Architecture Can Enhance Security: • Database server only accessible via middle tier • Clients cannot directly access database server

29. Three-tier client-server architecture

30. Three-tier architecture • The presentation layer displays information to the user • The business logic layer handles intermediate rules and constrains before data is passed up to the user or down to the DBMS • If the bottom layer is split into two layers (a web server and a database server), then it is a 4-tire architecture (possible to the n-tier)