Lecture 1. Database Management Systems
Why should you care about DMS?
- You are probably using various DBSM already (for instance canvas)
- Increasing growth of data (handle/manage/ collect data what we call at the end Big
Data)
- Real world
o Websites, projects etc.
o Big companies
o … all manage huge amounts of data
- Nearby future
o Managing data for your MSc thesis
o Working at a company
Reasons for DBMS
- In the early days:
o Database applications built on top of file systems (just file on a single
computer)
o Plethora of DRAWBACKS:
- Data redundancy (same info in different file) and inconsistency:
o Multiple file formats, duplication in different files
- Difficulty in accessing data
o Need to write a new program to carry out each new task
- Data isolation
o Multiple files and formats made by every person differently
- Integrity problem
o Integrity constraints (e.g. Account balance >0) become “buried” in program
code rather than being stated explicitly
o Hard to add new constraints or change existing ones
- Atomicity of updates
o Example: Transfer of funds from one to another account should either
complete or not happen at all
o Failures may leave data in an inconsistent state with partial updates carried
out
- Concurrent access by multiple users:
o Needed to performance
o Example: two people reading a balance (e.g. one sees 100 and takes 50, the
other sees still 100.
o Uncontrolled concurrent accesses can lead to inconsistencies
- Security problems
o Hard to provide user access to some, but not all.
DBMSs offer solutions to all above problems!
0
,DBMS Architecture:
Database (DB):
- Collection of data that follows the same structure and format
- Including correlations and relationships
- Common purpose i.e., defined for a particular use
- Shared, i.e., used by several users
Database Management System (DBMS):
- Collection of programs over DB
- Define i.e., specify the data types, structure, constraints
- Build & manipulate, i.e., Store on disk, retrieve, update
- Administrate, e.g., Manage access rights
Applications (tools):
- Access to DataBase for performing queries (questions)
- Android app, web applications
DBMS → black box interacting between users/applications and the database
Ultimate Goal
→ Separate data from application!
- Provide an interface that the application programmer must follow
- Allow system administrator to make modifications without having an impact on the
user
o E.g., improve or reconfigure system
- Users can change their view of the data without having to worry about how it is
stored
Layers of DBSM
External layer
i.e., communication with users
- Analysis of user requests (executed queries)
- Access control (not all the users can have the access)
- Answer presentation (once the queries are executed)
1
,Logical layer
- Optimization of queries
- Resolving conflicting accesses, i.e., multiple users (how the execution will be done)
- Guarantees constant availability even in case of failures
Internal layer
- Storing the data
- Software for structuring the data
- Efficient access methods
o i.e., keys, indices, etc.
Development Process (I.e., Lifecycle)
Planning
- Develop a preliminary understanding of the business situation and how information
systems might help solve the problems (what is the company actually doing)
- Step includes:
o Analyzing current data processing
o Analyzing general business functions and needs
Analysis
- Analyze the business situation thoroughly to determine requirements and to
structure those requirements
- Output of the analysis → Conceptual Schema
- Corresponds to a detailed, technology independent specification of the overall
organizational data structure (what the company would like to develop)
- E.g. Entity-Relationship Model (diagram)
Design
- Logical: representation of the DB
o Transform the conceptual schema, i.e. outcome of previous step, in terms of
the data management system (we have to go from the diagram what the
system would use at the end)
- Physical: the set of specifications that describe how data are stored in a computer’s
secondary memory by a specific database management system
Implementation
- Build a database implementation
- Populate with data
2
, - Install applications and test
- Complete documentation and training materials
Maintenance
- Monitor the operation and usefulness of the system
- Repair by fixing errors in database and applications
- Enhance/improve by analyzing the database and applications to ensure that
evolving information requirements are met
Different types of DBMS
- Traditional database management systems
o Text and numerical data
- Multimedia database management systems
o Multimedia data (movies, music, etc.)
- Spatial database management systems
o Geographic and geometric data
- Data warehouses
Relational Data Model
• An approach to managing data by representing it grouped into relations
• Developed by Ted Codd in 1970 at IBM
• 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
Structured Query Language (SQL)
- Language for creating & querying relational databases
- Simple, expressive, with efficient implementations
- Used by many commercial systems:
o Oracle, MySQL, MS Access, SQLite, DB2, Ingres, Sybase, dBase, etc.
- Standard for RDBMS:
o Reduced training costs
o Cross-system communication
SQL Environment
- Catalog: information for included databases
- Schema: structure of one/single database, i.e., tables, views
3 different languages:
- Data Definition Language (DDL): commands that define a database, including
creating, altering, and dropping tables and establishing constraints (granting access)
- Data Manipulation Language (DML): commands that maintain and query a database
- Data Control Language (DCL): commands that control a database, including
administering privileges and committing data
3
Why should you care about DMS?
- You are probably using various DBSM already (for instance canvas)
- Increasing growth of data (handle/manage/ collect data what we call at the end Big
Data)
- Real world
o Websites, projects etc.
o Big companies
o … all manage huge amounts of data
- Nearby future
o Managing data for your MSc thesis
o Working at a company
Reasons for DBMS
- In the early days:
o Database applications built on top of file systems (just file on a single
computer)
o Plethora of DRAWBACKS:
- Data redundancy (same info in different file) and inconsistency:
o Multiple file formats, duplication in different files
- Difficulty in accessing data
o Need to write a new program to carry out each new task
- Data isolation
o Multiple files and formats made by every person differently
- Integrity problem
o Integrity constraints (e.g. Account balance >0) become “buried” in program
code rather than being stated explicitly
o Hard to add new constraints or change existing ones
- Atomicity of updates
o Example: Transfer of funds from one to another account should either
complete or not happen at all
o Failures may leave data in an inconsistent state with partial updates carried
out
- Concurrent access by multiple users:
o Needed to performance
o Example: two people reading a balance (e.g. one sees 100 and takes 50, the
other sees still 100.
o Uncontrolled concurrent accesses can lead to inconsistencies
- Security problems
o Hard to provide user access to some, but not all.
DBMSs offer solutions to all above problems!
0
,DBMS Architecture:
Database (DB):
- Collection of data that follows the same structure and format
- Including correlations and relationships
- Common purpose i.e., defined for a particular use
- Shared, i.e., used by several users
Database Management System (DBMS):
- Collection of programs over DB
- Define i.e., specify the data types, structure, constraints
- Build & manipulate, i.e., Store on disk, retrieve, update
- Administrate, e.g., Manage access rights
Applications (tools):
- Access to DataBase for performing queries (questions)
- Android app, web applications
DBMS → black box interacting between users/applications and the database
Ultimate Goal
→ Separate data from application!
- Provide an interface that the application programmer must follow
- Allow system administrator to make modifications without having an impact on the
user
o E.g., improve or reconfigure system
- Users can change their view of the data without having to worry about how it is
stored
Layers of DBSM
External layer
i.e., communication with users
- Analysis of user requests (executed queries)
- Access control (not all the users can have the access)
- Answer presentation (once the queries are executed)
1
,Logical layer
- Optimization of queries
- Resolving conflicting accesses, i.e., multiple users (how the execution will be done)
- Guarantees constant availability even in case of failures
Internal layer
- Storing the data
- Software for structuring the data
- Efficient access methods
o i.e., keys, indices, etc.
Development Process (I.e., Lifecycle)
Planning
- Develop a preliminary understanding of the business situation and how information
systems might help solve the problems (what is the company actually doing)
- Step includes:
o Analyzing current data processing
o Analyzing general business functions and needs
Analysis
- Analyze the business situation thoroughly to determine requirements and to
structure those requirements
- Output of the analysis → Conceptual Schema
- Corresponds to a detailed, technology independent specification of the overall
organizational data structure (what the company would like to develop)
- E.g. Entity-Relationship Model (diagram)
Design
- Logical: representation of the DB
o Transform the conceptual schema, i.e. outcome of previous step, in terms of
the data management system (we have to go from the diagram what the
system would use at the end)
- Physical: the set of specifications that describe how data are stored in a computer’s
secondary memory by a specific database management system
Implementation
- Build a database implementation
- Populate with data
2
, - Install applications and test
- Complete documentation and training materials
Maintenance
- Monitor the operation and usefulness of the system
- Repair by fixing errors in database and applications
- Enhance/improve by analyzing the database and applications to ensure that
evolving information requirements are met
Different types of DBMS
- Traditional database management systems
o Text and numerical data
- Multimedia database management systems
o Multimedia data (movies, music, etc.)
- Spatial database management systems
o Geographic and geometric data
- Data warehouses
Relational Data Model
• An approach to managing data by representing it grouped into relations
• Developed by Ted Codd in 1970 at IBM
• 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
Structured Query Language (SQL)
- Language for creating & querying relational databases
- Simple, expressive, with efficient implementations
- Used by many commercial systems:
o Oracle, MySQL, MS Access, SQLite, DB2, Ingres, Sybase, dBase, etc.
- Standard for RDBMS:
o Reduced training costs
o Cross-system communication
SQL Environment
- Catalog: information for included databases
- Schema: structure of one/single database, i.e., tables, views
3 different languages:
- Data Definition Language (DDL): commands that define a database, including
creating, altering, and dropping tables and establishing constraints (granting access)
- Data Manipulation Language (DML): commands that maintain and query a database
- Data Control Language (DCL): commands that control a database, including
administering privileges and committing data
3
