Branch & Bound
Recall: Types of Problems
Constraint Satisfaction problems
Can we satisfy all given constraints?
If yes, how do we satisfy them?
(need a specific solution)
May have more than one solution
Examples: sorting, mazes, spanning tree
Optimiza
Backtracking
Algorithms with Search Space Pruning
Outline
Review
Backtracking vs. Branch and Bound
General Form
Examples
Types of Algorithm Problems
Constraint Satisfaction problems
Can we satisfy all given constraints?
If yes, how do we satisfy them?
EECS 281: Data Structures and Algorithms
Graphs and Graph Algorithms
Represent problem as a graph
Graph Algorithm Questions
Describe algorithm to determine all nonstop flights from JFK
Give complexity
Graph Algorithm Questions
Describe algorithm to determ
EECS 281: Data Structures and Algorithms
Recurrence Review
Recursion Tree Method
1.
Write out T(n), T(n-1), T(n-2)
Use n=ak trick if needed
2.
3.
4.
Substitute T(n-1) and T(n-2) into T(n)
Look for a pattern
Use a summation formula
Example: Quicksort Wors
EECS 281: Data Structures and Algorithms
Tree Algorithms
Tree Traversal
Systematic method to process every node in a tree
Preorder:
visit node,
recursively visit left subtree,
recursively visit right subtree
Inorder:
recursively visit left subtree,
v
EECS 281: Data Structures and Algorithms
Trees
Formal Definition: Tree
Tree: set of nodes storing elements in a
parent-child relationship with the
following properties:
T has a special node r, called the root of T,
with no parent node;
Each node v of T,
EECS 281: Data Structures and Algorithms
Recurrence Relations
Determining Complexity
From Lecture 03:
Method 1:
Experimentation: run tests on data sets
Method 2:
Solution of Recurrence: mathematical solution
to representation of recursive algorithm
Met
EECS 281: Data Structures and Algorithms
Quicksort
Last Time: Insertion and Merge
Insertion-Sort(A,n)
for j = 2 to n
key = A[j]
/insert A[j] into sorted
/sequence A[1.j-1]
i = j-1
while i > 0 and
A[i] > key
A[i+1] = A[i]
i = i-1
A[i+1] = key
Merge-Sort(A,
EECS 281: Data Structures and Algorithms
Sorting Intro: Insertion Sort and Merge Sort
CLRS Style
Outline
Properties of sorts
Insertion sort
Merge sort
Comparison of insertion and merge
Motivation
Learning simple sorts:
Terminology and basic context for
s
Query Optimization
Query Optimization
Chapter 15
Given a SQL query, how do we evaluate it
efficiently?
Query Optimizer Important component of
a DBMS
Convert SQL query blocks to extended
relational algebra expressions
Enumerate alternative evaluation plan
GFS and MapReduce
The Google File System
Search engines were among first apps to
address Web s scale directly
Search engines were first, but not last
We spoke about distributing search queries
Also, distributing index construction
Social networks
Web-anal
Operator Evaluation
How to implement common operators?
Evaluation of Relational Operations
Chapter 12 and 14
Selection
Projection (optional DISTINCT)
Join
Set Difference
Union
Aggregate operators (SUM, MIN, MAX, AVG)
GROUP BY
Next week How to choose a p
Physical Database Design
Indexes & Performance Tuning
Choice of physical schema, indexes have a
big effect on system performance
Important for DBA to choose indexes based
on expected workload
Chapter 8.5
Queries
Updates
11/3/12
EECS 484
1
11/3/12
Choi
Why Sort?
User wants query answers in some order
External Sorting
E.g., decreasing order of age
First step to bulk-loading B+ Tree
Eliminate duplicate records
Chapter 13
SELECT DISTINCT
Sort-merge join algorithm (later)
11/3/12
EECS484
1
11/3/12
EEC
Index Design Space
Tree-Structured Indexes
Organization
Structure for k*
Hash-based
+ Equality search
Chapter 10
Data Entry (k*) Contents
Tree-based
+ Range, equality search
B+Tree (dynamic)
ISAM (static)
10/20/12
EECS 484
1
1.
Actual Data record
- inde
Data on External Storage
Storage and Indexing
Each table stored as a file.
File contains a (multi)set of records
Each record has a unique RID; sufficient to
locate record on disk
Chapter 8
Data read and written to disk by buffer
manager
Main unit of
DBMS Organization
Storing Data: Disks and Files
SQL Query / Application
Query Evaluation Engine
Chapter 9
Files & Access Methods
Buffer Management
This
lecture
Disk Space Management
Disk
10/20/12
EECS 484
1
10/20/12
The Memory Hierarchy
Too expensive: RA
Overview
Physical Database Design
Chapter 20
After ER design, schema refinement, and the
definition of views, we have the conceptual and
external schemas for our database.
The next step is to choose indexes, make clustering
decisions, and to refine the co
Overview
Physical Database Design
Chapter 20
After ER design, schema refinement, and the
definition of views, we have the conceptual and
external schemas for our database.
The next step is to choose indexes, make clustering
decisions, and to refine the co
Database Design: The Story so Far
Normalization
Requirements Analysis
Conceptual Database Design
Logical Database Design
Schema Refinement
Physical Database Design
Security Design
Data stored, operations, apps,
Model high-level description of the data, c
SQL Query Language
SQL Queries
Implements relational algebra
Select, Project, Join, Set operators
And so much more
Correlated subqueries
Ordering of results
Aggregate queries (e.g., SUM, MAX, AVG)
Three-valued logic for NULL values
Etc.
Chapter 5
Relational Calculus
Relational Calculus
Alternative to relational algebra
Declarative (non-procedural) Describes answers without
saying how to compute them
Comes in two flavors (see textbook):
Chapter 4
TRC: Tuple relational calculus and
DRC: Domain
Formal Query Languages
Foundation for commercial query languages like SQL
Two types
Relational Algebra
Declarative: Relational Calculus
Describe what a user wants, rather than how to compute it.
Chapter 4
Procedural : Relational Algebra
Operational,
ER Model vs. Relational Model
Logical Database Design:
Mapping ER to Relational
ER Model used for conceptual design
Relational Model implemented by modern
DBMS
Important Step: Translate ER diagram to
Relational schema
Chapter 3, Section 3.5
9/16/12
EEC
Relational Databases
The Relational Model
Most common data model in modern
DBMS
Many commercial systems
Oracle, MS SQL Server, IBM DB2, more
Chapter 3
Also open source
MySQL, PostgreSQL, more
9/6/12
0
9/6/12
1
Terminology Parade
Instance of Athlete R
Database Design
The Entity-Relationship Model
Requirements Analysis
Data stored, operations, apps,
Conceptual Database Design
Chapter 2
Model high-level description of the data, constraints, ER model
Logical Database Design
Choose a DBMS and design