Transcript databaseConcept

Database System Concepts and
Architectures
Indra Budi
[email protected]
Which of the following is a problem of
lists that is solved by using a database?
Name
Card Number
Book
Due Date
Joe Smith
Sara Jones
Sam Archer
Sam Archer
Joe Smith
123-450201
123-450217
123-450326
123-450326
123-450201
How To Garden
Some Good Fiction
How To Build Things
More Good Fiction
More How To Garden
09/12/20xx
09/12/20xx
09/15/20xx
09/15/20xx
09/17/20xx





data inconsistencies
problems adding data
problems deleting data
missing data
All of the above
In database processing systems, the data is
directly accessed only by the ….
 In database application processing systems,
the user interacts directly with the …

Evolution of computing

Database has evolved since nearly every
stage of computing, from:
– mainframes, which are monolithic
– Client-server
– Web-based
Data Models

A collection of concepts used to describe
the structure of a database
–
–
–
–
Data types
Relationships
Constraints
Etc
Categories of Data Models
Conceptual data models: high level
 Physical data models: how data is actually
stored in a disk
 Representational data models: somewhere
between those two… let’s see…

Representational data model
Relational data model (our main discussion
in this class)
 Network data model (uses directed graphs)
 Hierarchical data model (history…)
 Object data model (this is new!)

Database schemas


Schemas: description of the database, but not the database itself!
Example of a schema diagram:

Not included in the schema diagram above:
– Data types?
– Relationships?
– Constraints, such as “students in CS major must
take CS1310…”
Database states
Also called ‘snapshot’
 After we define, generally a database is at
the ‘empty state’
 We get the ‘initial state’ after we first load
the database
 Valid state is a state which satisfies the
structure and constraints in the schema

Three Schema Model


ANSI/SPARC introduced the three schema model in 1975
It provides a framework describing the role and purpose of
data modeling
Three Schema Model (cont.)

External schema or user view
– Representation of how users view the database

Conceptual schema
– A logical view of the database containing a description of all the
data and relationships
– Independent of any particular means of storing the data
– One conceptual schema usually contains many different external
schemas

Internal schema
– A representation of a conceptual schema as physically stored on a
particular product
– A conceptual schema can be represented by many different internal
schemas
Data Independence



Application insulated from how data is structured
or stored
Logical data independence: we can change the
conceptual schema without changing the
application program (internal schema)
Physical data independence: we can change the
internal schema without changing the conceptual
schema. It might be because file database must be
reorganized for fine tuning.
Database Languages
Data definition language
 View definition language
 Data manipulation language

– High level: SQL, set-at-a-time processing
– Low level: VB, COBOL, record-at-a-time
processing
DBMS Interfaces






Menu based interface
Forms based interface
Graphical user interface  utilize menu & forms
Natural language interface, you can ask, “Show
me all the students with GPA > 3”
Parametric interface, such as application for Bank
Teller
Interface to DBA: create account, granting
account, changing schema, delete db, etc.
DBMS utilities
Concurrency Control
 Loading utility, or importer
 Backup utility, usually to tape
 File optimizer or reorganizer
 Performance monitoring: fragmentation,
load balancing
 Commit & Rollback
 etc

Transaction: An Execution of a
DB Program


Key concept is transaction , which is an atomic
(all-or-nothing property), sequence of database
actions (reads/writes).
Each transaction, executed completely, must leave
the DB in a consistent state, if DB is consistent
when the transaction begins.
– Users can specify some simple integrity constraints on
the data, and the DBMS will enforce these constraints.
– Beyond this, the DBMS does not really understand the
semantics of the data. (e.g., it does not understand how
the interest on a bank account is computed).
– Thus, ensuring that a transaction (run alone) preserves
consistency is ultimately the user’s responsibility!
Classification of DBMS
Data model: relational, network,
hierarchical, etc.
 Number of users: Single user vs multi user
 Centralized vs distributed
 Price…!
 OLTP support?
 OLAP support (read page 842 of Elmasri)

Which of the following plays an
important roles representing information
about a real world in the database ?
The data definition language
 The data manipulation language
 The buffer manager
 The data model

What is the differences ?

Database Schema Vs Database State ?
Group Assignment
Comparative Study of Popular DBMS
Which ones?
Group 1 (Mahesa, Evan
Bambang, Panca)
 MySQL
 MS-SQL Server
 FoxPro
Group 2 (Lamo, Siti, Arief)
 Informix
 MS-Access
 IBM DB2
Group 3 (Tyas, Dika, Ririn)
 Oracle database
 Postgres
 Borland Interbase
What to look for ?
Use theories in Elmasri Ch 1 & 2, and
Kroenke Ch 1!
 Examples: function, concurrency control,
type, price, primary use ?, performance,
capacity, etc.

Deliverables
Paper, submitted to me, you can give me
directly or email me at [email protected],
due date on Tuesday, September 14th 2004
 Presentation (of course)  Wednesday,
September 15th 2004
