CMPUT 391 Database Management Systems

1
CMPUT 391 Database Management Systems

• Li-Yan Yuan

2
Objectives

• To understand the fundamental concepts and
  advanced technology underlying database systems
• modern database systems
• object-relational databases
• multimedia support for databases
• database design methodology
• database management systems
• To gain hand-on experience with database
  application systems
• developing a small application system using
  ORACLE
• Java and database-backed web-sites

3
Textbooks
Database Systems by M. Kifer, A. Berstein, and P.
Lewis second edition
Other on line materials
Lecture Notes and Home Page
http//www.cs.ualberta.ca/yuan/courses/391/W08/
4
Course Requirements

• understand modern database systems
• learn how to use the relational database design
  theory and methods
• be able to develop database application systems
  with web interface

5
Contents

• Modern database systems
• object-relational databases
• active databases
• Relational database design method
• dependency theory
• normal forms
• Query processing and optimization
• Transaction management
• concurrence control
• crash recovery
• Security and authorization
• OLAP, data mining and data warehouse
• Network storage systems

6
Administration

• Office Hours
• MWF 1300 -1400 ATH 356
• Grading
• Assignment 5
• Project 35
• midterm exam 25
• Final exam 35
• Re-examination
• none, except as per regulation
• deferred exam must be scheduled by May 5, 2008
• Collaboration
• Collaborate on assignments, but do not merely
  copy.
• Course web page and the newsgroup

7
Laboratories

• Teach Assistants
• Sajib Barua, Amit Satsangi
• Lab times
• H01 W 1400-1650, CSC 219
• H02 Th. 1400-1650, CSC 219

8
Introduction to Database Management Systems
Q
9
A file processing system within an operation
system
a logic file
physical files
A file system sets up mappings between logical
files and physical files.
10
Data any information
Database a large collection of data
an integrated collection of
data Database management system
a software system that provides an efficient
as well as convenient
environment for accessing data in a
database.
11
Functionalities of DBMSs

• Specifying the database structure
• data definition language
• Manipulation of the database
• query processing and query optimization
• Integrity enforcement
• integrity constraints
• Concurrent control
• multiple user environment
• Crash recovery
• Security and authorization

12
Database Approach
PROGRAM 1
DBMS
Query Processor
Integrated Database
PROGRAM 1
Transaction Mgr

PROGRAM 2
13
Why file management systems? efficient
file accesses Why database
management system? efficient and
convenient data access
File management systems goal
efficiency problem high speed
ram vs. low speed disk access solution
complicated file structures Database
management systems goal
efficiency as well as convenience
problem conflicts between efficiency and
convenience solution data
independence supported by various database
models
14
History of File Organizations
Sequential search index sequential
B-tree Hashing Classification of
Database Models Entity-relationship
network hierarchical relation
object-oriented deductive
object-relational
Get information from a disk file in just a few
disk accesses
Various database models provide logical and
physical data independence to separate simple
logical database structures and complicated
physical file structures.
15
Role in an Information System
G.C. Everest. Database Management, McGraw Hill,
1986
16

• Goals of a database management system
• provides an efficient as well as convenient
  environment for accessing data in a database.
• database query and update
• enforce information security
• database security , concurrence control, and
  crash recovery
• Conflict interest between convenience and
  efficiency
• data abstraction

17

• Three level of data abstraction
• physical level
• describe how data are actually stored in a
  database
• conceptual level
• describe what data are actually stored in a
  database
• view level
• describe a part of the entire database
• Database scheme and instance
• Scheme overall structure (plan) of the database
• Instance the current contents of the database
• three levels of database scheme
• physical scheme file structure
• conceptual scheme (logical scheme) the database
  structure
• view scheme a part of database structure that is
  of interest to specified users.

18

• Data independence
• capability of changing a database scheme without
  having to change the scheme at the next higher
  level
• two level of data independence

view scheme
logical data independence
logic scheme
physical data independence
physical scheme
19

• Database Languages
• data definition language (DDL)
• specify the conceptual database scheme
• view definition language, storage definition
  language
• data manipulation language (DML) query language
• used to retrieve and update information in a
  database
• host language a conventional high level language
  used to write application programs
• database manager
• a program module within a DBMS which provides
• the interface between the lower level file
  manager and high level DML and application
  programs
• integrity checking
• security enforcement
• crash recovery
• concurrence control

20

• Database users
• naive users
• casual users
• application programmers
• Database administrator
• a person who is responsible for the database
  design, scheme modification, user authorization,
  etc..

21
Database Management System Structure
naive
application
casual
data
users
programmers
users
administrator
application
database
system
query
programs
scheme
calls
data manipulation
query
data definition
language
processor
language
precompiler
compiler
database
application
program
manager
DBMS
object code
file manager
data
files
data
dictionary
H.F. Korth and A. Silberschatz. Database System
Concepts, McGraw-Hill, 1986.
22

• Database models
• conceptual tools used to describe
• data
• data relationships
• data semantics
• data constraints
• Major database models
• E-R model a logic foundation for conceptual
  database design
• relational model a set of tables
• object-oriented mode
• deductive databases
• object-relational databases