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.