CMPT 354

1
CMPT 354

• Introduction

2
Course Website

• http//sleepy.cs.surrey.sfu.ca/cmpt/fall2005/cmpt3
  54/

3
Assessment

• Assignments 30
• Midterm exam in class 25
• Final exam on the 15th of April 45

4
Data Explosion

• Datasets are increasing in diversity and size
  e.g.
• Human genome project
• Web enterprises
• For example
• The data on weather and climate stored in the
  National Climatic Data Center has increased
  750-fold in the past two decades
• A 1998 survey identified that in the U.S. in 1975
  there were some 301 databases containing about 52
  million records, whereas in 1998 there were
  11,339 databases holding nearly 12.05 billion
  records!
• Martha E. Williams (1998), "State of Databases
  Today 1999," in Gale Directory of Databases, L.
  Kumar, ed.

5
What is a Database System?

• A database system consists of two components
• Database (DB) and
• Database Management System (DBMS)
• The DB contains the data
• The DBMS is software that stores, manages and
  retrieves the information in the DB

6
Data Storage Without DBMS

• Data would be collected in many different files
  and
• Used by many application programs

File 1
Application Program 1
File 2
Application Program 2


File m
Application Program n
7
What Happens If

• An attribute is added to one of the files?
• Information that exists in more than one file is
  changed by an application program that only
  interacts with one of those files?
• We need to access a single record out of millions
  of records?
• Several programs need to access and modify the
  same record at the same time?
• The system crashes while one of the application
  programs is running?

8
Data Storage with a DBMS
File 1
Application Program 1
File 2
Application Program 2
DBMS


File m
Application Program n
9
DBMS Functions

• All access to data is centralized and managed by
  the DBMS which provides
• Logical data independence
• Physical data independence
• Reduced application development time
• Efficient access
• Data integrity and security
• Concurrent access and concurrency control
• Crash recovery

10
Data Models

• A DBMS models a real-world enterprise
• A data model is a formal language for describing
  data
• A schema is a description of a particular
  collection of data using a particular data model
• The most widely used model is the relational data
  model, which we will describe and use in this
  course
• The main concept of this model is the idea of a
  relation, essentially a table with rows and
  columns

11
Data Abstraction

• A DBMS data can be described at three levels of
  abstraction
• Physical schema
• The lowest level schema
• Describes how data are stored and indexed
• Conceptual (or logical) schema
• What (not how) data are stored.
• Describes data in terms of the data model.
• External (or view) schema
• The highest level schema
• Describes how some users access the data
• There can be many different views

12
Levels of Abstraction
View 2
View 3
View 1
Conceptual Schema
Physical Schema
13
Typical DBMS Structure
DBMS
Query Evaluation
Transaction and Lock Manager
Recovery Manager
Conceptual Schema
Conceptual Schema
Disk Space Manager
14
Data Independence

• The different layers (of abstraction) of a
  database allow a schema at one level to be
  modified without affecting the others
• Allows application programs to be relatively
  independent from the data
• So that they do not need to be modified as a
  result of changes to the database structure or
  storage
• This results in reduced application development
  and maintenance time

15
Types of Data Independence

• Physical data independence
• Allows the physical schema to be modified without
  rewriting application programs
• Usually to improve performance
• e.g. adding or removing an index or moving a file
  to a different disk
• Logical data independence
• Shields users from changes in the logical schema
  i.e. their views remain unchanged
• Allows the logical schema to be modified without
  rewriting application programs
• e.g. adding an attribute to a relation

16
Efficient Access

• What happens when a user wants to find one record
  out of millions?
• An index structure maps the desired attribute
  values to the address of the record
• The desired records can be retrieved without
  scanning the whole relation
• This makes query processing efficient

17
Concurrency Control

• What happens when two users try to change the
  same record at the same time?
• Without concurrency control inconsistencies may
  arise
• e.g. processing an automated withdrawal at the
  same time that the account owner is making a
  deposit
• A DBMS system ensures that these problems do not
  occur while allowing for maximal possible access
  of the data
• e.g. many users can read the same record at the
  same time but only one user at a time can modify
  a record

18
Transactions

• Modifications to a DB occur as a result of
  transactions
• A transaction is a sequence of reads and writes
  to the DB caused by any one execution of user
  program and are
• Atomic all or nothing
• Consistent the DB must be in a consistent state
  after the transaction
• Isolated transactions are performed serially
• Durable the effects of a transaction are
  permanent

19
Data Integrity

• The data should be consistent with the
  information that it is modeling
• The DBMS cannot actually understand what the data
  represents
• Users can specify integrity constraints on the
  data and the DBMS will then enforce these
  constraints
• e.g. not allowing ages to be negative

20
Crash Recovery

• What happens if the system crashes in the middle
  of processing a transaction?
• The DBMS maintains a log of all writes to the DB
• The log is updated just before the write is
  processed
• After a crash occurs
• The log is used to undo any partially executed
  transactions and
• To redo any completed transactions (that were not
  saved to a disk before the crash)

21
Database Users

• End users
• May have specialized knowledge (CAD etc.) and may
  be familiar with SQL
• The majority have no DB knowledge, and should not
  require any.
• DB Administrators
• Have central control over data and programs that
  access that data.
• Database Application Programmers
• Write programs that need to interact with the DB
  (through ODBC, ADO etc.)
• DB Implementers and Vendors
• Build and sell DB products