Database & DBMS Concepts

Data
• Data is the raw form of information, a collection of facts, figures, symbols or observations that
represent details about events, objects or phenomena.
• Structured data: Highly organized data that can be easily searched and analyzed, like data in
a database table.
• Unstructured data: Data that is not organized in a predefined way, such as text documents,
images, or videos.
Importance of Data
• Decision-making and insights: data helps to make better decisions.
• Transformed into insights with the help of analytics.
• AI/ML and Innovation: Data is the fuel for AI & ML.
• More and higher-quality data means better training, more accurate predictions.
• Digital transformation: Generate capabilities i.e. from real-time analytics to personalized
services.
Database
The database is a collection of inter-related data which is used to retrieve, insert and delete the data
efficiently.
• Data is stored or organized in tables, schemas, views, and reports.
• It allows efficient retrieval, insertion, and deletion of data.
Example: A college database stores information about admin, staff, students, and faculty.

Database Management System (DBMS)

DBMS is software package used to store and manage databases. A DBMS acts as an interface between
users/ applications and a database, allowing them to create, read, update, and delete data
efficiently. DBMS ensures security, data consistency, and multi-user access.
Examples: MySQL, Oracle, SQL Server.
File System
A file system organizes files in storage devices like hard disks. Files are stored in directories and
subfolders. The file system performs basic operations like management, file naming, giving access
rules, etc.
Examples: NTFS (New Technology File System), EXT (Extended File System).
Advantages of DBMS
1. Reducing Data Redundancy
The file-based data management systems contained multiple files that were stored in many different
locations in a system. There were sometimes multiple copies of the same file which led to data
redundancy.
This is prevented in a database as there is a single database and any change in it is reflected immediately.
Because of this, there is no chance of encountering duplicate data.
2. Sharing of Data
In a database, the users of the database can share the data among themselves. There are various levels
of authorization to access the data, and consequently the data can only be shared based on the correct
authorization protocols being followed.
3. Data Integrity
Data integrity means that the data is accurate and consistent in the database. Data Integrity is very
important as there are multiple databases in a DBMS. All of these databases contain data that is visible
to multiple users. So, it is necessary to ensure that the data is correct and consistent in all the databases
and for all the users.
4. Data Security
Data Security is vital concept in a database. Only authorised users should be allowed to access the
database and their identity should be authenticated using a username and password.
5. Backup and Recovery
Database Management System automatically takes care of backup and recovery. The users don't need
to backup data periodically because this is taken care of by the DBMS. Moreover, it also restores the
database after a crash or system failure to its previous condition.
6. Data Consistency
Data consistency is ensured in a database because there is no data redundancy. Any changes made to
the database are immediately reflected to all the users and there is no data inconsistency.
Characteristics of DBMS
• DBMS can stores any type of data.
• Supports ACID properties.
• Allows multiple users to access at a same time.
• Supports backup and recovery, protects data from damage or loss.
• Provides multiple views for different users.
• Follows normalization to reduce redundancy.
• It provides query language for CRUD (Create, Read, Update, Delete) operations.

Difference between File System and DBMS:

Database Schema
• A schema is the logical representation of a database. It defines how data is logically organized
and structured.
• Design or blueprint of the database.
• Defines structure of tables, rows, columns, constraints, and relationships between data.
• Acts as the skeleton of the database.
Metadata / Data Dictionary
• Metadata is known as the data about the data.
• Stores schema details and constraints inside DBMS.
• Helps DBMS understand structure and rules of data.
• Make it easier to manage, organize, access, and interpret the actual raw data.
Data Constraints
• Restrictions or conditions applied on columns while creating tables, control what type of data
can be stored.
• Ensure accuracy and reliability of data.
Examples: NOT NULL, UNIQUE, PRIMARY KEY, CHECK.
Database Instance
• Current state of the database at a given time.
• Contains values stored in database variables.
• Schema remains same; instances change frequently.
• Defines the complete database environment and its components.
These Customer table 5 rows contain the information about Customers stored in database at the current
point in time. So, these are the instances of Customers table.
Query
• A command written to retrieve or manipulate data. A query is used to access data from the
database.
• Used to retrieve or manipulate data from the database with SELECT, INSERT, UPDATE,
DELETE operations.
Data Manipulation
In a database, we can easily manipulate data using the three main operations that is Insertion, Deletion,
and updation.
• Insertion – refers to adding new data.
• Deletion – refers to removing existing data.
• Updation – refers to modifying existing data.
Data Engine
• This is Core component, used to create and manage query processing, Data Storage & Retrieval
of data files.
• Executes CRUD operations and optimizes database queries.
• Transaction Management: Ensures ACID (Atomicity, Consistency, Isolation, Durability)
properties

Three Schema Architecture:

• It is also known as ANSI/SPARC architecture.
• This framework is used to describe the structure of a specific database system.
• Ensures data security, management and easy accessibility.
• Separates physical storage, logical design, and user views.

(ANSI/SPARC architecture)
Internal Schema (Physical schema)
• Defines physical storage structure or storage details of data on disk.
• How the data will be stored in Specifies data blocks and access paths.
• Describe complex low-level data structures in detail.
• Handles Storage space allocation using structures like hashing, B-Trees.
Conceptual Schema (Logical Level)
• Logical representation of entire database (entities, attributes, relationships).
• What data are to be stored and what relationship exists among data.
• Hides physical storage complexity.
• Programmers and DBA work at this level.
• Example: Define “Student” & “Course” tables and their link
External Schema (View Schema)
• Provide User-specific view of data.
• Show only relevant data, improves security by hiding unnecessary data & complexity.
• Describes the end user interaction with database systems.
• Example: A student sees their marks, while a teacher sees all students grades for a course.

Database Abstraction:
• A major purpose of a database is to provide the user with only as much information as is
required of them. Hides internal complexity from users.
• ordered through multiple levels of abstraction to facilitate their interaction with the system.
• The different levels of the database are implemented through three layers or levels:
1. Internal Level (Physical Level)
2. Conceptual Level
3. External Level (View Level)
Internal Level (Physical Level)
• Lowest level of abstraction.
• Describes actual storage of data (how the data is stored concretely on the storage medium)
• Uses physical data models like B-Trees, Hashing.

Conceptual Level
• Logical structure of entire database.
• Describes what data is concretely stored in the database and relationships between the data.
• Hides internal storage details.
• Users at this level are not concerned with how these logical data structures will be implemented
at the physical level.
• Used by DBAs and programmers.

External Level (View Level)

• It is the level closest to users and is related to the way the data is viewed by individual
users (Contains multiple user-specific views.). Users see only relevant data; rest is hidden.