DBMS an Example

DBMS an Example

• 1.
  Introduction Example databasesOverview of concepts Why use database systems
• 2.
  Example Databases UniversityData: departments, students, exams, rooms, ... Usage: creating exam plans, enter exam results, create statistics, build timetables, … Web-based access common Bank Data: clients, accounts, credits, funds,… Applications: accounting, transfers (> 50,000 per day), risk management Web-based access for some parts of the application Airline Data: flights, passengers, employees, airplanes,… Applications: reservation, booking, creating flight schedules, querying flight schedules Web-based access for some parts of the application
• 3.
  Example Databases (contd.)Genetics Data: DNA-sequences (> 1 Mio. bases for simple bacteria), proteins Applications: search similar sequences, predict protein structure, keep track of experiments… Online bookstore Data: all what is sold at the bookstore, customer information Applications: keyword search, booking, maintaining shopping basket, checkout, … Global Change Research Data: topographic maps, satellite data (NASA Earth Observation System: 1 Terabyte per day) Applications: Analysis and prediction of climate and environmental changes (e.g., greenhouse effect, waldsterben), visualization of data Stock market, libraries, etc.
• 4.
  The basic TerminologyDatabase: collection of data modeling a real world enterprise Database management system (DBMS) or database system (DBS): the software package to store and manage the data Application programs : the software to access and process the data (implements business logic, e.g., enter exam grades, purchase book) Information system (within this course): database + DBMS + application programs There exist other types of information systems, based on information-retrieval systems or knowledge-based systems
• 5.
  Client/Server-Architecture Database DatabaseManagement System Interpreter for ad-hoc queries End user Clients Server Application Programs Application Programmer Database Administrator
• 6.
  Example DBMS RelationalDBMS companies like Oracle IBM offers its relational DB2 system. With IMS, a non-relational system, IBM is by some accounts the largest DBMS vendor in the world Microsoft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the desktop, answered by “lite” systems from other competitors There exist several widely used open-source DBMS: PostgreSQL and MySQL being the best known of them XML database systems Object-oriented database systems Embedded database systems
• 7.
  Who wants tostudy databases? “ What is the use of all the courses I have taken so far?” This course shows very concrete how Information System is used in the outside world This courses uses a lot of the basics introduced in the 200/300 level courses “ I want to work in an interdisciplinary environment” Be an application developer building solutions for all kinds of fields working with people from other areas (e-commerce, science, administration, law, etc. etc.) “ I love the internals of how computers and systems work” Be a database administrator or a DBS developer: a DBMS is an entire operating system and more “ I am more a theoretical person” Database systems have a very sound theoretical foundation and there are many exciting open problems “ I want to work with computer languages, human-computer interaction, multimedia, logic, communication, distributed systems, knowledge management,…. It’s all there “ I want to make a lot of money” E-commerce, banks and business: here you are “ I am not interested in databases” You will have to use them anyway
• 8.
  Data Models A data model is a collection of concepts for describing data A schema is a description of a particular collection of data, using a given data model Most DBMS are based on the relational data model Main concept: relation , basically a table with rows and columns Every relation has a schema , which describes the columns (also called attributes or fields) Close to how the DBMS stores the data sid name login age gpa 31 Raja raj 22 3.4 34 Rani ran 21 3.3 Students Schemas are defined using a data definition language (DDL) Data is modified using a data manipulation language (DML)
• 9.
  Data Models (contd.)A Semantic Data Model provides abstract, high-level constructs with which it is “easy” to develop an initial description of the data (schema) in an enterprise that is, develop a schema using the semantic data model and then translate it into a schema based on the data model provided by the DBMS for instance, the entity-relation model (ER) allows us to pictorially denote entities and relationships among them
• 10.
  Example: University DatabaseStudents sid name login age gpa Enrolled Courses cid cname credits grade sid name login age gpa cid cname credits sid cid grade Students Enrolled Courses Entity Relationship Relation Relation Relation Entity
• 11.
  Querying Data Queryingthe database: Student record: Give me all courses a student XYZ has taken. Show all the grades he/she got plus the class average. Inserting data into the database A student registers for a course Updating existing data The instructor enters the grades for a course Specific query languages for DBMS: best known is SQL and OQL
• 12.
  Data Storage andData Access A typical DBMS has a layered architecture Disk space management, buffer management, files and access methods represent (nearly) an operating system on top of the operation system Fast and sophisticated data retrieval requires special index structures query optimization techniques Query Optimization And Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB
• 13.
  Transaction Management: Controllingthe Database Access Key concept is transaction , which is an atomic sequence of database actions (read and write operations on data items) A transaction represents a logical unit of operations (from the application point of view) Often “user program” = transaction For instance: transfer transaction = debit of account X, credit on account Y
• 14.
  Transactional Properties Atomicity: all or none of the operations of a transaction should succeed ( all-or-nothing property) All operations succeed = commit None of the operations succeed = abort (in the case of failure: undo all operations executed so far) Durability : the changes of a committed transaction must be persistent even in the case of failures write changes to disk before commit Recovery: when restarting a failed site, recovery brings the database back to a consistent state Exactly the updates of all committed transactions must be in the database and nothing else Isolation : don’t mess up the database when running several transactions at the same time
• 15.
  Why use aDBMS? (instead of files) appropriate data models (helps to make a good design) offer more than records, arrays and basic data types easy definition of data (declarative and set-oriented) define data once with simple constructs instead of spreading definitions over various program modules easy access to data query language allows for sophisticated data retrieval using simple query statements; simple creation, deletion and modification of data efficient access to data good index structures provided data independence application programs receive abstract view of data and are independent of how data is stored and accessed persistent data storage Gigabytes of data do not fit in main memory and require special file support guarantee that changes to data are on stable storage
• 16.
  Why use aDBMS? (contd.) (instead of files) data integrity simple to define constraints to keep data consistent (account must always be above zero) security and authorization very flexible access control and execution control good basis to allow different applications to work on the same data centralize data management provide unified interface uniform data administration good tools for tuning, upgrading, monitoring, ... concurrent access , recovery from crashes comes for free ... Reduced application development time Reduced application maintenance Safer
• 17.
  Emphasis of theCourse How to organize, maintain and retrieve information using a DBMS design of databases usage of DBMS Understand how DBMS work in order to use them appropriately
• 18.
  Levels of AbstractionSingle conceptual (logical) schema defines logical structure Conceptual database design Physical schema describes the files and indexes used Physical database design Different views describe how users see the data (also referred to as external schema) generated on demand from the real data View 1 View 2 View 3 Conceptual Schema Physical Schema
• 19.
  Example: University DatabaseConceptual schema: Students(sid:string, name:string, login:string, age:integer, gpa:real) Courses(cid:string, cname:string, credits:integer) Enrolled(sid:string, cid:string, grade:string) Physical schema: Relations stored as unordered files Index on first column of Students External schema (view): Course_info(cid:string, enrollment:integer)
• 20.
  Data Independence Applicationsisolated from how data is structured and stored Logical data independence : protection from changes in logical structure of data Physical data independence : protection from changes in the physical structure of data