Databases and Database Management System - PowerPoint PPT Presentation

Loading...

PPT – Databases and Database Management System PowerPoint presentation | free to view - id: cfb14-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Databases and Database Management System

Description:

issues in database design and use (views, integrity constraints, triggers, ... Two common applications of db. 19. Examples of databases. Airline reservation system ... – PowerPoint PPT presentation

Number of Views:227
Avg rating:3.0/5.0
Slides: 39
Provided by: TranC6
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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?
About PowerShow.com