Week 13 Lecture Slides - CST8215 - PostgreSQL PDF

INTRODUCTION TO DATABASE SYSTEMS (CST 8215) WEEK 13 VIEWS, INDEXES & TRANSACTIONS Introduction to VIEWs ❖ A View is a named query stored within the database. ❖ Every time the view is called by its name, it internally executes the query associated with the view. ❖ A view allo...

INTRODUCTION TO DATABASE SYSTEMS (CST 8215) WEEK 13 VIEWS, INDEXES & TRANSACTIONS Introduction to VIEWs ❖ A View is a named query stored within the database. ❖ Every time the view is called by its name, it internally executes the query associated with the view. ❖ A view allows you to hide the base table and only show a sub-set or summary of data from one or more base table ❖ Complex queries that need to be executed frequently may be saved as a view for easier access. Creating a View: To create a view, we can use the following syntax: CREATE [OR REPLACE] VIEW name_of_view AS SELECT columns FROM tables [WHERE conditions that may apply]; Reference: https://www.postgresql.org/docs/8.0/sql-createview.html Meaning of the Syntax Parameters These parameters used in the syntax can be interpreted as follows: ❖ OR REPLACE: This is optional and can only be used when a view already exists. ❖ name_of_view: This states the name of the view that is to be created. ❖ WHERE conditions: This optional statement shows the conditions required for the records to be included in the view. A Simple Example Create a view on airports table (flightdb) to list only those airports that are in the city of Toronto: CREATE VIEW toronto_airports AS SELECT * FROM airports WHERE city = 'Toronto’; SELECT * FROM toronto_airports; Updating a VIEW When required to update a view that has already been created, we must use the ‘OR REPACE’ clause with ‘CREATE VIEW’: CREATE OR REPLACE VIEW name_of_view AS SELECT columns FROM table WHERE conditions; Dropping a VIEW When required to drop or delete an existing view, we use the DROP VIEW statement. The syntax can be written as: DROP VIEW [IF EXISTS] name_of_view; Types of Views ❖ There are two types of views: Dynamic View Materialized View Each view has an advantage and unique purpose. Dynamic VIEW ❖Dynamic View: This is called virtual table or logical view. It is also known as a derived table. It does not occupy space in the hard disk. When invoked, the query (view) is executed by referring to the tables. A complex query can be simplified by creating a dynamic view Dynamic VIEW (2) ❖ Any change made to the tables will affect the result set each time the view is invoked, hence the name dynamic view. ❖ A dynamic view provides the most recent data. ❖ By default, all views are dynamic views. Hence, the example used earlier i.e. Toronto_Airports view is a dynamic view. CREATE VIEW toronto_airports AS SELECT * FROM airports WHERE city = 'Toronto’; Materialized VIEWs ❖This is a persistent view. ❖A data set is created from the tables and stored in the database. ❖The result set takes storage space, unlike a dynamic view where the result set does not occupy hard disk space. ❖Each time a materialized view is executed the result set that is stored is referenced. Materialized View - Example To create a materialized view, write the following statements (working from this specific database example): CREATE MATERIALIZED VIEW toronto_airports_mv AS SELECT * FROM airports WHERE city = 'Toronto'; Dropping a Materialized View You can see the result set from the materialized view created by writing: SELECT * FROM toronto_airports_mv; A materialized view is dropped by using the following statement: DROP MATERIALIZED VIEW IF EXISTS toronto_airports_mv; Observing some differences… ❖ Initial results of the select statements from both dynamic and materialized views are the same. ❖ However let us add a new airport to the existing airports table: INSERT INTO airports(id, name, city, x, y) VALUES(9600, 'Markham Airport’, 'Toronto', -79.37, 47.65); Observing some Differences (2)… Now, we execute each of the two VIEWs again: ❖ From the dynamic view execute: SELECT * FROM toronto_airports; ❖ From the materialized view execute: SELECT * FROM toronto_airports_mv; Observations: ❖ The dynamic view will show the updated result from the airports table, the materialized view will not show the newly added row. Issues with Materialized View ❖ Most times updated data may not be needed. However, issues with materialized views include: It must be updated/refreshed Data may be inconsistent with what is in the live data Refreshing a Materialized View Syntax: REFRESH MATERIALIZED VIEW name Using our example: REFRESH MATERIALIZED VIEW toronto_airports_mv; Updating a View ❖Updating a view means adding, modifying and deleting data using a view. ❖This is possible only if the constraints are honored. ❖The user or role needs to have the privileges to the base table (most employees may have access to views and not tables). INDEXES Purposes of Indexes Most queries only require a small amount of information from a database What if the only way to get the information was to search the ENTIRE database – INEFFICIENT! Indexes help us speed up our queries so we don’t have to search the entire database Purpose of Indexes Analogy: Instead of searching an entire textbook for the page we’re looking for, we have an index at the back of the book that allows us to look up the topic and page number. Definition: A data structure that is used to speed up data retrieval. It typically contains a list of keys used to identify columns in tables. Indexes Defined (cont.) Index: a table or other Other fields or data structure used to combinations of fields can Primary keys are determine in a file the also be indexed; these are automatically indexed location of records that called secondary keys (or satisfy some condition nonunique keys) 24 Indexes Create an index on name: Adam Betty Charles …. Smith …. B+ trees have fan-out of 100s: max 4 levels ! 25  Indexed file organization uses a tree search Average time to find desired record based on depth of the tree and length of the list 26  Unique and Nonunique Indexes Unique (primary) Index Typically done for primary keys, but could also apply to other unique fields Nonunique (secondary) index Done for fields that are often used to group individual entities (e.g. zip code, product category) 2 Creating Indexes Syntax: CREATE INDEX nameIndex ON Person(name) 28  Creating Indexes Indexes can be created on more than one attribute: Example: CREATE INDEX doubleindex ON Person (age, city) SELECT * Helps in: FROM Person WHERE age = 55 AND city = “Seattle” SELECT * But not in: FROM Person WHERE city = “Seattle” 2 TRANSACTIONS Transaction – Definition Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all. Syntax: BEGIN; DML Statement 1 DML Statement 2 DML Statement 3 COMMIT or ROLLBACK; Reference: https://www.postgresql.org/docs/9.5/tutorial-transactions.html Transaction Control Language (TCL) In PostgreSQL “Begin;” keywork signifies the start of transaction followed by at least one DML statement. A transaction ends with either: Commit – meaning save the changes or Rollback – meaning undo the changes. Transaction ends implicitly (automatically) with a commit when they encounter DDL statements. All DDL statements are autocommit. A unique feature of PostgreSQL is “Transactional DDL” which allows even DDL statements to be committed or rollbacked. Note: In PGAdmin tool, all statements are set to “Autocommit”. Characteristics of Transactions To capture the characteristics of transactions in a memorable way, we use the acronym ACID: ❖A – Atomic: This refers to the fact that every statement within the group (unit of work) is required to be performed successfully. ❖This is an all-or-nothing execution. If one operation in the group of statements fail, then all other operations will be undone. Characteristics of Transactions (2) ❖B – Consistent: This characteristic refers to the fact that the state of the database is modified when a transaction is committed successfully only. ❖ This characteristics ensures the protection of data from crashes. Characteristics of Transactions (3) ❖C – Isolation: This characteristic ensures that every operation within the group or unit of work designated for transaction is operated independently. ❖ Isolation also means that statements are transparent to each other. Characteristics of Transactions (3) ❖D – Durability: When transaction are committed, they are required to be permanent. ❖This characteristic ensures that the results obtained when transactions have been successfully executed are kept even if system failure or crashes occur. SAVEPOINT ❖In databases, a SAVEPOINT statement is used to creates a designation or mark using an identifier inside a transaction. ❖ Savepoint ensures that all statements performed after the savepoint can be rolled back. ❖ Transactions are restored to the former state prior to the savepoint. ❖ In a present transaction, when multiple savepoints are set with the same name, the newest savepoint is responsible for the rollback.