Summary Interactive Data Transformation

Summary Interactive Data Transformation

Lecture 1

Database Management systems - Reasons for DBMS – DBMS solves these problems
 In the early days:
o Database applications built on top of file systems -> Plethora of drawbacks
 Data redundancy and inconsistency
o Multiple file formats, duplication in different files
o Redundancy leads to inconsistency (change in one file need to be reflected in all other
files that contain that data)
 Difficulty in accessing data
o Need to write a new program to carry out each new task
 Data isolation
o Multiple files and formats
 Integrity problems
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 account to another 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 for performance
o Example: Two people, one adding money (e.g., 100) and the other withdrawing
money (e.g., 50 each) at the same time
o Uncontrolled concurrent accesses can lead to inconsistencies
 Security problems
o Hard to provide user access to some, but not all, data

DBMS Architecture
Database (DB):
 Collection of data with the same structure
 Including correlations and relationships
 Common purpose, i.e., defined for a particular use
 Shared, i.e., used by several users
Applications:
 Access to DB for performing queries
 Android app, Web application, etc.
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
 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
External Layer - I.e.: communication with users
 Analysis of user requests (queries)
 Access control
 Answer presentation
Logical Layer
 Optimization of queries
 Resolving conflicting accesses, i.e., multiple users want to do something
 Guarantees constant availability even in case of failures e.g. back-up DB
Internal Layer
 Storing the data
 Software for structuring the data
 Efficient access methods I.e., keys, indices, etc.

Development Process (i.e., lifecycle) for DBMS
Planning
 Develop a preliminary understanding of the business situation and how information systems
might help solve the problem
 Step includes:
o Understand current data processing
o Understand general business functions and needs

Analysis
 Analyze the business situation thoroughly to determine requirements
and to structure those requirements
 Output → Conceptual Schema Planning Analysis
o Corresponds to a detailed, technology independent specification
of the overall organizational data structure
o E.g., Entity-Relationship model that we will discuss in the
following lectures (image on the right)
Design

, Logical: representation of the DB
o Transform the conceptual schema, i.e., outcome of previous step, in terms of the data
management system
 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 database implementation
 Populate with data
 Install application(s) 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 by analyzing the database and applications to ensure that evolving
Information requirements are met

Different types
 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
 For each type there are different systems we can use

Relation Data Model - Defined
 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, and more.

, SQL Environment
 Catalog: information for included databases
 Schema: structure of one database, i.e., tables, views
 Data Definition Language (DDL): commands that define a database, including creating,
altering, and dropping tables and establishing constraints
 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

SQL Environment – simplified illustration




Data Definition/Manipulation/Control Language




Basic Concepts




Relational Schema
 A1, A2, …, An are attributes
 R = (A1, A2, …, An ) is a relation schema