Schemas authority from PUBLIC increases control over the use of schema names, it can result in authorization errors when existing applications attempt to create Schemas also have privileges, allowing the schema owner to control which users have the privilege to create, alter, and drop objects in the schema. A schema owner is initially given all of these privileges on the schema, with the ability to grant them to others. An implicitly created schema is owned by the ystem, and all users are initially given the privilege to create objects in such a schema. A user with SYSADM or DBADM authority can change the privileges held by users on any schema. Therefore, access to create, alter, and drop objects in any schema(even one that was implicitly created) can be controlled. Tables Tables are logical structures maintained by the database manager. Tables are made up of columns and rows. The rows are not necessarily ordered within a table(order is determined by the application program). At the intersection of every column and row is a specific data item called a ualue. A column is a set f values of the same type or one of its subtypes. A row is a sequence of values arranged so that the nth value is a value of the nth column of the table a base table is created with the CREatE table statement and is used to hold persistent user data. A result table is a set of rows that the database manager selects or generates from one or more base tables to satisfy a query A summary table is a table defined by a query that is also used to determine the data in the table Summary tables can be used to improve the performance of queries. If the database manager determines that a portion of a query can be resolved using a summary table, the database manager can rewrite the query to use the summary table. This decision is based on database configuration settings, such as the CURRENT REFRESH AGE and the CURRENT QUERY OPTIMIZATION special registers a table can define the data type of each column separately, or base the types on the attributes of a user-defined structured type. This is called a typed table A user-defined structured type may be part of a type hierarchy. A subtype inherits attributes from its supertype. Similarly, a typed table can be part of a table hierarchy. A subtable inherits columns from its supertable. Note that the term subtype applies to a user-defined structured type and all user-defined structured types that are below it in the type hierarchy. A proper subtype of a structured type T is a structured type below T in the type hierarchy. Similarly, the term subtable applies to a typed table and all typed tables that are below it in the table hierarchy. A proper subtable of a table T is a table below T in the table hierarchy Chapter 1.Concepts 5
authority from PUBLIC increases control over the use of schema names, it can result in authorization errors when existing applications attempt to create objects. Schemas also have privileges, allowing the schema owner to control which users have the privilege to create, alter, and drop objects in the schema. A schema owner is initially given all of these privileges on the schema, with the ability to grant them to others. An implicitly created schema is owned by the system, and all users are initially given the privilege to create objects in such a schema. A user with SYSADM or DBADM authority can change the privileges held by users on any schema. Therefore, access to create, alter, and drop objects in any schema (even one that was implicitly created) can be controlled. Tables Tables are logical structures maintained by the database manager. Tables are made up of columns and rows. The rows are not necessarily ordered within a table (order is determined by the application program). At the intersection of every column and row is a specific data item called a value. A column is a set of values of the same type or one of its subtypes. A row is a sequence of values arranged so that the nth value is a value of the nth column of the table. A base table is created with the CREATE TABLE statement and is used to hold persistent user data. A result table is a set of rows that the database manager selects or generates from one or more base tables to satisfy a query. A summary table is a table defined by a query that is also used to determine the data in the table. Summary tables can be used to improve the performance of queries. If the database manager determines that a portion of a query can be resolved using a summary table, the database manager can rewrite the query to use the summary table. This decision is based on database configuration settings, such as the CURRENT REFRESH AGE and the CURRENT QUERY OPTIMIZATION special registers. A table can define the data type of each column separately, or base the types on the attributes of a user-defined structured type. This is called a typed table. A user-defined structured type may be part of a type hierarchy. A subtype inherits attributes from its supertype. Similarly, a typed table can be part of a table hierarchy. A subtable inherits columns from its supertable. Note that the term subtype applies to a user-defined structured type and all user-defined structured types that are below it in the type hierarchy. A proper subtype of a structured type T is a structured type below T in the type hierarchy. Similarly, the term subtable applies to a typed table and all typed tables that are below it in the table hierarchy. A proper subtable of a table T is a table below T in the table hierarchy. Schemas Chapter 1. Concepts 5
Tables TaBle statement and is used to hold temporary data on behalf of a single 2 a declared temporary table is created with a DECLARE GLOBAL TEMPORAI application. This table is dropped implicitly when the application disconnects from the database Views A view provides a different way of looking at the data in one or more tables; it is a named specification of a result table. The specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval. whether a view can be used in an inser You can use views to control access to sensitive data, because views allow multiple users to see different presentations of the same data. For example, several users may be accessing a table of data about employees. A manager sees data about his or her employees but not employees in another department. A recruitment officer sees the hire dates of all employees, but not their salaries; a financial officer sees the salaries but not the hire dates. each of these users works with a view derived from the base table. each view appears to be a table and has its own name When the column of a view is directly derived from the column of a base table, that view column inherits any constraints that apply to the base table column. For example, if a view includes a foreign key of its base table, insert and update operations using that view are subject to the same referential constraints as is the base table. Also, if the base table of a view is a parent table, delete and update operations using that view are subject to the same rules as are delete and update operations on the base table A view can derive the data type of each column from the result table, or base he types on the attributes of a user-defined structured type. This is called a typed view. Similar to a typed table, a typed view can be part of a view hierarchy. A subview inherits columns from its superview. The term subview applies to a typed view and to all typed views that are below it in the view hierarchy. A proper subview of a view V is a view below V in the typed view hierarchy A view can become inoperative(for example, if the base table is dropped); if this occurs, the view is no longer available for SQL operations 6 SQL Reference, Volume 1
A declared temporary table is created with a DECLARE GLOBAL TEMPORARY TABLE statement and is used to hold temporary data on behalf of a single application. This table is dropped implicitly when the application disconnects from the database. Views A view provides a different way of looking at the data in one or more tables; it is a named specification of a result table. The specification is a SELECT statement that is run whenever the view is referenced in an SQL statement. A view has columns and rows just like a base table. All views can be used just like base tables for data retrieval. Whether a view can be used in an insert, update, or delete operation depends on its definition. You can use views to control access to sensitive data, because views allow multiple users to see different presentations of the same data. For example, several users may be accessing a table of data about employees. A manager sees data about his or her employees but not employees in another department. A recruitment officer sees the hire dates of all employees, but not their salaries; a financial officer sees the salaries, but not the hire dates. Each of these users works with a view derived from the base table. Each view appears to be a table and has its own name. When the column of a view is directly derived from the column of a base table, that view column inherits any constraints that apply to the base table column. For example, if a view includes a foreign key of its base table, insert and update operations using that view are subject to the same referential constraints as is the base table. Also, if the base table of a view is a parent table, delete and update operations using that view are subject to the same rules as are delete and update operations on the base table. A view can derive the data type of each column from the result table, or base the types on the attributes of a user-defined structured type. This is called a typed view. Similar to a typed table, a typed view can be part of a view hierarchy. A subview inherits columns from its superview. The term subview applies to a typed view and to all typed views that are below it in the view hierarchy. A proper subview of a view V is a view below V in the typed view hierarchy. A view can become inoperative (for example, if the base table is dropped); if this occurs, the view is no longer available for SQL operations. Tables 6 SQL Reference, Volume 1
Aliases Aliases An alias is an alternative name for a table or a view it can be used to reference a table or a view if an existing table or view can be referenced. An alias cannot be used in all contexts; for example, it cannot be used in the check condition of a check constraint. An alias cannot reference a declared temporary table Like tables or views, an alias can be created, dropped, and have comments associated with it. However, unlike tables, aliases can refer to each other in a process called chaining. Aliases are publicly referenced names, so no special authority or privilege is required to use them. Access to the table or the view referred to by an alias, however, does require the authorization associated with these objec There are other types of aliases, such as database and network aliases. Aliases can also be created for nicknames that refer to data tables or views located on federated systems Indexes An index is an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns. An index is an object that is separate from the data in the table. When an index is created, the database manager builds this object and maintains it automatically Indexes are used by the database manager to Improve performance. In most cases, access to data is faster with an index Although an index cannot be created for a view, an index created for the table on which a view is based can sometimes improve the performance of operations on that view Ensure uniqueness. A table with a unique index cannot have rows with identical keys a key is a set of columns that can be used to identify or access a particular row or rows. The key is identified in the description of a table, index, or referential constraint. The same column can be part of more than one key. a key that is composed of more than one column is called a composite key. In a table with a composite key, the order of the columns within the composite key is not constrained by the order of the columns within the table. The value of a composite key denotes a composite value. Thus, a rule such as"the value of Chapter 1.Concepts 7
Aliases An alias is an alternative name for a table or a view. It can be used to reference a table or a view if an existing table or view can be referenced. An alias cannot be used in all contexts; for example, it cannot be used in the check condition of a check constraint. An alias cannot reference a declared temporary table. Like tables or views, an alias can be created, dropped, and have comments associated with it. However, unlike tables, aliases can refer to each other in a process called chaining. Aliases are publicly referenced names, so no special authority or privilege is required to use them. Access to the table or the view referred to by an alias, however, does require the authorization associated with these objects. There are other types of aliases, such as database and network aliases. Aliases can also be created for nicknames that refer to data tables or views located on federated systems. Indexes An index is an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns. An index is an object that is separate from the data in the table. When an index is created, the database manager builds this object and maintains it automatically. Indexes are used by the database manager to: v Improve performance. In most cases, access to data is faster with an index. Although an index cannot be created for a view, an index created for the table on which a view is based can sometimes improve the performance of operations on that view. v Ensure uniqueness. A table with a unique index cannot have rows with identical keys. Keys A key is a set of columns that can be used to identify or access a particular row or rows. The key is identified in the description of a table, index, or referential constraint. The same column can be part of more than one key. A key that is composed of more than one column is called a composite key. In a table with a composite key, the order of the columns within the composite key is not constrained by the order of the columns within the table. The value of a composite key denotes a composite value. Thus, a rule such as “the value of Aliases Chapter 1. Concepts 7
the foreign key must be equal to the value of the primary key"means that each component of the value of the foreign key must be equal to the corresponding component of the value of the primary key A unique key is a key that is constrained so that no two of its values are equal columns of a unique key cannot contain null values. The constraint is enforced by the database manager during the execution of any operation that changes data values, such as INSERT or UPDATE. The mechanism used to of a unique index. Such an index is also said to have the uniQue attribute 2 enforce the constraint is called a unique index. Thus, every unique key is a ke A primary key is a special case of a unique key. a table cannot have more than one primary key. A foreign key is a key that is specified in the definition of a referential constraint A partitioning key is a key that is part of the definition of a table in a partitioned database. The partitioning key is used to determine the partition on which the row of data is stored. If a partitioning key is defined, unique keys and primary keys must include the same columns as the partitioning key, but can have additional columns. a table cannot have more than one partitioning key. Constraints A constraint is a rule that the database manager enforces There are three types of constraints: A unique constraint is a rule that forbids duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints. For example, a unique constraint can be defined on the supplier identifier in the supplier table to ensure that the same supplier identifier is not given to two suppliers. A referential constraint is a logical rule about values in one or more columns in one or more tables. For example, a set of tables shares information about a corporations suppliers. Occasionally, a suppliers name changes. You ca define a referential constraint stating that the ID of the supplier in a table must match a supplier ID in the supplier information. This constraint prevents insert, update, or delete operations that would otherwise result in missing supplier information. a table check constraint sets restrictions on data added to a specific table. For example, a table check constraint can ensure that the salary level for an 8 SQL Reference, Volume 1
the foreign key must be equal to the value of the primary key” means that each component of the value of the foreign key must be equal to the corresponding component of the value of the primary key. A unique key is a key that is constrained so that no two of its values are equal. The columns of a unique key cannot contain null values. The constraint is enforced by the database manager during the execution of any operation that changes data values, such as INSERT or UPDATE. The mechanism used to enforce the constraint is called a unique index. Thus, every unique key is a key of a unique index. Such an index is also said to have the UNIQUE attribute. A primary key is a special case of a unique key. A table cannot have more than one primary key. A foreign key is a key that is specified in the definition of a referential constraint. A partitioning key is a key that is part of the definition of a table in a partitioned database. The partitioning key is used to determine the partition on which the row of data is stored. If a partitioning key is defined, unique keys and primary keys must include the same columns as the partitioning key, but can have additional columns. A table cannot have more than one partitioning key. Constraints A constraint is a rule that the database manager enforces. There are three types of constraints: v A unique constraint is a rule that forbids duplicate values in one or more columns within a table. Unique and primary keys are the supported unique constraints. For example, a unique constraint can be defined on the supplier identifier in the supplier table to ensure that the same supplier identifier is not given to two suppliers. v A referential constraint is a logical rule about values in one or more columns in one or more tables. For example, a set of tables shares information about a corporation’s suppliers. Occasionally, a supplier’s name changes. You can define a referential constraint stating that the ID of the supplier in a table must match a supplier ID in the supplier information. This constraint prevents insert, update, or delete operations that would otherwise result in missing supplier information. v A table check constraint sets restrictions on data added to a specific table. For example, a table check constraint can ensure that the salary level for an Keys 8 SQL Reference, Volume 1
Constraints employee is at least $20,000 whenever salary data is added or updated in a table containing personnel information. Referential and table check constraints can be turned on or off. It is generally a good idea, for example, to turn off the enforcement of a constraint when large amounts of data are loaded into a database Unique constraints A unique constraint is the rule that the values of a key are valid only if they are unique within a table. Unique constraints are optional and can be defined in the CREATE TABLE or ALTER TABLE statement using the PRIMARY KEY ause or the UNIQUE clause. The columns specified in a unique constraint must be defined as NOT NULL. The database manager uses a unique index to enforce the uniqueness of the key during changes to the columns of the unique constraint. a table can have an arbitrary number of unique constraints, with at most one unique constraint defined as the primary key. a table cannot have more than one unique constraint on the same set of columns onstraint is called the parent key. by the foreign key of a referenti When a unique constraint is defined in a CREATE TABLE statement, a unique ndex is automatically created by the database manager and designated as a primary or unique system-required index When a unique constraint is defined in an ALTER TABLE statement and an index exists on the same columns, that index is designated as unique and ystem-required. If such an index does not exist, the unique index is automatically created by the database manager and designated as a primary or unique s ystem-required index. Note that there is a distinction between defining a unique constraint and creating a unique index. Although both enfo orce unie iqueness, a unique index illows nullable columns and generally cannot be used as a parent key Referential constraints Referential integrity is the state of a database in which all values of all foreign keys are valid. A foreign keyis a column or a set of columns in a table whose values are required to match at least one primary key or unique key value of a row in its parent table. A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is true They appear as values of a parent key Some component of the foreign key is null Chapter 1. Concepts 9
employee is at least $20,000 whenever salary data is added or updated in a table containing personnel information. Referential and table check constraints can be turned on or off. It is generally a good idea, for example, to turn off the enforcement of a constraint when large amounts of data are loaded into a database. Unique constraints A unique constraint is the rule that the values of a key are valid only if they are unique within a table. Unique constraints are optional and can be defined in the CREATE TABLE or ALTER TABLE statement using the PRIMARY KEY clause or the UNIQUE clause. The columns specified in a unique constraint must be defined as NOT NULL. The database manager uses a unique index to enforce the uniqueness of the key during changes to the columns of the unique constraint. A table can have an arbitrary number of unique constraints, with at most one unique constraint defined as the primary key. A table cannot have more than one unique constraint on the same set of columns. A unique constraint that is referenced by the foreign key of a referential constraint is called the parent key. When a unique constraint is defined in a CREATE TABLE statement, a unique index is automatically created by the database manager and designated as a primary or unique system-required index. When a unique constraint is defined in an ALTER TABLE statement and an index exists on the same columns, that index is designated as unique and system-required. If such an index does not exist, the unique index is automatically created by the database manager and designated as a primary or unique system-required index. Note that there is a distinction between defining a unique constraint and creating a unique index. Although both enforce uniqueness, a unique index allows nullable columns and generally cannot be used as a parent key. Referential constraints Referential integrity is the state of a database in which all values of all foreign keys are valid. A foreign keyis a column or a set of columns in a table whose values are required to match at least one primary key or unique key value of a row in its parent table. A referential constraint is the rule that the values of the foreign key are valid only if one of the following conditions is true: v They appear as values of a parent key. v Some component of the foreign key is null. Constraints Chapter 1. Concepts 9