Database System Implementation

1
Database System Implementation

• Spring 2001
• Prof. Sang Ho Lee
• School of Computing, Soongsil Univ.
• shlee_at_computing.soongsil.ac.kr

2
Isnt Implementing a Database System Simple?
3
Introducing the
MEGATRON 3000
Database Management System

• The latest from Megatron Labs
• Incorporates latest relational technology
• UNIX compatible

4
Megatron 3000 Implementation Details

• Relations stored in files (ASCII)
• e.g., relation R is in /usr/db/R

Smith 123 CS Jones 522 EE
.
.
.
5
Megatron 3000 Implementation Details

• Directory file (ASCII) in /usr/db/directory

R1 A INT B STR R2 C STR A INT

.
.
.
6
Megatron 3000Sample Sessions
MEGATRON3000 Welcome to MEGATRON 3000!
quit
.
.
.
7
Megatron 3000Sample Sessions
select from R Relation R A
B C SMITH 123 CS
8
Megatron 3000Sample Sessions
select A,B from R,S where R.A S.A and S.C
gt 100 A B 123 CAR 522 CAT
9
Megatron 3000Sample Sessions
select from R LPR
Result sent to LPR (printer).
10
Megatron 3000Sample Sessions
select from R where R.A lt 100 T
New relation T created.
11
Megatron 3000

• To execute select from R where condition
• (1) Read dictionary to get R attributes
• (2) Read R file, for each line
• (a) Check condition
• (b) If OK, display

12
Megatron 3000

• To execute select from R where
  condition T
• (1) Process select as before
• (2) Write results to new file T
• (3) Append new line to dictionary

13
Megatron 3000

• To execute select A,B from R,S where condition
• (1) Read dictionary to get R,S attributes
• (2) Read R file, for each line
• (a) Read S file, for each line
• (i) Create join tuple
• (ii) Check condition
• (iii) Display if OK

14
Whats wrong with the Megatron 3000 DBMS?
15
Whats wrong with the Megatron 3000 DBMS?

• Tuple layout on disk
• Change string from Cat to Cats and we have to
  rewrite file
• ASCII storage is expensive
• Deletions are expensive

16
Whats wrong with the Megatron 3000 DBMS?

• Search expensive no indexes
• Cannot find tuple with given key quickly
• Always have to read full relation

17
Whats wrong with the Megatron 3000 DBMS?

• Brute force query processing
• e.g., select
• from R,S
• where R.A S.A and S.B gt 1000
• - Do select first?
• - More efficient join?

18
Whats wrong with the Megatron 3000 DBMS?

• No buffer manager
• e.g., Need caching

19
Whats wrong with the Megatron 3000 DBMS?

• No concurrency control

20
Whats wrong with the Megatron 3000 DBMS?

• No reliability
• e.g., - Can lose data
• - Can leave operations half done

21
Whats wrong with the Megatron 3000 DBMS?

• No security
• e.g., - File system insecure
• - File system security is coarse

22
Whats wrong with the Megatron 3000 DBMS?

• No application program interface (API)
• e.g., How can a payroll program get at the data?

23
Whats wrong with the Megatron 3000 DBMS?

• Cannot interact with other DBMSs.

24
Whats wrong with the Megatron 3000 DBMS?

• Poor dictionary facilities

25
Whats wrong with the Megatron 3000 DBMS?

• No GUI

26
Course Overview

• File System Structure
• Records in blocks, dictionary, buffer
  management,
• Indexing Hashing
• B-Trees, hashing,
• Query Processing
• Query costs, join strategies,
• Crash Recovery
• Failures, stable storage,

27
Course Overview

• Concurrency Control
• Correctness, locks,
• Transaction Processing
• Logs, deadlocks,
• Security Integrity
• Authorization, encryption,
• Distributed Databases
• Interoperation, distributed recovery,

28
System Structure
Query Parser
User
Strategy Selector
User Transaction
Transaction Manager
Buffer Manager
Recovery Manager
Concurrency Control
File Manager
Log
Lock Table
M.M. Buffer
Statistical Data
Indexes
User Data
System Data
29
Course Details

• LECTURES Tuesday 900 1150 am
• TEXTBOOK Garcia-Molina, Ullman, Widom
  "DATABASE SYSTEM IMPLEMENTATION,
  Prentice Hall, 2000
• ASSIGNMENTS Written homework assignments. No
  programming. Significant
  reading assignments
• GRADING
• 3-4 times exams. (90)
• Attendance and homework (10)
• WEB SITE http//orion.soongsil.ac.kr(Please
  check it periodically for last minute
  announcements)

30
Read Carefully

• Course prerequisite
• Undergraduate database courses
• A First Course in Database Systems J. Ullman and
  J. Widom, Prentice Hall, 1997
• Should be familiar with
• ER/Relational/OO Data models
• Database design theory (FD, normalization, )
• Relational algebra, SQL, OQL,

31
Notice that

• This course requires considerable amount of
  self-reading.
• I do not cover every part of this book, but the
  exams do.
• My primary concern is about how to encourage you
  to study databases
• It will not be easy for you !!!