Summary: Interactive Data Transformation

Read the summary and the most important questions on Interactive Data Transformation

• 1 Week 1

• 1.1.1 Introduction

  This is a preview. There are 4 more flashcards available for chapter 1.1.1
  Show more cards here

• Why should you care about DBMS?

  • You're probably using various DBSM already
    

  • Increasing growth of data
    
  • Real world
    
  • Websites, projects, etc.
    
  • Big companies
    
  • .. all manage huge amounts of data
    
  • Nearby future
    
  • Managing data for your MSc thesis
    
  • Working at a company
    

• What elements are in a 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 use
    

  • 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 right
    
  • Applications:
    

  • Access to DB for performing queries
    
  • Android app, Web application, etc
    

• Give an example of a DBMS architecture of the Tilburg University and give some other examples where DBMS is used?

  See pic.
  

  • Airlines: reservations and schedule information. Among the first to use databases in a geographically distributed manner
    
  • Banking: customer information, accounts, loans, and banking transactions
    
  • Universities: student information, course registrations, and grades. Application program examples:
    
  • add new students, lecturers, courses
    
  • register students for courses
    
  • assign grades and generate transcripts
    

• What is the ultimate goal of a DBMS?

  • → 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
    
  • E.g., improve or reconfigure system
    
  • Users can change their view of the data without having to worry about how it is stored
    

• What type of layers does the DBMS have and what do the layers represent?

  • 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
    
  • Guarantees constant availability even in case of failures
    
  • Internal Layer
    
  • Storing the data
    
  • Software for structuring the data
    
  • Efficient access methods
    
  • I.e., keys, indices, etc.
    

• 1.1.2 SQL environment

  This is a preview. There are 4 more flashcards available for chapter 1.1.2
  Show more cards here

• What is Structured Query Language (SQL)?

  • Language for creating & querying relational databases
    
  • Simple, expressive, with efficient implementations
    
  • Used by many commercial systems:
    
  • Oracle, MySQL, MS Access, SQLite, DB2, Ingres, Sybase, dBase, etc.
    
  • Standard for RDBMS:
    
  • Reduced training costs
    
  • Cross-system communication
    

• What does the SQL environment consist of?

  • 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
    

• What can you see in a simplified ilustration of a SQL environment?

  • User connects to DBMS and see the catalogs.
    

• What queries do DDL, DML and DCL represent?

  • DDL: Define database with CREATE query. (Physical design, or maintenance)
    
  • DML: Load database with INSERT data, UPDATE database and SELECT. First two commands add information and SELECT only loads data. (Implementation)
    
  • DCL: Control database GRANT, ADD, REVOKE access to someone. (Implementation and Maintenance)
    

• What is a Schema, instance/record and population in a database?

  See pic