Live Lecture Materials Week 13

Question 1

A view is a

Answer 1

query stored within a database

Question 2

Views are best for

Answer 2

complex queries that need to be run often

Question 3

Correct syntax for creating a view

Answer 3

CREATE [OR REPLACE] VIEW name AS
–SELECT STATEMENT–

Question 4

Create a view for the hospital table called doctor, that selects name_of_hospital and doctor

Answer 4

CREATE VIEW doctor AS
SELECT name_of_hospital, doctor
FROM hospital

Question 5

Alter the doctor view so that it shows the pay, name_of_hospital, and the doctor from the hospital table

Answer 5

ALTER VIEW doctor AS
SELECT pay, name_of_hospital, doctor
FROM hospital

Question 6

All views need the _____ keyword added to the statement, even if it is not used

Answer 6

alias

Question 7

Dynamic view is also called a

Answer 7

virtual table or logical view. It’s also known as a derived table.

Question 8

T/F - Dynamic views occupy hard disk space

Answer 8

False

Question 9

T/F - A dynamic view provides the most recent data

Answer 9

True

Question 10

T/F - Materialized views occupy hard disk space

Answer 10

True

Question 11

Materialized views must be ________ to stay up to date

Answer 11

refreshed

Question 12

T/F - Data in materialized views is always consistent with live data

Answer 12

False

Question 13

Command to refresh a materialized view

Answer 13

REFRESH

Question 14

Updating a view is only possible if the ____________ are honored

Answer 14

constraints

Question 15

Most employee’s do not have access to ______, only _____

Answer 15

tables, only views

Question 16

The reason why indexes are effective is that most queries only require a

Answer 16

small amount of information from a database

Question 17

Purpose of indexes

Answer 17

Speed up queries so we don’t have to search the entire database

Question 18

Index analogy for a textbook

Answer 18

Instead of searching the entire textbook for a certain page, we have an index that allows us to look up the topic/pg number

Question 19

Index definition

Answer 19

A data structure that is used to speed up data retrieval. Typically contains a list of keys used to identify table columns.

Question 20

_______ ____ are automatically indexed

Answer 20

Primary keys

Question 21

An index is a table or data structure used to determine the ________ __ ________ that satisfy some condition

Answer 21

location of records

Question 22

T/F - A combination of fields can be indexed

Answer 22

True

Question 23

Secondary keys

Answer 23

Indexed field or fields that are not primary keys

Question 24

Indexed file organization uses something known as a ____ _______

Answer 24

Tree search

Question 25

In a tree search, the average time to find the desired record depends on the _____ and ______ of the tree

Answer 25

depth and length

Question 26

A unique (primary) index

Answer 26

Usually for primary keys, but can also apply to other unique fields

Question 27

A nonunique (secondary) index

Answer 27

An index of a non-unique table column. Often used to group data items (eg. Product Category)

Question 28

Syntax to create nameIndex for the source_airport_id column in the routes table

Answer 28

CREATE INDEX nameIndex ON routes( source_airport_id );

Question 29

A transaction is a

Answer 29

unit of work that changes the state of a database

Question 30

Transactions are a __________ group of statements

Answer 30

sequential

Question 31

Transactions can either be _________ or ______ ____ by the user

Answer 31

committed or rolled back

Question 32

3 Examples of transactions

Answer 32

INSERT, UPDATE, DELETE

Question 33

A transaction that ends after encountering a commit or roll back

Answer 33

It ended explicitly

Question 34

A transaction after encountering a DDL statement

Answer 34

It ended implicitly

Question 35

ACID - A

Answer 35

Atomic - Every statement within the unit of work must be performed successfully. If one operation fails, the group of statements fail.

Question 36

ACID - C

Answer 36

Consistent - Maintains data integrity. If a database violates data integrity it will be rolled back. Protects the data.

Question 37

ACID - I

Answer 37

Isolation - Every operation is independent within a unit of work Also means that statements are transparent to each other.

Question 38

ACID - D

Answer 38

Durability - When transactions are committed, they will survive permanently For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes

Question 39

2 synonyms for START TRANSACTION that also work

Answer 39

BEGIN and BEGIN WORK

Question 40

The ______ statement allows for durable modifications to the database

Answer 40

COMMIT

Question 41

___ ____-______ is used to enable or disable the auto-commit mode for a current transaction. Auto-commit commits a transaction when it is executed.

Answer 41

SET auto-commit

Question 42

T/F - by default, auto-commit is ON

Answer 42

True

Question 43

a savepoint ensures that all statements

Answer 43

performed after the savepoint can be rolled back

Question 44

During a transaction, if multiple savepoints are set with the same name, which one is used for the rollback?

Answer 44

The newest one

Question 45

Statement to rollback to the savepoint

Answer 45

ROLLBACK TO SAVEPOINT

Question 46

Statement that removes the given savepoint from the current transaction, without making any changes the query

Answer 46

RELEASE SAVEPOINT

Question 47

If you have a savepoint called name_of_savepoint, what is the syntax to rollback to it? to release it?

Answer 47

ROLLBACK TO SAVEPOINT name_of_savepoint RELEASE SAVEPOINT name_of_savepoint