New Factoids to remember

Question 1

Identify the data type that accepts character data; only the required number of bytes are used in the storage medium.

Answer 1

VARCHAR

Question 2

Identify the data type that accepts fixed length character data of up to 2000 characters.

Answer 2

CHAR

Question 3

In one of the the tables in the database at Ottawa Civic Hospital, X-Ray, MRI scans and other digital images are stored. Identify the datatype for storage of these images.

Answer 3

BLOB

Question 4

This datatype is used to store unicode character-based data, such as large documents in any character set.

Answer 4

CLOB

Question 5

The logical order of operators is

Answer 5

NOT, AND, OR

Question 6

The query “DELETE FROM Product_T;” will

Answer 6

Remove all rows from the Product_T table, assuming there are no referential integrity constraints

Question 7

Identify two advantages of dynamic views

Answer 7

• It can simplify large queries into shorter forms of views.
• It can facilitate access control by allowing selected rows and/or columns in a view.

Question 8

4 Characteristics of a Materialized view

Answer 8

Needs refreshing each time in order to update results

Less recent data, faster loading

CPU intense queries can be run periodically instead of every time you need a report

Occupies hard disk space

Question 9

T/F - A database admin drops a materialized view, the table from which the view was created will also be dropped

Answer 9

False

Question 10

Virtual table and logical view are other names for a

Answer 10

dynamic view

Question 11

What is the result of the following statement on the city table in the world database.

UPDATE City

SET Population = 500000;

Answer 11

This statement will set the population of all cities in the city table to 500000

Question 12

The purpose of Indexes

Answer 12

Speed up query efficiency for specific tables/columns

Question 13

During a transaction, when changes are executed successfully the transaction is ready to be ___________, when we want to undo the changes, we can _______

Answer 13

committed, rollback

Question 14

When a transaction ends explicitly it encountered a

When a transaction ends implicitly it encountered a

Answer 14

Commit or rollback

DDL statement

Question 15

ACID - A

Answer 15

Atomic - Every statement within the group (unit of work is required to be performed successfully

All or nothing - one thing fails everything fails

Question 16

ACID - C

Answer 16

Consistent - The state of the database is modified only when a transaction is committed successfully

This ensures protection of data

Question 17

ACID - I

Answer 17

Isolation - Ensures that every operation within the group or unit of work designated for transaction is operated independently

Also means the statements are transparent to each other

Question 18

ACID - D

Answer 18

Durability - When a transaction is committed, it is permanent

Ensures that results obtained when transactions have been successfully executed are kept, even if the system failure

Question 19

SET auto-commit

Answer 19

Used to enable or disable auto-commit mode for the current transaction

Question 20

Inside of a transaction, this ensures that all statements performed after it can be rolled back

Answer 20

Savepoint

Question 21

ROLLBACK TO SAVEPOINT

Answer 21

Rolls back transactions to the savepoint provided ensuring that no transaction is undone

Question 22

RELEASE SAVEPOINT

Answer 22

Removes the given savepoint without changing the effects of queries

Question 23

Savepoint syntax

Answer 23

SAVEPOINT name_of_savepoint

ROLLBACK TO SAVEPOINT name_of_savepoint