TPC BENCHMARK (TM) H, страница 5

Ifconstraints are used, they must satisfy the following requirements:They must be specified using SQL. There is no specific implementation requirement. For example,CREATE TABLE, ALTER TABLE, CREATE UNIQUE INDEX, and CREATE TRIGGER are all validstatements;Constraints must be enforced either at the statement level or at the transaction level;All defined constraints must be enforced and validated before the load test is complete (see Clause 5.1.1.2);1.4.2.1 The NOT NULL attribute may be used for any column.1.4.2.2 The following columns or set of columns listed in Clause 1.4.1 as ‘Primary Key’ may be defined as primary keyconstraints (using the PRIMARY KEY clause or other equivalent syntax):P_PARTKEY;S_SUPPKEY;PS_PARTKEY, PS_SUPPKEY;C_CUSTKEY;O_ORDERKEY;L_ORDERKEY, L_LINENUMBER;N_NATIONKEY;R_REGIONKEY.Defining a primary key constraint can only be done for the columns listed above.1.4.2.3 Columns listed in the comments of Clause 1.4.1 as ‘Foreign Key’ may be defined as foreign key constraints.

Thereis no specific requirement to use referential actions (e.g., RESTRICT, CASCADE, NO ACTION, etc.). If anyforeign key constraint is defined by an implementation, then all the foreign key constraints listed below must bedefined by the implementation (using the FOREIGN KEY clause or other equivalent syntax):S_NATIONKEY(referencing N_NATIONKEY);PS_PARTKEY (referencing P_PARTKEY);PS_SUPPKEY (referencing S_SUPPKEY);C_NATIONKEY (referencing N_NATIONKEY);O_CUSTKEY (referencing C_CUSTKEY);L_ORDERKEY (referencing O_ORDERKEY);L_PARTKEY (referencing P_PARTKEY);L_SUPPKEY (referencing S_SUPPKEY);L_PARTKEY, L_SUPPKEY (referencing PS_PARTKEY, PS_SUPPKEY);N_REGIONKEY (referencing R_REGIONKEY);Defining a foreign key constraint can only be done for the columns listed above.1.4.2.4 Check Constraints: Check constraints may be defined to restrict the database contents.

In order to supportevolutionary change, the check constraints must not rely on knowledge of the enumerated domains of each column.The following list of expressions defines permissible check constraints:1.Positive KeysP_PARTKEY >= 0S_SUPPKEY >= 0C_CUSTKEY >= 0TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 18PS_PARTKEY >= 0R_REGIONKEY >= 0N_NATIONKEY >= 02.Open-interval constraintsP_SIZE >= 0P_RETAILPRICE >= 0PS_AVAILQTY >= 0PS_SUPPLYCOST >= 0O_TOTALPRICE >= 0L_QUANTITY >= 0L_EXTENDEDPRICE >= 0L_TAX >= 03.Closed-interval constraintsL_DISCOUNT between 0.00 and 1.004.Multi-column constraintsL_SHIPDATE <= L_RECEIPTDATEComment: The constraints rely solely on the diagram provided in Clause 1.2and the description in Clause 1.4.

Theyare not derived from explicit knowledge of the data population specified in Clause 4.2.1.5Implementation Rules1.5.1The database shall be implemented using a commercially available database management system (DBMS).1.5.2The physical clustering of records within the database is allowed as long as this clustering does not alter the logicalindependence of each table.Comment: The intent of this clause is to permit flexibility in the physical design of a database while preserving astrict logical view of all the tables.1.5.3At the end of the Load Test, all tables must have exactly the number of rows defined for the scale factor, SF, and thedatabase population, both specified in Clause 4: .1.5.4Horizontal partitioning of base tables or auxiliary structures created by database directives (see Clause 1.5.7) isallowed.

Groups of rows from a table or auxiliary structure may be assigned to different files, disks, or areas. If thisassignment is a function of data in the table or auxiliary structure, the assignment must be based on the value of apartitioning field. A partitioning field must be one and only one of the following:A column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary keyconstraint;A column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;A column having a date datatype as defined in Clause 1.3.Some partitioning schemes require the use of directives that specify explicit values for the partitioning field.

If suchdirectives are used they must satisfy the following conditions:They may not rely on any knowledge of the data stored in the table except the minimum and maximumvalues of columns used for the partitioning field. The minimum and maximum values of columns arespecified in Clause 4.2.3Within the limitations of integer division, they must define each partition to accept an equal portion of therange between the minimum and maximum values of the partitioning column(s). For date-based partitions,it is permissible to partition into equally sized domains based upon an integer granularity of days, weeks,months, or years (e.g., 30 days, 4 weeks, 1 month, 1 year, etc.).

For date-based partition granularities otherTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 19than days, a partition boundary may extend beyond the minimum or maximum boundaries as established inthat table’s data characteristics as defined in Clause 4.2.3.The directives must allow the insertion of values of the partitioning column(s) outside the range covered bythe minimum and maximum values, as required by Clause 1.5.13.Multiple-level partitioning of base tables or auxiliary structures is allowed only if each level of partitioning satisfiesthe conditions stated above and each level references only one partitioning field as defined above. If implemented,the details of such partitioning must be disclosed.1.5.5Physical placement of data on durable media is not auditable.

SQL DDL that explicitly partitions data vertically isprohibited. The row must be logically presented as an atomic set of columns.Comment: This implies that vertical partitioning which does not rely upon explicit partitioning directives isallowed. Explicit partitioning directives are those that assign groups of columns of one row to files, disks or areasdifferent from those storing the other columns in that row.1.5.61.5.7Except as provided in Clause 1.5.7, logical replication of database objects (i.e., tables, rows, or columns) is notallowed.

The physical implementation of auxiliary data structures to the tables may involve data replication ofselected data from the tables provided that:All replicated data are managed by the DBMS, the operating system, or the hardware;All replications are transparent to all data manipulation operations;Data modifications are reflected in all logical copies of the replicated data by the time the updatingtransaction is committed;All copies of replicated data maintain full ACID properties (see Clause 3: ) at all times.Auxiliary data structures that constitute logical replications of data from one or more columns of a base table (e.g.,indexes, materialized views, summary tables, structures used to enforce relational integrity constraints) mustconform to the provisions of Clause 1.5.6.

The directives defining and creating these structures are subject to thefollowing limitations:Each directive may reference no more than one base table, and may not reference other auxiliary structures.Each directive may reference one and only one of the following:oA column or set of columns listed in Clause 1.4.2.2, whether or not it is defined as a primary keyconstraint;oA column or set of columns listed in Clause 1.4.2.3, whether or not it is defined as a foreign key constraint;oA column having a date datatype as defined in Clause 1.3.Each directive may contain functions or expressions on explicitly permitted columnsNo directives (e.g. DDL, session options, global configuration parameters) are permitted in TPC-H scripts whoseeffect is to cause the materialization of columns (or functions on columns) in auxiliary data structures other thanthose columns explicitly permitted by the above limitations.

Further, no directives are permitted whose effect is tocause the materialization of columns in auxiliary data structures derived from more than one table.Comment: Database implementations of auxiliary structures generated as a result of compliant directives usuallycontain embedded pointers or references to corresponding base table rows. Database implementations thattransparently employ either ‘row IDs’ or embedded base table ‘Primary Key’ values for this purpose are equallyacceptable.In particular, the generation of transparently embedded ‘Primary Key’ values required by auxiliary structures is apermitted materialization of the ‘Primary Key’ column(s). ‘Primary Key’ and ‘Foreign Key’ columns are listed inClause 1.4.1.TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 201.5.8Table names should match those provided in Clause 1.4.1.

In cases where a table name conflicts with a reservedword in a given implementation, delimited identifiers or an alternate meaningful name may be chosen.1.5.9For each table, the set of columns must include all those defined in Clause 1.4. No column can be added to any ofthe tables. However, the order of the columns is not constrained.1.5.10Column names must match those provided in Clause 1.41.5.11Each column, as described in Clause 1.4, must be logically discrete and independently accessible by the datamanager. For example, C_ADDRESS and C_PHONE cannot be implemented as two sub-parts of a single discretecolumn C_DATA.1.5.12Each column, as described in Clause 1.4, must be accessible by the data manager as a single column.