4

Chapter 2. Database System Concepts ad Architecture

2.1 Data Models, Schemas, and Instances

- Data models

 A data model is a collection of concepts that can be used to describe the structure of
a database’s information content.
the data types, relationships, and
constraints that should hold on the data

 A data model provides the necessary means to achieve some level of data
abstraction.

(1) High-level (or conceptual) data models

- A conceptual data model describes a system in terms that its users will
understand such as a diagram that displays the main kinds of objects and their
relationships.

- It should not be far away from implementation.

- (ex) E-R (Entity-Relationship) model, Enhanced E-R model

(2) Representation (implementation, record-based or logical) data models

- A logical data model provides concepts that may be understood by end-users.

It specifies the structure of a database system. For a relational database, the
logical data model is a collection of table definitions.

- It should not be too far removed from the way data is organized within the
computer.

- It is used most frequently in traditional commercial DBMSs.

- (ex) Relational data model, network model, hierarchical model, and etc.

(3) Low-level (or physical) data models

- A physical data model describes the way in which a logical data model will be
represented in storage (i.e. how data is stored as files in the computer).

- It generally meant for computer specialist, not for typical end-users.

- (ex) record formats, record orderings, access paths, and etc.

 5

- Database Schemas

 Schema: Description of something

 A database schema is a precise description of the structure of information in a

database system. (eg) Figure 2.1 (p34) Schema diagram for a database

 It is specified during database design and it is not expected to change frequently.

2.2 Three-Schema Architecture and Data Independence

- The three-schema architecture (: level of abstraction)

Consider Figure 2.2 (p36) for the three-schema architecture

(1) External schema

- An external schema gives each class of users a view of the database that is
trailed to their needs.

- A high-level or implementation data model can be used at this level.

(2) Conceptual (or logical) schema

- A conceptual schema describes the structure of the whole database. For

example, the conceptual schema sees a relational database as a collection of
tables.

- Concentrates on describing entities, data types, relationships, user operations,

and constraints

- A high-level or implementation data model can be used at this level.

(3) Internal schema

- An internal schema describes the physical storage structure of the database. At

this level, the computer sees the database as a collection of files and software.

- Describes the complete details of data storage and access paths for the database

- A physical data model can be used at this level.

- The division of the database system into these three levels enables both developers and
users to work within their own levels without having to know the details of the other
levels, and without having to respond to changes in the other levels. Most DBMSs do
 6

not separate the three levels completely, but support the three-schema architecture to
some extent.

- Processes of transforming requests and results between levels are called mapping.

- Data Independence

 Logical data independence is the capacity to change the conceptual schema without
having to change external schemas or application programs.

 Physical data independence is the capacity to change the internal schema without
having to change the conceptual schema.

2.3 Database Languages and Interfaces

- The DBMS must provide appropriate languages and interfaces for each category of
users.

(1) Data definition language (DDL)–to define conceptual and internal schemas

(2) Storage definition language (SDL)–to specify the internal schema

(3) View definition language (VDL)–to specify user views and their mappings to the
conceptual schema

(4) Data manipulation language (DML)–to manipulate (retrieval, insertion, deletion,

modification, etc.) data

2.5 Centralized and Client/Server Architectures for DBMSs

- Centralized DBMSs Architecture (see Figure 2.4, page 47 of the textbook)

● Application programs run on a mainframe and users access them through display
terminals that could perform only data input and display.

● As prices of hardware declined, most users replaced their terminals with PCs and
workstations, and most recently with mobile devices.

● At first, database systems used these computers similarly to how they had used
display terminals.

● Advantage: Easily maintained by a central administrator.

● Disadvantage: Users expect graphical interfaces that require much more

computational power than simple terminals.
 7

- Client/Server Architectures for DBMSs

● The commoditization of the PC and availability of cheap client computers led to the
development of the two-tier architecture (see Figure 2.5, page 48 of the textbook).

● Consists of client computers and server computers, which interact through a well-
defined protocol.

(1) Basic Client/Server Architecture

- The client machines provide the user with appropriate interfaces to utilize
servers, as well as with local processing power to run local applications (i.e
“thin-clients”).

- A server is a system containing both hardware and software that can provide
services to the client machines, such as file access, printing, archiving, or
database access.

(2) Two-Tier Client/Server Architecture

- More powerful clients that implement both user interface programs and
application programs (i.e. “thick-clients”).

- A standard called Open Database Connectivity (ODBC) provides an application

programming interface (API), which allows client-side programs to call the
DBMS.

- A client program can actually connect to several RDBMSs and send query and
transaction requests using the ODBC API, which are then processed at the
server sites. Any query results are sent back to the client program, which can
process and display the results as needed.

- Advantages: simplicity and seamless compatibility with existing systems.

- Disadvantages: (1) No central place to update and maintain the business logic
since the application code runs at many client sites.
(2) A large amount of trust is required between the server and
the clients.
(ex) The DBMS of a bank has to trust the ATM machine.

(3) Three-Tier and n-Tier Architectures for Web Applications

- The emergence of the Web changed the roles of clients and servers, leading to
the three-tier architecture.
 8

- Many Web applications use an architecture called the three-tier architecture,

which adds an intermediate layer between the client and the database server
(see Figure 2.7(a), page 50 of the textbook)

- The three-tier architecture separates application logic from data management.

● The business logic layer (a.k.a. application server or web server) runs
application programs and stores business rules (procedures or constraints) that
are used to access data from the database server. It can also improve database
security by checking a client’s credentials before forwarding a request to the
database server.

● The presentation tier displays information to the user and allows data entry.
Users require a natural interface to make requests, provide input, and to see
results. The widespread use of the Internet has made web-based interfaces
increasingly popular.

● The database services layer includes all data management services.

- Advantages: (1) Heterogeneous system (2) Thin Clients

(3) Integrated Data Access (4) Scalability

2.6 Classification of DBMS

- Several criteria can be used to classify DBMSs.

(1) By data models

 Relational model represents a database as a collection of tables.

 Object data model defines a database in terms of objects, their properties

and their operations.

 Object-relational data model

 Network model represents data as record type and also represents a limited type
of 1: N relationship called a set type.

 Hierarchical model represents data as hierarchical tree structures.

 Data models for big data systems such as key-value data model, document data
model, graph data model, column-based data model

(2) By number of users: single-user systems vs. multi-user systems

 9

(3) By number of sites: centralized vs. distributed