Textbook and Notes

CMPT 354 Textbook:

Database Systems and Structures Database System Concepts, 3rd Edition
Abraham Silberschatz, Henry Korth, S. Sudarshan
◆
Course home page:
◆ Osmar R. Zaïane http://www.cs.sfu.ca/CC/354/zaiane/
◆

Summer 1998

Office Hours Course Objectives

To provide an introduction to data models, database systems,

Wednesdays 17:30 to 19:30 the structure and use of relational database systems and
Thursdays 20:30 to 21:30 relational languages, indexing and storage management,
query processing in relational databases, and the theory of
Teaching Assistant relational database design.
Kris Koperski
Mondays 16:30 to 18:45
Tuesdays 16:30 to 18:15 Students will understand the fundamental
Fridays 16:30 to 17:30 concepts underlying database systems and
gain hand-on experience with applications
using database systems.

Contents
Grading
◆ Architecture of database systems
◆ Data models » Assignments ??%
– entity-relationship model
» Project ??%
– relational model
◆ Relational Languages
» Midterm ??%
◆ Relational Database Design » Final exam ??%
◆ Object-Oriented Databases
◆ Storage and File Structures
◆ Indexing and Hashing To be discussed...
◆ Query Processing

1
Chapter 1 Objectives a logic file

Get a rough initial idea what a database Operating physical

management system (DBMS) is and what system files
types of models exist.

Get an overview about how databases work

and the different issues with DBMSs. A file system sets up mappings between logical files and
physical files.

File management system: efficient file access

Database management system: efficient data access Purpose of a Database System
DBMS developed to handle the following difficulties:
Database:
a large collection of data ◆ Data redundancy and inconsistency
an integrated collection of data
◆ Difficulty in accessing data
Database management system: ◆ Data isolation - multiple files and formats
a software system that provides an efficient as
◆ Integrity problems
well as convenient environment for accessing as
well as updating data in a database. ◆ Atomicity of updates
◆ Concurrent access by multiple users
◆ Security problems

Functionality of a Database System Database Approach

◆ Specifying the database structure
– data definition language
PROGRAM 1
◆ Manipulation of the database DBMS
– query processing and query optimization
Query Processor
◆ Integrity enforcement Integrated
PROGRAM 1
Transaction Mgr Database
– integrity constraints …
◆ Concurrent control
– multiple user environment PROGRAM 2

◆ Crash recovery
◆ Security and authorization

2
 History of File Organizations:
Sequential search
index sequential Get information from a disk file
B-tree in just a few disk accesses ■ Goals of a database management system
Hashing ● provides an efficient as well as convenient
environment for accessing data in a database.
Classification of Database Models: ➠ database query and update
Entity-relationship
● enforce information security
network
➠ database security , concurrence control, and crash recovery
hierarchical
relation Various database models provide ■ Conflict interest between convenience and
logical and physical data independence
object-oriented efficiency
deductive to separate simple logical database
structures and complicated physical ● data abstraction
object-relational
file structures.

■Three level of data abstraction ■ Data independence

●physical level ● capability of changing a database scheme without having
➠describe how data are actually stored in a database to change the scheme at the next higher level
●conceptual level ● two level of data independence
➠describe what data are actually stored in a database
●view level
➠describe a part of the entire database
■Database scheme and instance view scheme

●Scheme: overall structure (plan) of the database logical data independence

●Instance: the current contents of the database logic scheme
●three levels of database scheme
➠physical scheme: file structure physical data independence
➠conceptualscheme (logical scheme): the database structure physical scheme
➠view scheme: a part of database structure that is of interest to specified users.

■ Database Languages
● data definition language (DDL) :
➠ specify the conceptual database scheme
✦ view definition language, storage definition language
● data manipulation language (DML): query language ■ Database users
➠ used to retrieve and update information in a database
● naive users
● host language: a conventional high level language used
● casual users
to write application programs
● application programmers
■ database manager
● a program module within a DBMS which provides
■ Database administrator
➠ the interface between the lower level file manager and high level ● a person who is responsible for the database design,
DML and application programs scheme modification, user authorization, etc..
➠ integrity checking
➠ security enforcement
➠ crash recovery
➠ concurrence control

3
 ■ Database models:
● conceptual tools used to describe
Database Management System Structure
➠ data

naive application casual data ➠ data relationships

users programmers users administrator ➠ data semantics

application system database ➠ data constraints

query
programs calls scheme
■ Major database models
data manipulation
language query data definition ● E-R model: a logic foundation for conceptual database
processor language
precompiler
compiler
design
application
program
database ● network model: a set of records connected by links
manager
object code DBMS
● hierarchical model: a set of database trees
file manager ● relational model: a set of tables

data
files
data
dictionary

H.F. Korth and A. Silberschatz. Database System Concepts, McGraw-Hill, 1986.