Tilburg University
Study Program: Master Data Science and Society
Academic Year 2021/2022, Semester 2, Block 4 (April to June 2022)
Course: Interactive Data Transformation (320099-M-6)
Lecturers: E. Ioannou
,Lecture 1: Data Base Management Systems and SQL
Database management systems
Reasons for DBMS
• Data redundancy and inconsistency: Multiple file formats, duplication in different files
o Redundancies: same info in more than one places
o Inconsistencies: different values for the same info
• Difficulty in accessing data: Need to write a new program to carry out each new task
• Data isolation: Multiple files and formats
• Integrity problems: Integrity constraints become buried in program code rather than being
stated explicitly, hard to add new constraints or change existing ones
• Atomicity of updates: Failures may leave data in an inconsistent state with partial updates
• Concurrent access by multiple users: Needed for performance, Uncontrolled concurrent
accesses can lead to inconsistencies
• Security problems: Hard to provide user access to some, but not all, data
DBMS Architecture
• Database (DB)
o Collection of data with the same structure
o Including correlations and relationships
o Common purpose, i.e., defined for a particular use
o Shared, i.e., used by several users
• Database Management System (DBMS)
o Collection of programs over DB
o Define, i.e., specify the data types, structure, constraints
o Build & manipulate, i.e., store on disk, retrieve, update
o Administrate, e.g., manage access rights
• Applications
o Access to DB for performing queries
o Android app, Web application, etc.
o Users, Applications, Systems, Clients
• Internal Layer
o Storing the data
o Software for structuring the data
o Efficient access methods (keys, indices, etc.)
• Logical Layer: Optimization of queries
o Resolving conflicting accesses, i.e., multiple users
o Guarantees constant availability even in case of failures
• External Layer: communication with users
o Analysis of user requests (queries)
o Access control
o Answer presentation
, Development Process, Lifecycle
1. Planning: Preliminary understanding of the business situation and how information systems
might help solving the problem
a. Analyzing current data processing
b. Analyzing general business functions and needs
2. Analysis: Analyze the business situation thoroughly to determine requirements and to
structure those
a. Develop Conceptual Schema (Entity Relationship Diagram)
b. Corresponds to a detailed, technology independent specification of the overall
organizational data structure
3. Design
a. Logical representation of the DB: Transform the
conceptual schema to a data management system
b. Physical: Specify how data are stored in a
computer’s secondary memory by a specific DBMS
4. Implementation
a. Build database implementation
b. Populate with data
c. Install application(s) and test
d. Complete documentation and training materials
5. Maintenance
a. Monitor the operation and usefulness of the system
b. Repair by fixing errors in database and applications
c. Enhance by analyzing the database and applications
to ensure that evolving information requirements are met
Types of DBMS
• Traditional database management systems: Text and numerical data
• Multimedia database management systems: Multimedia data
• Spatial database management systems: Geographic and geometric data
• Data Warehouses
Relational Data Model
• An approach to managing data by representing it grouped into relations
• Relational Database Management Systems (RDBMS):
o A database management system that manages data as a collection of tables in which
all relationships are represented by common values in related tables
Relational Schema
• A database consists of a finite set of relations (often referred to as tables)
• Each relation has a name and a set of attributes
• Each attribute has a name and a domain
o Domain is the collection of values an attribute may take: Null, numeric (integer,
float), Boolean, date, timestamp, string etc.
• A relation contains a set of instances (rows / tuples / records)
Study Program: Master Data Science and Society
Academic Year 2021/2022, Semester 2, Block 4 (April to June 2022)
Course: Interactive Data Transformation (320099-M-6)
Lecturers: E. Ioannou
,Lecture 1: Data Base Management Systems and SQL
Database management systems
Reasons for DBMS
• Data redundancy and inconsistency: Multiple file formats, duplication in different files
o Redundancies: same info in more than one places
o Inconsistencies: different values for the same info
• Difficulty in accessing data: Need to write a new program to carry out each new task
• Data isolation: Multiple files and formats
• Integrity problems: Integrity constraints become buried in program code rather than being
stated explicitly, hard to add new constraints or change existing ones
• Atomicity of updates: Failures may leave data in an inconsistent state with partial updates
• Concurrent access by multiple users: Needed for performance, Uncontrolled concurrent
accesses can lead to inconsistencies
• Security problems: Hard to provide user access to some, but not all, data
DBMS Architecture
• Database (DB)
o Collection of data with the same structure
o Including correlations and relationships
o Common purpose, i.e., defined for a particular use
o Shared, i.e., used by several users
• Database Management System (DBMS)
o Collection of programs over DB
o Define, i.e., specify the data types, structure, constraints
o Build & manipulate, i.e., store on disk, retrieve, update
o Administrate, e.g., manage access rights
• Applications
o Access to DB for performing queries
o Android app, Web application, etc.
o Users, Applications, Systems, Clients
• Internal Layer
o Storing the data
o Software for structuring the data
o Efficient access methods (keys, indices, etc.)
• Logical Layer: Optimization of queries
o Resolving conflicting accesses, i.e., multiple users
o Guarantees constant availability even in case of failures
• External Layer: communication with users
o Analysis of user requests (queries)
o Access control
o Answer presentation
, Development Process, Lifecycle
1. Planning: Preliminary understanding of the business situation and how information systems
might help solving the problem
a. Analyzing current data processing
b. Analyzing general business functions and needs
2. Analysis: Analyze the business situation thoroughly to determine requirements and to
structure those
a. Develop Conceptual Schema (Entity Relationship Diagram)
b. Corresponds to a detailed, technology independent specification of the overall
organizational data structure
3. Design
a. Logical representation of the DB: Transform the
conceptual schema to a data management system
b. Physical: Specify how data are stored in a
computer’s secondary memory by a specific DBMS
4. Implementation
a. Build database implementation
b. Populate with data
c. Install application(s) and test
d. Complete documentation and training materials
5. Maintenance
a. Monitor the operation and usefulness of the system
b. Repair by fixing errors in database and applications
c. Enhance by analyzing the database and applications
to ensure that evolving information requirements are met
Types of DBMS
• Traditional database management systems: Text and numerical data
• Multimedia database management systems: Multimedia data
• Spatial database management systems: Geographic and geometric data
• Data Warehouses
Relational Data Model
• An approach to managing data by representing it grouped into relations
• Relational Database Management Systems (RDBMS):
o A database management system that manages data as a collection of tables in which
all relationships are represented by common values in related tables
Relational Schema
• A database consists of a finite set of relations (often referred to as tables)
• Each relation has a name and a set of attributes
• Each attribute has a name and a domain
o Domain is the collection of values an attribute may take: Null, numeric (integer,
float), Boolean, date, timestamp, string etc.
• A relation contains a set of instances (rows / tuples / records)
