Fundamentals of Database system

Course code: CoSc2041

1
 Chapter two

Database System Architecture

2
 Contents
• Data models, Schemas, and Instances

• Over view of data models

• Architecture and Data Independence

• Database Language and Interface

• The Database System Environment

• Classification of DBMS

3
Data models, Schemas, and Instances

4
 Data Models
• A data model is a collection of concepts and rules that describe how
data is represented, stored, and related in a database system.
• Purpose:
• To provide an abstraction of the real-world data.
• Data abstraction generally refers to the suppression of details of
data organization and storage, and the highlighting of the essential
features for an improved understanding of data.
• To define a structure that DBMS can use to store and manage data.
• By structure of a database we mean the data types, relationships,
and constraints that apply to the data.
• Acts as a bridge between real-world problems and the database
design.
5
 Categories of data models
Data models are categorized into three major types:
High-Level (Conceptual) Data Models
• Provide concepts that are close to how end-users understand data.
• Focus on entities, attributes, and relationships.
• Example: Entity–Relationship (ER) model.
• Useful during database design to communicate with non-technical stakeholders.
Representational (Logical) Data Models
• Provide concepts that are easily understood by users and can be implemented
by most DBMS.
• Hide many physical details but are closer to implementation than conceptual
models.
• Examples:
• Relational Model (tables, rows, columns)
• Network Model
• Hierarchical Model
6
Categories of data models…
Low-Level (Physical) Data Models
• Describe how data is stored in the computer.
• Deal with storage paths, indexing, data compression, record formats,
etc.
• Used by database administrators and DBMS internals.
• Data Model Operations:
• These operations are used for specifying database retrievals and updates by
referring to the constructs of the data model.
• Operations on the data model may include basic model operations (e.g.
generic insert, delete, update) and user-defined operations (e.g.
compute_student_gpa, update_inventory)
7
 Schemas versus Instances
• Schemas: The logical structure or design of the entire database.
• Specified during the database design phase.
• Rarely changes after creation.
• Example: A schema might define STUDENT(Student_ID, Name, Department, Year).
• Instances: The actual content of the database at a particular point in time.
• Instances change frequently due to insertions, deletions, or updates.
• Example: The actual student records stored in the STUDENT table.
• Summary Table
• Database State:
• The actual data stored in a database at a particular moment in time.
• This includes the collection of all the data in the database.
• Also called database instance (or occurrence or snapshot).
Term Description Frequency of
Change Data
Data Model Rules/structure for organizing data Rare
Schema Logical design (blueprint) of DB Rare
Instance Actual data stored Frequent
8
Example of a Database Schema

9
Example of a database state

10
Architecture and Data Independence

11
 Architecture and Data Independence
Three-Level Database Architecture (ANSI/SPARC Model)
External Level (View Level)
• The closest to users.
• Describes different user views of the database.
• Each view may contain only part of the data relevant to a specific user.
• Uses External or high level data model.
Conceptual Level (Logical Level)
• Describes the entire logical structure of the database.
• It hides physical details like file storage details.
• Uses a conceptual or Logical data model.
• Example: Entity types, relationships, constraints.
Internal Level (Physical Level)
• Describes how data is physically stored in storage media.
• Involves file structures, indexes, and access paths.
• Uses physical data model 12
The Three-Schema Architecture

13
 The Three-Schema Architecture

• Mappings among schema levels are needed to transform requests and

data.
• Programs refer to an external schema, and are mapped 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)

14
 Data Independence
• Logical Data Independence:
• The capacity to change the conceptual schema without having to
change the external schemas and their associated application
programs.
• Adding a new column to a table without changing external views
• Physical Data Independence:
• The capacity to change the internal schema without having to change
the conceptual schema.
• Changing file organization or storage device

15
 Data Independence…

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

16
DBMS Languages

17
DBMS Languages
• Data Definition Language (DDL):
• DDL is used to define and manage the structure of the database.
It deals with the schema: the blueprint of the database.

• Data Manipulation Language (DML)

• DML is used to retrieve, insert, update, and delete data stored in
the database.
• It interacts with instances of the database rather than its structure.

18
DBMS Languages…
Main Types of DML
• Procedural DML:
• Requires the user to specify what data is needed and how to get it.
• It require detailed instructions for data access
• Example: Older programming languages embedded with SQL.
• More control but more complex.
• Non-Procedural (Declarative) DML:
• User specifies what data is required, not how to get it.
• SQL SELECT is the best example.
• Easier for users; the DBMS optimizer decides how to retrieve data.
19
DBMS Languages…
• Data Control Language (DCL):
• DCL is used to control access to data stored in the database by
granting or revoking privileges.
• Transaction Control Language (TCL)
• TCL manages transactions in a database to ensure that a group of
operations are executed in a safe, consistent, and reliable way.
• A transaction is a logical unit of work that must either be fully
completed or fully undone to maintain database integrity.

20
DBMS Languages…
DBMS Summary:

21
 User-Friendly DBMS 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
• Combinations of the above:
• For example, both menus and forms used extensively in Web
database interfaces

22
 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 user accounts, granting authorizations
• Setting system parameters
• Changing schemas or access paths

23
The Database System Environment

24
The Database System Environment
• The database system environment refers to the collection of
components (both human and machine) that work together to design,
manage, store, retrieve, and secure data within a database system.

• A database environment includes various components and utilities which

support data management, user interaction, and system performance.

• A clear understanding of this environment helps in efficient database

design, implementation, and administration.

25
 The Database System Environment
Typical DBMS Component Modules

26
Core DBMS Component Modules
• DDL Compiler
• Data Definition Language (DDL) compiler processes schema definitions and
updates the DBMS catalog with metadata.
• It updates schema changes, like creating tables or modifying columns, are
reflected in the catalog etc.
• Example − When a company expands its product line, the DBA uses the DDL
compiler to modify existing tables or add new ones to store additional product
details.
• Query Compiler and Optimizer
• The query compiler parses and validates user queries, translating them into an
internal format.
• This optimized query structure is then processed by the query optimizer.
• The optimizer ensures that the most efficient execution plan is used.
• Example: If a user runs a query to find all orders shipped last month, the
optimizer gives it uses the best index to retrieve data quickly. 27
 Core DBMS Component Modules…
• Runtime Database Processor
• The runtime database processor gives the transactions and interacts with the
system catalog.
• It is used for real-time data processing.
• This module supports data consistency and manages the main memory buffer.
• Example: When an e-commerce site updates product stock levels after a sale, the
runtime database processor updates the database and the in-memory buffers.
• Concurrency Control and Backup Systems
• Backup systems are critical for ensuring data integrity and recovery.
• Concurrency Control: Manages simultaneous database access to prevent data
conflicts.
• Backup System: Regularly creates copies of the database to safeguard against
data loss.
• Example: During peak shopping hours, there are multiple customers may place
orders simultaneously. Concurrency control helps to maintain stock levels are updated
accurately without overlaps or errors.
28
Database System Utilities
• DBMS utility programs help DBAs manage and maintain databases efficiently.
Here are some vital utilities:
• Data Loading Utility: The loading utility transfers the data from external files
into the database.
• It is often converting formats in the process.
• Backup and Restore Utilities: The backup utility is used for a complete copy
of the database is available in case of failure, while the restore utility reverts to
the backup to recover lost data.
• Performance Monitoring Tools: These tools analyze the database usage
patterns and provide statistics.
• The DBAs use this data to make decisions about indexing.
• The file reorganization, and system upgrades.

29
DBMS Environment – IDEs and Tools
• Another important part of the system is the development tools that
streamline the creation of database applications. Examples include:
• CASE Tools: CASE (Computer-Aided Software Engineering) tools
are used for database design by automating parts of the process.
• This is helping designers maintain consistency.
• For example, a tech firm uses CASE tools to ensure their database schema
matches the evolving needs of their SaaS product.
• Integrated Development Environments (IDEs): Development
environments like PowerBuilder or JBuilder simplify GUI design.
• Also simplifies coding, and testing of database applications.
• A developer using PowerBuilder may create a front-end interface for a medical
records system that interacts seamlessly with a backend DBMS.
30
DBMS Environment – Communication and
Connectivity
• DBMSs are designed to connect with remote users and integrate with other
systems through network interfaces and middleware.
• This is especially critical for distributed databases:
• Network Software: Allows remote access to the DBMS.
• Middleware: Acts as a bridge, facilitating communication between the
client applications and the DBMS.
• For example, a distributed retail chain may have a central database that stores
product data. Each outlet accesses it through network connections.

31
 Centralized and Client-Server Architectures
• Centralized DBMS:
• A centralized system operates through a central node.
• In a centralized DBMS architecture, all database operations, user interfaces, and
applications are managed by a single central computer typically a mainframe or a powerful
server.
• This type of architecture was common in the early days of computing, when most
processing power resided in a central location.
• Users would access the mainframe through terminals connected via a network.
• These terminals had minimal processing capabilities and served primarily as input/output
devices.
• Example. Consider a university that uses a centralized DBMS to manage its student
database.
• All student-related queries such as checking grades or registering for classes are
processed on a central mainframe.
• Terminals located across the campus send requests to this central server, which
processes the queries, retrieves the required data, and sends the results back to the
terminals for display.
32
A physical centralized architecture.

33
Client-Server DBMS Architecture
• The client-server system is evolvement of centralized system.
• This architecture divides the workload between clients (user-facing systems)
and servers (back-end systems).
• The clients handle the user interface and local processing.
• It is the servers manage data storage, complex processing, and business logic.
Basic Structure of Client-Server Architecture
• In its simplest form, the client-server model has the following objects:
• Clients: Machines or software applications where the users interact. These
handle user inputs and present results.
• Servers: Systems that store the database and execute data processing tasks.

34
 Two-Tier Client-Server Architecture
• In a two-tier client-server architecture,
• the client handles the user interface and application logic.
• The server focuses on data storage and retrieval.
• The connection between the client and the server allows clients to submit requests (queries).
• The queries uses the server processes and returns as results.
• For example, a banking application where tellers use client-side software to check account balances. The
client software connects to a centralized database server, retrieves data, and displays it on the teller's screen.

35
 Three-Tier Client-Server Architecture
• The speciality of three-tier architecture is that, it has an additional layer between the client
and the server.
• This is known as application server.
• This intermediate layer helps manage business logic, application rules, and data processing
more effectively.

Following are the components of three-tier client-server DBMS architecture −

•Presentation Layer (Client): Displays data and collects user input.
•Application Layer (Middle Tier): Processes user requests and interacts with the database.
•Data Layer (Server): Handles database storage and management. 36
 Three Tier Client-Server Architecture
• Common for Web applications
• Intermediate Layer called Application Server or Web Server:
• Stores the web connectivity software and the business logic part of the
application used to access the corresponding data from the database server
• Acts like a conduit for sending partially processed data between the
database server and the client.
Advantages of using three-tier client-server DBMS architecture:
• Better Load Management: The middle tier processes requests before
sending them to the database server, reducing the server's direct load.
• Enhanced Security: The application server acts as a gatekeeper, validating
user requests and providing controlled database access.
37
 Beyond Three Tiers: n-Tier DBMS Architectures
• Three-tier systems are common, some applications extend it to n-tier
architectures.
• Such architectures can include additional processing layers for more
specialized tasks such as separate layers for authentication, data
aggregation, or specific application services.
Example: Large enterprise applications, like those used in CRM or ERP,
often utilize n-tier structures to balance tasks across various layers.
Following are the benefits of using n-tier systems:
• Modularity: Breaking down the application into multiple layers allows
teams to develop and manage parts independently.
• Resilience: Failures in one layer can be managed without affecting the
entire system, which greatly enhances reliability.
38
Classification of DBMSs

39
 Classification of DBMSs
Based on the data model used:
• Relational DBMS (RDBMS): use a table-based format for data storage.
• The data is stored in rows and columns.
• MySQL is one of the most popular RDBMSs
• Object-Oriented DBMS (OODBMS): store data as objects.
• These DBMSs are great for applications that involve complex data structures.
• Hierarchical DBMS: organizes its data in a tree-like structure.
• Each parent record has one or more child records, resembling a family tree.
• This model works well for situations where data relationships are naturally
hierarchical.
• IBM's IMS (Information Management System) is a classic hierarchical DBMS.
• It is used in industries like banking to maintain customer records. 40
 Classification of DBMSs…
• Network DBMS: allows multiple parent-child relationships, forming a web-like data model.
• This flexibility supports more complex relationships.
• IDMS (Integrated Database Management System) is a network DBMS.
• It is often used for applications requiring many-to-many relationships, such as supply
chain management.
• NoSQL DBMS: designed to handle large volumes of unstructured or semi-structured data.
• They are more flexible than traditional relational databases and cater to the needs of modern
applications, where variable data schemas are used.
A NoSQL DBMS can be further classified into the following categories:
• Document-based: Stores data in documents, often in JSON format (e.g., MongoDB).
• Key-Value Stores: Simple databases where data is stored as a key and associated value
(e.g., Redis).
• Column-family Stores: Store data in columns rather than rows, suitable for analytical
processing (e.g., Cassandra).
• Graph Databases: Designed to handle data with complex relationships (e.g., Neo4j).41
 Classification of DBMSs…
Classification by User Support
We can also classify a database based on the number of users it can support simultaneously:
• Single-User Systems: the systems support one user at a time.
• These are often used for personal or small-scale applications.
• They typically run on individual PCs or workstations.
• For example, Microsoft Access is a single-user DBMS suitable for small business data
management tasks.
• Multi-User Systems: allow multiple users to access and work with the database
simultaneously.
• These are needed for collaborative environments and enterprise-level applications.
• For example, PostgreSQL is a powerful multi-user DBMS, known for its advanced
features like concurrency control and high availability.
• It is used by large businesses for data warehousing and complex transactions.

42
 Classification of DBMSs…
Classification by Database Distribution
• We can classify a database based on the way it stores the data:
• Centralized DBMS: the data is stored in a single location.
• It is such as one server or data center.
• All user access and data processing are handled centrally.
• Like a university’s library system that stores all the records in one main server and allows students
and faculty to access them remotely is a form of centralized DBMS.
• Distributed DBMS (DDBMS)
• A distributed DBMS is used to store data across multiple locations, connected through a network.
• This setup gives data to be processed locally while still being part of a larger database.
• There are quite a few such solutions like Amazon DynamoDB.
• They are distributed NoSQL database service, designed to handle large amounts of data across
different regions and data centers.
• It is often used for high-availability applications like online gaming or financial transactions.
• Distributed DBMS can be further classified into two different categories −
• Homogeneous DDBMS: All sites use the same DBMS software.
• Heterogeneous DDBMS: Different sites may use varied DBMS software, but a middleware
facilitates integration.
43
 Classification of DBMSs…
Classification by Cost and Licensing
• One can also consider the financial aspects of a DBMS, like its cost and licensing, to put it
into a category
• Open-Source DBMS: free to use and can be modified as needed.
• Such databases are ideal for small and medium businesses or development projects with
budget constraints.
• For example, MySQL and PostgreSQL are popular open-source DBMSs.
• Commercial DBMS: come with licensing fees and often provide robust customer support,
additional features, and service-level agreements.
• For example, Oracle Database is a commercial RDBMS with extensive capabilities like
real-time data analytics and high transaction processing speeds.
• It is a go-to database for financial institutions and large corporations that need reliable,
scalable database solutions.

44
 Classification of DBMSs…
Classification by Purpose

• Some DBMSs are classified based on their specific uses or performance requirements:

• General-Purpose DBMS: can handle various types of data and support multiple applications
and use cases.
• For example, SQL Server is a general-purpose DBMS used for everything from small
business applications to large-scale enterprise systems.

• Special-Purpose DBMS
• Special-purpose DBMSs are built to meet specific requirements.
• For instance, some are designed for handling streaming data or specialized analytics.
• Real-time processing systems, like those used in telecommunications for call records, often
rely on specialized DBMSs optimized for rapid data ingestion and retrieval.
45
End of chapter two

Any questions?

46