Course overview

Intro: types of data

Types of data:

Structured data:

All records have the same format

Example: employee records

Employees: ssn fname lname salary dname --------- ------ -------- --------- --------------- 123456789 John Smith 20900.00 Research 333445555 Frankl Wong 50000.00 Research 999887777 Alicia Zelaya 25000.00 Administration 987654321 Jennif Wallace 43000.00 Administration 666884444 Ramesh Narayan 38000.00 Research 453453453 Joyce English 25000.00 Research 987987987 Ahmad Jabbar 25000.00 Administration 888665555 James Borg 55000.00 Headquarters

Unstructured data:

This course::

deals mainly with structured data

However:

Many techniques discussed can be used/adapted to store and access unstructured data
Example:

Database

Database:

Example: database on a company

Employees: ssn fname lname salary superssn dno --------- ------ -------- --------- --------- ----------- 123456789 John Smith 20900.00 333445555 5 333445555 Frankl Wong 50000.00 888665555 5 999887777 Alicia Zelaya 25000.00 987654321 4 987654321 Jennif Wallace 43000.00 888665555 4 666884444 Ramesh Narayan 38000.00 333445555 5 453453453 Joyce English 25000.00 333445555 5 987987987 Ahmad Jabbar 25000.00 987654321 4 888665555 James Borg 55000.00 NULL 1 Departments: dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71 (More data possible, but omitted for brevity)

Software to manage databases

Database Management System:

A (large) software system that managed the data in a database
(Traditionally, it was used to manage structured data.
It is being adapted to manage unstructured data)

Graphically:

Demo: mySQL Database Software

/home/cs554001/bin/cs554-sql

Database operations
- Operations on the data:

Query

Sample Queries:

Example query:

select * from employee where salary > 40000

Query processing

Query language:

Best known query language:

SQL = Structured Query Language

Example:

select fname, lname from employee where salary > 50000

Query processing in a DMBS:

A query Q in SQL is first translated into a number initial steps that will return the result
The initial steps are then transformed into an optimal execution plan
The optimal plan is executed and the result (answer) is returned to the user

Schematically:

Main goal of this course:

We will study techniques that enable us to process queries efficiently

The techniques include:

How to store data files
Indexing/hashing techniques to access data stored efficiently
Efficient algorithms to implement relational algebra operation
Query optimization techniques to transform SQL queries to an optimal equivalent relational algebra query

Secondary goal of CS554: maintaining data integrity in spite of system failure and concurrent execution

Fact:
Effect of system failure on data processing:

Example:

Transfer $100 from account John to another account Jane update BankAccount where ID = 'John' set balance = balance - 100 update BankAccount where ID = 'Jane' set balance = balance + 100
Suppose: Read John's balance John.balance = John.balance - 100 Write John's balance ******************************** Power failure - System crashs..... Read Jane's balance Jane.balance = Jane.balance + 100 Write Jane's balance

Result of the system failure:

$100 deducted from John's account
But: $100 is not credited to Jane's account !!!!!

We will study techniques to overcome system failures (logging)

Concurrent insert, delete and update operations

Fact:
Unsynchronized concurrent update operations can reult in:

Example: concurrent update to a bank account balance

Homer and Marge share a bank account
Homer withdraws $100 at ATM 1
Marge withdraws $50 at ATM 2

Sequential execution of updates:

Homer withdraws $100: read balance; --> balance = $400 if (balance > amount) then { balance = balance - 100; --> balance = $300 write balance; ==> Writes: $300 } 1 hour later: Marge withdraws $50: read balance; --> balance = $300 if (balance > amount) then { balance = balance - 50; --> balance = $250 write balance; ==> Writes: $250 }

Final balance = $250 (correct outcome)

If Homer does this at the same time as Marge:

Homer withdraws $100: read balance; --> balance = $400 Marge withdraws $50: read balance; --> balance = $400 if (balance > amount) then { balance = balance - 50; --> balance = $350 write balance; ==> Writes: $350 } if (balance > amount) then { balance = balance - 100; --> balance = $300 (400 − 100) write balance; ==> Writes: $300 }

Final balance = $300 !!! (inconsisten outcome)

We will study techniques to overcome simultaneous updates (concurrency control)

Overview of a Database Management System (DBMS) system
- A Database Management System (DBMS) must provide:
- Typical Database System:

Architecture of a modern DBMS

Building blocks of a modern DBMS:

Parser: break down an SQL command into a number of logical steps to retrieve the data
Query optimizer: re-arrange the logical steps to obtain the best query plan
Query executor: execute the (best) query plan
Storage manager: maintain the files and indexes needed to store the data and access the data efficiently

We will study how to build each component.

Text book
- Database Systems: The Complete Book, by H. Garcia-Molina, J. D. Ullman, and J. Widom