The major purpose of DBMS is to provide users with an abstract view of the data. The system hides certain details of how the data is stored and maintained
The main objective of three level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once
Levels
Physical level/Internal level
The lowest level of abstraction describes how the data are stored
Low-level data structures used
It has Physical schema which describes physical storage structure of DB
Talks about Storage allocation (N-ary tree etc), Data compression & encryption etc
Goal => We must define algorithms that allow efficient access to data
Logical level/Conceptual level
The conceptual schema describes the design of a database at the conceptual level, describes what data are stored in DB, and what relationships exist among those data
User at logical level does not need to be aware about physical-level structures
DBA, who must decide what information to keep in the DB use the logical level of abstraction
Goal => Ease to use
View level/External level
Highest level of abstraction aims to simplify users’ interaction with the system by providing different view to different end-user
Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group
At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different view of the database
At views also provide a security mechanism to prevent users from accessing certain parts of DB
Instances
The collection of information stored in the DB at a particular moment is called an instance of DB
Schemas
The overall design of the DB is called the DB schema
Schema is structural description of data. Schema doesn’t change frequently. Data may change frequently
DB schema corresponds to the variable declarations (along with type) in a program
We have 3 types of Schemas
Physical
Logical
View schemas called subschemas
Logical schema is most important in terms of its effect on application programs, as programmers construct apps by using logical schema
Physical data independence, physical schema change should not affect logical schema/application programs
Data Models
Provides a way to describe the design of a DB at logical level
Underlying the structure of the DB is the Data Model; a collection of conceptual tools for describing data, data relationships, data semantics & consistency constraints
Examples
ER
Relational
Object Oriented
Object Relational
Hierarchical
Network
Database Languages
Practically, both language features are present in a single DB language
Example => SQL language
DDL
Data definition language (DDL) to specify the database schema
Specify consistency constraints, which must be checked, every time DB is updated
DML
Data manipulation language (DML) to express database queries and updates
Data manipulation involves
Retrieval of information stored in DB
Insertion of new information into DB
Deletion of information from the DB
Updating existing information stored in DB
Query language, a part of DML to specify statement requesting the retrieval of information
How is Database accessed from Application programs?
Apps (written in host languages, C/C++, Java) interacts with DB
API is provided to send DML/DDL statements to DB and retrieve the results
C/C++ => Open Database Connectivity (ODBC)
Java => Java Database Connectivity (JDBC)
Database Administrator (DBA)
A person who has central control of both the data and the programs that access those data
Functions of DBA
Schema Definition
Storage structure and access methods
Schema and physical organization modifications
Authorization control
Routine maintenance
Periodic backups
Security patches
Any upgrades
DBMS Application Architectures
Client machines, on which remote DB users work
Server machines on which DB system runs
Tier-1 Architecture
The client, server & DB all present on the same machine
T2 Architecture
App is partitioned into 2-components
Client machine, which invokes DB system functionality at server end through query language statements
API standards like ODBC & JDBC are used to interact between client and server
T3 Architecture
App is partitioned into 3 logical components
Client machine is just a frontend and doesn’t contain any direct DB calls
Client machine communicates with App server, and App server communicated with DB system to access data
Business logic, what action to take at that condition is in App server itself
T3 architecture are best for WWW Applications
Advantages
Scalability due to distributed application servers
Data integrity => App server acts as a middle layer between client and DB, which minimize the chances of data corruption
Security => Client can’t directly access DB, hence it is more secure
