VIEW OF DATA IN DBMS

• View of data in DBMS narrate how the data is visualized at

each level of data abstraction.

• Data abstraction allow developers to keep complex data

structures away from the users.

• The developers achieve this by hiding the complex data

structures through levels of abstraction.

02/09/2024 08:24 1
DATA ABSTRACTION

• Data abstraction is hiding the complex data structure

in order to simplify the user’s interface of the system.

• It is done because many of the users interacting with

the database system are not that much computer
trained to understand the complex data structures of
the database system.

02/09/2024 08:24 2
 THREE-SCHEMA ARCHITECTURE

Levels of Data Abstraction

• The three-schema architecture defines the view of data at three levels

which include physical level (internal level), logical level (conceptual
level) and view level (external level)

• The main objective of this architecture is to have an effective

separation between the user interface and the physical database.

• So, the user never has to be concerned regarding the internal storage of
the database and it has a simplified interaction with the database
system.
02/09/2024 08:24 3
THREE-SCHEMA ARCHITECTURE

The three schema architecture includes the

following levels:

• Physical level (Internal level)

• Logical level (Conceptual level)
• View level (External level/ User level)
02/09/2024 08:24 4
THREE-SCHEMA ARCHITECTURE…
PHYSICAL LEVEL/ INTERNAL LEVEL

• The physical or the internal level schema describes how the data
is stored and how it can be accessed on the hardware. It includes
details like data files and partitions.

• The physical level shows the data abstraction at the lowest level.

• Only the database administrator operates at this level.

02/09/2024 08:24 5
THREE-SCHEMA ARCHITECTURE…
LOGICAL LEVEL/ CONCEPTUAL LEVEL

• It is a level above the physical level. Here, the data is stored in

the form of tables, views and relationships.

• The logical schema is independent of the physical aspects of data

storage and focuses on how the data is logically organized.

• It is the developer and database administrator who operate at the

logical or the conceptual level.
02/09/2024 08:24 6
THREE-SCHEMA ARCHITECTURE…

VIEW LEVEL/ USER LEVEL/ EXTERNAL LEVEL

• It is the highest level of data abstraction and represents the

view of the database from the perspective of a specific user or
group of users.

• It defines how different users see the data, which could vary
depending on their roles and permissions.
02/09/2024 08:24 7
02/09/2024 08:24 8
EXAMPLE
• Suppose we create a database of a college. Now, what entity
sets (tables) would be involved? Student, Lecturer,
Department, Course and so on…

• Now, the entity sets; Student, Lecturer, Department, Course

will be stored in the storage as the consecutive blocks of the
memory location.

• This is the physical or internal level and is hidden from the

programmers but the database administrator is it aware of it.
02/09/2024 08:24 9
EXAMPLE…

• At the logical level, the programmers define the entity sets

and relationship among these entity sets using a programming
language like SQL.

• So, the programmers work at the logical level and even the
database administrator also operates at this level.

• At the view level, the users have the set of applications which
they use to retrieve the data they are interested in.
02/09/2024 08:24 10
INSTANCES AND SCHEMAS

What is an instance?

• An instance refers to a specific, concrete occurrence of

the database at a particular moment in time.

• A database instance is the actual data stored in the

database at a particular moment, reflecting the current
state of the database according to its schema.
02/09/2024 08:24 11
Importance of Database Instances:
• Snapshot of Data: A database instance provides a snapshot of the data
in the database at any given time, which is crucial for operations like
backups, data analysis, and reporting.

• Data Integrity: The instance is where data integrity constraints (such

as primary keys, foreign keys, and unique constraints) are enforced.

• Query Execution: When executing queries, the database engine

operates on the current instance, retrieving and manipulating the data
as per the query instructions.

02/09/2024 08:53 12
What is schema?
• In database systems, a schema refers to the structure
or blueprint of how data is organized within a
database.

• It defines the tables, fields, relationships, views,

indexes, and other elements that make up the database.

• A schema serves as a framework that dictates how data

is stored, organized, and managed.
02/09/2024 08:24 13
Importance of a Schema:
• Data Organization: A schema provides a clear structure for how data
is organized, making it easier to manage and retrieve information.

• Data Integrity: By defining constraints and relationships, a schema

ensures the integrity and consistency of the data.

• Efficiency: A well-designed schema optimizes the performance of

queries, making data retrieval faster and more efficient.

• Scalability: Schemas help in planning for future growth, allowing the

database to be expanded and adapted as needed.
02/09/2024 09:00 14
 DATA INDEPENDENCE

• Data independence is the ability to change the

schema at one level of a database system without
altering the schema at the next higher level.

• It is a key goal in database design, promoting

flexibility and longevity of the database system.

02/09/2024 08:24 15
Importance of data independence

• Flexibility: Allows for evolving database structures without

rewriting application code.

• Cost Efficiency: Reduces the cost of maintaining and

upgrading the database system.

• Future-Proofing: Ensures the database can adapt to future

technological advancements or changes in business
requirements.
02/09/2024 08:24 16
Challenges of data independence
• Achieving true data independence can be difficult, especially in
complex systems where dependencies between layers are tightly
coupled.

• Performance trade-offs: Sometimes, achieving data

independence may require compromises in performance.

• Tool and DBMS limitations: Not all database management

systems support full data independence, especially for complex
queries and large datasets.
02/09/2024 08:24 17
Types of Data Independence
Logical Data Independence: This refers to the capacity to
change the conceptual schema (e.g., adding new fields) without
altering the external schemas or application programs.
• Compared to Physical Data independence, it is challenging to
achieve logical data independence.

Physical Data Independence: This is the ability to modify the

internal schema (e.g., changing storage structures) without
affecting the conceptual schema.
• Compared to Logical Independence, it is easy to achieve physical
data independence.

02/09/2024 08:24 18
DIFFERENCES BETWEEN LOGICAL AND PHYSICAL DATA
INDEPENDENCE

Logical Data Independence Physical Data Independence

1. Mainly concerned with - Mainly concerned with the
changing the data definition storage of the data
2. It is difficult as the - It is easy to retrieve data
retrieving of data is mainly
dependent on the logical
structure of data
3. It is difficult to achieve - It is easy to achieve physical
logical data independence data independence
02/09/2024 08:24 19
DIFFERENCES BETWEEN LOGICAL AND PHYSICAL DATA
INDEPENDENCE…
Logical Data Independence Physical Data Independence
4. There is need to make changes in - A change in the physical level
the application program if new usually does not need change at the
fields are added or deleted from application program level
the database
5. Modification at the logical level is - Modifications made at the internal
significant whenever the logical levels may or may not be needed to
structures of the database are improve the performance of the
changed structure
6. Concerned with conceptual schema - Concerned with internal schema
7. Examples: add/modify/delete a new - Examples: Change in compression
attribute techniques, storage devices, etc.
02/09/2024 08:24 20
DATA QUERY LANGUAGE (DQL)

• DQL is a subset of SQL (Structured Query Language)

used in database systems to perform queries on data.

• Unlike Data Definition Language (DDL) or Data

Manipulation Language (DML), which deal with the
structure and modification of data, DQL focuses
specifically on retrieving data.

02/09/2024 08:24 21
Example of a DQL Query:

02/09/2024 08:24 22
Example of a DQL Query…

• SELECT; specifies the columns to retrieve (first_name,

last_name, salary).

• FROM; specifies the table to query (employees).

• WHERE; filters the results to include only those employees

with a salary greater than 50,000.

• ORDER BY; sorts the results by salary in descending order.

02/09/2024 08:24 23
Key Features of DQL:

1. Primary Command:
• The primary command in DQL is the ‘SELECT’ statement,
which is used to query data from one or more tables in a
database

• It allows you to specify which columns of data you want to

retrieve and from which table

02/09/2024 08:24 24
Key Features of DQL…

02/09/2024 12:36 25
Key Features of DQL…

1. Filtering and Sorting:

• DQL allows you to filter data using the ‘WHERE’ clause,
which specifies conditions that the data must meet to be
included in the results.

• You can sort the query results using the ‘ORDER BY ’ clause
allowing for ascending or descending order based on one or
more columns

02/09/2024 12:33 26
02/09/2024 12:40 27
Key Features of DQL…
3. Aggregation:
• DQL includes aggregation functions perform calculations
on a set of values and return a single value.

• Common functions include:

• ‘COUNT()’ : Counts the number of rows.
• ‘SUM()’ : Adds up all the values in a column
• ‘AVG()’ : Calculates the average of the values in a column
• ‘MAX()’ : Finds the maximum value in a column
• ‘MIN()’ : Finds the minimum value in a column

02/09/2024 08:24 28
Example:

• The query below counts the total number of employees.

02/09/2024 09:50 29
 • The ‘GROUP BY’ clause is used is used to group rows that
have the same values in specified columns, often used with
aggregation functions like ‘COUNT’, ‘SUM’, ‘AVG’, ‘MAX’
and ‘MIN’

• Example:

02/09/2024 09:40 30
Key Features of DQL…
4. Joins:
• DQL supports various types of joins (‘INNER JOIN’, ‘LEFT
JOIN’, ‘RIGHT JOIN’, ‘FULL JOIN’) to combine rows from
one or more tables based on a related column.
• Example:

02/09/2024 12:29 31
Key Features of DQL…
5. Subqueries:
• DQL allows the use of subqueries, which are queries nested
inside other queries. Subqueries can be used in the ‘SELECT’,
‘FROM’, ‘WHERE’ OR ‘HAVING ’ clauses to perform
complex queries.
• Example

02/09/2024 08:24 32
Key Features of DQL…
6. Limitations and Paginations
• DQL supports limiting the number of rows returned by a query
using the ‘LIMIT’ clause (in database like MYSQL) or ‘TOP’
clause (in SQL Server).
•This is useful for pagination or retrieving a subset of results

02/09/2024 12:30 33
Role of DQL in Database Systems:
• Data Retrieval: DQL is fundamental for retrieving and analyzing
data stored in a database.

• Reports and Analytics: DQL is heavily used in generating

reports, performing data analysis, and extracting meaningful
insights from large datasets.

• Efficiency: Proper use of DQL allows for efficient data retrieval,

even from complex and large datasets.

02/09/2024 08:24 34