Arch. Ch. 8 & 9

NCHAR

(length) Unicode-only data type. When you create a table with this type of column, you define the column length in characters.

NVARCHAR2

(size [BYTE|CHAR]) Unicode-only data type. Like NCHAR except that its maximum length is 4,000 bytes and it is not blank-padded.

RAW

(size) Raw binary data of length size bytes. The maximum size is 2,000 bytes. You must specify the size.

PRIMARY Key

Each table in the database can have at most one. The values in the group of one or more columns that are subject to this constraint constitute the unique identifier of the row. Does not allow nulls and no two rows have duplicate values in the column or set of columns

INTEGER

Equivalent to NUMBER (p,0)

CHAR

Fixed-length character data of size bytes or characters.

single-byte

For ________ character sets, columns that are defined in character semantics are basically the same as those defined in byte semantics.

unique indexes

For nondeferrable constraints the primary key and unique key constraints need _________, but if they already have a non-________, that index will be used.

ENABLE VALIDATE

Both new and existing data conform to the constraint. This is the typical and default state of a constraint.

LONG

Character data of variable length of up to 2 GB. This is a deprecated data type. Use LOB instead.

NCLOB

Character large object containing Unicode characters.

CLOB

Character large object containing single-byte or multiple-byte or multibyte characters. Both fixed-width and variable-width character sets are supported and both use the CHAR database character set.

BFILE

Contains a locator to a large binary file stored outside the database. it enables byte stream I/O access to external LOBs residing on the database server. Max size is 4GB

SYSTEM schema

Contains additional tables and views that store administrative information.

same

Deferred constraints are most useful when both the parent and child rows in a foreign key relationship are entered at the ________ time.

automatically

During a complete installation of an Oracle database, sample schemas are installed _________.

LONG RAW

Raw binary data of variable length of up to 2 GB.

schema objects

logical structures that directly refer to a database's data including structures such as tables, views and indexes.

4000 bytes

maximum size for VARCHAR2.

ENABLE NOVALIDATE

new data conforms to the constraint, but existing data is in an unknown state. Frequently used when it is known that clean and conforming data exists in the table so there is no need for validation. However, new violations are not allowed to enter the system.

constraint violation

occurs when DML is submitted that does not comply with the constraint.

indexes

optional structures associated with tables that can be created to improve the performance of data update and retrieval.

view

representations of queries of data from one or more tables or other things like it.

NOT NULL

requires that a column of a table must contain no no null values.

Check constraints

requires that a specified condition be true or unknown for every row of the table.

UNIQUE Key

requires that every value in a column or set of columns be unique.

Referential integrity constraints

requires that for each row of a table, the value in the foreign key must match a value in a parent key.

interlinked

sample schemas are a set of ________ schemas aimed at providing examples of different levels of complexity.

Sales History

schema allows demonstrations with larger amounts of data. An extension to this schema provides support for advanced analytic processing.

Product Media

schema dedicated to multimedia data types

sample schemas

serve the purpose of providing a common platform for examples in Oracle documentation and curricula.

8 TB - 128 TB

size range for all LOB (BLOB, CLOB, NCLOB) data types.

OC (Online Catalog)

subschema is a collection of object-relational objects built inside the OE schema.

BI

the Business Intelligence schema contains a set of objects used to demonstrate Business Intelligence capabilities.

base tables

the tables in which views derive their data.

DATE

valid date ranging from january 1, 4712 (B.C.) through December 31, 9999 (A.D.) It also stores the time (hours, minutes, and seconds)

VARCHAR2

variable-length character string having maximum length size bytes or characters.

stored queries

views are _______ because they can hide very complex conditions and joins as well as other complex expressions and SQL constructs.

tables

views derive their data from the _______ on which they are based.

data

views do not actually contain _______.

Number

with precision p and scale s. The precision can range from 1 through 39. The scale can range from -84 through 127.

$ and #

you are strongly discouraged from using the ___ and the ___ in nonquoted identifiers.

cache

you cannot ________ more values than would fit in a given cycle of sequence numbers.

BINARY_FLOAT

32-bit floating-point number. This data type requires 5 bytes, including the length byte.

BINARY_DOUBLE

64-bit floating-point number. This data type requires 9 bytes.

nondeferrable

A constraint that is defined as ________ cannot be changed to a deferrable constraint.

single

A schema does not necessarily have to be directly related to a _________ tablespace.

owns

A schema has the same name as the user that _______ the schema.

FLOAT

ANSI data type. It is a floating-point number with a binary precision (p). The default precision for this data type is 126 binary or 38 decimal.

Cycle Values

After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

Uniqueness

An attempt is made to have duplicate values in a column that has a unique constraint.

Check violation

An attempt is made to store a value in a column that does not follow the rules defined for that column.

UROWID

Base-64 string representing the logical address of a row of an index-organized table.

ROWID

Base-64 string representing the unique address of a row in the database. This data type is primarly for values returned by the ROWID pseudocolumn.

BLOB

Binary large object

Order Values

Guarantees that sequence numbers are generated in the order of request.

IMMEDIATE

If a DML statement results in the condition of a constraint evaluating to false, the statement is rolled back if the constraint is __________.

DISABLE VALIDATE

If a constrain is in this state, modification of the constrained columns is not allowed because it would be inconsistent to validate the existing data and allow unchecked data to enter the table. Used when existing data must be validated but not modified and when the index is not otherwise needed for performance.

commit time

If constraint violations are detected at ________, the entire transaction is rolled back.

DEFERRED

If the constraint is deferrable and is set to ________ , rollback occurs at commit rather than at DML execution.

caching

If you are not _______ the sequence is in order by default.

error

If you do not choose the cycle value option, an _________ is returned when you attempt to retrieve a value after the sequence has been exhausted.

2000

Maximum size of CHAR datatype is _______ bytes or characters.

1 byte

Minimum (and default) size of CHAR datatype

DISABLE NOVALIDATE

Often used when the data is from an already validated source and the table is read-only, so no new data is being entered into the table. Used in data warehousing situations where the data has already been cleaned up. No validation is needed, thereby saving time.

PL/SQL

Procedure Language for SQL

Cache Option

Specifies how many values of the sequence the Oracle database preallocates and keeps in memory for faster access.

Initial

Specifies the first sequence number to be generated. Use this clause to start an ascending sequence a t a value greater than its minimum or to start a descending sequence at a value less than its maximum.

Interval

Specifies the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. It can have 28 or fewer digits. Default is 1.

Order Entry

The ________ schema deals with matters of intermediate complexity.

Human Resources

The _________ schema is a simple schema for introducing basic topics. An extension to this schema supports Oracle Internet Directory demonstrations.

Information Exchange

The __________ schema contains a set of objects used to demonstrate shipping through business-to-business applications.

uppercase

When a SQL statement is processed, nonquoted names are converted to all ________.

referential integrity violation

When the rule that every child row has a parent row is violated.

Enterprise Manager

When you use ________ the underlying SQL is generated for you.

double quotation marks

You can break several of the naming rules by enclosing an object name within _______

SQL

You can create and manipulate schema objects by using _______ or Enterprise Manager.

configurations

You can define _________ so that objects in a single schema can be in different tablespaces and so that a tablespace can hold objects from different schemas.

schema page

You can quickly access many types of schema objects from the ________.

National character

You defined the ____________ set when you create your database

user

_____ typically represents not a person but an application.

Guaranteeing order

______ is usually not important for sequences that are used to generate primary keys.

Nonquoted

______ names are stored in all-uppercase characters and are not case-sensitive.

schema

a collection of database objects that are owned by a particular user.

sequence

a mechanism for automatically generating integers that follow a pattern.

NEXTVAL

a pseudocolumn that increments the sequence and returns the next value.

CURRVAL

a pseudocolumn that returns the current value of a sequence

Initially immediate

a specification for deferrable constraints that specifies that by default it must function as an immediate constraint unless explicity set otherwise.

Initially deferred

a specification for deferrable constraints that specifies that by default the constraint must be enforced only at the end of the transaction.

Nondeferred constraints

also known as immediate constraints are enforced at the end of every DML statement.

Deferred constraints

are constraints that are checked only when a transaction is committed.

Character semantics

are useful for defining varying-width multi-byte strings, reducing the complexity when defining the actual length requirements for data storage.

Database links

can also contain periods and the @ sign.

Quoted Identifiers

can contain any characters and punctuation marks as well as spaces.

Nonquoted identifiers

can contain only alphanumeric characters from your database character set as well as the underscore, dollar sign and pound sign.

SYS schema

contains the data dictionary

non-unique

for deferred constraints, primary key and unique keys need ________ indexes, if the columns already have a unique index on them, constraint creation fails because those indexes cannot be deferred.

TIMESTAMP

fractional_seconds_precision: specifies the year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is teh number of digits in the fractional part of a second. The accepted values are 0 to 9. the default is 6.

disabled

if a constraint is ________ the nonconforming data can be entered into the database.

enabled

if a constraint is ________, the data is checked as it is entered or updated in the database. Data that does not conform to the constraint's rule is prevented from being entered.