Chapter 1: Introduction
FileSystem Database System
Data Redundancy and inconsistency Levels of Abstract: Physical, Logiccal, View
Difficulty in Accessing Data
Data isolation
INTEGRITY PROBLEM
Atomicity of Updates
Multiple User for Current Access
Security Problem
Some concepts:
Schema: Logical Structure of Database
Instance: The Actual Content of Database IN A Particular Point In Time.
Physical Data Independence - The Ability To Modify The Physical Schema without Changing The Logical Schema
Entity Relationship Model
Entities and rateships Between Entities.
DDL
DDL Compiler Generates a Set of Tables Stored in A Data Dictionary
DML
SQL: Widely Used Non-Procedural Language
Chapter 2: Entity-Relationship Model
Entity: is a object
Entity Set :: an entity set is a set of enttive the same.
Domain - The Set of Permitted Values for Each Attribute
Attribute Types: Simple and Composite Attributes.single-Valued and Multi-Valued AttributeSderived Attributes
Relationship: Is An Association Among Several Entities
RELATIONSHIP SET: IS A Mathematical RELATION AMONG N> = 2 Entities, Each Taken from Entity Sets
DEGREE OF A Relationship Set:
RELATIONSHIP STS That Involve Two Entity Sets Are Binary (Or Degree TWO). Generally, MOST RELATIONSHIP SETS IN A DATABASE SYSTEM ARE Binary.
E-r diagrams:
Rectangles represent entity sets.
Chapter13:
Query Processing Basic Steps: (a Picture In The PPT)
Parsing and translation
.
2. Optimization
Relational algebra can be expressed by many forms, and choose the one with lowest cost.3. Evaluation The query-execution engine takes a query-evaluation plan, executes that tlan, and returns the answers to the query.
Measure of query cost:
Time Cost: Disk Accesses, CPU, or NetWork Communication
Selection Operation:
File scan:
(Br Denotes Number of Blocks Containing Records from Relation R)
A1 (Linear Search) ---- Scan Each File Blocks to Check WHENER Satisfy The Selection Contression. Cost = Br OR BR / 2 (When the Selection Is on A Key Attribute)
A2 (Binary Search) ---- Applicable if the selection is an equality commit 安 t on on on on file is orderred cost = [log2 (br)]
Index Scan - Search Algorithms That Uses AN Index
A3 (Primary Index On Candidate Key, Equality):
COST = HT 1
PRIAMRY INDEX ON NON-key, equality
COST = HT Number of Blocks Containing Retrieved Records
A5 (Equality on search-key of secondary index).
If Search-Key Is A Candidate Key
COST = HTI 1
Retrieve Multiple Records if Search-Key Is Not a Candidate Key
Cost = HTI NUMBER of Records Retrieved
Select Involving Comparisons (RELATION IS SORTED ON A)
PRIMARY INDEX Comparison
For Δa> v (r) Use index to find first tuple> = v and scan reasoning sequentially from there
For Δa
A7 (Secondary INDEX, Comparison).
For ΔA> V (r) Use index to find first index entry> = V and scan index sequentially from there, to find points to records.
For Δa
Complex Select: Conjunction
A8 (Conjunctive Selection Using One Index) .Select a Combination of Qi and Algorithms A1 Through A7 That Results in The Least Cost
Conjunctive Selection Using Multiple-Key Index.
Use appriate composite (Multiple-key) Index if Available
Conjunctive Selection by InterSection Of Identifiers.
Requires INDES with RECORD POINTERS.
Use Corresponding Index for Each Condition, And Take InterSection of All The Obtained Sets of Record Pointers.
The Fetch Records from File
Complex Select: Disjunction
A11 (Disjunctive Selection by Union of Identifier).
Applicable if All Conditions Have Available INDs.
Otherwise use linear scan.
Use Corresponding Index for Each Condition, And Take Union of All The Obtained Sets of Record Pointers.
The Fetch Records from File
NEGATION:
Use linear scan on file
-------------------------------------------------- -------------------------------------------------- ----------------
Sorting:
For Relations That Fit in Memory, Techniques Like Quicksort Can Be Used. For Relations That Don't FIT IN MEMORY, EXTERNAL ORT-MERGE IS A Good Choice.
Let M Denote Memory Size (IN PAGES).
External Sort-Merge
Cost: Thus Total Number of Disk Accesses for External Sorting:
BR (2 [log m-1 (br / m)] 1)
-------------------------------------------------- -------------------------------------------------- ---------
Join Operation:
(R is Called The Outer Relation and s the inner rellation of the join.)
NESTED-loop join:
Forum for Each Tr in r do beg
Requires no indices and can be used with any kind of join condition.so expensive! The Worst Case Cost = NR * BS Br Disk Accesses.
Cost `= Br BS Disk Accesses.
Block Nested-loop join:
For Each Block Br Of r Do Begin for Each Block Bs of S Do Begu Each TUPLE TUPLE TS in BS Do Begin Check IF (TR, TS) Satisfy The Join Condition If DO, ADD TR • Ts to the result. End end end
Worst Case Estimate: Cost = Br * BS Br Block Accesses.
BEST CASE: Br BS Block Accesses.
Nimprovements to Nested loop and block nested loop algorithms:
COST = [BR / (m-2)] * BS BR
Indexed Nested-loop Join
Cost of the join: Br NR * C
Note: if Indices Are Available ON Join Attributes of Both R and S, Use The Relation with Fewer Tuples as The Outer Relation.
Merge-Join
Sort Both Relations on Their Join Attribute (if not already sorted on the join attributes).
Can Be Used Only for Equi-Joins and Natural Jointes
COST = Br BS The Cost of Sorting If Relations Are Unsorted.
Hybrid Merge-Join:
Hash-join
Applicable for Equi-Joins and Natural Joins.
-------------------------------------------------- -------------------------------------------------- ------------
November 23, 2004 11:07:52
