DBMS vs File Systems

A Database Management System (DBMS) and a File System are both technologies
used for organising and managing data, but they serve different purposes and have
distinct characteristics. Here's a comparison between the two:

1. Purpose:

●​ DBMS: A DBMS is designed to manage large amounts of structured data in a

way that facilitates efficient storage, retrieval, modification, and analysis of
the data. It provides a structured environment for creating, managing, and
interacting with databases.
●​ File System: It provides a way to manage individual files and directories but
lacks the advanced capabilities of data organisation and manipulation
provided by a DBMS.

2. Data Structure:

●​ DBMS: A DBMS uses a structured data model, typically based on tables, rows,
and columns. The data is organised into tables with predefined schemas,
allowing for relationships, constraints, and data integrity.
●​ File System: A file system organises data into files and directories. Each file
can be of any format and structure, and there is usually no inherent
relationship between files. Metadata (file attributes) might be associated with
each file.

3. Data Integrity and Relationships:

●​ DBMS: DBMSs offer mechanisms for enforcing data integrity through

constraints, such as primary keys, foreign keys, and unique constraints. They
also support relationships between different tables, ensuring referential
integrity.
●​ File System: File systems generally do not enforce relationships or data
integrity between files. Maintaining consistency and integrity across related
files is the responsibility of the application using the files.

4. Query and Manipulation:

●​ DBMS: DBMSs provide query languages (e.g., SQL) that allow users to retrieve
and manipulate data using powerful querying capabilities.
●​ File System: File systems do not provide advanced querying capabilities.
Accessing and manipulating data usually involve reading or writing entire files,
which can be inefficient for complex data retrieval tasks.
DBMS Three Schema Architecture

1. Internal Schema (Physical View):

●​ The internal schema represents the physical storage and access methods
used to implement the database on the underlying hardware.
●​ It includes details about how data is stored on disk, indexing methods, data
compression, and other low-level considerations.

2. Conceptual Schema (Logical View):

●​ The conceptual schema represents the logical organisation of the entire

database for the enterprise.
●​ It defines the overall structure of the data, including tables, relationships,
constraints, and security measures.
●​ The conceptual schema is designed to be independent of the physical storage
details and is often presented using an Entity-Relationship Diagram (ERD) or a
similar modelling technique.

3. External Schema (User View):

●​ This is the highest level of abstraction and represents the way individual users
or user groups can see the data.
●​ Each external schema corresponds to a specific user's or application's view of
the database, defining what data that user can access and how it is
presented.
●​ Users interact with the database through their external schemas, using
queries and updates tailored to their needs.

Benefits of the Three Schema Architecture:

●​ Data Independence: The separation between external, conceptual, and

internal schemas provides a high degree of data independence. Changes
made at one level can be isolated from the others, minimising the impact of
modifications.
●​ Flexibility: External schemas allow different user groups to interact with the
database according to their specific needs, without affecting the overall
database structure.
●​ Maintenance: Modifications to the conceptual or internal schema can be
managed more easily, as they are isolated from users' views and application
requirements.
Data independence in a Database Management System (DBMS) :

It refers to the concept that the way data is stored, organised, and accessed can be
modified without affecting the way users, applications, and queries interact with the
data.

It provides a separation between the logical and physical aspects of the database,
allowing changes to be made at one level without requiring corresponding changes
at other levels.

There are two types of data independence: logical data independence and physical
data independence.

1. Logical Data Independence: Logical data independence refers to the ability to

change the logical schema or structure of the database without affecting the
external schemas or the applications that use the data.​
For example, if the structure of a table is changed (e.g., adding or removing
columns), applications and queries that use the data should not need to be updated
as long as the meaning of the data remains the same. This is particularly important
for minimising disruptions during database evolution and system upgrades.

2. Physical Data Independence: Physical data independence refers to the ability to

modify the physical storage and access methods of the database without affecting
the conceptual or external schemas. Changes made at the physical level, such as
reorganising data storage, indexing methods, or storage optimization techniques,
should not require changes to the way users or applications interact with the data.​
For example, if the database's storage structure is reorganised to improve
performance, applications and queries should not need to be rewritten as long as the
logical structure and semantics of the data remain unchanged.

Data Model in DBMS

At logical level, data model is a logical/conceptual representation of how data is

organised, stored, and accessed within a database (ER Model, Relational Model,
Hierarchical Model).

It defines the structure of the database, including the types of data that can be
stored, the relationships between different pieces of data, and the constraints that
govern the data's behaviour.
1. Relational Data Model: The relational data model organises data into tables
(relations) consisting of rows (tuples) and columns (attributes). It represents
relationships between tables using keys, particularly primary keys and foreign keys.
The relational model is based on the principles of set theory and provides a clear and
structured way to represent data.

Examples of relational database management systems (RDBMS) include MySQL,

PostgreSQL, and Microsoft SQL Server.

2. Entity-Relationship (ER) Model: The ER model is used to visualise and design the
logical structure of a database. It represents entities (objects, concepts, or things)
and their relationships using diagrams. Entities have attributes that define their
properties, and relationships describe how entities are related to each other. The ER
model is often used as a precursor to designing the database schema in the
relational model.

3. Hierarchical Data Model: In a hierarchical model, data is organised in a tree-like

structure with a single root and parent-child relationships between nodes. Each
record has one parent and zero or more children. This model was widely used in
early database systems but is less common today due to its limitations in handling
complex relationships.

4. NoSQL Data Models: NoSQL database uses various data models to handle data
that doesn't fit well into the rigid structure of relational databases.

Examples of NoSQL data models include document-oriented, key-value, columnar,

and graph models. NoSQL databases are designed to handle large-scale,
unstructured, or semi-structured data.

Why do we need SQL if we have NoSQL :

SQL (Structured Query Language) and NoSQL (Not Only SQL) are two different
approaches to manage and manipulate data in databases, each with its own
strengths and weaknesses. The choice between SQL and NoSQL depends on the
specific requirements of the application and the nature of the data being stored.

Here are some reasons why SQL is still relevant even when NoSQL databases are
available:
[Link] Integrity and Structured Data: SQL databases, often based on the RDBMS,
which provides a structured way to organise and ensure the integrity of data. If our
data has a well-defined structure and requires strong consistency and relational
integrity(such as enforcing foreign key relationships), SQL databases are a better
choice.

2. ACID Properties: SQL database offers ACID properties, which ensure data integrity
and consistency even in the case of failures. This makes them a suitable choice for
applications where transactional reliability is critical, such as financial systems.

3. Security and Access Control: SQL databases often provide more advanced
security features, including user authentication, encryption etc making them suitable
for applications that require strict data access controls.

However, NoSQL databases have their own advantages:

1. Flexibility in Handling Unstructured data : NoSQL databases are more flexible in

handling unstructured or semi-structured data, making them suitable for applications
that deal with rapidly changing data formats.

2. Scalability: NoSQL databases are designed to scale out easily across multiple
servers or clusters, making them suitable for applications with high volumes of data
and traffic.

3. Performance: NoSQL databases can offer better performance in certain

scenarios, especially when it comes to simple read and write operations.

4. Distributed Systems: Many NoSQL databases are designed as distributed

systems, which can provide better fault tolerance and availability compared to some
traditional SQL databases.

Database Languages:

Database languages are designed in order to interact with databases.

These languages provide a way to define, manipulate, retrieve, and manage data
stored in a database management system (DBMS).

There are several types of database languages, each serving a specific purpose
within the database environment. Here are some important types of database
languages:
1. Data Definition Language (DDL): DDL is used to define and manage the structure
of the database objects. It includes commands to create, modify, and delete
database objects such as tables, indexes, views, and schemas. Common DDL
commands include CREATE, ALTER, and DROP:- (CAD) (COD: cash on delivery).

2. Data Manipulation Language (DML): DML is used to interact with the data stored
in the database. It allows you to insert, update, retrieve, and delete data records.
Common DML commands include :

SELECT, INSERT, DELETE,UPDATE:-(SIDU–SIDDHU).

3. Query Languages: Query languages are used to retrieve data from the database.
SQL (Structured Query Language) is the most common query language used in
relational databases. It allows you to formulate complex queries to retrieve specific
subsets of data.

4. Data Control Language (DCL): DCL is used to control requests for accessing
database objects by using GRANT and REVOKE commands.

5. Transaction Control Language (TCL): TCL is used to manage database

transactions, ensuring data integrity and consistency. It includes commands to start,
commit, or roll back transactions. Common TCL commands include COMMIT,
ROLLBACK, SAVEPOINT.

6. Procedural Languages: Some database systems provide procedural extensions

that allow you to write procedural code (such as stored procedures, functions, and
triggers) that can be executed within the database. Examples include PL/SQL for
Oracle databases and T-SQL for Microsoft SQL Server.

7. Object-Relational Languages: These languages combine object-oriented

programming concepts with relational databases, allowing you to work with complex
data types and create user-defined data types, methods, and inheritance hierarchies.

8. NoSQL Query Languages: NoSQL databases often come with their own query
languages tailored to the specific data model they use. For example, MongoDB uses
a query language to work with its JSON-like documents, and Neo4j uses Cypher for
querying graph databases.

9. Host Languages and APIs: These are programming languages and interfaces that
allow applications to interact with databases programmatically. Examples include
Java Database Connectivity (JDBC), Python's Database API (DB-API), and .NET's
Entity Framework.
Strong and Weak Relationship
In the context of a Database Management System (DBMS), strong and weak
relationships refer to two types of associations or connections between entities in a
database schema, particularly in entity-relationship modelling (ERM). These
concepts help define the degree of dependency between entities in a relational
database. Let's explore both types of relationships:

1. Strong Relationship:

●​ Definition: A strong relationship aka a total relationship, indicates that one

entity (the "parent" or "owner" entity) is entirely dependent on another entity
(the "child" or "subordinate" entity) for its existence.
●​ Notation: In an entity-relationship diagram (ERD), a strong relationship is
represented using a solid line connecting the parent entity to the child entity,
often accompanied by a crow's foot notation (an inverted "T" shape) at the
end of the line.
●​ Example: In a library database, a "Library" entity may have a strong
relationship with a "Book" entity. This means that a library cannot exist
without books in its collection. If all books were removed, the library would no
longer exist.

2. Weak Relationship:

●​ Definition: A weak relationship aka partial relationship, indicates that the

existence of one entity is not entirely dependent on the presence of another
entity. In other words, the child entity can exist independently of the parent
entity.
●​ Notation: In an ERD, a weak relationship is represented using a dashed or
double line connecting the parent entity to the child entity, often accompanied
by a double oval notation at the end of the line.
●​ Example: In a hospital database, a "Patient" entity may have a weak
relationship with a "Medical History" entity. This means that a patient can
exist without having a medical history, but if a medical history is created, it is
associated with a specific patient.

Foreign Key
A foreign key is a database constraint that establishes a link or relationship between
two tables in a relational database. It is a fundamental concept in database
management systems (DBMS) and is used to maintain referential integrity, which
ensures that the data in the database remains consistent and accurate.

Here are the key points about foreign keys:

1.​ Relationship between Tables: A foreign key is used to define a relationship

between two tables in a database. These tables are typically referred to as the
"parent" table and the "child" table.
​
2.​ Column(s) Link: A foreign key is a column or a set of columns in the child
table that is used to reference the primary key of the parent table. This
establishes a connection between the data in the two tables.
​
3.​ Referential Integrity: The primary purpose of a foreign key is to enforce
referential integrity. Referential integrity ensures that data in the child table
corresponds to valid data in the parent table. In other words, it prevents the
creation of "orphan" records in the child table that do not have a
corresponding record in the parent table.
​
4.​ Cascade Actions: Depending on the database system and the foreign key
constraints defined, you can specify actions to be taken when records in the
parent table are modified or deleted. Common options include cascading
updates (update child records when the parent record is updated) and
cascading deletes (delete child records when the parent record is deleted).
​ Syntax: In SQL, foreign keys are defined using the FOREIGN KEY constraint.
Here's a basic syntax example:
​
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
 ​ In this example, the CustomerID column in the Orders table is a foreign key
that references the CustomerID column in the Customers table.

Foreign keys are essential for maintaining data integrity and ensuring that database
relationships are properly enforced. They play a critical role in relational databases
and are a key component of database design.

Surrogate Key :-
A surrogate key is an artificial or synthetic key used in a database table to uniquely
identify each record or row. Unlike natural keys, which are based on real-world data
attributes (e.g., a person's Social Security Number, a product's ISBN), surrogate keys
are generated or assigned by the database system and have no inherent meaning or
significance outside of the database. They are primarily used for the purpose of
uniquely identifying records within a table.

Here are some key characteristics and benefits of surrogate keys:

1.​ Uniqueness: Surrogate keys are designed to be unique within a table, ensuring
that each record can be uniquely identified. This is essential for maintaining
data integrity and avoiding duplicate records.
​ Lack of Meaning: Surrogate keys have no inherent meaning or significance.
They are typically numeric or alphanumeric values generated by the database
management system, such as auto-incrementing integers or universally
unique identifiers (UUIDs).
​
2.​ Independence: Surrogate keys are independent of the underlying data and are
not subject to changes in the data. This makes them stable and reliable
identifiers even when the attributes used for natural keys change.
​ Performance: Surrogate keys can improve database performance, especially
in scenarios where natural keys are lengthy or complex. Since they are
typically numeric and smaller in size, they can be more efficient for indexing
and querying.
3.​ Consistency: Surrogate keys help maintain data consistency in cases where
natural keys are not consistent or standardized across different data sources
or systems.
​ Security: Surrogate keys do not reveal any sensitive information about the
data they represent, which can be important for privacy and security
considerations.
Here's an example of how a surrogate key might be used in a database table:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)

);

In this example, the CustomerID column serves as a surrogate key. It is an

auto-incrementing integer that uniquely identifies each customer record in the
Customers table.

Surrogate keys are especially useful in cases where natural keys are not available,
are subject to change, or are complex to work with. They simplify database design
and can enhance data management and performance in many situations. However,
it's essential to choose an appropriate type of surrogate key (e.g., integer, UUID)
based on the specific requirements of your database system and application.