ECE 569 Database System Engineering Fall 2004

1
ECE 569 Database System EngineeringFall 2004

• Yanyong Zhang www.ece.rutgers.edu/yyzhang
• Course URL www.ece.rutgers.edu/yyzhang/fall04

2
About the instructor (Yanyong Zhang)

• Yanyong
• Office Core 518
• Office hours TBD (Th 1-250??)
• Office number 5-0608
• Email yyzhang_at_ece.rutgers.edu
• URL www.ece.rutgers.edu/yyzhang
• Research interests
• distributed computing
• operating systems
• sensor networks

3
Something about the background

• What is database?
• a very large, integrated collection of data
• Query
• Transaction
• A group of queries which possess the ACID
  (atomic, consistent, isolated, and durable)
  property
• DBMS (DataBase Management System)
• a software package designed to store and manage
  databases

4
Overview
User programs
Database System
Application programs / Queries
DBMS
Software to process queries
Software to access stored data
Stored database definition
Stored database
5
DBMS Overview

• A database management system (DBMS) provides
  efficient access to large amounts of persistent
  data
• Data models and query languages allow efficient
  access while hiding complexity from users
• Efficient shared access requires concurrency.
  Transactions provide transparency to this
  concurrency. Application programs are easier to
  write.
• In many cases the data is valuable. It must be
  protected from the effects of failure
  (resiliency) and sabotage (security).

6
Files vs. DBMS

• Applications must stage large datasets between
  main memory and secondary storage (e.g.,
  buffering, page-oriented access, 32-bit
  addressing)
• Special code for different queries
• Must protect data from inconsistency due to
  multiple concurrent users
• Crash recovery
• Security and access control

7
Why DBMS?

• Data independence and efficient data access
• Reduced application development time
• Data integrity and security
• Uniform data administration
• Concurrent accesses, recovery from crashes

8
Data Models

• A data model is a collection of concepts for
  describing data
• A schema is a description of a particular
  collection of data, using a given data model.
• The relational model of data is the most widely
  used model today
• Main concept relation, basically a table with
  rows and columns
• Every relation has a schema, which describes the
  columns, or fields

9
Levels of Abstractions

• Abstraction is used to hide complexity and allow
  for a separation of concerns (What vs. How).
• Many views, single conceptual (logical) schema,
  and single physical schema
• Views describe how users see the data
• Conceptual schema defines logical structure
• Physical schema describes the files and indexes
  used.

View 1
View 2
View 3
Specialized view of enterprise
Subschema definition language
Conceptual Schema
Complete model of enterprise
Data definition language
Physical Schema
Records, pointers, indices
10
Example

• Sample applications
• Admit_patient
• Make_diagnosis
• Record_vital_signs
• In relational data model we can express schema
  with following tables
• patient (name, address, balance_due, room)
• payments (name, amount, date)
• vital_signs (name, pulse, bp, time)
• diagnosis (patient_name, disease_name)
• disease (disease_name, treatment)

11
Examples

• Physical Level
• Specify indices, e.g.,
• CREATE INDEX room_index ON patient(room)
• Specify performance related characteristics of
  relations
• Conceptual Level
• Define tables, specifying data types for each
  attribute.
• CR CREATE TABLE patient (
• name char(30),
• address char(100),
• balance_due number(6,2),
• room integer,
• PRIMARY KEY (name))

12
Examples contd

• External Level
• Define views for various purposes, e.g.,
• CREATE VIEW doctor-view-diagnosis AS
• SELECT name, room, disease_name,treatment
• FROM patient, diagnosis, diseases
• WHERE name patient_name AND
• diagnosis.disease_name
  disease.disease_name

13
Data Independence

• Applications insulated from how data is
  structured and stored
• Logical data independence protection from change
  in logical structure of data
• Physical data independence protection from
  changes in physical structure of data

14
Concurrency Control

• Concurrent execution of user programs is
  essential for good DBMS performance
• Why??
• Interleaving actions of different user programs
  can lead to inconsistency e.g., check is cleared
  while account balance is being computed
• DBMS ensures such problems dont arise users can
  pretend they are using a single-user system

15
Transaction An execution of a DB program

• Key concept is transaction, which is an atomic
  sequence of database actions
• 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 DBMS will enforce
  them
• DBMS doesnt understand the semantics of the data
• Ensuring that a transaction (run alone) preserves
  consistency is ultimately the users
  responsibility.

16
Scheduling concurrent transactions

• DBMS ensures that execution of T1, T2, , Tn is
  equivalent to some serial execution T1Tn.
• locking scheme
• Two-phase locking

17
Ensuring atomicity

• DBMS ensures atomicity (all-or-nothing property)
  even if system crashes in the middle of a Xact.
• Idea keep a log (history) of all actions carried
  out by the DBMS while executing a set of Xacts

18
Structure of a DBMS

• A typical DBMS has a layered architecture
• The figure does not show the concurrency control
  and recovery component
• This is one of several possible architectures
  each system has its own variations.

Query optimization and execution
Relational operators
Files and access methods
Buffer management
Disk space management
19
About the course

• What will we focus on?
• Relational data model
• Transaction processing
• DBMS design
• What will we not focus on?
• OO data model, etc
• SQL programming
• Goal
• Understand DBMS design issues
• Develop background for research in database area

20
What should youve know

• Data structure and algorithms
• Operations system knowledge
• C, Unix
• Background in data model and query languages
  recommended

21
What will you encounter - topics

• 1. Relational Data Model (2-4)
• 2. DBMS Design / Implementation (5-11)
• a) File organization (5-6)
• b) Access methods (7-9)
• c) Query processing (10-11)
• 3. Transaction Processing
• a) Transaction Models (12-13)
• b) Isolation (14-20)
• c) Performance (21-22)
• d) B-tree Synchronization (23-24)
• e) Recovery (25-29)

22
What will you encounter - projects

• Projects
• Develop a client/server relational DBMS
• Query processing / Physical data model / Data
  dictionary
• Concurrency control / Recovery
• Work in groups of at most 4.
• You may choose groups but I must approve.
• At least three members of each group should be
  strong C programmers.
• Projects are difficult and time-consuming.
• 10K lines of codes
• Use threads and RPC
• Code is difficult to debug
• Projects are interesting and rewarding.

23
Grading Policy

• 3 Homework assignments (15)
• Project (45)
• Two exams (20 each)
• Course URL www.ece.rutgers.edu/yyzhang/fall04

24
Database Literature

• Journals
• IEEE Transaction on Knowledge and Data
  Engineering
• ACM Transactions on Database Systems
• VLDB Journal
• Conferences
• IEEE Data Engineering Conference
• ACM SIGMDO
• Very Large Database (VLDB)

25
Example medical database

• Entities in database, the types and names of
  their attributes, and relationships between
  entities.

26
System Architecture

• DDL Data Definition Language
• DML Data Manipulation Language