Cis243-Chapter 9

Front 1

What are constraints?

Back 1

rules or restrictions that guide database inserts, updates and deletions

Front 2

What is the function of constraints?

Back 2

to keep invalid or erroneous data out of the database

Front 3

In what three ways can integrity constraints be enforced?

Back 3

-Declare an integrity constraint
-Write a database trigger
-Include constraints in an application

Front 4

What is the biggest advantage and disadvantage to including constraints in an application?

Back 4

Advantage: flexibility/fine tuning
Disadvantage: outside the db, so modifications made outside of the application are not affected by the constraint

Front 5

What are three advantages to declaring an integrity constraint to enforce integrity?

Back 5

-simple to create and maintain
-always enforced
-perform faster because the optimizer handles them efficiently

Front 6

What are the five types of integrity constraints supported by Oracle 10g?

Back 6

-PRIMARY KEY
-UNIQUE
-FOREIGN KEY
-NOT NULL
-CHECK

Front 7

What is a PRIMARY KEY?

Back 7

a column or set of columns that define a unique identifying value for every row in the table

Front 8

What two terms can be used to describe a PRIMARY KEY which is made up of multiple columns?

Back 8

-compound key
-composite key

Front 9

What are two rules underly the enforcement of the PRIMARY KEY constraint?

Back 9

-UNIQUE
-NOT NULL

Front 10

How many primary keys may a table have?

Back 10

0 or 1

Front 11

What is referential integrity?

Back 11

the integrity, or soundness of data, maintained between two values which reference each other (a primary key in one table and foreign key in another table)

Front 12

What is a UNIQUE key?

Back 12

similar to PRIMARY KEY in that it enforces unique values, but unlike it in that it allows nulls in its columns (although it ignores them)

Front 13

May a table have more than one UNIQUE key constraint?

Back 13

Yes

Front 14

What is a FOREIGN KEY?

Back 14

a value in the child table, referencing the PRIMARY KEY of the parent table

Front 15

What is the purpose of a FOREIGN KEY?

Back 15

To establish a relationship between two tables, in which one is the parent table and the other is the child

Front 16

Does the FOREIGN KEY reside in the parent table or the child table?

Back 16

the child table

Front 17

What does the FOREIGN KEY constraint require of its column?

Back 17

that every row in the child table contain either a value that matches a PRIMARY KEY value from the parent table, or contains a NULL value

Front 18

What is a NOT NULL constraint?

Back 18

indicates that every row in the table must contain a value in the column on which the constraint is placed

Front 19

What is a CHECK constraint?

Back 19

enforces a predefined list of values for a column.

Front 20

Can a column with a CHECK constraint contain null values?

Back 20

Yes, unless another constraint restricts it (such as NOT NULL)

Front 21

What two DDL commands can be used to create an integrity constraint?

Back 21

CREATE TABLE
ALTER TABLE

Front 22

Describe defining constraints inline vs out of line

Back 22

-inline: applies to only one column and appears immediately next to the column
-out of line: applies to one or multiple columns and appears after the full list of columns

Front 23

What is the only type of constraint that may not be defined out of line?

Back 23

NOT NULL

Front 24

What is the only type of constraint that may be defined inline in an ALTER TABLE statement?

Back 24

NOT NULL

Front 25

When multiple conditions apply to a column, is it better to write one CHECK constraint to check multiple conditions, or write multiple CHECK constraints to check each condition separately, and why?

Back 25

Write multiple CHECK constraints separately, giving them specific, useful names. When a constraint is violated, the name of the constraint appears to the user, and may help them correct the data.

Front 26

Which constraint type must be defined out of line if custom-naming is desired?

Back 26

FOREIGN KEY

Front 27

What is a constraint state?

Back 27

attribute that tells Oracle 10g who to use the constraint when data is added to the table

Front 28

Name seven constraint state pairs

Back 28

- ENABLE | DISABLE
- VALIDATE | NOVALIDATE
- INITIALLY IMMEDIATE | INITIALLY DEFERRED
- DEFERRABLE | NOT DEFERRABLE
- RELY | NORELY
- USING INDEX <index> | <storage>
- EXCEPTIONS | EXCEPTIONS INTO <tablename>

Front 29

Is ENABLE or DISABLE the default constraint state?

Back 29

ENABLE

Front 30

What effect does DISABLE have on existing data?

Back 30

none, except if the constraint is a PRIMARY KEY or UNIQUE key, the associated unique index is dropped

Front 31

What effect does re-enabling a disabled constraint using the ENABLE keyword have on existing data?

Back 31

It checks all the rows for compliance, unless you specify ENABLE NOVALIDATE

Front 32

Is VALIDATE or NOVALIDATE the default constraint state?

Back 32

VALIDATE

Front 33

What effect does NOVALIDATE have on existing and new data?

Back 33

existing data is not checked, but new data must comply with the constraint if it is ENABLEd

Front 34

If you add several thousand new rows which you know comply with an existing constraint, how might you save time?

Back 34

Specify DISABLE, load the rows, then specify ENABLE NOVALIDATE to avoid Oracle 10g running through all the rows looking for rows that fail to comply

Front 35

What is the difference between INITIALLY IMMEDIATE and INITIALLY DEFERRED constraint states and which is the default?

Back 35

INITIALLY IMMEDIATE means that data is validated against the constraint as soon as the statement is executed. INITIALLY DEFERRED means that data is only validated against the constraint when a COMMIT command is executed.
INITIALLY IMMEDIATE is the default state for Oracle 10g. INITIALLY DEFERRED was not available in prior versions of Oracle.

Front 36

What is the difference between DEFERRABLE | NOT DEFERRABLE constraint states and which one is the default?

Back 36

Determines whether or not a constraint is allowed to be changed to a deferred state. NOT DEFERRABLE is the default.

Front 37

Which constraint state must be specified upon creation and may not use ALTER TABLE to change it? How do you change the constraint state?

Back 37

DEFERRABLE | NOT DEFERRABLE
To change the constraint state, you must drop the constraint and recreate it.

Front 38

What is the difference between RELY and NORELY constraint states and which one is default?

Back 38

RELY tells the optimizer to use disabled constraints, and NORELY to ignore them, when rewriting queries.
NORELY is default

Front 39

Which constraint state is usually only used for data warehouses using materialized views and query rewrites?

Back 39

RELY

Front 40

Which constraints can utilize a constraint state USING INDEX?

Back 40

PRIMARY KEY
UNIQUE

Front 41

What are the 2 choices you have for USING INDEX constraint state?

Back 41

-using a pre-existing index for the constraint
-specifying the storage setting for an index that will be created for the constraint

Front 42

What must you do or ensure before using the EXCEPTIONS | EXCEPTIONS INTO constraint state?

Back 42

Create a table for the exceptions, either named EXCEPTIONS or with your own specified name. To do this, run a script in ORACLE_HOME/rdbms/admin, either utlexcpt.sql (supports physical ROWIDs) or utlexpt1.sql (supports universal ROWIDs-such as on index-organized tables)

Front 43

How can you tell a FOREIGN KEY constraint to delete all related rows when a parent row is deleted, and how can you tell it to keep the related rows but change the FOREIGN KEY column to null?

Back 43

-ON DELETE CASCADE
-ON DELETE SET NULL

Front 44

What are the only two constraint-related data dictionary views?

Back 44

- ALL_CONSTRAINTS
- ALL_CONS_COLUMNS

Front 45

Is it better to have the index and table in the same tablespace or different tablespaces?

Back 45

different tablespaces

Front 46

What type of index stores an entry for a row with all null values in the indexed columns?

Back 46

bitmap index

Front 47

Name six types of indexes

Back 47

-BTree
-Bitmap
-Local partitioned index
-Global partitioned index
-Cluster index
-Domain index

Front 48

Name two specialized types of BTree indexes

Back 48

-Reverse key index
-Function-based index

Front 49

Which index type has an index structure in which data is divided and subdivided based on the index key values?

Back 49

BTree Index

Front 50

What is Oracle 10g's default type of index that is used when building tables?

Back 50

BTree Index

Front 51

Which index type takes up less storage space, BTree Index or Bitmap Index?

Back 51

Bitmap Index

Front 52

What is the starting point to find a value on a binary tree or BTree?

Back 52

root

Front 53

In a binary tree structure, a _____ is any point on the tree.

Back 53

node

Front 54

In a binary tree structure, a ______ is a node that has more nodes below it.

Back 54

branch

Front 55

In a binary tree structure, a _____ is the bottom level and is a node with no nodes below it.

Back 55

leaf

Front 56

Know when it is appropriate to use a Bitmap index.

Back 56

- The indexed columns should have low cardinality. (the number of distinct values in an indexed column should be low compared to the number of rows in a table).
- The table should used primarily for queries, rather than updates.
- The majority of the queries should use AND, OR, NOT, and "equal to" in the WHERE clause referencing the table's indexed columns.
- The majority of the queries include complex conditions in the WHERE clause.
- The table should not have concurrent updates.
- A bitmapped index cannot be a unique index.
- A bitmapped index cannot contain any DESC columns.

Front 57

What type of index is used on tables in which indexes are created for each individual partition?

Back 57

Local partitioned index

Front 58

What specialized type of BTree index contains columns that have been stored in reverse byte order?

Back 58

Reverse Key Indexes

Front 59

What is the difference between specifying REVERSE on an index and specifying DESC on an indexed column?

Back 59

DESC is used for descending order on an indexed column. When using the REVERSE function it essentially spells each name backwards and then alphabetizes the list.

Front 60

Which specialized type of BTree index substitutes a function or expression for a column?

Back 60

Function-based Indexes

Front 61

Which command allows you to make changes to the index?

Back 61

ALTER INDEX

Front 62

What allows easy access to database objects across schemas and databases?

Back 62

Synonyms

Front 63

What command can modify an index to help when too much unused storage is allocated or to fix incorrect settings for PCTINCREASE, NEXT or other storage settings?

Back 63

ALTER INDEX ... REBUILD

Front 64

What clause of the ALTER INDEX command can be used (faster than rebuilding) to consolidate fragmented storage space in the leaf blocks?

Back 64

COALESCE

Front 65

What clause of the ALTER INDEX command can be used only on normal indexes and index-reorganized tables to update the physical guesses of the indexed row's location stored in the index?

Back 65

UPDATE BLOCK REFERENCES

Front 66

What clause of the ALTER INDEX command can be used to cause the optimizer to ignore the index when determining execution plans?

Back 66

UNUSABLE

Front 67

Name and describe two distinct forms of views.

Back 67

1. Simple view-contains a query on one of more tables, narrowing the focus or visible data window of a specific user to a subset of the rows and/or a subset of the columns.
2. Constraint view-can be used to insert a new row as long as the row would be returned by the query, or the row exists for the view.

Front 68

Name and describe four common reasons for using views.

Back 68

1. Security-limiting the subset of data a user can view
2. Simplicity-combining tables that have complex relationships to easy query-writing for other users.
3. Complex joins-such as creating a view with a GROUP BY clause and then joining that summary data with other tables.
4. Materialized views-not as much of a view because the data is actually stored in it. Most commonly used in data warehouses.

Front 69

What is the advantage of Reverse Key indexing, and where is it useful?

Back 69

helps prevent locking delays in RAC

Front 70

What are some advantages of Materialized Views?

Back 70

-contains a copy of the data
-able to be indexed

Front 71

How can materialized views be refreshed?

Back 71

-real time
-on commit
-periodic

Front 72

What is a more efficient method to acheive the results of using a function-based index and why?

Back 72

-Use case statements before the if-then-else statements so that the result can be used and the index can still be used as well
-because a function based index creates a physical structure that must also be maintained

Front 73

Give two tips for what to do when rebuilding indexes?

Back 73

1. move it to another tablespace
2. change the INITTRANS parameter

Front 74

When is a check constraint preferable to a lookup table?

Back 74

when there are fewer choices (about 5) and the column is static

Front 75

What type of index is useful for a data warehouse but not userful for OLTP because it locks the table to update?

Back 75

Bitmap index

Front 76

What is a sequence?

Back 76

It is like an identity column, but it is not tied to a specific table... it is a separate object.

Front 77

Is it advisable to cache a sequence and why or why not?

Back 77

-No, because it would be detrimental if the database crashed because you would lose the unused values in the cache.