Databases and Database Management System

1
Databases and Database Management System
2
Goals

• comprehensive introduction to
• the design of databases
• database transaction processing
• the use of database management systems for
  applications

3
Topics

• the entity-relationship (E/R) and relational data
  models
• approaches to database design
• abstract query languages (relational algebra)
• SQL
• issues in database design and use (views,
  integrity constraints, triggers, transactions,
  and security)
• physical data organization
• query processing and optimization
• transaction processing
• advanced topics (data warehouses, data mining,
  temporal databases, and XML) time permit

4
Book

1. Textbook Databases and Transaction Processing
   An Application-Oriented Approach by Lewis,
   Bernstein, and Kifer (Addison Wesley)
2. Additional reading
3. A first course in Database Systems by Ullman
   Widom
4. Database systems the complete book by
   Garcia-Molina, Ullman, Widom

5
Organizational issues

• Class web site http//www.cs.nmsu.edu/tson/class
  es/fall03-482
• 5 weeks First mid-term (September 18)
• 11 weeks Second mid-term (October 28)
• December 8 Final
• Office hours 4-5 pm Tues Thus. I will need to
  go home right after the class on Tues, please do
  not try to ask questions after Tues class?
• Use of emails questions, notifications
• Homework submission web based

6
What is a Database?

• American Heritage Dictionary a collection of
  data organized for easy and speed of search and
  retrieval

7
What is a Database?

• Related to data, perhaps lot of them
• same characteristics
• over long period of time (months, years, )
• Pertinent to people who use the data
• Might or might not be of interest to other

8
Definition and Properties

• Definition A database is a collection of data
  central to some enterprise that is managed by a
  Database Management System (DBMS)
• Properties
• Essential to operation of enterprise (contains
  the only record of enterprise activity)
• Valuable to the enterprise (Historical data can
  guide enterprise strategy, might be of interest
  to other enterprises)
• Reflection of the state of the enterprise
  (database is persistent)

9
DBMS

• Specialized software manages databases
• create new databases
• modify existing databases (update data, create
  reports for different purposes)
• Supports
• high-level access language (e.g. SQL)
• application describes database accesses using
  that language.

10
DBMS

• Provides users with
• Persistent storage like file system but much
  more flexible
• Programming interface accessing and modifying
  data through a query language
• Transaction management concurrent access to data

11
Overview of a DBMS
Admin
DBMS (Oracle, DB2, MySQL, )
Data-bases
Results
Modify/ Retrieval (Command)
Users
12
Overview of a DBMS (Cont.)

Transaction Manager
Results
Storage Manager
commands
M
Query Processor
E
Users

• Storage manager uses OS techniques in memory
  management
• (buffer, page, read/write pages)
• Query processor receive queries, create query
  plans, send to execution
• engine primitive commands (index,
  file, record requests),
• which will be satisfied by the
  corresponding manager
• Transaction manager maintains the consistency
  of the database
• (control read from/write to a database,
  concurrency execution,
• recovery)

Data-bases
E
M
13
Overview of a DBMS (Cont.)

• Database administrator
• set-up databases
• creates new schema
• modifies existing schema
• manages users (authorization, permission, etc.)
• uses Data Definition Language a specialized
  language for creating and defining database
  schema

14
Query Processing
User/application
queries/updates
Query Compiler
15
Transaction Manager

• Responsible for the consistency of database
• changes in the real-world are reflected correctly
  in the database
• every time a real-world event happens, a
  transaction occurs to cause the corresponding
  changes in the database
• Definition A transaction is an application
  program with special properties see next slides
  to guarantee it maintains database correctness

16
Properties of Transactions (ACID)

• Atomicity ALL-or-NOTHING execution (a sequence
  of primitive commands that needs to be executed
  ALL or NONE).
• Isolation No two transactions should be executed
  at the same time.
• Durability Effects of a transaction can never be
  lost
• Consistency Constraints are satisfied all the
  time

17
Transaction Manager

• Log manager every change in the database is
  logged separately on the disk (for recovery or
  durability)
• Concurrency-control manager for isolation (uses
  lock, similar to lock in OS)
• Deadlock resolution resources control

18
Today

• Examples of database systems (databases)
• Characteristics of current db vs. old one
• Requirements on db systems
• People involving in designing, implementing, and
  maintaining of db
• Study directions in db systems
• Two common applications of db

19
Examples of databases

• Airline reservation system
• Banking system
• Student registration system
• Supermarket
• Corporate record
• .

20
Airline reservation system

• Data Information about flights
• Flight number, type of aircraft
• Date, time, departure airport, arrival airport
• Number of seats (1st, 2nd class if applicable)
• Lists of travelers, their reservation
• Ticket prices, number of available seats
• Operations (Queries/Transactions)
• Customer inquires about the availability of a
  flight, ticket for a flight
• Customer makes a reservation
• Customer cancels a reservation
• Properties
• Large number of transactions (very frequently)
• Cannot be processed in batch mode (on-line
  transaction processing)
• Concurrency required

21
Banking system

• Data Account information
• Customer information (name, address, accounts,
  balances)
• Relationship between customers and accounts
• Operations (Queries/Transactions)
• Customer inquires about the balance of one of its
  accounts
• Customer makes a deposit
• Customer withdraws
• Properties
• Large number of transactions (very frequently)
• Cannot be processed in batch mode (on-line
  transaction processing)
• Concurrency required
• Recovering from failures

22
Student Registration System

• Data Information about students and courses
• Student information (name, address, SSN, status,
  major, minor, courses taken and grade, courses
  enrolled, balance, picture)
• Course information (name, call number, number,
  credit hours, department, instructor, date and
  time, location, number of students)
• Operations (Queries/Transactions)
• Students ask for a transcript, list of enrolled
  classes
• Adding/Dropping classes
• Prerequisites enforcement
• Properties
• Large number of transactions at the beginning and
  end of semester
• Batch mode processing possible (better with
  on-line transaction processing)
• Concurrency required

23
Databases (Now vs. Then)

• Relational model using SQL - high-level view of
  data
• Older systems presented low-level view
• Might contain multimedia data
• Older systems restricted to alphanumeric data
• On-line database accessed at time of event
• Older systems were off-line, batch

24
Databases (Now vs. Then)

• Concurrent - multiple transactions execute
  simultaneously
• Older systems processed transactions sequentially
• Distributed computation - different parts of the
  application execute on different computers
• Older systems were centralized

25
Databases (Now vs. Then)

• Distributed data - different parts of the data
  are stored in different databases on different
  computers
• Older systems were centralized
• Heterogeneous - involves HW and SW modules from
  different manufacturers
• Older systems were homogeneous
• Accessed by everyone (e.g., e-commerce)
• Older systems restricted to trained personnel

26
Database (System) Requirements
27
Database (System) Requirements

• High Availability on-line gt must be operational
  while enterprise is functioning
• High Reliability correctly tracks state, does
  not lose data, controlled concurrency
• High Throughput many users gt many
  transactions/sec
• Low Response Time on-line gt users are waiting

28
Requirements (cont.)

• Long Lifetime complex systems are not easily
  replaced
• Must be designed so they can be easily extended
  as the needs of the enterprise change
• Security sensitive information must be carefully
  protected since system is accessible to many
  users
• Authentication, authorization, encryption

29
People in Design, Implementation, and Maintenance
of a Database

• System Analyst - specifies system using input
  from customer provides complete description of
  functionality from customers and users point of
  view
• Database Designer - specifies structure of data
  that will be stored in database
• Application Programmer - implements application
  programs (transactions) that access data and
  support enterprise rules

30
People (cont.)

• Database Administrator - maintains database once
  system is operational space allocation,
  performance optimization, database security
• System Administrator - maintains transaction
  processing system monitors interconnection of HW
  and SW modules, deals with failures and congestion

31
Database System Studies
32
Design of databases

• how to design a database
• what should be stored
• which structure for the data
• what assumptions should be made
• how is the connection between data

33
Database programming

• how to write queries on the database
• how to use other capabilities of a DBMS in an
  application
• how is database programming combined with
  conventional programming

34
Database System Implementation

• how to build a DBMS (query processing,
  transaction processing, storage manager etc.)
• This will not be discussed in this course.

35
Application of Database
36
Decision Support System(OLTP vs. OLAP)

• On-line Transaction Processing (OLTP)
• Day-to-day handling of transactions that result
  from enterprise operation
• Maintains correspondence between database state
  and enterprise state
• On-line Analytic Processing (OLAP)
• Analysis of information in a database for the
  purpose of making management decisions

37
On-Line Analytical Processing

• Analyzes historical data (terabytes) using
  complex queries
• Due to volume of data and complexity of queries,
  OLAP often uses a data warehouse
• Data Warehouse - (offline) repository of
  historical data generated from OLTP or other
  sources
• Data Mining - use of warehouse data to discover
  relationships that might influence enterprise
  strategy

38
Exp Airline reservation system

• OLTP
• Event customer A books tickets from ELP to NY
  update database to reflect that event
• OLAP
• During the last holiday season, how many
  customers fly from ELP to Dallas and NY?
• Data Mining
• Are there any airports in which more than 50 of
  travelers from ELP need to change their flight?