Introduction to Database Systems - Hahsler

1 1 / 16 Introduction to Database SystemsBased on slides by Dan SuciuAdapted by Michael Hahsler 2 / 16 DatabaseWhat is a Database ? Physical storage: A collection of files storing related data. Logical: A collection of tables (or objects).Examples of databases Accounts Database ; payroll Database ; SMU s students Database ; Amazon s products Database ; airline reservation Database . 3 / 16 Database Management SystemWhat is a DBMS? A complicated (and often expensive) piece of software typically running on a large (remote) server written by someone else that allows us to manage efficiently a large Database and allows it to persist over long periods of of DBMS Commercial: DB2 (IBM), SQL Server (MS), Oracle, Sybase Open Source: MySQL, Postgres, SQLite.

2 Big Data: often NoSQL like MongoDB, Apache Cassandra, etc. 4 / 16 Architecture: Using a DMBSData filesDatabase server running the DBMSA pplicationsrunning a clientconnection(ODBC, JDBC) Client-server Architecture 5 / 16 Operations: Query/UpdateAssume we have a Database for movies and actors. Simple query: In what year was Star Wars produced? Multi-table query: Find all movies with Harrison Ford (combine actor and movie tables) Complex query: For each actor, count her/his movies Updating Insert a new movie; add an actor to a movie; etcMoviesActors 6 / 16 Operations: Query/Update Files ( , CSV) Spreadsheets DBMSS imple queriesMulti-table queries(maybe)AllUpdates: generally OK 7 / 16 Change the Structure of a DBAdd Address to each Actor Files ( , CSV) Spreadsheets DBMSVery hardYesYes 8 / 16 Issue: Concurrent AccessMultiple users access/update the data concurrently What can go wrong?

3 Lost update; resulting in inconsistent data How do we protect against that in OS? Locks Databases need a similar concept to deal with concurrent updates. 9 / 16 Issue: Recover from crashes Transfer $100 from account #4662 to #7199:X = Read(Accounts, 4662); = - 100;Write(Accounts, 4662, X);Y = Read(Accounts, 7199); = + 100;Write(Accounts, 7199, Y);X = Read(Accounts, 4662); = - 100;Write(Accounts, 4662, X);Y = Read(Accounts, 7199); = + 100;Write(Accounts, 7199, Y);CRASH !What is the problem ? 10 / 16 Concurrency & Recovery: Transactions A transaction = sequence of statements that either all succeed, or all fail together. , Transfer $100 BEGIN TRANSACTION; UPDATE AccountsSET amount = amount - 100 WHERE number = 4662 UPDATE AccountsSET amount = amount + 100 WHERE number = 7199 COMMITBEGIN TRANSACTION; UPDATE AccountsSET amount = amount - 100 WHERE number = 4662 UPDATE AccountsSET amount = amount + 100 WHERE number = 7199 COMMIT 11 / 16 TransactionsTransactions have the ACID properties: A = atomicity C = consistency I = isolation D = durabilityTransactions also allow rollbacks (undo).

4 Transactions are independentAll or nothingValid state to valid stateNo data loss after commit 12 / 16 Relational Data Base = Collection of TablesActors:Movie_Actors:Movies:idfName lName15901 HarrisonFord..midTitleYear130128 Star Wars1977..idmid15901130128.. 13 / 16 Create/Store Large DatasetsUse SQL to create and populate tables:CREATE TABLE Actors ( fName CHAR(30), lName CHAR(30), .. ) CREATE TABLE Actors ( fName CHAR(30), lName CHAR(30), .. ) INSERT INTO ActorsVALUES('Harrison', 'Ford', ..)INSERT INTO ActorsVALUES('Harrison', 'Ford', ..)Physical organization of the data is handled by DBMSWe focus on modeling the Database ! 14 / 16 Querying Find all movies with Harrison Ford What happens behind the scene ?

5 The DBMS uses indices and optimizes automatically the titleFROM Movies, Actors, Movie_ActorsWHERE = Ford and = Harrison and = and = titleFROM Movies, Actors, Movie_ActorsWHERE = Ford and = Harrison and = and = 15 / 16 Change the Structure of a TableAdd Address to each ActorALTER TABLE Actor ADD address CHAR(50) DEFAULT unknown ALTER TABLE Actor ADD address CHAR(50) DEFAULT unknown 16 / 16 What comes next?1) Using a DBMS2) Using SQL Query Databases3) Designing a Databas