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

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

3. Categories of data models • One fundamental characteristic of the database approach is that it provides some level of data abstraction • 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 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. Database V.S. File • In the database approach, a single repository of data is maintained that is defined once then accessed by various users • The major differences between DB and File are: • Self-describing of a DB • Insulation between programs and data • Support of multiple views of the data • Sharing of data and multiuser transaction processing

8. University Database

9. Example of a Database Schema (incomplete)

10. Example of a Database Schema

11. 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

12. 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.

13. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • Centralized and client/server architecture

14. 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

15. 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.

16. The three-schema architecture

17. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • Centralized and client/server architecture

18. 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

19. 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

20. Typical DBMS Component Modules

21. Outline • Data Models, Schemas, and Instances • Three-Schema Architecture • Database language and Interfaces • Centralized and client/server architecture

22. 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

23. A Physical Centralized Architecture

24. 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.

25. Logical two-tier client server architecture

26. 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

27. 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

28. Three-tier client-server architecture

29. 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)