Database Management Module 6
To create a new trigger in PostgreSQL:
1. First, create a trigger function using CREATE FUNCTION statement 2. Second, bind the trigger function to a table by using CREATE TRIGGER statement
Database Cost Analysis
In databases, cost analysis refers to calculating an estimate computational cost involved in the manipulation and extraction of data • It has many variables like: • Index method and algorithm used • Fields that are indexed • Type of frequent operations and queries performed • Query performance • And others...
Secondary Indexes
Secondary Indexes • Ordered index created on field that is not ordered and contains duplicates • Provide secondary means of accessing a data file • Some primary access exists • Ordered file with two fields • Indexing field, K(i) • Block pointer or record pointer, P(i) • Produce dense indexes frequently • Usually need more storage space and longer search time than primary index • Improved search time for arbitrary record
Stored Functions & Procedures
Stored Functions & Procedures • Allow us to use programming to manipulate and take actions in the database • Can be done in different programming languages • PostgreSQL uses PL/pgSQL by default • Complex programming operations are possible • Functions can be used inside other functions or SQL
Using Conditions and RAISE messages
https://drive.google.com/file/d/1-sa0WlClPCyKjxGcYnzWn6U5Bav9aAr8/view?usp=sharing
Summary: Types of Single-Level Ordered Indexes
https://drive.google.com/file/d/10DLp-Ze68A4XAxov8qcD5mKtAAflJEs_/view?usp=sharing
PostgreSQL Function Example2
https://drive.google.com/file/d/15Hyy50gN7-OcmKNeTTtZm43MjN79wLuA/view?usp=sharing
How Records are Organized
https://drive.google.com/file/d/15I6Y20dFCe3FzZbdme82rSkb-vkU8xEN/view?usp=sharing
Hash Indexes
https://drive.google.com/file/d/15WcdqqUTQnYY3lcL_iRu3T9Z9puv5TfN/view?usp=sharing
ISAM Tree-Structured Indexes4
https://drive.google.com/file/d/16J5PjUJIOe2koVgVjzI300CUZU9bVLmC/view?usp=sharing
Searching in ISAM Index
https://drive.google.com/file/d/17M5832J5Fj3ybldpuY9HViQFkW4fSNNR/view?usp=sharing
ROLLBACK
https://drive.google.com/file/d/18sStEiTL4Po8xaebzBGMRDqXXuhtt8xQ/view?usp=sharing
ISAM Tree-Structured Indexes: non-Leaf Level2
https://drive.google.com/file/d/1CW4wJE2s0Qv0mtZSRs0VFYec6cKqeMqq/view?usp=sharing
Postgres Views
https://drive.google.com/file/d/1D8rI53ecY1D6ZL0JBvBEa7zk1AXQUHH0/view?usp=sharing
STEP 2: Bind Trigger Function to Table
https://drive.google.com/file/d/1DDKS7L4DtoIKtdbw7ZJrNpVemup5RvZm/view?usp=sharing
Add a new value in ISAM Index
https://drive.google.com/file/d/1G9D5n622w4RS1-8LoIP9hi7tljTSRbwu/view?usp=sharing
PostgreSQL Functions Basic Syntax:
https://drive.google.com/file/d/1GsJ23_t7eJYwZ3J5BLDGzsYrDyTczDf9/view?usp=sharing
B+ Tree Indexes2
https://drive.google.com/file/d/1GuNt7l2GUmc6QaQq75TRhjKPuKfegn5D/view?usp=sharing
Primary Index Example
https://drive.google.com/file/d/1HmtVGe7ctoQDXRCtFeV3-XlM5PPyoseX/view?usp=sharing
Example of Function Returning Records
https://drive.google.com/file/d/1J8quASEErn6oICW5CQK0O67xR9a4SOHg/view?usp=sharing
Add a new value in ISAM Index2
https://drive.google.com/file/d/1KGOWXLC_tdScsjxH2IQLi6DVBXV7_40E/view?usp=sharing
Triggers
https://drive.google.com/file/d/1KGWysyb81lKYQJb7bbAhWqE-Rdv5tZIU/view?usp=sharing
ISAM Tree-Structured Indexes: Leaf Level
https://drive.google.com/file/d/1LwCt8Ny80Hx5Ad6-A_B_Wzamx4_OGnfx/view?usp=sharing
Views
https://drive.google.com/file/d/1Mz6KYBsaaMw_TOewL9f0JW2PYPfPeEp5/view?usp=sharing
Traversing Records Example
https://drive.google.com/file/d/1O9FlIl6v5OWTZjf3PLMyUSlY1pgKnG1j/view?usp=sharing
PostgreSQL Function Example
https://drive.google.com/file/d/1R0e8Mflykigz9Ed0IamUUQfxxZilD5Jt/view?usp=sharing
Hash Tables for Indexing
https://drive.google.com/file/d/1WiYDerUfOIzGtflMBfd9HpYSMGjv47W5/view?usp=sharing
Simple Multiple level Index
https://drive.google.com/file/d/1YXsZ0d-0E-ZGSVLLB8Rf6Z3hEqa4-DQs/view?usp=sharing
Clustering Index Example
https://drive.google.com/file/d/1ZFBWRFJzsRqVoa18eNyzdi2rQDx2yQ_P/view?usp=sharing
ISAM Tree-Structured Indexes: non-Leaf Level3
https://drive.google.com/file/d/1c5_9nYLIx6z2EA0dzbXwOiTj_2d1lrJj/view?usp=sharing
Examples of Creating Indexes in PostgreSQL
https://drive.google.com/file/d/1dr6mNwxXVetuuj3-JqZrwup2eLOi-oOm/view?usp=sharing
Transactions2
https://drive.google.com/file/d/1fBl7tg0EJX-M_rWT_gEqig_23mHS7kFk/view?usp=sharing
ISAM Tree-Structured Indexes: non-leaf Levels
https://drive.google.com/file/d/1fuzyT6wa_vR_Lnsr_SP787aGmtUAeHs2/view?usp=sharing
STEP 1: Create a Trigger Function
https://drive.google.com/file/d/1hhfOmuvYeyMtUxqhbkTqkEY-pplKinvh/view?usp=sharing
ISAM Tree-Structured Indexes: non-Leaf Level
https://drive.google.com/file/d/1i2iHo5I79EcAFNrogEfHyRF5NeenopH9/view?usp=sharing
Indexes on Multiple Fields
https://drive.google.com/file/d/1inHjCXoIFU_pmGafc5Rft8DAx0mzzFdL/view?usp=sharing
Secondary Index for Non-Key
https://drive.google.com/file/d/1iw5yK3F5rD_CpjLtoSIB24XgQMfFqQc2/view?usp=sharing
Indexes
https://drive.google.com/file/d/1naJsk7FUchJe2N5QruQzjhPq0_1wAmxp/view?usp=sharing
STEP 1: Create a Trigger Function2
https://drive.google.com/file/d/1phibXT4EBHfWoJ0ZLFa-YzD09Q3_Uhw3/view?usp=sharing
Loop Example in a Function
https://drive.google.com/file/d/1puXKpP2Wvrf8q7VY4-XNu_blT66h_kjd/view?usp=sharing
More conditionals in functions examples:
https://drive.google.com/file/d/1qPeLxxtPNYknO_4sVMi75ilTY_aQsr6g/view?usp=sharing
B+ Tree Indexes
https://drive.google.com/file/d/1tgUB5Y5S3oIRQI43E9EsIwzri2jRNDks/view?usp=sharing
Single-Level Ordered Indexes
https://drive.google.com/file/d/1udFt-_voE4bsb8SZhdE_UnbFzFqW8jDE/view?usp=sharing
Example B+ Tree3
https://drive.google.com/file/d/1vY05LyNzn-qKFegb6QVBksUzsLEHMWv7/view?usp=sharing
Transactions
• A database transaction is a single unit of work which may consist of one or more SQL statements • A transaction bundles multiple steps into a single, all-ornothing operation • The intermediate states between the steps are not visible to other concurrent transactions and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. • Example: • In a funds transfer the process must complete from start to end • Money must leave the first bank, then, safely and accurately be deposited in the other bank for the transaction to be complete
Views.
• A view is a saved named SELECT query that provides another way to present data from the database tables • Used to store complex query definitions for later use and reference • Views CAN insert, add, or delete data from their source tables • Views do not store any data (except for "Materialized Views"), they run every time they are referenced • Views can also be used for access control
When to Use Indexes
• As a rule of thumb, consider using an Index for: • Large tables • Primary and foreign keys • Fields that are used frequently in WHERE clauses
Storage of Files
• DBMS stores data in files • Files in a DBMS contain one or many pages (also called blocks) • Think of files as books, and blocks as chapters in a book • The DBMS is in charge of storing data in an organized and efficient way in order to ensure fast and reliable access • Some data operations can be expensive and must be optimized according to the database design
Indexes2
• Data structures mapping search keys to sets of rows in a database table • Searches and updates are faster than sorted files • There is storage overhead • Think of an index in a book • We can define an index in one or more fields
Hash indexes Vs. B+ Tree Indexes
• Hash indexes work well when the queries done involving the index field are equality comparisons using the = or <=, >= operators that find a single value • They are not used for comparison operators such as < that find a range of values • PostgreSQL recommends (and uses by default) B+ Trees indexes in most, if not all, circumstances
Multilevel Indexes
• Indexes of Indexes • Designed to greatly reduce remaining search space as search is conducted • Index file • Considered first (or base level) of a multilevel index • Second level • Primary index to the first level • Third level • Primary index to the second level
B+ Trees
• Newer and dynamic index method • Adapts to changes • Improved based upon ISAM idea • Mostly used for indexes (ISAM is almost never used nowadays)
Some Definitions: Types of Ordered Indexes
• Primary Index • Index created on a field that is an ordered candidate key • Clustering Index • Index created on an ordered field that is not unique (contains duplicates) • Secondary index • Index created on field that is not ordered and contains duplicates • Dense or sparse index • Dense index has an index entry for every search key value in the data file • Sparse index has entries for only some search values
Disadvantage of ISAM
• Static index structure • Only good for some limited applications • If table changes a lot (many new insertions and deletions) it is not effective • Frequent updates causes structure degradation and slowdown • Some range of values may have too many overflow pages
Transactions A.C.I.D. Properties
• Transactions have the following properties • Atomicity: guarantees that the transaction completes in an all-or-nothing manner • Consistency: ensures data is valid and follow predefined rules • Isolation: determines visibility among transactions (when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others) • Durability: guarantees that committed transactions will be stored permanently Note: Transactions are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.
Indexes Should be Avoided When...
• Using small tables • Tables that have frequent, large batch update or insert operations • Indexes should not be used on columns that contain a high number of NULL values
