Introduction to

Databases
Databases are powerful tools that help organizations to manage
and store vast amounts of data. They provide a structured way to
organize, access, and manipulate information, making it easier to
make informed decisions and extract valuable insights.

Dr. Nadeem Y. K. 1
 Database M a n a g e m e n t S y s t e m s ( D B M S )
A Database Management System (DBMS) is the software that
enables the creation, maintenance, and use of databases. It
acts as an interface between the database and the applications
or users that interact with it. DBMS software provides
features such as data storage, retrieval, security, and backup,
making it easier to manage large amounts of data efficiently.
DBMS systems can be classified into different types based on
their data models, such as relational, object-oriented, and
NoSQL databases. These systems offer various capabilities,
including SQL querying, transaction management, and
scalability, allowing organizations to select the most
appropriate DBMS for their specific needs.
Selecting the right DBMS is crucial for an organization's data
management strategy, as it can impact factors like performance,
data integrity, and overall system reliability. DBMS software is
a fundamental component of modern information systems,
enabling businesses to store, analyze, and retrieve data in a
secure and efficient manner.
Relational Database C o n c e p t s

Database S c h e m a K e y Relationships Q u e r y i n g D ata

The relational database model Relational databases use primary Relational databases use SQL, a
represents data in tables, where and foreign keys to establish powerful language for querying and
each table has a schema that relationships between tables. manipulating data. SQL statements
defines the structure of the data. Primary keys uniquely identify allow users to select, insert, update,
The schema specifies the names each row, while foreign keys in one and delete data, as well as perform
and data types of the columns, as table reference the primary key in complex operations like joining
well as relationships between another table, allowing data to be multiple tables and applying filters and
tables. linked across the database. aggregations.
23
T y p i c a l D B M S Functionality
▪ Define a particular database in terms of its data types, structures, and constraints
▪ Construct or Load the initial database contents on a secondary storage medium
▪ Manipulating the database:
• Retrieval: Querying, generating reports
• Modification: Insertions, deletions and updates to its content
• Accessing the database through Web applications
▪ Processing and Sharing by a set of concurrent users and application programs – yet, keeping
all data valid and consistent
▪ DBMS may additionally provide:
⚫ Protection or Security measures to prevent unauthorized access
⚫ “Active” processing to take internal actions on data
⚫ Presentation and Visualization of data
⚫ Maintenance of the database and associated programs over the lifetime of the database
application
⚫ Called database, software, and system maintenance
24
Typical D B M S C o m p o n e n t Modules
Data m o d e l s , schemas & instances

A data model is a conceptual representation of how data is organized and structured within a database. It
defines the entities, attributes, and relationships that make up the database. The database schema is the
blueprint that describes the structure of the database, including the tables, columns, and constraints.
Database instances refer to the actual data stored in the database at a particular point in time, which can
vary as new data is added or existing data is modified.

Understanding data models, schemas, and instances is crucial for designing and managing databases
effectively. Robust data modeling ensures the database can accurately represent and store the necessary
information, while the schema provides a clear roadmap for how that data is organized. Database
instances then reflect the real-world data that is being captured and utilized by the system.
26

D ata M o d e l s

▪ Network Model
▪ Hierarchical Model
▪ Relational Model
▪ Object-oriented Data Models
▪ Object-Relational Models
28
Data M o d e l s

▪ Hierarchical Data Model:

• Initially implemented in a joint effort by IBM and North American Rockwell around 1965.
Resulted in the IMS family of systems.
• IBM’s IMS product had (and still has) a very large customer base worldwide
• Hierarchical model was formalized based on the IMS system
• Other systems based on this model: System 2k (SAS inc.)
▪ Advantages:
• Simple to construct and operate
• Corresponds to a number of natural hierarchically organized domains, e.g., organization (“org”)
chart
• Language is simple:
• Uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT, etc.
▪ Disadvantages:
• Navigational and procedural nature of processing
• Database is visualized as a linear arrangement of records
• Little scope for "query optimization"
28
Data M o d e l s
▪ Hierarchical Data Model:
28
Data M o d e l s
▪ Hierarchical Data Model:
28
Data M o d e l s
▪ Hierarchical Data Model:
27
D ata M o d e l s
▪ Network Model:
• The first network DBMS was implemented by Honeywell in 1964-65 (IDS System).
• Adopted heavily due to the support by CODASYL (Conference on Data Systems Languages) (CODASYL -
DBTG report of 1971).
• Later implemented in a large variety of systems - IDMS (Cullinet - now Computer Associates), DMS 1100
(Unisys), IMAGE (H.P. (Hewlett-Packard)), VAX -DBMS (Digital Equipment Corp., next COMPAQ, now
H.P.).
Advantages:
• Network Model is able to model complex relationships and represents semantics of add/delete on the
relationships.
• Can handle most situations for modeling using record types and relationship types.
• Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set,
GET, etc.
• Programmers can do optimal navigation through the database
Disadvantages:
• Navigational and procedural nature of processing
• Database contains a complex array of pointers that thread through a set of records.
• Little scope for automated “query optimization”
27
D ata M o d e l s
▪ Network Model:
29
Data M o d e l s
▪ Relational Model:
• Proposed in 1970 by E.F. Codd (IBM), first commercial system in 1981-82.
• Now in several commercial products (e.g. DB2, ORACLE, MS SQL Server, SYBASE,
INFORMIX).
• Several free open source implementations, e.g. MySQL, PostgreSQL
• Currently most dominant for developing database applications.
• SQL relational standards: SQL-89 (SQL1), SQL-92 (SQL2), SQL-99, SQL3, …
29
Data M o d e l s
30

Data M o d e l s

▪ Object-oriented Data Models:

• Several models have been proposed for implementing in a database system.
• One set comprises models of persistent O-O Programming Languages such as C++ (e.g.,
in OBJECTSTORE or VERSANT), and Smalltalk (e.g., in GEMSTONE).
• Additionally, systems like O2, ORION (at MCC - then ITASCA), IRIS (at H.P.- used in
Open OODB).
• Object Database Standard: ODMG-93, ODMG-version 2.0, ODMG-version 3.0.
• The trend to mix object models with relational was started with Informix Universal Server.
• Relational systems incorporated concepts from object databases leading to object-relational.
• Exemplified in the versions of Oracle, DB2, and SQL Server and other DBMSs.
• Current trend by Relational DBMS vendors is to extend relational DBMSs with capability to
process XML, Text and other data types.
• The term “Object-relational” is receding in the marketplace.
30

Data M o d e l s
▪ Object-oriented Data Models:
30

Data M o d e l s
▪ Object-oriented Data Models:
Three-Schema
Architecture
The three-schema architecture is a conceptual model for database systems
that separates the logical and physical representation of data. Itprovides a
framework for achieving data independence, allowing applications to
access data without needing to know its physical storage details.
 Three-Schema Architecture
32

▪ Defines DBMS schemas at three levels:

• Internal schema at the internal level to
describe physical storage structures and
access paths (e.g indexes).
• Typically uses a physical data model.
• Conceptual schema at the conceptual
level to describe the structure and
constraints for the whole database for a
community of users.
• Uses a conceptual or an implementation
data model.
• External schemas at the external level to
describe the various user views.
• Usually uses the same data model as the
conceptual schema.
Conceptual Schema

1 High-Level View
The conceptual schema provides a high-level, abstracted view of the data,
focusing on the key entities, their relationships, and the overall data structure.

2 Technology-Independent
This schema is independent of any specific technology or implementation
details, allowing for flexibility in the actual database design.

3 Business Perspective
The conceptual schema represents the data requirements from a business
perspective, capturing the essential information needed to support the
organization's operations and decision-making.
Logical Schema

Entities
1
Defining the key objects and their attributes

Relationships
2
Specifying how the entities are connected

Constraints
3
Establishing rules and boundaries for the data

The logical schema focuses on the conceptual model of the data, independent of any specific implementation details. This layer
defines the entities, their attributes, and the relationships between them, as well as any constraints that must be enforced. The logical
schema serves as a bridge between the high-level conceptual design and the low-level physical implementation.
Physical Schema
Data Representation
The physical schema defines how data is physically stored and accessed on
storage devices, such as hard drives or solid-state drives.

Storage Structures
Itspecifies the physical data structures, such as tables, indexes, and file
formats, used to store the data.

Access Methods
The physical schema also determines the access methods, like sequential
scanning or indexed lookups, used to retrieve data efficiently.
Data Independence

Physical
1 Independent from logical

Logical
2
Independent from conceptual

Conceptual
3
Captures the semantic meaning

The three-schema architecture provides data independence, which means that changes to one schema do not require changes to the
other schemas. This allows the database to evolve over time without disrupting existing applications that access the data. Data
independence is a key benefit of this architecture, enabling more flexibility and adaptability in data management.
Data Independence
Data Independence
Benefits of Three-Schema Architecture

Abstraction Levels Data Independence

The three-schema architecture provides different levels of By separating the conceptual, logical, and physical
abstraction, allowing users to interact with the data at the schemas, changes to one level do not impact the others,
most appropriate level for their needs. promoting data independence and flexibility.

Ease of Maintenance Improved Security

The modular design of the three-schema architecture The layered approach allows for more granular access
makes it easier to maintain and update the database controls, ensuring sensitive data is only accessible to
system over time, reducing the burden on developers. authorized users or applications.
Mapping Between Schemas

Conceptual to Logical Logical to Physical Bidirectional Mapping

The conceptual schema is mapped to The logical schema is then mapped to Mapping between the schemas is a
the logical schema, translating high- the physical schema,which defines bidirectional process, allowing for
level data requirements into a the actual storage structures and data independence and the ability to
detailed data model compatible with access methods used to implement make changes at one level without
the chosen database management the database. affecting the others.
system.
Abstraction Levels
The three-schema architecture defines three key abstraction levels for
database design: conceptual, logical, and physical. Each level provides a
different perspective and degree of detail, enabling modular development
and data independence.

The conceptual schema represents the overall logical structure of the

database, focusing on high-level entities and relationships. The logical
schema defines the logical data model, while the physical schema specifies
the low-level storage implementation.
Challenges in Implementation
Implementing the three-schema architecture can present several challenges. Establishing clear mappings between the conceptual,
logical, and physical schemas requires extensive data modeling and integration efforts. Maintaining data independence across the
layers while ensuring efficient data access can be complex, particularly in large-scale, heterogeneous [Link]
andprivacy across the abstraction levels is also a critical concern that must be addressed.

Additionally, managing schema evolution and seamless updates to the different schema levels can be a significant challenge, as
changes in one layer may impact the others. Effective version control and change management processes are essential to maintain the
integrity of the three-schema architecture over time.
Centralized and
Client/Server
Architecture of DBMS
42

Centralized Architecture
▪ Centralized DBMS:
• Combines everything into single system including- DBMS
software, hardware, application programs, and user
interface processing software.
• User can still connect through a remote terminal –
however, all processing is done at centralized site.
• All data and processing are managed and controlled by a
central server
• This server hosts the database, handles all data
management tasks, and provides access to the data for the
connected clients
• Clients interact with the server through a standardized
interface, such as a database management system or an
application programming interface (API).
Clients, which can be individual users or applications, connect to
the central server to access and manipulate the data. The central
server is responsible for managing the database, processing
queries, and ensuring data integrity and security.
Advantages of Centralized
DBMS
A centralized DBMS offers several key advantages. It provides a single,
unified view of the entire database, making data management and access
more efficient. With all data stored in a central location, data consistency
and integrity are easier to maintain. Centralized backups and disaster
recovery processes are also simpler to implement, enhancing data security
and reliability.

Another major advantage is improved performance. The centralized

architecture allows for better optimization of queries and more efficient
utilization of computing resources. This results in faster response times and
improved user experience. Additionally, centralized administration and
control over the DBMS simplifies management tasks such as user access
control, schema modifications, and system upgrades.
Disadvantages of Centralized DBMS
One of the primary drawbacks of a centralized DBMS is the potential for a single point of
failure. If the central server or mainframe hosting the database goes down, the entire
system becomes unavailable, disrupting access to critical data and applications. This can
lead to significant downtime and lost productivity, impacting an organization's
operations and bottom line.

Another disadvantage of the centralized approach is the limited scalability. As the

number of usersand data volumes grow, the central server may become overwhelmed,
leading to performance issues and slower response times. Expanding the capacity of a
centralized DBMScan be a complex and costly endeavor, requiring extensivehardware
upgrades and software modifications.

Additionally, a centralized DBMS can present challenges in terms of data security and
accessibility. With all the data stored in a single location, the risk of unauthorized access
or data breaches [Link] robust security measures and ensuring data
backup and disaster recovery strategies become critical to mitigate these risks.
 Client/Server Architecture

In a client/server DBMS architecture, the database management system is divided into two main components: the client
and the server. The client is the user interface that interacts with the database, sending requests and receiving
responses. The server is the central component that manages the database, processing queries, storing and retrieving
data, and enforcing security and integrity rules.

This distributed architecture allows for more scalability, flexibility, and efficiency compared to a centralized DBMS. Clients
can be located remotely and access the database resources through a network connection, while the server can be
optimized to handle the heavy computational and storage tasks involved in database management.
Components of Client/Server DBMS
Aclient/serverDBMSis composed of several key componentsthat work togetherto provide a distributed database solution. Themain componentsinclude:

ClientApplications:Theseare the programs or interfaces usedby end-usersto accessand interact with the database. Theycan be desktop applications,
webbrowsers,or mobile apps that sendrequeststo theserver.

Server:Theserver is thecentral componentthat managesthe database, processesclient requests, and handlesdata storage,retrieval, and
manipulation. Ittypically runsa DBMSsoftwarelike Oracle,MySQL,or PostgreSQL.

Network:Theclient and servercommunicateover a computer network,suchas a local areanetwork(LAN)or the internet,using standardizedprotocols
like TCP/IP.
48

Basic 2-tier Client-Server Architectures

▪ Client and server must install appropriate client module and server
module software for ODBC or JDBC
▪ A client program may connect to several DBMSs, sometimes called the
data sources.
▪ In general, data sources can be files or other non-DBMS software that
manages data.
▪ Clients can access the specialized servers as needed
▪ Specialized Servers with Specialized functions
• Print server
• File server
• DBMS server
• Web server
• Email server
43
49
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.
▪ Three-tier Architecture Can Enhance Security:
• Database server only accessible via middle tier
• Clients cannot directly access database server
• Clients contain user interfaces and Web browsers
• The client is typically a PC or a mobile device
connected to the Web
Advantages of Client/Server DBMS
The client/server architecture of database management systems (DBMS) offers several key advantages over the traditional centralized
approach. One of the primary benefits is improved scalability. By distributing processing and storage across multiple client and
server components, the system can more easily handle growing data volumes and user demands without performance degradation.

Increasedreliability is another advantage, as the failure of a single client or server component does not bring down the entire system.
The modular design also allows for easier maintenanceandupgrades, as changes can be made to individual components without
disrupting the overall system.

Finally, the client/server DBMS model enables better datasecurityandaccess control. Data can be partitioned and managed more
granularly, with specific permissions and restrictions enforced at the client-server level. This helps organizations maintain tighter
control over sensitive information and comply with regulatory requirements.
Disadvantages of Client/Server DBMS
One key disadvantageof the client-server architecture for database managementsystems(DBMS) is the increased complexity in setup and maintenance.
The client and server components must be properly configured and integrated, which can be challenging,especially in larger, distributed environments.
Additionally, the reliance on network connectivity means that any issues with the network can disrupt access to the database, leading to potential
downtime and reduced productivity.

Another drawback is the potential for higher overall costs. The hardware and software requirementsfor both the client and server components can be
more extensive than a centralized DBMS, leading to higher initial investmentand ongoing maintenance [Link], the need for specialized IT
supportto manage the distributed system can add to the overall cost of ownership.

Security and data privacy can also be a concern in client-server DBMS architectures. The distributed nature of the systemincreasesthe potential attack
surface,making it more vulnerable to security [Link] securitymeasures, such as firewalls,access controls, and data encryption, must be
implemented to mitigate these risks.
Comparison of Centralized and
Client/Server DBMS

1 Scalability 2 Flexibility
Centralized DBMS can face scalability Client-server DBMS offers greater
challenges as the number of users flexibility, as clients can access the
and data volume grows, while client- database from various locations and
server DBMS can scale more easily by use different applications, while
adding more client machines and centralized DBMS are more rigid and
spreading the workload. tied to a single physical location.

3 Availability
Client-server DBMS can provide higher availability, as the failure of a single client does
not affect the overall system, unlike a centralized DBMS where a single point of failure
can disrupt the entire system.
Database Languages
and Interfaces

Unlock the power of data with a deep dive into the world of database
languages and interfaces. Discover the tools and techniques that allow you
to efficiently manage, query, and interact with complex data systems.
Explore the nuances of SQL, NoSQL, and emerging data paradigms,
empowering you to make informed decisions and drive innovation.
54

DBMS Languages

▪ Data Definition Language (DDL)

▪ Data Manipulation Language (DML)
• High-Level or Non-procedural Languages: These include the relational language
SQL
• May be used in a standalone way or may be embedded in a programming language
• Low Level or Procedural Languages:
• These must be embedded in a programming language
Data Definition Language (DDL)

The Data Definition Language (DDL) is a set of SQL commands used to

create, modify, and delete database structures, such as tables, indexes, and
views. DDL statements are essential for defining the schema of a database,
which is the overall structure and organization of the data. These statements
allow developers to specify the data types, constraints, and relationships
between different database objects, ensuring the integrity and consistency
of the data. DDL commands are typically used during the initial setup and
design phase of a database project, but they can also be used to make
changes to the database structure over time as requirements evolve.
Data Manipulation Language
(DML)
The Data Manipulation Language (DML) is a subset of SQL used to interact
with and manage the data stored within a database. It provides the
fundamental commands for inserting, updating, deleting, and querying
data. DMLoperationsarethe coreofanydatabaseapplication, allowing
developers to create, modify, and retrieve information as needed to power
their applications.
The primary DML statements are SELECTto retrieve data, INSERTto add
new data, UPDATE to modify existing data, and DELETE to remove data.
These commands give developers the ability to dynamically manipulate the
contents of a database, making DML an essential tool for working with
relational and NoSQL databases alike.
57

Types of DML

▪ High Level or Non-procedural Language:

• For example, the SQL relational language
• Are “set”-oriented and specify what data to retrieve rather than how to retrieve it.
• Also called declarative languages.
▪ Low Level or Procedural Language:
• Retrieve data one record-at-a-time;
• Constructs such as looping are needed to retrieve multiple records, along with
positioning pointers.
Structured Query Language (SQL)
SQL is a programming language used to manage and manipulate relational databases. It allows users to create, modify, and query
data stored in tables, as well as control access to the database. SQL is a powerful tool that enables developers to build robust,
scalable, and efficient data-driven applications. With its intuitive syntax and extensive functionality, SQL has become the industry
standard for database management and data processing.

SQL queries can be used to perform a wide range of tasks, from simple data retrieval to complex data analysis and manipulation.
Some of the key features of SQL include SELECTfor retrieving data, INSERTfor adding new data, UPDATEfor modifying existing data,
and DELETE for removing data. SQL also supports advanced features like joins, subqueries, and aggregate functions for more
sophisticated data processing and analysis.

NoSQL Databases
NoSQL databases are a class of database management systems that differ from traditional relational databases in their data model
and schema design. Unlike SQL databases that rely on tables with predefined schemas, NoSQL databases offer flexible, schema-less
architectures that are well-suited for handling large, unstructured datasets.
These databases come in various forms, such as key-valuestores, document-oriented databases,column-familystores, and
graph databases. They are designed to scale horizontally, provide high availability, and handle real-time, high-throughput
applications with ease.
59
DBMS Interfaces
▪ Stand-alone query language interfaces
• Example: Entering SQL queries at the DBMS interactive SQL interface (e.g. SQL*Plus in ORACLE)
▪ Programmer interfaces for embedding DML in programming languages
▪ User-friendly interfaces
• Menu-based, forms-based, graphics-based, etc.
▪ Mobile Interfaces: interfaces allowing users to perform transactions using mobile apps
▪ Programmer interfaces for embedding DML in a programming languages:
• Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for Java)
• Procedure Call Approach: e.g. JDBC for Java, ODBC (Open Databse Connectivity) for other
programming languages as API’s (application programming interfaces)
• Database Programming Language Approach: e.g. ORACLE has PL/SQL, a programming language
based on SQL; language incorporates SQL and its data types as integral components
• Scripting Languages: PHP (client-side scripting) and Python (server-side scripting) are used to write
database programs.
Object-Relational Mapping (ORM)
ORM is a programming technique that allows developers to interact with a database using an object-oriented approach. Rather than
writing raw SQL queries, ORMs provide a set of functions and methods that abstract away the complexity of database interactions.
This enables developers to work with data as objects in their code, rather than having to manage low-level database operations.

ORMs provide several benefits, such as improved developer productivity, reduced risk of SQL injection attacks, and simplified
database migration. Popular ORM frameworks include Django ORM, SQLAlchemy, and Laravel Eloquent. These tools handle tasks like
object-relational mapping, query generation, and database connection management, making it easier to build data-driven
applications.
Application Programming
Interfaces (APIs)
APIs are the gatekeepers to database functionality, allowing applications to
securely access and manipulate data. They define the communication
protocols, data formats, and authentication methods for interacting with a
database. APIs abstract away the complex inner workings of a database,
enabling developers to focus on building applications that leverage the
power of the data.

Well-designed APIs provide a consistent, user-friendly interface for CRUD

(Create, Read, Update, Delete)operations, as well as advanced querying
capabilities. They typically support common data exchange formats like
JSON and XML, and incorporate authentication and authorization
mechanisms to ensure data security and privacy.
Database Connectivity Drivers
Database connectivity drivers are software components that enable
applications to interact with database management systems (DBMS). These
drivers serve as the interface between the application and the database,
translating the application's requests into a format the DBMS can
understand and vice versa.

Some of the most common database connectivity drivers include ODBC

(Open Database Connectivity), JDBC (Java Database Connectivity), and
[Link] (Active Data Objects for .NET). These drivers provide a
standardized way for applications to access data stored in various database
systems, regardless of the underlying database technology.
Thank You