Deck 9: Constraints, indexes, and Other Specialized Objects

Question 1

You cannot change the state of a NOT NULL constraint.

Answer 1

True

Question 2

The NOT NULL constraint must always be defined out of line.

Answer 2

False

Question 3

Integrity constraints are always enforced,no matter what application or tool is used to modify a table's data.

Answer 3

True

Question 4

You should use the NOT NULL constraint for columns holding essential information.

Question 5

A UNIQUE constraint is often used in place of a PRIMARY KEY.

Question 6

When you join tables together,you are nearly always using a column or set of columns in one table that corresponds to a column,or set of columns in another table.

Question 7

Frequently,NOT NULL and CHECK constraints are not named.

Question 8

Columns with null values are ignored by the UNIQUE constraint.

Question 9

Application constraints cannot be fine-tuned for each application.

Question 10

Modifications to the database are affected by application constraints.

Question 11

A table can have more than one UNIQUE constraint.

Question 12

The column or columns of a PRIMARY KEY may contain null values.

Question 13

A FOREIGN KEY constraint that is defined out of line is not allowed to have a name.

Question 14

The PRIMARY KEY constraint is not important for the integrity of the table.

Question 15

Although CHECK constraints can check multiple conditions,it is usually better to use multiple CHECK constraints instead of one complicated constraint.

Question 16

A table can have multiple PRIMARY KEY constraints.

Question 17

Triggers are at the database level and provide more flexibility in designing complex constraints than integrity constraints,while keeping the constraint within the database.

Question 18

Constraints are enabled by default when you create them.

Question 19

Naming constraints is mandatory.

Question 20

Most tables have a primary key to comply with the standards of normalization of tables.

Question 21

CHECK constraints cannot contain queries or any references to other tables.

Question 22

A(n)____ constraint appears immediately next to the column to which it applies.

A) single column
B) multiple column
C) inline
D) out of line

Question 23

By default,the initial value of a sequence when it is first created is 0.

Question 24

The ____ constraint is similar to the PRIMARY KEY constraint because both enforce unique values;the difference is that it allows null values in the column or columns that are named in the constraint.

A) CHECK
B) FOREIGN KEY
C) UNIQUE
D) NOT NULL

Question 25

You can use ALTER INDEX to change the column order of an existing index.

Question 26

An index does not store an entry for a row when all the indexed columns are null except if the index is a bitmap index.

Question 27

A ____ is a program that runs when a certain event occurs.

A) stored procedure
B) constraint
C) trigger
D) PL / SQL

Question 28

It is possible to create an index owned by one user on a table owned by another user,provided that the creating user has either the CREATE ANY INDEX privilege or the INDEX privilege on the target table,or the user has been granted specific privileges for creating an index on that table.

Question 29

A bitmap index stores ROWID values and key values.

Question 30

A(n)____ constraint appears after the full list of columns in the CREATE TABLE command and is usually used to define constraints to multiple columns.

A) single column
B) multiple column
C) inline
D) out of line

Question 31

The ____ constraint enforces a predefined list of values for a column.

A) CHECK
B) FOREIGN KEY
C) UNIQUE
D) NOT NULL

Question 32

____ checking performs faster than other methods of constraint enforcement,because the optimizer has been programmed to handle them efficiently.

A) Trigger
B) Integrity constraint
C) Application constraint
D) Index

Question 33

____ integrity is the integrity,or soundness of data,maintained between two values,which reference each other.

A) Constraint
B) Spatial
C) Normalization
D) Referential

Question 34

You can use expressions in function-based indexes.

Question 35

A bitmap index can be a UNIQUE index.

Question 36

Placing the ____ constraint on a column indicates that all rows in the table must contain a value in the column.

A) CHECK
B) FOREIGN KEY
C) UNIQUE
D) NOT NULL

Question 37

The syntax for adding a CHECK constraint with CREATE TABLE and ALTER TABLE is quite different.

Question 38

A(n)____ integrity constraint is the column or set of columns that define a unique identifying value for every row in the table.

A) primary key
B) foreign key
C) unique
D) not null

Question 39

Oracle 10g supports ____ types of integrity constraints.

A) three
B) four
C) five
D) six

Question 40

An index does not have its own storage specification,so it has to be in the same tablespace as the associated table.

Question 41

When viewing constraints from ALL_CONSTRAINTS,foreign key constraints are indicated with the letter ____.

A) F
B) G
C) K
D) R

Question 42

To add a ____ constraint successfully,all rows in the table must contain values for the column.

A) UNIQUE
B) CHECK
C) NOT NULL
D) NULL

Question 43

The ____ command provides a way to change a named constraint or all constraints to a deferred state during your session.

A) DEFERRED
B) DEFER
C) SET DEFER
D) SET CONSTRAINT

Question 44

The ____ data dictionary view contains the definition of a constraint.

A) ALL_CONS_DEF
B) ALL_DEF_CONS
C) ALL_CONSTRAINTS
D) ALL_CONS_COLUMNS

Question 45

The ____ data dictionary view lists the columns associated with each constraint.

A) ALL_CONSTRAINTS
B) ALL_CONS_COLUMNS
C) ALL_COLUMNS
D) ALL_CONS_COLS

Question 46

When viewing constraints from ALL_CONSTRAINTS,NOT NULL constraints are listed as ____ constraints,and have system-assigned names.

A) referential
B) CHECK
C) UNIQUE
D) special

Question 47

There are only ____ constraint-related data dictionary views.

A) two
B) three
C) four
D) five

Question 48

A constraint ____ is the attribute that tells Oracle 10g how to use the constraint when data is added to the table.

A) attribute
B) state
C) value
D) restriction

Question 49

____ is a pseudocolumn that represents the current date and time.

A) DATE
B) TODAY
C) CURRENT
D) SYSDATE

Question 50

The ON DELETE ____ setting tells Oracle 10g that when a parent row is deleted,all the related child rows are also deleted.

A) DEFER
B) CASCADE
C) SET NULL
D) UPDATE

Question 51

Any constraint other than ____ can be dropped using the ALTER TABLE DROP CONSTRAINT command.

A) NOT NULL
B) CHECK
C) PRIMARY KEY
D) FOREIGN KEY

Question 52

ON DELETE ____ instructs Oracle 10g to set the value of the foreign key column in the related child rows to null instead of deleting the rows.

A) DEFER
B) CASCADE
C) SET NULL
D) UPDATE

Question 53

The UNIQUE key constraint and the ____ constraint are very similar.

A) NOT NULL
B) CHECK
C) PRIMARY KEY
D) FOREIGN KEY

Question 54

The ____ constraint helps validate the value within a column or a set of columns within one row.

A) NOT NULL
B) CHECK
C) PRIMARY KEY
D) FOREIGN KEY

Question 55

If you omit the ____ parameter,you cannot delete a row from the parent table,if it has related children in other tables.

A) ON DELETE
B) CASCADE
C) DEFER
D) SET NULL

Question 56

You can use the ____ constraint to compare one column against another column.

A) NOT NULL
B) CHECK
C) PRIMARY KEY
D) FOREIGN KEY

Question 57

It is advisable to specifically name at least all PRIMARY KEY and ____ constraints.

A) FOREIGN KEY
B) CHECK
C) NOT NULL
D) UNIQUE KEY

Question 58

CHECK constraints cannot use pseudocolumns,such as ____ and USER.

A) TODAY
B) TIME
C) DATE
D) SYSDATE

Question 59

PRIMARY KEY column(s)is / are usually placed ____ the table.

A) at the beginning of
B) at the end of
C) anywhere in
D) randomly in

Question 60

When you drop a ____ constraint,the associated index is also dropped.

A) NOT NULL
B) CHECK
C) PRIMARY KEY
D) FOREIGN KEY

Question 61

A(n)____ index is an index that is partitioned when either the table is not partitioned or the table is partitioned in a different way than the index.

A) logical partitioned
B) special partitioned
C) global partitioned
D) local partitioned

Question 62

The ____ index is used for application- or cartridge-specific indexes.

A) domain
B) cluster
C) local partitioned
D) global partitioned

Question 63

If successful,the ALTER INDEX ...REBUILD command automatically corrects an index that has been marked ____ because a change was made to the structure of the underlying table or partition.

A) "DAMAGED"
B) "UNUSABLE"
C) "DIRTY"
D) "ALTERED"

Question 64

The reverse key index is a type of ____ index used to improve efficiency of Oracle Real Application Clusters.

A) cluster
B) global partitioned
C) BTree
D) bitmap

Question 65

____________________ constraints enforce only those changes that are made within the application.

Question 66

An index created to support a ____ constraint does not allow any null values in any of the indexed columns,because there is an automatic NOT NULL constraint added to a primary key column.

A) PRIMARY KEY
B) FOREIGN KEY
C) UNIQUE
D) CHECK

Question 67

The ____ index is used on partitioned tables where the index is created on all partitions for the entire table,as a single index.

A) domain
B) cluster
C) local partitioned
D) global partitioned

Question 68

A ____ view is a view that can be used to insert a new row into the underlying table,as long as the row would be returned by a query,or the row exists for the view.

A) simple
B) constraint
C) materialized
D) query

Question 69

An index can have from one to ____ columns (only 30 columns if it is a bitmap index).

A) 32
B) 35
C) 40
D) 60

Question 70

When executing a package or a procedure,you must either wrap the command in a PL / SQL block by using the BEGIN and END;clauses on either side of the command,or type ____ and the command all on a single line.

A) INIT
B) COMMAND
C) RUN
D) EXECUTE

Question 71

Sequence values can be accessed using the ____ and NEXTVAL pseudocolumns.

A) PREVVAL
B) FIRSTVAL
C) CURRVAL
D) UPVAL

Question 72

A data dictionary view named ____ lists indexed column names and statistics either owned by the user or accessible to the user.

A) ALL_IND_COLUMNS
B) DBA_IND_COLUMNS
C) USER_IND_COLUMNS
D) ALL_INDEXES

Question 73

The ____ index is used to store pre-computed expression values based on table columns.

A) domain
B) function-based
C) BTree
D) reverse key

Question 74

You can create a ____ index on a nonpartitioned table.

A) logical partitioned
B) special partitioned
C) global partitioned
D) local partitioned

Question 75

The ____ index is by default a BTree but can also be a hash-clustered index.

A) domain
B) cluster
C) local partitioned
D) global partitioned

Question 76

The indexed columns in a ____ index should have low cardinality.

A) cluster
B) BTree
C) domain
D) bitmap

Question 77

A ____ index cannot contain any DESC columns.

A) cluster
B) bitmap
C) BTree
D) domain

Question 78

____________________ constraints are constraints defined at the database level on either a column or a table.

Question 79

In addition to providing faster access to data,certain indexes also have a second purpose: enforcing a PRIMARY KEY constraint or ____ constraint.

A) FOREIGN KEY
B) CHECK
C) NOT NULL
D) UNIQUE

Question 80

Unless you specifically instruct Oracle 10g to create a bitmap index,it always creates a ____ index.

A) domain
B) BTree
C) cluster
D) local partitioned