Transcript Database System Concepts - Department of Systems Engineering

Database System Concepts
Database System Concepts - 6th Edition
1.1
©Silberschatz, Korth and Sudarshan
Top 10 Largest Databases 2013
By Siliconindia News
 The World Data Centre for Climate (WDCC): 220 terabytes of data,
plus 110 terabytes of data for climate simulation, and 6 petabytes of
extra data stored in magnetic tapes.
 National Energy Research Scientific Computing Center (NERSC)
2.8 petabytes
 AT&T: It has the largest volume of data in one unique database, with
the most number of rows, 1.9 trillion.
 Google: Google accounts every single search that makes each day
into its database which is around 91 million searches per day.
 Sprint (Telecom company)
 LexisNexis
 Youtube
 Amazon
 Central Intelligence Agency (CIA)
 Library of Congress (USA)
Database System Concepts - 6th Edition
1.2
©Silberschatz, Korth and Sudarshan
Database Management System (DBMS)
 DBMS contains information about a particular enterprise

Collection of interrelated data

Set of programs to access the data

An environment that is both convenient and efficient to use
 Database Applications:

Banking: transactions

Airlines: reservations, schedules

Universities: registration, grades

Sales: customers, products, purchases

Online retailers: order tracking, customized recommendations

Manufacturing: production, inventory, orders, supply chain

Human resources: employee records, salaries, tax deductions
 Databases touch all aspects of our lives
Database System Concepts - 6th Edition
1.3
©Silberschatz, Korth and Sudarshan
Database Landscape Map – June 2013
Database System Concepts - 6th Edition
1.4
©Silberschatz, Korth and Sudarshan
Google Data Center
 Google Cloud SQL
allows users to use
relational database in
Google’s Cloud.
Database System Concepts - 6th Edition
1.5
©Silberschatz, Korth and Sudarshan
SAP HANA
 SAP HANA is an in-memory relational database system.
Taken from a talk given by Dr. Wen-Syan Li (VP of SAP)
Database System Concepts - 6th Edition
1.6
©Silberschatz, Korth and Sudarshan
Taken from a talk given by Dr. Wen-Syan Li (VP of SAP)
Database System Concepts - 6th Edition
1.7
©Silberschatz, Korth and Sudarshan
Taken from a talk given by Dr. Wen-Syan Li (VP of SAP)
Database System Concepts - 6th Edition
1.8
©Silberschatz, Korth and Sudarshan
Amazon Web Services (AWS)
 Amazon RDS: a relational database server with minimal
administration, using MySQL, Oracle, or SQL Server.
 Amazon DynamoDB: a fast highly scalable NoSQL
database service.
 Amazon SimpleDB: A NoSQL database service for
smaller dataasets
 Amazon EC2 and EBS: A relational database you can
manage on your own.

EC2: Amazon Elastic Compute Cloud

EBS: Amazon Elastic Block Storage
Database System Concepts - 6th Edition
1.9
©Silberschatz, Korth and Sudarshan
Relational Model
 Relational model (Chapter 2)
 Example of tabular data in the relational model
Columns
Rows
Database System Concepts - 6th Edition
1.10
©Silberschatz, Korth and Sudarshan
Schemas and Instances
 Schema – the logical structure of the database (like variables)

Example: The database consists of information about a set
of customers and accounts and the relationship between
them

Physical schema: database design at the physical level
(how we store data on disk for system to fast access data)

Logical schema: database design at the logical level
(how users see the data format in order to access data)
 Instance – the actual content of the database (like values)
 Physical Data Independence – the ability to modify the
physical schema without changing the logical schema

Applications depend on the logical schema

Changes in some parts do not seriously influence others.
Database System Concepts - 6th Edition
1.11
©Silberschatz, Korth and Sudarshan
Data Definition Language (DDL)
 Specification notation for defining the database
schema
Example: create table instructor (
ID
char(5),
name
char(20),
dept_name char(20),
salary
numeric(8,2))
 DDL compiler generates a set of tables stored in a
data dictionary
Database System Concepts - 6th Edition
1.12
©Silberschatz, Korth and Sudarshan
Data Manipulation Language (DML)
 Language for accessing and manipulating the data
organized by the appropriate data model

DML also known as query language
 SQL is the most widely used query language

Users specifies what data is required and how to
get those data
Database System Concepts - 6th Edition
1.13
©Silberschatz, Korth and Sudarshan
SQL
 Find the name of the instructor with ID 22222
select name
from instructor
where instructor.ID = ‘22222’
 Find all instructor IDs if they are in a department whose budget >
95000
select instructor.ID, department.dept_name
from instructor, department
where instructor.dept_name= department.dept_name and
department.budget > 95000
 Application programs generally access databases through one of

Language extensions to allow embedded SQL

Application program interface (e.g., ODBC/JDBC) which allow
SQL queries to be sent to a database
Database System Concepts - 6th Edition
1.14
©Silberschatz, Korth and Sudarshan
Storage Management
 Storage manager provides the interface between the
low-level data stored in the database and the application
programs and queries submitted to the system.
 The storage manager is responsible to efficient storing,
retrieving and updating of data
 Issues:

Storage access

File organization

Indexing and hashing
Database System Concepts - 6th Edition
1.15
©Silberschatz, Korth and Sudarshan
Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
Database System Concepts - 6th Edition
1.16
©Silberschatz, Korth and Sudarshan
Query Processing (Cont.)
 Alternative ways of evaluating a given query

Equivalent expressions

Different algorithms for each operation
 Cost difference between a good and a bad way of
evaluating a query can be enormous
 Need to estimate the cost of operations

Depends critically on statistical information about
relations which the database must maintain

Need to estimate statistics for intermediate results
to compute cost of complex expressions
Database System Concepts - 6th Edition
1.17
©Silberschatz, Korth and Sudarshan
Atomicity of Updates & Concurrent
Access
 Atomicity of updates
Failures may leave database in an inconsistent state with
partial updates carried out
 Example: Transfer of funds from one account to another
should either complete or not happen at all
 Concurrent access by multiple users
 Uncontrolled concurrent accesses can lead to
inconsistencies
 Example: Two people reading a balance (say 100) and
updating it by withdrawing money (say 50 each) at the
same time
 Concurrent access needed for performance

Database System Concepts - 6th Edition
1.18
©Silberschatz, Korth and Sudarshan
Transaction Management
 To deal with the system fails.
 To control when many users concurrently update the same
data.
 A transaction is a collection of operations that performs a
single logical function in a database application
 Transaction-management component ensures that the
database remains in a consistent (correct) state despite system
failures (e.g., power failures and operating system crashes)
and transaction failures.
 Concurrency-control manager controls the interaction among
the concurrent transactions, to ensure the consistency of the
database.
Database System Concepts - 6th Edition
1.19
©Silberschatz, Korth and Sudarshan
Database System Internals
Database System Concepts - 6th Edition
1.20
©Silberschatz, Korth and Sudarshan