Module 1 Introduction about the Database Conceptual Designing

Basic Concept: Database system concept, architecture, History of Database, Data

Independence, Database system Vs File system, Data models & Types of Data Models,
schema and instances.
Data Modelling using the Entity Relationship Model: ER model concepts, Degree of
relationship, Notation for ER diagram, mapping constraints, Generalization, Aggregation,
Reduction of an ER diagrams to tables. Extended ER Diagram & reduction of EER. Codd
Rules.
Introduction on SQL: Types of SQL commands: -DDL, DML, DCL, TCL. Basic of
Relation Algebra & Operations, Query Optimization.

Database System Concept

Data
Data is a collection of a distinct small unit of information. It can be used in a variety of forms
like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory,
etc.

Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is
plural of the word datum.

In computing, Data is information that can be translated into a form for efficient movement
and processing. Data is interchangeable.

Properties of data in a Database:

The main properties of data in a database are:

1. The data in a database should be well organized.

2. It should be well integrated.
3. It should be stored permanently so that it can be used later.
4. It should be recoverable in case of any damage to the database.
5. The data should be resilient.
6. It should be shared among different users as well as within the application.
7. Data should be accessible when and where needed.

To maintain large database, we need some operations that help in maintaining the data in the
database efficiently. Following operations performed on the database are:

1. Insertion: This operation is used to add a new record to the database. For Example: to
add a new number to the phone number directory.
 2. Updation: This operation is used to update the existing data. For Example: Suppose
there is a change in the surname of a particular person, so you have to modify the
surname by replacing the surname number with a new surname.
3. Deletion: This operation is used to delete the existing data from the database. For
Example: suppose a person shifts from Ludhiana to Chandigarh then the information
about that person should be deleted from Ludhiana phone number directory.
4. Selection: This operation is used to retrieve the existing data. For Example: to view a
phone number directory of a particular person, we can search for his number from the
phone number directory.
5. Sorting: This operation is used to arrange the data in ascending or descending order.
For Example: in a phone number directory, the phone number should be arranged in
alphabetical order for fast retrieval.

Database

A database is an organized collection of data, so that it can be easily accessed and managed.

You can organize data into tables, rows, columns, and index it to make it easier to find
relevant information.

Database handlers create a database in such a way that only one set of software program
provides access of data to all the users.

The main purpose of the database is to operate a large amount of information by storing,
retrieving, and managing data.

There are many dynamic websites on the World Wide Web nowadays which are handled
through databases. For example, a model that checks the availability of rooms in a hotel. It is
an example of a dynamic website that uses a database.

There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix,
PostgreSQL, SQL Server, etc.

Modern databases are managed by the database management system (DBMS).

Types of Databases

Database
A database is a group of interconnected data that is stored in a database server.

Purpose of Database:

1. The primary purpose of a database is to deliver a method to store and retrieve

database statistics in a fast and efficient manner.
2. The second foremost characteristic of the database is that it permits multiple users to
access the database concurrently and it makes it possible to share data.
3. It contains concurrency control software to make sure that multiple users are trying to
update the same data at the same time.

There are various types of databases used for storing different varieties of data:

1) Centralized Database
It is the type of database that stores data at a centralized database system. It comforts the
users to access the stored data from different locations through several applications. These
applications contain the authentication process to let users access data securely. An example
of a Centralized database can be Central Library that carries a central database of each library
in a college/university.

Advantages of Centralized Database

1. It has decreased the risk of data management, i.e., manipulation of data will not affect
the core data.
2. Data consistency is maintained as it manages data in a central repository.
3. It provides better data quality, which enables organizations to establish data standards.
4. It is less costly because fewer vendors are required to handle the data sets.
Disadvantages of Centralized Database
 1. The size of the centralized database is large, which increases the response time for
fetching the data.
2. It is not easy to update such an extensive database system.
3. If any server failure occurs, entire data will be lost, which could be a huge loss.
2) Relational Database
This database is based on the relational data model, which stores data in the form of
rows(tuple) and columns(attributes), and together forms a table(relation). A relational
database uses SQL for storing, manipulating, as well as maintaining the data. E.F. Codd
invented the database in 1970. Each table in the database carries a key that makes the data
unique from others. Examples of Relational databases are MySQL, Microsoft SQL Server,
Oracle, etc.
Advantages of the Relational Model
1. It gives a succinct aspect of the data.
2. This model is very easy and simple to design.
3. It supports data independence.
4. It assists the user to employ a query language to query the database.
5. The relational data model provides a high level of database security.
Disadvantages of the Relational Model
1. Relational model is not suitable for larger databases.
3) Cloud Database
A type of database where data is stored in a virtual environment and executes over the cloud
computing platform. It provides users with various cloud computing services (SaaS, PaaS,
IaaS, etc.) for accessing the database. There are numerous cloud platforms, but the best
options are:
 Amazon Web Services(AWS)
 Microsoft Azure
 Kamatera
 PhonixNAP
 ScienceSoft
 Google Cloud SQL, etc.

RDBMS (Relational Database Management System)

RDBMS stands for Relational Database Management System.

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE,
My-SQL, and Microsoft Access are based on RDBMS.

It is called Relational Database Management System (RDBMS) because it is based on the

relational model introduced by E.F. Codd.
How it works
Data is represented in terms of tuples (rows) in RDBMS.

A relational database is the most commonly used database. It contains several tables, and
each table has its primary key.

Due to a collection of an organized set of tables, data can be accessed easily in RDBMS.

Brief History of RDBMS

From 1970 to 1972, E.F. Codd published a paper to propose using a relational database
model.

RDBMS is originally based on E.F. Codd's relational model invention.

Following are the various terminologies of RDBMS:

Table/Relation

Everything in a relational database is stored in the form of relations. The RDBMS database
uses tables to store data. A table is a collection of related data entries and contains rows and
columns to store data. Each table represents some real-world objects such as person, place, or
event about which information is collected. The organized collection of data into a relational
table is known as the logical view of the database.

Properties of a Relation:

 Each relation has a unique name by which it is identified in the database.

 Relation does not contain duplicate tuples.
 The tuples of a relation have no specific order.
 All attributes in a relation are atomic, i.e., each cell of a relation contains exactly one
value.
A table is the simplest example of data stored in RDBMS.
Example of the student table

Row or record
A row of a table is also called a record or tuple. It contains the specific information of each
entry in the table. It is a horizontal entity in the table. For example, The above table contains
5 records.

Properties of a row:

 No two tuples are identical to each other in all their entries.

 All tuples of the relation have the same format and the same number of entries.
 The order of the tuple is irrelevant. They are identified by their content, not by their
position.
One record/row in the table:

Column/attribute
A column is a vertical entity in the table which contains all information associated with a
specific field in a table. For example, "name" is a column in the above table which contains
all information about a student's name.

Properties of an Attribute:

 Every attribute of a relation must have a name.

 Null values are permitted for the attributes.
 Default values can be specified for an attribute automatically inserted if no other
value is specified for an attribute.
 Attributes that uniquely identify each tuple of a relation are the primary key.
Data item/Cells

The smallest unit of data in the table is the individual data item. It is stored at the intersection
of tuples and attributes.

Properties of data items:

 Data items are atomic.

 The data items for an attribute should be drawn from the same domain.
In the below example, the data item in the student table consists of Ajeet, 24 and Btech, etc.

Degree:

The total number of attributes that comprise a relation is known as the degree of the table.

For example, the student table has 4 attributes, and its degree is 4.

Cardinality:

The total number of tuples at any one time in a relation is known as the table's cardinality.
The relation whose cardinality is 0 is called an empty table.

For example, the student table has 5 rows, and its cardinality is 5.
Domain:

The domain refers to the possible values each attribute can contain. It can be specified using
standard data types such as integers, floating numbers, etc. For example, An attribute entitled
Marital_Status may be limited to married or unmarried values.

Consider an Employee Relation as shown below.

NULL Values

The NULL value of the table specifies that the field has been left blank during record
creation. It is different from the value filled with zero or a field that contains space.

For example, If we say that the person's height is null, it means that such a person exists, he
must have height but we don't know what the height was at the time the data was collected.
So we mark it as null and we explain that we don't know what the actual value is,

Now shall be discussing the examples showing missing or unknown information and
inapplicable data.

Consider an Employee Relation as shown below.

Difference between File System and DBMS

In this section, we would read up on the dissimilarities between File system approach and
DBMS in detail. First of all we understand the basic of both the approach after that we will
understand the differences between them with the help of some examples.

File System Approach

File based systems were an early attempt to computerize the manual system. It is also called a
traditional based approach in which a decentralized approach was taken where each
department stored and controlled its own data with the help of a data processing specialist.
The main role of a data processing specialist was to create the necessary computer file
structures, and also manage the data within structures and design some application programs
that create reports based on file data.

In the above figure:

Consider an example of a student's file system. The student file will contain information
regarding the student (i.e. roll no, student name, course etc.). Similarly, we have a subject file
that contains information about the subject and the result file which contains the information
regarding the result.

Some fields are duplicated in more than one file, which leads to data redundancy. So to
overcome this problem, we need to create a centralized system, i.e. DBMS approach.

DBMS:

A database approach is a well-organized collection of data that are related in a meaningful

way which can be accessed by different users but stored only once in a system. The various
operations performed by the DBMS system are: Insertion, deletion, selection, sorting etc.

In the above figure:

In the above figure, duplication of data is reduced due to centralization of data. The fields
such as Room, Sub_Name, and Marks which were being repeated in the file system approach
are not repeated in this database approach due to centralization of data from different tables
by joining those using relationships when needed on the basis of the common fields.
Duplicity cannot be eliminated completely because we may need Redundancy in some cases
to access data quickly. So we control it but not eliminate it.

There are the following differences between DBMS and File systems:
Introduction of DBMS

DBMS (Database Management System) is a software system that manages, stores, and
retrieves data efficiently in a structured format.

1. It allows users to create, update, and query databases efficiently.

2. Ensures data integrity, consistency, and security across multiple users and
applications.
3. Reduces data redundancy and inconsistency through centralized control.
4. Supports concurrent data access, transaction management, and automatic backups

Problems with Traditional File-Based Systems

Before the introduction of modern DBMS, data was managed using basic file systems on
hard drives. While this approach allowed users to store, retrieve and update files as needed, it
came with numerous challenges
 1. Data Redundancy: Duplicate entries across files
2. Inconsistency: Conflicting or outdated information
3. Difficult Access: Manual file search required
4. Poor Security: No control over data access
5. Single-User Access: No support for collaboration
6. No Backup/Recovery: Data loss was often permanent
A university file-based system storing data in separate files (e.g., Academics, Results,
Hostels) often faced these problems.

Components of DBMS Applications

Any DBMS based applications is made up of six key components that work together to
handle data effectively.

1. Hardware
 Physical devices like servers, disks, input-output devices (keyboard, monitor, printer).
 Stores and processes data; interfaces between real-world inputs and digital systems.
 Examples: Personal computer hard disk, RAM, network devices used for DBMS
operations.
2. Software
 Actual DBMS software like MySQL, Oracle, PostgreSQL.
 Includes the database engine, OS, network software, and application tools.
 Translates database access languages into operations.
3. Data
 Raw facts stored in structured or unstructured formats.
 Operational Data: Actual user data (e.g., name, age).
 Metadata: Data about data (e.g., storage time, size, data type).
 Core reason DBMS exists—to manage and store data efficiently.
4. Procedures
 Instructions and rules for using DBMS effectively.
 Covers setup, login/logout, data validation, backup, access control, and report
generation.
  Helps ensure consistent and secure use of the system.
5. Database Access Language
 Used to interact with the database (create, read, update, delete data).
 Examples: SQL, MyAccess, Oracle PL/SQL.
 DDL (Data Definition Language) – CREATE, ALTER, DROP
 DML (Data Manipulation Language) – INSERT, UPDATE, DELETE
6. People
 Users interacting with DBMS at different levels:
 Database Administrators (DBA) – Manage security, performance, user access.
 Developers – Build applications using the database.
 End Users – Use applications to access the database (e.g., students, employees).

Types of DBMS
There are several types of Database Management Systems (DBMS), each tailored to different
data structures, scalability requirements and application needs. The most common types are
as follows:

1. Relational Database Management System (RDBMS)

It organizes data into tables (relations) composed of rows and columns.
Uses primary keys to uniquely identify rows and foreign keys to establish relationships
between tables.
Queries are written in SQL (Structured Query Language), which allows for efficient data
manipulation and retrieval.
Examples: MySQL oracle, Microsoft SQL Server and Postgre SQL.

2. NoSQL DBMS
They are designed to handle large-scale data and provide high performance for scenarios
where relational models might be restrictive.
They store data in various non-relational formats, such as key-value pairs, documents, graphs
or columns.
These flexible data models enable rapid scaling and are well-suited for unstructured or semi-
structured data.
Examples: MongoDB, Cassandra, DynamoDB and Redis.

3. Object-Oriented DBMS (OODBMS)

It integrates object-oriented programming concepts into the database environment, allowing
data to be stored as objects.
Supports complex data types and relationships, making it ideal for applications requiring
advanced data modeling and real-world simulations.
Examples: ObjectDB, db4o.

4. Hierarchical Database
Organizes data in a tree-like structure, where each record (node) has a single parent and have
multiple children.
This model is similar to a file system with folders and subfolders.
It is efficient for storing data with a clear hierarchy, such as organizational charts or file
directories.
Navigation is fast and predictable due to the fixed structure.
It lacks flexibility and difficult to restructure or handle complex many-to-many relationships.
Example: IBM Information Management System (IMS).
5. Network Database
It uses a graph-like model to allow more complex relationships between entities.
Unlike the hierarchical model, it permits each child to have multiple parents, enabling many-
to-many relationships.
Data is represented using records and sets, where sets define the relationships.
It is more flexible than the hierarchical model and better suited for applications with complex
data linkages.
Example: Integrated Data Store (IDS), TurboIMAGE.

6. Cloud-Based Database
They are hosted on cloud computing platforms like AWS, Azure or Google Cloud.
They offer on-demand scalability, high availability, automatic backups and remote
accessibility.
These databases can be relational (SQL) or non-relational (NoSQL) and are maintained by
cloud service providers, reducing administrative overhead.
They support modern application requirements, including distributed access and real-time
analytics.
Example: Amazon RDS (for SQL), MongoDB Atlas (for NoSQL), Google BigQuery.

Database Languages
Database languages are specialized sets of commands and instructions used to define,
manipulate and control data within a database. Each language type plays a distinct role in
database management, ensuring efficient storage, retrieval and security of data. The primary
database languages include:

1. Data Definition Language (DDL)

DDL is the short name for Data Definition Language, which deals with database schemas and
descriptions, of how the data should reside in the database.

 CREATE: to create a database and its objects like (table, index, views, store
procedure, function and triggers)
 ALTER: alters the structure of the existing database
 DROP: delete objects from the database
 TRUNCATE: remove all records from a table, including all spaces allocated for the
records are removed
 COMMENT: add comments to the data dictionary
 RENAME: rename an object

2. Data Manipulation Language (DML)

DML focuses on manipulating the data stored in the database, enabling users to retrieve, add,
update and delete data.

 INSERT: insert data into a table

 UPDATE: updates existing data within a table
 DELETE: Delete all records from a database table
 MERGE: UPSERT operation (insert or update)
 CALL: call a PL/SQL or Java subprogram
 EXPLAIN PLAN: interpretation of the data access path
 LOCK TABLE: concurrency Control
3. Data Control Language (DCL)

DCL commands manage access permissions, ensuring data security by controlling who can
perform certain actions on the database.

 GRANT: Provides specific privileges to a user (e.g., SELECT, INSERT).

 REVOKE: Removes previously granted permissions from a user.
4. Transaction Control Language (TCL)

TCL commands oversee transactional data to maintain consistency, reliability and atomicity.

 ROLLBACK: Undoes changes made during a transaction.

 COMMIT: Saves all changes made during a transaction.
 SAVEPOINT: Sets a point within a transaction to which one can later roll back.

5. Data Query Language (DQL)

DQL is a subset of SQL used to retrieve data from a database without modifying it. Its main
command is SELECT, which allows users to fetch specific information based on their
requirements.

Applications of DBMS

 Banking: Manages accounts and transactions.

 E-commerce: Tracks products, orders, and customers.
 Healthcare: Stores patient records and diagnoses.
 Education: Handles student grades and schedules.
 Social Media: Manages user profiles and interactions.
 Data Science: Supports analytics and predictions.

DBMS Architecture
There are several types of DBMS Architecture that we use according to the usage
requirements.

1-Tier Architecture
2-Tier Architecture
3-Tier Architecture

1-Tier Architecture

In 1-Tier Architecture, the user works directly with the database on the same system. This
means the client, server and database are all in one application. The user can open the
application, interact with the data and perform tasks without needing a separate server or
network connection.
  A common example is Microsoft Excel. Everything from the user interface to the
logic and data storage happens on the same device. The user enters data, performs
calculations and saves files directly on their computer.
 This setup is simple and easy to use, making it ideal for personal or standalone
applications. It does not require a network or complex setup, which is why it's often
used in small-scale or individual use cases.
 This architecture is simple and works well for personal, standalone applications where
no external server or network connection is needed.

Advantages of 1-Tier Architecture

Below mentioned are the advantages of 1-Tier Architecture.

1. Simple Architecture: 1-Tier Architecture is the most simple architecture to set up, as
only a single machine is required to maintain it.
2. Cost-Effective: No additional hardware is required for implementing 1-Tier
Architecture, which makes it cost-effective.
3. Easy to Implement: 1-Tier Architecture can be easily deployed and hence it is
mostly used in small projects.

Disadvantages of 1-Tier Architecture

1. Limited to Single User: Only one person can use the application at a time. It’s not
designed for multiple users or teamwork.
2. Poor Security: Since everything is on the same machine, if someone gets access to
the system, they can access both the data and the application easily.
3. No Centralized Control: Data is stored locally, so there's no central database. This
makes it hard to manage or back up data across multiple devices.
4. Hard to Share Data: Sharing data between users is difficult because everything is
stored on one computer.

2-Tier Architecture
The 2-tier architecture is similar to a basic client-server model. The application at the client
end directly communicates with the database on the server side. APIs like ODBC and JDBC
are used for this interaction. The server side is responsible for providing query processing and
transaction management functionalities.

On the client side, the user interfaces and application programs are run. The application on
the client side establishes a connection with the server side to communicate with the DBMS.
For

Example: A Library Management System used in schools or small organizations is a classic

example of two-tier architecture.

Client Layer (Tier 1): This is the user interface that library staff or users interact with. For
example they might use a desktop application to search for books, issue them, or check due
dates.

Database Layer (Tier 2): The database server stores all the library records such as book
details, user information and transaction logs.
The client layer sends a request (like searching for a book) to the database layer which
processes it and sends back the result. This separation allows the client to focus on the user
interface, while the server handles data storage and retrieval.

Advantages of 2-Tier Architecture

 Easy to Access: 2-Tier Architecture makes easy access to the database, which makes
fast retrieval.
 Scalable: We can scale the database easily, by adding clients or upgrading hardware.
 Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture and Multi-Tier
Architecture.
 Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier Architecture.
 Simple: 2-Tier Architecture is easily understandable as well as simple because of
only two components.
Disadvantages of 2-Tier Architecture
  Limited Scalability: As the number of users increases, the system performance can
slow down because the server gets overloaded with too many requests.
 Security Issues: Clients connect directly to the database, which can make the system
more vulnerable to attacks or data leaks.
 Tight Coupling: The client and the server are closely linked. If the database changes,
the client application often needs to be updated too.
 Difficult Maintenance: Managing updates, fixing bugs, or adding features becomes
harder when the number of users or systems increases.

3-Tier Architecture
In 3-Tier Architecture, there is another layer between the client and the server. The client
does not directly communicate with the server. Instead, it interacts with an application server
which further communicates with the database system and then the query processing and
transaction management takes place. This intermediate layer acts as a medium for the
exchange of partially processed data between the server and the client. This type of
architecture is used in the case of large web applications.

Example: E-commerce Store

 User: You visit an online store, search for a product and add it to your cart.
 Processing: The system checks if the product is in stock, calculates the total price and
applies any discounts.
 Database: The product details, your cart and order history are stored in the database
for future reference.

Advantages of 3-Tier Architecture

 Enhanced scalability: Scalability is enhanced due to the distributed deployment of

application servers. Now, individual connections need not be made between the client
and server.
 Data Integrity: 3-Tier Architecture maintains Data Integrity. Since there is a middle
layer between the client and the server, data corruption can be avoided/removed.
  Security: 3-Tier Architecture Improves Security. This type of model prevents direct
interaction of the client with the server thereby reducing access to unauthorized data.

Disadvantages of 3-Tier Architecture

 More Complex: 3-Tier Architecture is more complex in comparison to 2-Tier

Architecture. Communication Points are also doubled in 3-Tier Architecture.
 Difficult to Interact: It becomes difficult for this sort of interaction to take place due
to the presence of middle layers.
 Slower Response Time: Since the request passes through an extra layer (application
server), it may take more time to get a response compared to 2-Tier systems.
 Higher Cost: Setting up and maintaining three separate layers (client, server and
database) requires more hardware, software and skilled people. This makes it more
expensive.

History of Database
A database is an organized collection of data that allows easy access, management, and
updating. The concept of databases evolved as the need to store and process large amounts of
data increased.

1. File-Based System (1950s – 1960s)

 Data was stored in flat files (text files).

 Each application had its own data files.

 Programming languages like COBOL and FORTRAN were used.

Problems:

 Data redundancy (same data stored multiple times)

 Data inconsistency

 Difficult to access and update data

 No data security

Example: Student data stored in separate files for each program.

2. Hierarchical Database Model (1960s)

 Data organized in a tree structure (parent–child relationship).
 Each parent can have many children, but each child has only one parent.

Advantages:

 Simple structure
  Fast data access

Disadvantages:

 Inflexible
 Complex relationships not supported

Example: IBM IMS (Information Management System)

3. Network Database Model (1970s)

 Data organized as a graph structure.
 A child can have multiple parents.

Advantages:

 More flexible than hierarchical model

 Supports complex relationships

Disadvantages:

Complex to design and maintain

Example: IDMS

4. Relational Database Model (1970s – Present)

 Proposed by Dr. E. F. Codd (1970).

 Data stored in tables (rows and columns).

 Uses SQL (Structured Query Language).

Advantages:

 Easy to understand

 Data integrity and security

 Reduces redundancy

Examples: Oracle, MySQL, SQL Server, PostgreSQL

This is the most widely used database model today.

5. Object-Oriented Database Model (1980s)

 Data stored as objects, similar to object-oriented programming.

 Supports complex data like images, audio, and video.

Advantages:
  Supports real-world objects

 Good for multimedia applications

Example: ObjectDB

6. Distributed Databases (1990s)

 Data stored across multiple locations connected by a network.

 Appears as a single database to users.

Advantages:

 High availability

 Better performance

 Fault tolerance

Example: Distributed SQL databases

7. NoSQL Databases (2000s)

 Designed for big data and high scalability.

 Handles unstructured and semi-structured data.

Types:

 Key-Value

 Document

 Column-Based

 Graph

Examples: MongoDB, Cassandra, Redis

8. Cloud Databases (2010s – Present)

 Databases hosted on cloud platforms.
 Accessible via the internet.

Advantages:

 Scalable
 Cost-effective
 High availability

Examples: Amazon RDS, Google Firestore, Azure SQL

 Data Independence
Data Independence is a fundamental concept in Database Management Systems (DBMS) that
refers to the ability to modify the schema at one level of the database without affecting the
schema at the next higher level. This concept ensures that changes in how data is stored or
structured internally do not impact how users or applications access and interact with the
data.

Data independence refers to the ability to change the schema (structure) at one level without
affecting the schema at higher or lower levels in the DBMS architecture. It ensures that
changes in the data storage and structure do not impact the overall functioning or application
programs using the database.

There are two key types of data independence:

1. Physical Data Independence

2. Logical Data Independence

Why is Data Independence Important?

 Reduces Maintenance: Developers don't need to update applications every time the
database structure changes.
 Increases Flexibility: The database can be reorganized or optimized internally
without affecting user queries.
 Supports Long-Term Growth: As business needs evolve, the database can be updated
without breaking existing systems.

DBMS Three-Level Architecture

To understand data independence, it’s essential to know how DBMS is organized:

 Internal Level : Deals with physical storage (files, indexing, compression).

 Conceptual Level : Describes structure like tables, fields and relationships.
 View Level : Defines how users and applications see the data.
DBMS divides the database into three levels:

Internal Level – How data is physically stored

Conceptual Level – Logical structure of the database

External Level – User views (what users see)

Data independence exists between these levels:

 Physical Data Independence: Change in the internal level without affecting the
logical level.
 Logical Data Independence: Change in the logical level without affecting the view
level.

Logical Data Independence

Ability to change the logical structure (tables, columns, relationships) without affecting
external views or application programs. Purpose of this to allow the database structure to
evolve without impacting user access or requiring changes in application code.
Why it matters:

 Helps modify the structure of the database as business needs evolve.

 Applications and user interfaces remain unaffected.
Example:

 Adding a column like email in the employees table.

 Creating a new relationship between two tables.
 Merging two tables into a view for simplified access.
Benefits:

 Easier maintenance of application code.

 Allows smoother updates in growing systems.
 Helps support new business requirements without rewriting existing queries.

 Logical Data Independence is the ability to change the logical

structure of the database without affecting the application programs.

What kind of changes

 Add a new column

 Remove a column

 Split a table

 Merge tables

 Add new relationships

Example
Original table:

STUDENT (RollNo, Name, Marks)

Modified table:

STUDENT (RollNo, Name, Marks, Address)

Physical Data Independence

 Physical data independence can be defined as the capacity to change the internal
schema without having to change the conceptual schema.
 If we do any changes in the storage size of the database system server, then the
Conceptual structure of the database will not be affected.
 Physical data independence is used to separate conceptual levels from the internal
levels.
  Physical data independence occurs at the logical interface level.

For example:
 A new index is added to a table to improve the performance.
 Existing index is dropped when it is no longer needed.

Database system Vs File system

File System
The file system is basically a way of arranging the files in a storage medium like a hard disk.
The file system organizes the files and helps in the retrieval of files when they are required.
File systems consist of different files which are grouped into directories. The directories
further contain other folders and files. The file system performs basic operations like
management, file naming, giving access rules, etc.

A File System is a traditional way of storing data where information is saved in separate files
on a storage device (hard disk, USB, etc.).
Each application manages its own files, formats, and access methods.
Example
 Student data stored in [Link]
 Marks stored in [Link]
 Attendance stored in [Link]
 Each file is independent.
Characteristics of File System
 Data is stored in flat files (text/binary)
 Programs control how data is stored and accessed
 No centralized control
 Limited security and sharing

DBMS (Database Management System)

Database Management System is basically software that manages the collection of related
data. It is used for storing data and retrieving the data effectively when it is needed. It also
provides proper security measures for protecting the data from unauthorized access. In
Database Management System the data can be fetched by SQL queries and relational algebra.
It also provides mechanisms for data recovery and data backup.
Example:
Oracle, MySQL, MS SQL server.

A Database System stores data in a structured and centralized manner using a DBMS
(Database Management System) such as MySQL, Oracle, SQL Server, etc.
The DBMS handles:
 Data storage
 Data retrieval
 Security
 Consistency
 Backup & recovery
Example
A Student database with tables:
 Student
 Marks
 Attendance

All related data is connected.

Characteristics of Database System
 Data stored in tables (rows and columns)
 Centralized control through DBMS
 Multiple users can access data simultaneously
 High security and integrity

Difference Between File System and DBMS

 Data models & Types of Data Models
Data Model
A data model is a collection of concepts, rules, and standards used to describe:
 The structure of data
 The relationships among data
 The constraints on data
 The operations that can be performed on data
A data model tells how data is stored, organized, and accessed in a database.

Objectives of Data Models

 To represent real-world entities in a database
 To define relationships between data
 To ensure data consistency and integrity
 To provide a blueprint for database design
Components of a Data Model
1. Data Structure – How data is organized (tables, records, fields)
2. Relationships – How data items are related
3. Constraints – Rules on data (keys, domain rules)
4. Operations – Insert, update, delete, retrieve

Types of Data Models

Data models are broadly classified into three categories:

1. Conceptual (High-Level) Data Models

 Focus on what data is required, not how it is stored
 Used by database designers and users
 Independent of DBMS

Example: Entity–Relationship (ER) Model

ER Model
Represents data using:
 Entities (objects)
 Attributes (properties)
 Relationships (associations)
Example:
Entity: Student
Attributes: RollNo, Name, Age
Relationship: Student enrolls in Course

Advantages
 Easy to understand
 Good for database planning
 Hides technical details
Disadvantages
 Cannot be implemented directly
 No storage details

2. Logical (Representational) Data Models

 Describe data in a logical structure
 Used to design databases
 Independent of physical storage

Types of Logical Data Models

a) Relational Data Model
 Data is stored in tables (relations)
 Rows → Tuples
 Columns → Attributes
 Uses primary keys and foreign keys

Example:
StudentID Name Age
101 Rahul 20

Advantages
 Simple and flexible
 Data integrity using constraints
 Most widely used (MySQL, Oracle, SQL Server)

Disadvantages
  Complex queries for large databases
 Performance issues with many joins

b) Hierarchical Data Model

 Data organized in a tree structure
 One parent → many children
 One-to-many relationship

Example:
markdown
Copy code
Company
└── Department
└── Employee
Advantages
 Fast access
 Simple structure

Disadvantages
 No many-to-many relationships
 Difficult to modify

c) Network Data Model

 Data organized as a graph
 Supports many-to-many relationships
 Uses pointers
Advantages
 Flexible relationships
 Efficient for complex data
Disadvantages
 Complex design
 Difficult to maintain

d) Object-Oriented Data Model

 Data stored as objects
  Supports:
Encapsulation
Inheritance
Polymorphism
Advantages
 Suitable for complex data (multimedia, CAD)
 Reusability
Disadvantages
 Complex
 Less popular than relational model

Data Models
A Data Model in Database Management System (DBMS) is the concept of tools that are
developed to summarize the description of the database. Data Models provide us with a
transparent picture of data which helps us in creating an actual database. It shows us from the
design of the data to its proper implementation of data.
Types of Relational Models
1. Conceptual Data Model
2. Representational Data Model
3. Physical Data Model

1. Conceptual Data Model

The conceptual data model describes the database at a very high level and is useful to
understand the needs or requirements of the database. It is this model, that is used in the
requirement-gathering process i.e. before the Database Designers start making a particular
database. One such popular model is the entity/relationship model (ER model). The E/R
model specializes in entities, relationships, and even attributes that are used by database
designers. In terms of this concept, a discussion can be made even with non-computer
science(non-technical) users and stakeholders, and their requirements can be understood.
Entity-Relationship Model (ER Model): It is a high-level data model which is used to define
the data and the relationships between them. It is basically a conceptual design of any
database which is easy to design the view of data.

Components of ER Model:

Entity: An entity is referred to as a real-world object. It can be a name, place, object, class,
etc. These are represented by a rectangle in an ER Diagram.
Attributes: An attribute can be defined as the description of the entity. These are represented
by Ellipse in an ER Diagram. It can be Age, Roll Number, or Marks for a Student.
Relationship: Relationships are used to define relations among different entities. Diamonds
and Rhombus are used to show Relationships.

Characteristics of a conceptual data model

 Offers Organization-wide coverage of the business concepts.
 This type of Data Models are designed and developed for a business audience.
 The conceptual model is developed independently of hardware specifications like data
storage capacity, location or software specifications like DBMS vendor and
technology. The focus is to represent data as a user will see it in the “real world.”
Conceptual data models known as Domain models create a common vocabulary for all
stakeholders by establishing basic concepts and scope

2. Representational Data Model

This type of data model is used to represent only the logical part of the database and does not
represent the physical structure of the database. The representational data model allows us to
focus primarily, on the design part of the database. A popular representational model is a
Relational model. The relational Model consists of Relational Algebra and Relational
Calculus. In the Relational Model, we basically use tables to represent our data and the
relationships between them. It is a theoretical concept whose practical implementation is
done in Physical Data Model.
The advantage of using a Representational data model is to provide a foundation to form the
base for the Physical model.
Characteristics of Representational Data Model
 Represents the logical structure of the database.
 Relational models like Relational Algebra and Relational Calculus are commonly
used.
 Uses tables to represent data and relationships.
 Provides a foundation for building the physical data model.

3. Physical Data Model

The physical Data Model is used to practically implement Relational Data Model.
Ultimately, all data in a database is stored physically on a secondary storage device such as
discs and tapes. This is stored in the form of files, records, and certain other data structures. It
has all the information on the format in which the files are present and the structure of the
databases, the presence of external data structures, and their relation to each other. Here, we
basically save tables in memory so they can be accessed efficiently. In order to come up with
a good physical model, we have to work on the relational model in a better way. Structured
Query Language (SQL) is used to practically implement Relational Algebra.
This Data Model describes HOW the system will be implemented using a specific DBMS
system. This model is typically created by DBA and developers. The purpose is actual
implementation of the database.

Characteristics of a physical data model:

 The physical data model describes data need for a single project or application though
it may be integrated with other physical data models based on project scope.
 Data Model contains relationships between tables that which addresses cardinality and
null ability of the relationships.
 Developed for a specific version of a DBMS, location, data storage or technology to
be used in the project.
 Columns should have exact datatypes, lengths assigned and default values.
 Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are
defined
Some Other Data Models
1. Hierarchical Model
The hierarchical Model is one of the oldest models in the data model which was developed by
IBM, in the 1950s. In a hierarchical model, data are viewed as a collection of tables, or we
can say segments that form a hierarchical relation. In this, the data is organized into a tree-
like structure where each record consists of one parent record and many children. Even if the
segments are connected as a chain-like structure by logical associations, then the instant
structure can be a fan structure with multiple branches. We call the illogical associations as
directional associations.

2. Network Model
The Network Model was formalized by the Database Task group in the 1960s. This model is
the generalization of the hierarchical model. This model can consist of multiple parent
segments and these segments are grouped as levels but there exists a logical association
between the segments belonging to any level. Mostly, there exists a many-to-many logical
association between any of the two segments.

3. Object-Oriented Data Model

In the Object-Oriented Data Model, data and their relationships are contained in a single
structure which is referred to as an object in this data model. In this, real-world problems are
represented as objects with different attributes. All objects have multiple relationships
between them. Basically, it is a combination of Object Oriented programming and a
Relational Database Model.

Schema and Instances

In a Database Management System (DBMS), two main terms that define the structure and
content of a database are Schema and Instance. The schema refers to the overall design or
blueprint of the database, describing its structure (like tables, columns and relationships). It
remains relatively stable over time. On the other hand, an instance represents the actual data
within the database at any particular moment, which can change frequently as the database is
updated.

Schema
A schema in DBMS refers to the overall design or blueprint of a database. It describes how
the data will be organized, how the relationships between different entities will be maintained
and how constraints will be applied. Schemas are important to defining the structure of the
database and once defined, they remain relatively stable over time.

Key Characteristics
 Acts as a framework for data storage and manipulation.
 Defined during the database design phase.
 Changes to the schema are rare and require careful planning.

Types of Schema:
1. Logical Schema: Describes the logical structure, such as tables, columns, data types
and relationships.
2. Physical Schema: Details how the data is stored physically on hardware (e.g., file
paths, indexing).
3. View Schema: Defines how data is presented to end-users, possibly as a subset or a
formatted view.
Example: Let's say a table teacher in our database named school, the teacher table requires
the name, dob and doj in their table so we design a structure as:
Teacher (
name VARCHAR,
dob DATE,
doj DATE)

Advantages of Schema

 Data Consistency and Integrity: A well-defined schema enforces rules and constraints
(like data types, primary keys, foreign keys, unique constraints, etc.).
 Structured Organization of Data: Schema provides a systematic and logical structure to
store data.
 Simplifies Data Access: By knowing the schema, users and developers can easily query
data using SQL, knowing exactly where and how data is stored.
 Supports Data Security and Authorization: Schema-level definitions can help in
applying access controls and roles, allowing only authorized users to view or manipulate
certain parts of the data.
 Facilitates Database Design and Documentation: The schema serves as documentation
for the database, making it easier to understand its structure and relationships.
 Enables Logical and Physical Data Independence: Through logical and physical
schema separation, changes in storage details (physical schema) do not affect how users
interact with data (logical schema).

Disadvantages of Schema

 Rigidity and Inflexibility: Once a schema is defined and implemented, modifying it can
be challenging, especially in production environments.
 High Complexity in Large Systems: For complex systems with hundreds of tables and
relationships, schema design becomes very complex and time-consuming.
 Requires Skilled Designers: Designing an optimal schema is not trivial. It demands deep
knowledge of data modelling, normalization and performance tuning.
 Limited Adaptability to Changing Requirements: If business requirements evolve
(e.g., new data fields or relationships), adapting the schema might require schema
migration scripts, application code changes and extensive testing, making it less agile.
 Overhead in Schema Maintenance: As the database grows and evolves, maintaining the
schema can be overhead-heavy, especially when dealing with distributed systems, version
control of schema files and synchronization across environments (development, staging,
production).
 Dependency on Schema Knowledge: Application developers and users must be aware
of the schema structure to work effectively with the data.

Instance
An instance of DBMS refers to real data present in a database at some particular point in
time. It refers to the content in the database in as much as it refers to the structure defined
under a particular schema at a given point.

Key Characteristics

 Represents the current state of the database.

 Dynamic in nature data can change every second.
 Reflects the real usage and transactions happening in the system.
Example: Let say a table teacher in our database whose name is School,
suppose the table has 50 records so the instance of the database has 50
records for now and tomorrow we are going to add another fifty records so
tomorrow the instance has a total of 100 records. This is called an
instance.

Advantages of Instance

 Real-Time Representation of Data: An instance reflects the current state of the database
at a specific point in time.
 Flexibility in Data Handling: While schemas are rigid and rarely change, instances are
highly dynamic.
 Supports Day-to-Day Operations: Since instances store actual data, they are essential
for performing CRUD operations (Create, Read, Update, Delete) that are part of daily
business processes.
 Basis for Decision-Making: Instances provide the live data required for data analytics,
forecasting, dashboards and reporting tools.
 Multiple Instances Over Time Enable History Tracking: Though each instance is a
snapshot in time, by capturing them periodically (e.g., through backups or logs), one can
recreate historical data trends.
Disadvantages of Instance

 Volatile and Continuously Changing: Instances change constantly due to ongoing

operations, making it difficult to maintain a consistent view of data over time unless
snapshots or backups are taken.
 Potential for Data Inconsistency: If constraints (like foreign keys, validations) are not
properly enforced, bad or corrupt data can be entered into the instance, leading to
inaccurate or inconsistent data.
 Difficult to Audit Without Proper Logging: Since data in the instance can change
frequently, without audit trails or version control, it may be impossible to track who
changed what and when.
 Performance Overhead on Frequent Access: Real-time access and updates to instances
in large-scale systems can cause performance issues if not properly optimized with
indexing, caching or partitioning.
 Storage and Memory Concerns: Instances grow as more data is inserted into the
database.
 Dependent on Schema Integrity: While schema and instance are separate, a poorly
designed schema can make instance data difficult to interpret or use.