Database System Concepts and Architectures

1
Database System Concepts and Architectures

• Indra Budi
• indra_at_cs.ui.ac.id

2
Which of the following is a problem of lists that
is solved by using a database?

• data inconsistencies
• problems adding data
• problems deleting data
• missing data
• All of the above

3

• In database processing systems, the data is
  directly accessed only by the .
• In database application processing systems, the
  user interacts directly with the

4
Evolution of computing

• Database has evolved since nearly every stage of
  computing, from
• mainframes, which are monolithic
• Client-server
• Web-based

5
Data Models

• A collection of concepts used to describe the
  structure of a database
• Data types
• Relationships
• Constraints
• Etc

6
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 lets see

7
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!)

8
Database schemas

• Schemas description of the database, but not the
  database itself!
• Example of a schema diagram

9

• Not included in the schema diagram above
• Data types?
• Relationships?
• Constraints, such as students in CS major must
  take CS1310

10
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

11
Three Schema Model

• ANSI/SPARC introduced the three schema model in
  1975
• It provides a framework describing the role and
  purpose of data modeling

12
(No Transcript)
13
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

14
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.

15
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

16
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 gt 3
• Parametric interface, such as application for
  Bank Teller
• Interface to DBA create account, granting
  account, changing schema, delete db, etc.

17
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

18
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 users
  responsibility!

19
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)

20
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

21
What is the differences ?

• Database Schema Vs Database State ?

22
Group Assignment

• Comparative Study of Popular DBMS

23
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

24
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.

25
Deliverables

• Paper, submitted to me, you can give me directly
  or email me at indra_at_cs.ui.ac.id, due date on
  Tuesday, September 14th 2004
• Presentation (of course) ? Wednesday, September
  15th 2004