DBMS for Data Science
Cambiago Silvia Academic Year 2024-2025 Prof. Agnès Voisard
FILE-BASED SYSTEMS VS DATABASE SYSTEMS
In real-world applications, traditional and simple file-based systems are subject to several
problems. First of all, data redundancy, which means that the same information might be stored
in multiple places, leading to the risk of inconsistency if those copies don’t match. Also
enforcing rules about the data, such as making sure that a certain ID is unique, becomes very
difficult. Handling situations where multiple people need to access and change the same
information at the same time is also problematic, often leading to accidental overwriting. On
top of that, there are serious security limitations because there is no standard way to control
who can see or modify which parts of the data. Even finding specific information in a large set
of files can be slow and inefficient. The result of these issues is chaos, inefficiency, errors, and
high maintenance costs.
Databases solve these issues by providing a more structured, reliable, and secure approach.
They store information according to a well-defined schema, meaning that the data is organized
in a predictable and logical way. This structure makes it possible to access information quickly,
thanks to search and indexing mechanisms. They maintain integrity and consistency, ensuring
that the data follows rules and remains correct even if something goes wrong, for example
during a system failure. Security is built in, allowing fine control over who can access or modify
specific parts of the data. Finally, databases allow many people to use the system at the same
time without risking corruption or data loss.
DATABASE SYSTEMS FUNDAMENTALS
Now, some basics definitions will follow:
Y Database (DB): a collection of related data that is organized according to a clear
structure, known as schema;
Y Database Management System (DBMS): set of programs that allow to define the
structure of the database, build it, and manipulate the data inside it;
Y Database System (DBS): combination of DB and DBMS software.
Essentially, the DB itself is just the data, and it needs software to handle it properly, the DBMS,
and when talking about a database system, what is meant is usually the combination of both.
Keeping the database and the DBMS as separate concepts is important because this separation
allows flexibility, modularity, and more efficient management of data.
The DBMS supports three main core functions. In order, they are:
Y Defining databases: specifies the schema, the kind of data that will be stored, the types
of values that will be allowed and the constraints that will have to be respected;
Y Constructing databases: actually creates the structures and stores the data physically on
disk;
Y Manipulating databases: executes the CRUD queries (CREATE, READ, UPDATE, and
DELETE).
Within a database system, there are several types of users, each with different responsibilities:
Y End users: interact with the database indirectly, often through applications, to get the
information they need;
1
, Y Database Administrators (DBA): manage, secure, and optimize the system;
Y Database designers: responsible for creating the logical structure of the database,
deciding how the data is organized;
Y System analysts: work on understanding what the users need and translating those
requirements into technical specifications;
Y Application programmers: develop the software that connects to the database and uses
its data.
For example, in the context of a university database, students are end users, administrative staff
act as database administrators, and developers and designers create and maintain the system.
Two central ideas help to understand how databases are organized:
Y Database schema: a static, structural description of the database. It defines the types of
data, how they are structured, and how they relate to each other. Metadata, which
describes the schema, is stored separately and is self-describing;
Y Database: the actual collection of data. This part is dynamic: it changes often as new
information is inserted, existing information is updated, or outdated data is deleted.
Together, schema and data represent real-world entities.
A database contains both data and metadata. The metadata explains the data structures,
schemas, and constraints, and is stored in a system catalog managed by the DBMS. Because
the database is self-describing, it stores information about its own structure, which allows both
software and users to work with it without needing to know the physical details of where or
how the data is stored.
This design brings a major advantage: modularity. The DBMS software is general-purpose and
not tied to one specific application or data structure, making it reusable and adaptable to many
different situations.
Databases face a series of fundamental challenges, whether it is a traditional SQL database,
NoSQL, or cloud-based:
Y Scalability: the ability to deal efficiently with growing amounts of data;
Y Security and Compliance: the database must prevent unauthorized access and comply
with laws and regulations;
Y Performance and Efficiency: ensure that queries run quickly and resources are used
optimally;
Y Reliability and Fault Tolerance: the database needs to remain available even when parts
of the system fail;
Y Concurrency and Transactions: multiple users need to be able to work with the data at
the same time without causing conflicts or corrupting the information.
Security deserves special attention, because a lack of it can lead to serious consequences. A
secure database uses authentication and encryption to prevent unauthorized access, follows
regulatory standards (like GDPR and HIPAA) to avoid legal violations, and maintains audit
logs to track who accessed or modified the data. Without these measures, the risk of data
breaches increases, and such breaches can result in significant legal and financial damage to an
organization.
Small systems can usually run on a single server, but as databases grow, larger systems may
need to be distributed across multiple machines. There are two main strategies for scaling:
vertical scaling, which means adding more power (CPU, RAM) to a single machine, and
horizontal scaling, which means spreading the data across multiple servers.
2
,Once the data is spread out, the challenge becomes keeping the database fast. This is done
through the implementation of indices, which make finding information quicker, query
optimization, which improves the way requests are processed, and caching mechanisms, which
store frequent results so they can be retrieved instantly.
Finally, databases must handle failures safely. This is achieved through ACID (Atomicity,
Consistency, Isolation, Durability) transactions, which ensure that operations are carried out
completely, consistently, and without interference, even in the event of a failure. Backup and
recovery strategies allow the system to restore lost data, while replication and redundancy
create multiple copies of the data to prevent loss if one copy becomes unavailable. These
measures together ensure that a database can operate reliably, securely, and efficiently, even
under challenging conditions.
DATA MODELS AND DB SCHEMAS
A data model is essentially the blueprint that defines how information is organized in a
database. It determines:
Y Data Structures: structures used for storing data. For example, whether the data will be
arranged in tables, graphs, or trees;
Y Operations: specifies the queries to retrieve data, insertions of new records, or updates
to existing ones;
Y Constraints: sets the rules that ensure integrity and correctness.
The data model is used to define the database schema through a Data Definition Language
(DDL) and to access or update the database through a Data Manipulation Language (DML).
When a data model is translated into an actual database, it takes the form of a schema and an
instance. The database schema is static, and it defines the structure of the data, such as the table
definitions, the data types allowed, the relationships between entities, and the constraints to be
enforced. The schema remains relatively stable over time.
In contrast, the database instance is dynamic, and it represents the actual data at any given
moment. It changes frequently, reflecting the real-world operations happening in the system,
and can be thought of as a snapshot of the database at a specific point in time.
To manage complexity,
database systems are organized
into three levels of abstraction:
Y External (logical) layer: it
is what specific user groups see,
made of tailored views that
present only the relevant
information to them;
Y Conceptual layer: it is a
complete logical description of
the whole database,
independent of how it is
physically stored, often represented through entity-relationship models or tables;
Y Internal (physical) layer: deals with the actual storage structures, such as indexes, B-
trees, or hashing, which handle how data is stored and retrieved on disk.
3
, This multi-level abstraction offers a key advantage, which is data independence, the ability to
change the database schema at one level without affecting the schema at a higher level. For
example, physical data independence means that changes in how data is physically stored (such
as changing indexing methods or moving to a different storage device) do not affect the logical
structure of the database or the applications that use it. Logical data independence means that
changes to the logical schema (such as modifying the table structure) have little or no effect on
existing applications. The ultimate goal of data independence is to reduce maintenance
overhead and give the database flexibility to evolve without constantly breaking dependent
systems.
STRUCTURED QUERY LANGUAGE
Structured Query Language (SQL) is the standard language used to interact with relational
databases. After being standardized first by ANSI then by ISO, today is the primary query
language for most DBMS, and it supports both data definition and data manipulation through
DDL (Data Definition Language) and DML (Data Manipulation Language).
In SQL, data is usually organized in tables, which are also referred to as relations. Each row in
a table is a tuple, representing a single record, and each column is an attribute, representing a
particular property of that record. A schema in SQL is a way of grouping together related tables
that belong to the same database application. A schema has a name, an owner (usually a user
or account identified by an authorization identifier), and descriptors for all the tables, views,
and other database objects it contains. Schemas are created using the CREATE SCHEMA
statement, for example:
CREATE SCHEMA Company AUTHORIZATION Jackson
This statement creates a schema called “Company” owned by the user Jackson.
A collection of schemas in an SQL environment is called a catalog. This is useful for organizing
and managing large databases that might contain multiple applications or modules.
Tables in SQL are created using the CREATE TABLE command, followed by the table name
and the definitions of its columns. Each column is given a data type, which can be numeric, a
string of characters, a bit string, or a date/time format, among others. For example:
CREATE TABLE Company.EMPLOYEE
could define an employee table within the Company schema. In a more complete example:
Foreign keys establish links to other tables. For example, linking NumDept to the
DEPARTMENT table’s department number, and BossSSN to another employee’s SSN in the
same table. This setup enforces relationships and ensures data integrity across the database.
4
Cambiago Silvia Academic Year 2024-2025 Prof. Agnès Voisard
FILE-BASED SYSTEMS VS DATABASE SYSTEMS
In real-world applications, traditional and simple file-based systems are subject to several
problems. First of all, data redundancy, which means that the same information might be stored
in multiple places, leading to the risk of inconsistency if those copies don’t match. Also
enforcing rules about the data, such as making sure that a certain ID is unique, becomes very
difficult. Handling situations where multiple people need to access and change the same
information at the same time is also problematic, often leading to accidental overwriting. On
top of that, there are serious security limitations because there is no standard way to control
who can see or modify which parts of the data. Even finding specific information in a large set
of files can be slow and inefficient. The result of these issues is chaos, inefficiency, errors, and
high maintenance costs.
Databases solve these issues by providing a more structured, reliable, and secure approach.
They store information according to a well-defined schema, meaning that the data is organized
in a predictable and logical way. This structure makes it possible to access information quickly,
thanks to search and indexing mechanisms. They maintain integrity and consistency, ensuring
that the data follows rules and remains correct even if something goes wrong, for example
during a system failure. Security is built in, allowing fine control over who can access or modify
specific parts of the data. Finally, databases allow many people to use the system at the same
time without risking corruption or data loss.
DATABASE SYSTEMS FUNDAMENTALS
Now, some basics definitions will follow:
Y Database (DB): a collection of related data that is organized according to a clear
structure, known as schema;
Y Database Management System (DBMS): set of programs that allow to define the
structure of the database, build it, and manipulate the data inside it;
Y Database System (DBS): combination of DB and DBMS software.
Essentially, the DB itself is just the data, and it needs software to handle it properly, the DBMS,
and when talking about a database system, what is meant is usually the combination of both.
Keeping the database and the DBMS as separate concepts is important because this separation
allows flexibility, modularity, and more efficient management of data.
The DBMS supports three main core functions. In order, they are:
Y Defining databases: specifies the schema, the kind of data that will be stored, the types
of values that will be allowed and the constraints that will have to be respected;
Y Constructing databases: actually creates the structures and stores the data physically on
disk;
Y Manipulating databases: executes the CRUD queries (CREATE, READ, UPDATE, and
DELETE).
Within a database system, there are several types of users, each with different responsibilities:
Y End users: interact with the database indirectly, often through applications, to get the
information they need;
1
, Y Database Administrators (DBA): manage, secure, and optimize the system;
Y Database designers: responsible for creating the logical structure of the database,
deciding how the data is organized;
Y System analysts: work on understanding what the users need and translating those
requirements into technical specifications;
Y Application programmers: develop the software that connects to the database and uses
its data.
For example, in the context of a university database, students are end users, administrative staff
act as database administrators, and developers and designers create and maintain the system.
Two central ideas help to understand how databases are organized:
Y Database schema: a static, structural description of the database. It defines the types of
data, how they are structured, and how they relate to each other. Metadata, which
describes the schema, is stored separately and is self-describing;
Y Database: the actual collection of data. This part is dynamic: it changes often as new
information is inserted, existing information is updated, or outdated data is deleted.
Together, schema and data represent real-world entities.
A database contains both data and metadata. The metadata explains the data structures,
schemas, and constraints, and is stored in a system catalog managed by the DBMS. Because
the database is self-describing, it stores information about its own structure, which allows both
software and users to work with it without needing to know the physical details of where or
how the data is stored.
This design brings a major advantage: modularity. The DBMS software is general-purpose and
not tied to one specific application or data structure, making it reusable and adaptable to many
different situations.
Databases face a series of fundamental challenges, whether it is a traditional SQL database,
NoSQL, or cloud-based:
Y Scalability: the ability to deal efficiently with growing amounts of data;
Y Security and Compliance: the database must prevent unauthorized access and comply
with laws and regulations;
Y Performance and Efficiency: ensure that queries run quickly and resources are used
optimally;
Y Reliability and Fault Tolerance: the database needs to remain available even when parts
of the system fail;
Y Concurrency and Transactions: multiple users need to be able to work with the data at
the same time without causing conflicts or corrupting the information.
Security deserves special attention, because a lack of it can lead to serious consequences. A
secure database uses authentication and encryption to prevent unauthorized access, follows
regulatory standards (like GDPR and HIPAA) to avoid legal violations, and maintains audit
logs to track who accessed or modified the data. Without these measures, the risk of data
breaches increases, and such breaches can result in significant legal and financial damage to an
organization.
Small systems can usually run on a single server, but as databases grow, larger systems may
need to be distributed across multiple machines. There are two main strategies for scaling:
vertical scaling, which means adding more power (CPU, RAM) to a single machine, and
horizontal scaling, which means spreading the data across multiple servers.
2
,Once the data is spread out, the challenge becomes keeping the database fast. This is done
through the implementation of indices, which make finding information quicker, query
optimization, which improves the way requests are processed, and caching mechanisms, which
store frequent results so they can be retrieved instantly.
Finally, databases must handle failures safely. This is achieved through ACID (Atomicity,
Consistency, Isolation, Durability) transactions, which ensure that operations are carried out
completely, consistently, and without interference, even in the event of a failure. Backup and
recovery strategies allow the system to restore lost data, while replication and redundancy
create multiple copies of the data to prevent loss if one copy becomes unavailable. These
measures together ensure that a database can operate reliably, securely, and efficiently, even
under challenging conditions.
DATA MODELS AND DB SCHEMAS
A data model is essentially the blueprint that defines how information is organized in a
database. It determines:
Y Data Structures: structures used for storing data. For example, whether the data will be
arranged in tables, graphs, or trees;
Y Operations: specifies the queries to retrieve data, insertions of new records, or updates
to existing ones;
Y Constraints: sets the rules that ensure integrity and correctness.
The data model is used to define the database schema through a Data Definition Language
(DDL) and to access or update the database through a Data Manipulation Language (DML).
When a data model is translated into an actual database, it takes the form of a schema and an
instance. The database schema is static, and it defines the structure of the data, such as the table
definitions, the data types allowed, the relationships between entities, and the constraints to be
enforced. The schema remains relatively stable over time.
In contrast, the database instance is dynamic, and it represents the actual data at any given
moment. It changes frequently, reflecting the real-world operations happening in the system,
and can be thought of as a snapshot of the database at a specific point in time.
To manage complexity,
database systems are organized
into three levels of abstraction:
Y External (logical) layer: it
is what specific user groups see,
made of tailored views that
present only the relevant
information to them;
Y Conceptual layer: it is a
complete logical description of
the whole database,
independent of how it is
physically stored, often represented through entity-relationship models or tables;
Y Internal (physical) layer: deals with the actual storage structures, such as indexes, B-
trees, or hashing, which handle how data is stored and retrieved on disk.
3
, This multi-level abstraction offers a key advantage, which is data independence, the ability to
change the database schema at one level without affecting the schema at a higher level. For
example, physical data independence means that changes in how data is physically stored (such
as changing indexing methods or moving to a different storage device) do not affect the logical
structure of the database or the applications that use it. Logical data independence means that
changes to the logical schema (such as modifying the table structure) have little or no effect on
existing applications. The ultimate goal of data independence is to reduce maintenance
overhead and give the database flexibility to evolve without constantly breaking dependent
systems.
STRUCTURED QUERY LANGUAGE
Structured Query Language (SQL) is the standard language used to interact with relational
databases. After being standardized first by ANSI then by ISO, today is the primary query
language for most DBMS, and it supports both data definition and data manipulation through
DDL (Data Definition Language) and DML (Data Manipulation Language).
In SQL, data is usually organized in tables, which are also referred to as relations. Each row in
a table is a tuple, representing a single record, and each column is an attribute, representing a
particular property of that record. A schema in SQL is a way of grouping together related tables
that belong to the same database application. A schema has a name, an owner (usually a user
or account identified by an authorization identifier), and descriptors for all the tables, views,
and other database objects it contains. Schemas are created using the CREATE SCHEMA
statement, for example:
CREATE SCHEMA Company AUTHORIZATION Jackson
This statement creates a schema called “Company” owned by the user Jackson.
A collection of schemas in an SQL environment is called a catalog. This is useful for organizing
and managing large databases that might contain multiple applications or modules.
Tables in SQL are created using the CREATE TABLE command, followed by the table name
and the definitions of its columns. Each column is given a data type, which can be numeric, a
string of characters, a bit string, or a date/time format, among others. For example:
CREATE TABLE Company.EMPLOYEE
could define an employee table within the Company schema. In a more complete example:
Foreign keys establish links to other tables. For example, linking NumDept to the
DEPARTMENT table’s department number, and BossSSN to another employee’s SSN in the
same table. This setup enforces relationships and ensures data integrity across the database.
4
