DATABASE MANAGEMENT

SYSTEMS
Module 1
What is a Database?

Prepared by Amrutha N
 DATABASE

• A database is a collection of related data.

• Data mean known facts that can be recorded and that have implicit meaning.

Prepared by Amrutha N
For example, consider the names, telephone numbers, and addresses of the people you
know.

Nowadays, this data is typically stored in mobile phones, which have their own simple
database software.

This data can also be recorded in an indexed address book or stored on a hard drive, using
a personal computer and software such as Microsoft Excel. This collection of related data
with an implicit meaning is a database.

Prepared by Amrutha N
 DATABASE

When NOT to Use a DBMS

• Small and simple data requirements: If the application deals with a very small
amount of data and does not require complex operations, a DBMS may be excessive.
Simple file systems or spreadsheets can handle such cases efficiently.
• No need for data sharing or multi-user access: When only a single user accesses the
data and there is no requirement for concurrent usage, the advanced features of a
DBMS (like concurrency control) are not needed.
• High performance with minimal overhead required: DBMS introduces overhead due
to features like security, recovery, and transaction management. In real-time systems
(e.g., embedded systems), where speed is critical, using a DBMS may slow down
performance.
Prepared by Amrutha N
 DATABASE

When NOT to Use a DBMS

• Simple data processing without relationships: If the data does not have complex
relationships and does not require relational operations (joins, normalization), flat
files may be sufficient.

• Temporary or short-lived data: For applications where data is temporary and does not
need to be stored long-term, using a DBMS is not justified.

Prepared by Amrutha N
 DATABASE

• A database can be of any size and complexity.

• For example, the list of names and addresses referred to earlier may consist of only a
few hundred records, each with a simple structure.

• An example of a large commercial database is [Link]. It contains data for over

60 million active users, and millions of books, CDs, videos, DVDs, games,
electronics, appliances, and other items.

Prepared by Amrutha N
 DATABASE

• A Database Management System (DBMS) is a computerized system that enables users

to create and maintain a database.

• The DBMS is a general-purpose software system that facilitates the processes of

defining, constructing, manipulating, and sharing databases among various users and
applications.

Prepared by Amrutha N
• An application program accesses the database by
sending queries or requests for data to the DBMS.

• A query typically causes some data to be

retrieved; a transaction may cause some data to be
read and some data to be written into the database.

• Database and DBMS software together a database

system.

Prepared by Amrutha N
CHARACTERISTICS OF DATABASE APPROACH

Prepared by Amrutha N
 CHARACTERISTICS OF DATABASE APPROACH

The main characteristics of the database approach versus the file-processing approach
are the following:

• Self-describing nature of a database system

• Insulation between programs and data, and data abstraction

• Support of multiple views of the data

• Sharing of data and multiuser transaction processing

Prepared by Amrutha N
Self-describing nature of a database system

• A database system not only stores the actual data, but also stores information
about the data.

• This information about the data is called metadata.

EmployeeID Name Department Salary

45 Ram HR 50000
Along with the data, the DBMS also stores:

• Table name → EMPLOYEE

• EmployeeID → Integer (Primary Key)

• Name → Varchar(50)

• Department → Varchar(30)

• Salary → Decimal Prepared by Amrutha N

Insulation between programs and data, and data abstraction

• Insulation means separating application programs from physical data storage details.

• In traditional file systems, if file structure changes the program must be modified.

• In DBMS changes in data structure do not affect application programs.

• This is achieved using data independence.

Prepared by Amrutha N
Insulation between programs and data, and data abstraction

• Suppose we have a table containing fields: RollNo, Name, Department and Marks.

• If we add a new column: Email

• In DBMS old programs that use RollNo, Name, Department and Marks will still work.

• No need to rewrite the program. The DBMS handles the internal changes.

Prepared by Amrutha N
Insulation between programs and data, and data abstraction

• Data abstraction means hiding complex details and showing only necessary information
to users.

Prepared by Amrutha N
Support of Multiple Views of the Data

• Database Management System allows different users to see different parts of the same
database according to their needs.

• In simple words, each user can have their own view of the data without affecting others.

Prepared by Amrutha N
Support of Multiple Views of the Data

Rollno Name Department Marks Attendance Feespaid

101 Amal ISE 85 87 Yes

102 Rahul CSE 78 99 No

Teacher View

• Teacher only required the RollNo, Name, Marks, Attendance

• Teacher does not need fee details.

Student View

• Student only required the Marks, Attendance and Feespaid.

Account session View

• Student only required the Name, Department and Feespaid.

Prepared by Amrutha N
Sharing of Data and Multiuser Transaction Processing

• A multiuser DBMS, as its name implies, must allow multiple users to access the
database at the same time.

• This is essential if data for multiple applications is to be integrated and maintained in a

single database.

• The DBMS must include concurrency control software to ensure that several users
trying to update the same data.

• The DBMS must enforce several transaction properties.

Prepared by Amrutha N
Sharing of Data and Multiuser Transaction Processing

• In a Database Management System, every transaction must follow the ACID properties to
ensure data accuracy and reliability.

• Atomicity: A transaction must complete fully or not execute at all. If any part of the
transaction fails, the entire transaction is rolled back.

• Consistency: Ensures the database transforms from one valid state to another,
maintaining all predefined rules, constraints, and triggers.

• Isolation: When multiple users perform transactions simultaneously,

each transaction should work independently. One transaction should not see the
incomplete result of another.

• Durability: Once a transaction is committed, its changes are permanently saved in the
system, even in the event of a power loss or system crash. Prepared by Amrutha N
ACTORS ON THE SCENE

Prepared by Amrutha N
 ACTORS ON THE SCENE

• In large organizations, many people are involved in the design, use, and maintenance of a
large database with hundreds or thousands of users.

Database Administrators

• In a database environment, the main resource is the database, while the secondary
resources include the DBMS and related software.

• Managing these resources is the responsibility of the Database Administrator (DBA).

Prepared by Amrutha N
 ACTORS ON THE SCENE

Database Administrators

The DBA’s duties include:

• Granting and controlling user access to the database

• Coordinating and monitoring database usage

• Procuring required hardware and software resources

• Ensuring system security and preventing unauthorized access

• Maintaining good system performance and resolving issues such as slow response
time

Prepared by Amrutha N
 ACTORS ON THE SCENE

Database Designers

• Database designers are responsible for determining what data should be stored in the
database and selecting suitable structures to organize and store that data efficiently.

• These activities are generally completed before the database is implemented and filled
with actual data.

• To create an effective design, database designers must communicate with all potential
users of the database to understand their data and processing requirements.

• Based on these discussions, they develop designs that satisfy user needs.

Prepared by Amrutha N
 ACTORS ON THE SCENE

End Users

• End users are people who use the database for their work.

• They are classified into:

• Casual Users: Access the database occasionally and use query tools.

• Naive (Parametric) Users: Use predefined applications (e.g., bank clerks,

reservation clerks).

• Sophisticated Users: Use advanced tools and write complex queries.

• Standalone Users – Maintain personal databases using ready-made software.

Prepared by Amrutha N
ADVANTAGES OF USING THE DBMS APPROACH

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Controlling Redundancy

• Redundancy means duplicate or repeated data stored in different places.

• In traditional file systems, the same data may be stored multiple times in different
files.

• This leads to data duplication, which wastes storage and may create inconsistencies.

• A Database Management System (DBMS) helps control and reduce this redundancy
by storing data in a centralized database and allowing multiple applications to access
the same data.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Controlling Redundancy

• Consider a student database without DBMS:

Rollno Name Department HoD

1 Arun CS Dr. Ram
2 Meera CS Dr. Ram
3 John CS Dr. Ram

• Here, Department HOD (Dr. Ram) is repeated in every record. This is data
redundancy.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Controlling Redundancy

• With DBMS: Student table

Rollno Name Department_ID
1 Arun D1
2 Meera D1
3 John D1

• Department table

Department_ID Department HoD

D1 CS Dr. Ram

• Now Dr. Ram is stored only once, which reduces redundancy.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Restricting Unauthorized Access

• When many users share a large database, not everyone should be allowed to see or
change all the data.

• Different users may have different permissions:

• Some users can only view (retrieve) data.

• Some users can view and update data.

• To control access, each user is given an account with a username and password.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Restricting Unauthorized Access

• The Database Administrator (DBA) creates these accounts and decides:

• Who can access the database

• What type of operations they can perform (view or update data)

• Example

• Database Administrator: Can create accounts and manage the database.

• Teacher: Can view and update student marks.

• Student: Can only view their results.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Providing Persistent Storage for Program Objects

• In programming, objects are created to store data and perform operations.

• Normally, these objects exist only while the program is running. When the program
stops, the objects and their data disappear.

• A DBMS provides persistent storage, which means the data stored in objects can be
saved permanently in the database.

• Even if the program is closed or the system is restarted, the data remains stored safely.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Providing Storage Structures and Search Techniques for Efficient Query Processing

• A DBMS stores a large amount of data. To make this process fast and efficient, the
DBMS provides:

• Storage Structures: These are special ways of organizing data in the database so that it
can be stored and accessed easily. Proper storage structures help the system store data
systematically.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Providing Backup and Recovery

• A DBMS must provide facilities for recovering from hardware or software

failures.

• The backup and recovery subsystem of the DBMS is responsible for recovery.

• For example, if the computer system fails in the middle of a complex update
transaction, the recovery subsystem is responsible for making sure that the
database is restored to the state it was in before the transaction started executing

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH

• Representing Complex Relationships among Data

• A DBMS can represent complex relationships between different types of data in a

database.

• This means the system can show how different data items are connected to each other.

• In real-world situations, data is often related in many ways, and DBMS helps to
organize and manage these relationships properly.

Prepared by Amrutha N
 ADVANTAGES OF USING THE DBMS APPROACH
• Representing Complex Relationships among Data

Student_ID Name Course_ID Course_name Student_ID Course_ID

1 Arun C1 Data structures 1 C1
2 Meera C2 DBMS 2 C2
1 C1
Student table Course table
Enrollment table

• One student can enroll in many courses

• One course can have many students

Prepared by Amrutha N
HISTORY OF DATABASE APPLICATIONS

Prepared by Amrutha N
 HISTORY OF DATABASE APPLICATIONS

• Early Database Applications Using Hierarchical and Network Systems

• Providing Data Abstraction and Application Flexibility with Relational Databases

• Object-Oriented Applications and the Need for More Complex Databases

• Interchanging Data on the Web for E-Commerce Using XML

• Extending Database Capabilities for New Applications

• Emergence of Big Data Storage Systems and NOSQL Databases

Prepared by Amrutha N
 Extending database capabilities for new applications
• Script programming languages such as PHP and JavaScript allow generation of dynamic
Web pages that are partially generated from a database.

• New functionality is being added to DBMSs in the following areas:

• Scientific Applications

• XML (extensible Markup Language)

• Image Storage and Management

• Audio and Video data management

• Data Warehousing and Data Mining

• Spatial data management

• Time Series and Historical Data Management Prepared by Amrutha N

 Extending database capabilities for new applications

• New functionality is being added to DBMSs in the following areas:

• The above gives rise to new research and development in incorporating new data
types, complex data structures, new operations and storage and indexing schemes in
database systems.

• Also allow database updates through Web pages.

Prepared by Amrutha N
DATA MODELS, SCHEMAS AND INSTANCES

Prepared by Amrutha N
 DATA MODELS

• A data model is a collection of concepts used to describe the structure of a

database.

• It defines how data is stored, organized, and related in a database system.

• Most data models also include a set of basic operations for specifying retrievals
and updates on the database.

Prepared by Amrutha N
 Categories of Data Models

• High-Level Data Models (Conceptual Data Models): These models describe data in a way
that is easy for users to understand. They focus on what data is stored and the
relationships between data, not how it is stored in the computer.

• An entity represents a real-world object or concept, such as an employee or a

project from the miniworld that is described in the database.

• An attribute represents some property of interest that further describes an entity,

such as the employee’s name or salary.

• A relationship among two or more entities represents an association among the

entities.

Prepared by Amrutha N
 Categories of Data Models

• Low-Level Data Models (Physical Data Models): These models describe how data is
actually stored in the computer memory. Concepts provided by physical data models are
generally meant for computer specialists, not for end users.

• Representational (Logical) Data Models: These models describe data using structures that
the DBMS can implement, but they are still easy for users to understand.

• Hierarchical Data Model: This is a data model in which data is organized in a tree-like
structure using parent–child relationships.

• Object-Oriented Data Model: It represents data as objects that contain both data
(attributes) and operations (methods).
Prepared by Amrutha N
 SCHEMA
• In a data model, it is important to distinguish between the description of the database and
the database itself.

• A schema is the overall design or structure of the database.

• It describes how the data is organized and the relationships between tables.

• Schema does not change frequently.

Roll_no Name Department Age

INT VARCHAR VARCHAR INT

• This structure of the table is called the database schema.

• The design or blueprint of the database.

Prepared by Amrutha N
 INSTANCE

• An instance refers to the actual data stored in the database at a particular moment.

• Unlike schema, instances change frequently because data is inserted, updated, or

deleted.

Roll_no Name Department Age

101 Amal ISE 20
102 Rahul CSE 21

Prepared by Amrutha N
• When we define a new database, we specify its database schema only to the DBMS. At this
point, the corresponding database state is the empty state with no data.
• We get the initial state of the database when the database is first populated or loaded with the
initial data.
• From then on, every time an update operation is applied to the database, we get another
database state.
• At any point in time, the database has a current state.
• The DBMS is partly responsible for ensuring that every state of the database is a valid state
that is, a state that satisfies the structure and constraints specified in the schema.
• The DBMS stores the descriptions of the schema constructs and constraints also called the
meta-data in the DBMS catalog.
• The schema is sometimes called the INTENSION, and a database state is called an
EXTENSION of the schema.
Prepared by Amrutha N
THREE-SCHEMA ARCHITECTURE & DATA
INDEPENDENCE

Prepared by Amrutha N
THREE-SCHEMA ARCHITECTURE

Prepared by Amrutha N
 THREE-SCHEMA ARCHITECTURE

• The goal of the three-schema architecture is to separate the user applications from the
physical database.

• Internal level:

• The internal level has an internal schema, which describes the physical storage
structure of the database.

• The internal schema uses a physical data model and describes the complete
details of data storage and access paths for the database.

Prepared by Amrutha N
 THREE-SCHEMA ARCHITECTURE

• Conceptual level:

• The conceptual level has a conceptual schema, which describes the structure of
the whole database for a community of users.

• The conceptual schema hides the details of physical storage structures and
concentrates on describing entities, data types, relationships, user operations, and
constraints.

Prepared by Amrutha N
 THREE-SCHEMA ARCHITECTURE

• External level:

• The external level shows how different users see the database.

• It contains external schemas (user views).

• Each external schema shows only the part of the database that a particular user
needs and hides the remaining data.

• This helps protect data and makes the database easier for users to understand.

Prepared by Amrutha N
 THREE-SCHEMA ARCHITECTURE

• The DBMS must transform a request specified on an external schema into a request against
the conceptual schema, and then into a request on the internal schema for processing over the
stored database.
• If the request is a database retrieval, the data extracted from the stored database must be
reformatted to match the user’s external view.
• The processes of transforming requests and results between levels are called mappings.

Prepared by Amrutha N
 Data Independence

• Data independence means the ability to change the database structure at one level
without affecting the next higher level.

• There are two types of data independence:

• Logical Data Independence

• Physical Data Independence

Prepared by Amrutha N
 Data Independence

• Physical data independence

• It is the ability to change the internal schema without affecting the conceptual
schema.

• This means changes in how data is stored do not affect the logical structure of the
database.

• Suppose the database administrator moves database from HDD to SSD. These
changes happen at the internal level, but the table structure remains the same.
Applications still use the same table.

Prepared by Amrutha N
 Data Independence

• Logical data independence

• Logical data independence is the ability to change the conceptual schema without
affecting the external schema or user views.

• Suppose, If a user view only shows Roll_no and Name and we add a new column:
age, that view remains unchanged.

Roll_no Name Department

101 Amal ISE
102 Rahul CSE

Prepared by Amrutha N
DATABASE LANGUAGES

Prepared by Amrutha N
 DATABASE LANGUAGES

• Database languages are special languages used to define, manipulate, control, and query
data in a database.

• These languages help users interact with the database system.

Prepared by Amrutha N
 DATABASE LANGUAGES

• Data Definition Language (DDL)

• DDL is used to define, create, and modify the structure of a database.

• It deals with database schema, tables, indexes, and constraints.

• In simple terms, DDL is used to design the structure of the database, not to
manipulate the data inside it.

• Common Commands

CREATE: Create a table or database

ALTER: Modify table structure

DROP: Delete a table

Prepared by Amrutha N
 DATABASE LANGUAGES

• Data Manipulation Language (DML)

• DML is a set of SQL commands used to insert, update, delete, and retrieve data
from database tables.

• DML operations are performed on the data stored inside tables, not on the table
structure.

• Common Commands

INSERT: Add new records into a table

SELECT: Retrieve data from table

UPDATE: Modify existing data in the table

Prepared by Amrutha N
 DATABASE LANGUAGES

• Data Control Language (DCL)

• DCL is used to control access and permissions in the database.

• Common Commands

• GRANT: It is used to give specific privileges to users on database objects such

as tables, views, or databases.

• REVOKE: It is used to remove or cancel the privileges given to a user.

Prepared by Amrutha N
 DATABASE LANGUAGES

• Transaction Control Language (TCL)

• TCL is used to manage database transactions.

• Common Commands

• COMMIT: It is used to permanently save all changes made in the current

transaction to the database. Once the COMMIT command is executed, the
changes cannot be undone.

• ROLLBACK: It is used to undo changes made during the current transaction

and restore the database to the previous state.

Prepared by Amrutha N
 DATABASE LANGUAGES

• View definition language (VDL) is to specify user views and their mappings to the
conceptual schema.

• Storage definition language (SDL) is used to specify the internal schema.

Prepared by Amrutha N
DBMS INTERFACES

Prepared by Amrutha N
 DATABASE INTERFACES

• Menu-based interfaces for web clients or browsing

• Menu-based interfaces show users a list of options (menus) to help them make requests to
the database.

• Instead of remembering commands or the syntax of SQL, users can select options step by
step from the menu.

• Apps for mobile devices

• Mobile apps allow users to access their data using smartphones or other mobile devices.

• Many organizations like banks, reservation services, and insurance companies provide
mobile apps for this purpose.

• Users log in using their account name and password, and the app shows a limited menu of
options to access their data. Prepared by Amrutha N
 DATABASE INTERFACES
• Form based interface

• A forms-based interface shows users a form on the screen to enter data. Users can
fill in all the fields to add new data to the database.

• They can also search and retrieve matching data from the database.

• Graphical User Interfaces

• A GUI typically displays a schema to the user in diagrammatic form.

• Users interact with the database through menus, icons, and graphical elements.

• No need to write SQL commands.

• Example: phpMyAdmin

Prepared by Amrutha N
 DATABASE INTERFACES

• Natural Language Interface

• These interfaces accept requests written in English or some other language and
attempt to understand them.

• Users interact with the database using normal human language.

• The system converts the request into a database query.

Prepared by Amrutha N
 DATABASE INTERFACES

• Keyword-based database search

• These interfaces are similar to web search engines like Google Search.

• Users can type words in natural language (such as English), and the system
searches for related documents or web pages.

• Search engines use indexes of words and ranking methods to find and display the
most relevant results first.

Prepared by Amrutha N
THE DATABASE SYSTEM ENVIRONMENT

Prepared by Amrutha N
DBMS COMPONENT MODULES

Prepared by Amrutha N
 DBMS COMPONENT MODULES
Users of the Database System

• DBA Staff (Database Administrators)

• They manage and control the database.

• They use DDL statements and privileged

commands.

• The DDL compiler converts DDL commands

into internal tables stored in the data dictionary.

• Privileged Commands are special administrative

commands used only by the DBA.

• These commands go directly to the Runtime

Database Processor.
Prepared by Amrutha N
 DBMS COMPONENT MODULES

Users of the Database System

• Casual Users

• These users interact with the database

occasionally.

• They use interactive queries (SQL queries).

• Query compiler converts SQL queries into a

low-level internal form.

• Query optimizer chooses the most efficient

method to execute the query.

Prepared by Amrutha N
 DBMS COMPONENT MODULES

Users of the Database System

• Application Programmers
• They write programs using languages like Java, C, or
Python with database commands.
• Precompiler separates database commands.
• DML Compiler compiles database manipulation
commands.
• Host Language Compiler compiles the rest of the
program.
• Output becomes compiled transactions.
Prepared by Amrutha N
 DBMS COMPONENT MODULES

Users of the Database System

• Parametric Users

• These users repeatedly run predefined

transactions.

• Examples: bank clerks, reservation staff,

ATM users

• They directly use compiled transactions

created by programmers.

Prepared by Amrutha N
 DBMS COMPONENT MODULES

• Runtime Database Processor

• Executes database operations.

• It interacts with: Stored data manager, Concurrency

Control & Backup/Recovery Subsystems, Stored
database

• Stored Data Manager

• Handles actual data storage and retrieval.

Prepared by Amrutha N
 DBMS COMPONENT MODULES

• Concurrency Control & Backup/Recovery Subsystems

• Maintains data consistency

• Handles simultaneous users

• Performs backup and recovery

• Stored Database

• The actual database where data is stored.

• System Catalog / Data Dictionary

• Contains metadata (information about tables,

fields etc.).
Prepared by Amrutha N
DATABASE SYSTEM UTILITIES

Prepared by Amrutha N
 DATABASE SYSTEM UTILITIES

• DBMSs have database utilities that help the DBA manage the database system.

• Loading:
• It is the process of transferring existing data files, such as text files or sequential
files, into a database.
• A loading utility is used for this purpose.
• The utility takes the source format of the data file and the target database
structure as input.
• It then automatically converts the data into the required format and stores it in the
database.

Prepared by Amrutha N
 DATABASE SYSTEM UTILITIES

• Backup:

• A backup utility is used to create a copy of the database.

• Usually, the entire database is copied and stored on another storage device such as
a tape, disk, or other storage media.

• This backup copy is used to restore the database if a serious failure occurs, such
as a disk crash.

Prepared by Amrutha N
 DATABASE SYSTEM UTILITIES

• Database Storage Reorganization:

• This utility is used to rearrange or reorganize the database files into a better structure.

• It may also create new access paths (such as indexes) to make data retrieval faster and
improve overall database performance.

• Performance Monitoring:

• This utility checks how the database is being used and collects usage statistics.

• It provides this information to the Database Administrator (DBA).

• The DBA uses these statistics to decide how to improve database performance, such as
reorganizing files.

Prepared by Amrutha N
 CENTRALIZED DBMS ARCHITECTURE

• In Centralized DBMS architecture, the entire

database system is located at a single central
location.

• All the data, database management software,

and processing are stored on one central
computer (server).

Prepared by Amrutha N
 CENTRALIZED DBMS ARCHITECTURE

• Single database location: The whole database is stored in one place.

• Central control: The central server controls data storage, processing, and security.

• Multiple Users: Many users can access the database at the same time through terminals or
network connections.

• Easy management: Since everything is in one location, backup, security, and maintenance
are easier.

Prepared by Amrutha N
 BASIC CLIENT/SERVER ARCHITECTURES

• In Client–Server Architecture, the database system is divided into two parts: client and
server.

• The client is the user’s computer or application that sends requests.

• The server is the central computer that stores the database and DBMS and processes the
requests.

Prepared by Amrutha N
 TWO-TIER CLIENT/SERVER ARCHITECTURES FOR DBMS

• In a two-tier client/server architecture, the user interface and application programs run on the
client computer.

• When the program needs to access the database, it connects to the DBMS located on the
server.

• After the connection is established, the client program can send queries and transaction
requests to the DBMS.

• A standard called Open Database Connectivity (ODBC) provides an Application

Programming Interface (API) that allows client programs to communicate with the DBMS.

Prepared by Amrutha N
 TWO-TIER CLIENT/SERVER ARCHITECTURES FOR DBMS

• Using ODBC, a client program can connect to multiple relational databases and send queries
to them.

• The server processes the query, and the results are sent back to the client, where they are
displayed to the user.

• For Java programs, another standard called Java Database Connectivity (JDBC) is used.

• JDBC allows Java applications to access one or more DBMSs through a standard interface.

Prepared by Amrutha N
 THREE-TIER ARCHITECTURES FOR WEB APPLICATIONS

• These architectures are used to separate different parts of a web application into layers
(tiers).

• This separation improves scalability, security, and maintainability.

• A Three-Tier Architecture divides a web application into three layers:

Prepared by Amrutha N
 THREE-TIER ARCHITECTURES FOR WEB APPLICATIONS

• Client Layer

• This is the user interface.

• It is where users interact with the system.

• Usually implemented using web browsers, mobile apps, or GUI applications.

• Application Tier (Middle Layer / Logic Layer)

• This layer contains the application logic or business logic.

• It processes user requests received from the presentation layer.

• Communicates with the database server.

Prepared by Amrutha N
 THREE-TIER ARCHITECTURES FOR WEB APPLICATIONS

• Data Tier (Database Layer)

• This layer stores and manages the database.

• It handles data storage, retrieval, and management.

Prepared by Amrutha N
CLASSIFICATION OF DATABASE MANAGEMENT
SYSTEM

Prepared by Amrutha N
CLASSIFICATION OF DATABASE MANAGEMENT SYSTEM

Classification Based on Data Model

• This classification depends on how data is organized and stored in the database.

a) Relational DBMS (RDBMS)

• Data is stored in the form of tables (relations).

• Tables consist of rows (records) and columns (attributes).

• Relationships are created using keys.

Prepared by Amrutha N
Classification Based on Data Model

b) Object-Oriented Database (OODB)

• An Object-Oriented Database (OODB) is a type of database that stores data in the

form of objects, similar to how objects are used in object-oriented programming
languages like Java, C++, and Python.

• Instead of storing data in tables like in relational databases, an object-oriented

database stores objects that contain both data and methods.

Prepared by Amrutha N
Classification Based on Data Model

c) Hierarchical DBMS

• It stores data in a hierarchical structure similar to a tree, where each parent record can
have one or more child records, but each child record has only one parent.

d) Network DBMS

• It is a type of database management system where data is organized in the form of a

network structure.

• In this model, records are connected with multiple relationships, allowing a child to
have more than one parent.

Prepared by Amrutha N
Based on number of users

a) Single-user systems

• It support only one user at a time and are mostly used with PCs.

b) Multi-user systems

• Allows multiple users to access the database simultaneously.

Prepared by Amrutha N
Based on sites

a) Centralized DBMS

• The entire database is stored at one physical location (one site).

• All users access the database from this central server.

• Data processing and management happen at the same site.

• Easier to manage but if the server fails, the whole system stops.

b) Distributed DBMS

• The database is stored at multiple locations (sites) connected through a network.

• Each site can store part of the database or a copy of the database.

Prepared by Amrutha N
CONCEPTUAL DATA MODELLING

Prepared by Amrutha N
 CONCEPTUAL DATA MODELLING

• Conceptual modeling is the initial stage of database design in which the main entities,
attributes, and relationships of the system are identified and represented in a model.

• Conceptual modeling is commonly represented using an Entity–Relationship model.

Prepared by Amrutha N
 USING HIGH-LEVEL CONCEPTUAL DATA MODELS FOR
DATABASE DESIGN

• The Miniworld represents the real-world environment or

organization for which the database is being created.

• Requirements collection and analysis

• In this stage, the database designers collect

information from users and organizations.

• Two types of requirements are identified: functional

and data requirements.

Prepared by Amrutha N
• Functional requirements

• These describe what operations the system should perform.

• In functional analysis stage the system operations are

analyzed.

• Data requirements

• These describe what data must be stored in the database.

• Conceptual design focuses on what data should be stored and

relationships between data.

• It creates a conceptual schema using a high-level data model.

• Common model used: ER Model (Entity Relationship Model)

Prepared by Amrutha N
• Logical Design (Data Model Mapping)

• Here the conceptual schema is converted into a logical schema

based on the specific DBMS data model.

• Student entity becomes student table.

• Physical Design

• Physical design determines how the database is stored on disk.

• Application Program Design

• This stage focuses on developing programs that interact with

the database.

• Programs are written using languages like SQL, Java, Python

etc. Prepared by Amrutha N
• Transaction Implementation

• Now the actual application programs are developed and

implemented.

• DBMS-Independent vs DBMS-Specific Levels

• The diagram shows a dotted line separating these two levels.

• DBMS-Independent

• These stages do not depend on a specific database system.

• They include requirements analysis, functional analysis,

conceptual design.

• DBMS-Specific

• These depend on the database system used.

Prepared by Amrutha N
 ER DIAGRAM

• An ER Diagram (Entity–Relationship Diagram) is a graphical representation used in database

design to show the entities, attributes, and relationships in a database system.

• It helps designers understand how data is organized and how different data items are related
before creating the database tables.

Prepared by Amrutha N
 Entity

• An entity is any real-world object or concept about which data is stored in the database.

• Entities are represented by means of rectangles.

• Rectangles are named with the entity set they represent.

• Examples: Employee, Student

Prepared by Amrutha N
 Strong & Weak Entity Type
Strong entity type

• A strong entity type is an entity that has its own key attribute to uniquely identify each entity.

• Example: EMPLOYEE with key attribute Employee_ID. So, each employee can be identified
independently.

• It is represented by rectangle.

• Weak entity

• A weak entity type does not have its own key attribute to uniquely identify its entities.

• Instead, it is identified using the key of another entity (owner entity) & its own partial key
attribute.

• Because of this, a weak entity depends on another entity for its existence.

• It is represented by double rectangle. Prepared by Amrutha N

• Example for weak entity

Prepared by Amrutha N
 Attributes
• Each entity has attributes, the particular properties that describe it.

• Attributes are represented by means of ellipses.

• Every ellipse represents one attribute and is directly connected to its entity (rectangle).

• For example, a STUDENT entity may be described by the student’s name, Roll_No and
BirthDate.

Prepared by Amrutha N
 Types of Attributes
• Composite versus Simple (Atomic) Attributes:

• Composite attributes can be divided into smaller subparts, which represent more
basic attributes with independent meanings.

• Composite attributes can form a hierarchy; for example, Street_address can be further
subdivided into three simple component attributes: Number, Street, and
Apartment_number,

Prepared by Amrutha N
 Types of Attributes
• Composite versus Simple (Atomic) Attributes:

Prepared by Amrutha N
 Types of Attributes
• Composite versus Simple (Atomic) Attributes:

• Attributes that are not divisible are called simple or atomic attributes.

Prepared by Amrutha N
 Types of Attributes
• Single-Valued versus Multivalued Attributes

• A single-valued attribute is an attribute that has only one value for each entity in the
database.

• Age of a person

• Date of Birth of a student

• Roll Number of a student

• A multivalued attribute is an attribute that can have multiple values for a single entity.

• Phone numbers of a person

• Email addresses Skills of an employee

• Degrees obtained by a person Prepared by Amrutha N

 Types of Attributes
• Single-Valued versus Multivalued Attributes

• Multivalued attributes are depicted by double ellipse.

Prepared by Amrutha N
 Types of Attributes
• Stored versus derived attributes:

• A stored attribute is an attribute whose value is directly stored in the database.

• Date_of_birth

• Marks

• A derived attribute is an attribute whose value is calculated from other attributes.

• Age

• Total_marks

• Represented by a dashed oval.

Prepared by Amrutha N
 Types of Attributes
• Stored versus derived attributes:

Prepared by Amrutha N
 Types of Attributes
• NULL Values

• In some cases, a particular entity may not have an applicable value for an attribute.

• For example, the Apartment_number attribute of an address applies only to addresses

that are in apartment buildings and not to other types of residences, such as single-
family homes.

• Similarly, a College_degrees attribute applies only to people with college degrees.

For such situations, a special value called NULL is created.

Prepared by Amrutha N
 Types of Attributes
• Complex Attributes

• A complex attribute is an attribute that contains multiple components and may also
have multiple values for each component.

• Consider an entity Employee with an attribute Address.

• Address can be broken into sub-attributes street, city, state, pin code.

• This makes Address a composite attribute.

• If an employee has more than one address (e.g., permanent and temporary address),
then Address becomes multivalued.

Prepared by Amrutha N
 Key Attributes

• A Key Attribute is an attribute whose value is unique for every entity in the entity set.

• Key attributes are underlined in ER diagrams.

• It represents a primary key.

Prepared by Amrutha N
 Entity Types, Entity Sets

• Entity Type

• An entity type is a collection of entities that have the same set of attributes.

• Student (Student_ID, Name, Age, Department)

• Employee (Employee_ID, Name, Age, Department)

• Entity Sets:

• An entity set is a group of entities that belong to the same entity type.

• In simple words, it is the actual data or instances of an entity type.

• Characteristics

• Contains actual instances (records).

• Changes as new entities are added or removed. Prepared by Amrutha N

• An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and
Manager_start_date. Locations is the only multivalued attribute. Specify that both Name and
Number are key attributes.

Prepared by Amrutha N
 KEYS

• Keys play an important role in the relational database.

• It is used to uniquely identify any record or row of data from the table.

• For example: In Student table, ID is used as a key because it is unique for each student.

Prepared by Amrutha N
 Types of keys
Primary key

• It is the first key which is used to identify one and only one instance of an entity uniquely.

• In the EMPLOYEE table, ID can be primary key since it is unique for each employee.

• In the EMPLOYEE table, we can even select License_Number and Passport_Number as

primary key since they are also unique.

• For each entity, selection of the primary key is based on requirement and developers.

Prepared by Amrutha N
 Types of keys
Candidate key

• A candidate key is an attribute or set of an attribute which can uniquely identify a tuple.

• The remaining attributes except for primary key are considered as a candidate key.

• The candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the
attributes like SSN, Passport_Number, and License_Number, etc. are considered as a candidate
key.

Prepared by Amrutha N
 Types of keys
Foreign key

• Foreign keys are the column of the table which is used to point to the primary key of
another table.

• In a company, every employee works in a specific department, and employee and

department are two different entities.

• So we can't store the information of the department in the employee table.

• That's why we link these two tables through the primary key of one table.

Prepared by Amrutha N
 Types of keys
Foreign key

• We add the primary key of the DEPARTMENT table, Department_Id as a new attribute in
the EMPLOYEE table.

• Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

Prepared by Amrutha N
 ROLE NAMES

• A role name explains how an object participates in the relationship.

• The role name signifies the role that a participating entity from the entity type plays in each
relationship instance.

• For example, in the WORKS_FOR relationship type, EMPLOYEE plays the role of employee
and DEPARTMENT plays the role of department.

Prepared by Amrutha N
 Relationship Types, Relationship Sets, and Structural Constraint

• Relationship

• Relationship shows how entities are connected or related to each other in a database.

• Consider two entities: Student, Course: A student enrolls in a course.

• Relationship represented as Diamond shape.

• Relationship between two entities of the same type is called recursive relationship.

• A Recursive relationship is nothing but, simply an entity is having a relationship with

self.

Prepared by Amrutha N
 Relationship Types

• A relationship where two entities are participating is called a binary relationship.

• Cardinality is the number of instance of an entity from a relation that can be associated with
the relation.

• Relationship Types

• One to one relationship

• One to many relationship

• Mant to one relationship

Prepared by Amrutha N
 Relationship Types

• One to one relationship

• When only one instance of an entity is associated with the relationship, it is marked as
'1:1’.

• It is used to create a relationship between two tables in which a single row of the first
table can only be related to one and only one record of a second table.

In a university, each department has only one head of the department. And one HoD can
take only one department. This shows a one-to-one (1:1) relationship between the
department and the person as a head.

Prepared by Amrutha N
 Relationship Types

• One to many relationship

• When more than one instance of an entity is associated with a relationship, it is marked
as '1:N’.

A department that has many students. Each student is assigned to one department.

A customer can place

c many orders.

Prepared by Amrutha N
 Relationship Types

• Many to one relationship

• When more than one instance of entity is associated with the relationship, it is marked as
‘N:1’.

Many students can belong to one department. But each student belongs to only one
c
department.

Many students can enroll

c to one course.

Prepared by Amrutha N
 Relationship Types

• Many to many relationship

• More than one instance of an entity on the left and more than one instance of an
entity on the right can be associated with the relationship.

Many students enroll in many courses.

There are many employees involved in each project, and every employee can involve
in more than one project.

Prepared by Amrutha N
• Example:

Prepared by Amrutha N
 Relationship Degree

• Relationship degree refers to the number of entity types participating in a relationship.

• Unary Relationship

• A unary relationship occurs when an entity is related to itself.

• Example: EMPLOYEE manages EMPLOYEE

Prepared by Amrutha N
 Relationship Degree

• Binary Relationship

• A binary relationship occurs when two entity types are involved.

• Example: STUDENT enrolls COURSE

Prepared by Amrutha N
 Relationship Degree

• Ternary Relationship

• Ternary relationship set is a relationship set where three entity sets participate in a
relationship set.

Prepared by Amrutha N
Prepared by Amrutha N
• Consider a system for managing a Software Development Company with the following
requirements:

• A Customer is identified by Name and Contact details.

• A Project has attributes Project ID and Name.

• A Software Development Company has Name and Location.

• A Developer has Name, Technology, and Hourly Rate.

• The relationships among these entities are as follows:

• A Customer places Orders for Projects.

• A Software Development Company Pays Customers.

• A Software Development Company Specializes in Projects.

• A Software Development Company Employs Developers.

• A Developer Works on Projects.

Prepared by Amrutha N
Prepared by Amrutha N
An ER schema diagram for the COMPANY database.

Prepared by Amrutha N
 STRUCTURAL CONSTRAINTS OF RELATIONSHIPS IN ER
MODEL
• To understand Structural Constraints, we must take a look at Cardinality Ratios and
Participation Constraints.

Cardinality Ratios of relationships

• The entities are denoted by rectangle and relationships by diamond.

• There are numbers (represented by M and N) written above the lines which connect
relationships and entities.

Prepared by Amrutha N
 STRUCTURAL CONSTRAINTS OF RELATIONSHIPS IN ER
MODEL
• These are called cardinality ratios.

• These represent the maximum number of entities that can be associated with each other
through relationship, R.

Types of Cardinality

• There can be 4 types of cardinality

Prepared by Amrutha N
 STRUCTURAL CONSTRAINTS OF RELATIONSHIPS IN ER
MODEL
• These are called cardinality ratios.

• These represent the maximum number of entities that can be associated with each other
through relationship, R.

Types of Cardinality

• There can be 4 types of cardinality

Prepared by Amrutha N
 Participation Constraints

• Participation Constraints tell us that that the participation in a relationship can either be total
or partial.

Prepared by Amrutha N
 Participation Constraints

Total Participation

• When each entity in an entity set participates in a relation, it is called Total

Participation.

• Total participation is represented by double lines.

Partial participation

• However, when all entities in the given entity set do not participate in a relation, it is
called Partial Participation.

• Partial participation is represented by single lines.

Prepared by Amrutha N
 Generalization

• Generalization is like a bottom-up approach in which two or more entities of lower level
combine to form a higher level entity if they have some attributes in common.

• In generalization, an entity of a higher level can also combine with the entities of the lower
level to form a further higher level entity.

• Generalization is more like subclass and super class system, but the only difference is the
approach.

• Generalization uses the bottom-up approach.

• In generalization, entities are combined to form a more generalized entity, i.e., subclasses are
combined to make a super class.
Prepared by Amrutha N
 Generalization

• For example, Faculty and Student entities can be generalized and create a higher level entity
Person.

Prepared by Amrutha N
 Specialization

• Specialization is a top-down approach, and it is opposite to Generalization.

• In specialization, one higher level entity can be broken down into two lower level entities.

• Specialization is used to identify the subset of an entity set that shares some distinguishing
characteristics.

• Normally, the super class is defined first, the subclass and its related attributes are defined
next, and relationship set are then added.

Prepared by Amrutha N
 Specialization

• For example: In an Employee management system, EMPLOYEE entity can be specialized as

TESTER or DEVELOPER based on what role they play in the company.

Prepared by Amrutha N