Lecture
Это продолжение увлекательной статьи про физическая модель хранилища данных.
...
rgb(0, 0, 0); font-family: "lucida grande", tahoma, verdana, arial, sans-serif; font-size: 12px;">
The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. In addition, specifying this property does not automatically generate values for new rows inserted into the table. To generate unique values in each column, use the NEWID or NEWSEQUENTIALID functions in INSERT statements, or use these functions as the column default.
The table must also contain a column of type uniqueidentifier with the ROWGUIDCOL attribute. This column must not allow NULL values and must have a single-column UNIQUE or PRIMARY KEY constraint. The GUID identifier value for the column must be supplied by the application at insert time, or by a DEFAULT constraint that uses the NEWID () function.
The ROWGUIDCOL column cannot be dropped and its related constraints cannot be modified if the table has a FILESTREAM column defined. The ROWGUIDCOL column can only be dropped after the last FILESTREAM column has been dropped.
If the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container in the file system.
In a CREATE TABLE statement, the CLUSTERED parameter can be set for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, then NONCLUSTERED is applied to the PRIMARY KEY by default.
To set different compression types for different partitions, specify the DATA_COMPRESSION parameter several times:
The arguments of the CREATE TABLE command have been described above. In this lecture we will not discuss all of the listed arguments. Table partitioning will be covered in "Metadata in Data Warehouses". For now, let us focus on the use of constraints.
In earlier sections we already encountered several types of constraints in column specifications — NOT NULL — and table constraints — PRIMARY KEY, FOREING KEY. In this section we will look in practical terms at several more types of constraints supported in relational databases. Constraints are an important tool for the designer, used to maintain (strong) database integrity. They can be used to ensure that a table's primary key column is unique and always contains a value. Constraints are also used to support referential integrity, meaning that values in a foreign key column must exist as some value in the primary key column of another table.
Constraints are a way of enforcing domain business rules at the database level and guarantee that inserted data is compatible with the data already present in the tables. In a relational database, a constraint is understood as a rule (condition) that some element of the database must satisfy. For example, the conditions that a table column's values must additionally satisfy within the data type defined for it (that is, data type plus rule) fully embody the concept of a domain in the physical data model of the database.
As we saw above, constraints can be applied at the column level ( column constraints ) or at the table level ( table constraints ). Primary key constraints are constraints acting at the table level, whereas NOT NULL constraints are column constraints. There are three main types of constraints used in a relational database — data integrity constraints, referential integrity constraints, and primary key constraints. Data integrity constraints relate to data values in certain columns and are defined in the column specification using the elements NOT NULL, UNIQUE, CHECK. Referential integrity constraints relate to relationships between tables based on the primary key/foreign key relationship. Primary key constraints relate to the data values in a table's primary key columns and must be imposed on every base table of a relational database. Table 11.3 lists the constraints used in relational databases.
| Constraint | Description | |
|---|---|---|
| CHECK | Ensures that values fall within a specified range defined by a predicate | |
| 2 | DEFAULT | Places a default value into a column. Ensures that the column always has a value |
| 3 | FOREING KEY | Ensures that values exist as values in the primary key column of another table. Provides for deleting child rows when the related parent rows are deleted |
| 4 | NOT NULL | Ensures that the column always contains a value |
| 5 | PRIMARY KEY | Ensures that the column always contains a value and that it is unique within the table |
| 6 | UNIQUE | Ensures that the value will be unique within the table |
The use of the NOT NULL and PRIMARY KEY constraints was discussed earlier in this lecture. The use of the FOREING KEY constraint will be discussed when we cover creating the fact table.
The CHECK constraint allows the contents of a column to be validated against certain conditions and a list of values. It is imposed using the CHECK clause. To add this constraint, the syntactic construct CHECK (predicate) must be defined after the column declaration in the column specification. Per the standard's requirements, the VALUE keyword is used in the predicate to refer to the column's value. But in practice, almost all dialects use the column name for this purpose.
The DEFAULT option causes the DBMS to place a default value in a column when a tuple is inserted into the table and no value is provided for that column. To specify a default value, add the DEFAULT keyword to the column specification, followed by any value that is a valid instance of the column's data type.
The UNIQUE constraint guarantees the uniqueness of a data value in a column. It is applied when you need to ensure that the values of a non-primary-key column are unique within the table. In doing so, uniqueness is checked for all values other than NULL.
Examples of using constraints will be given below when we analyze the commands for creating database objects for our tutorial example.
When designing the database objects for a data warehouse, the designer starts with the physical data model, the dimension and fact tables, and must produce as output a set of CREATE TABLE commands that will be used to create the tables and other database objects.
For all surrogate keys of the dimension tables and the fact table we use a column definition with the uniqueidentifier data type. Defining a column of this type allows the surrogate key value to be incremented automatically. For these columns the NEWSEQUENTIALID() function is used in a DEFAULT constraint to supply values for new rows. The ROWGUIDCOL property is also applied to columns of type uniqueidentifier, so that the column can be referenced using the $ROWGUID keyword, along with a primary key constraint.
The CREATE TABLE command for creating the "Customer" dimension table in the SQL dialect of the MS SQL Server DBMS family looks like this:
create table Customer (
Cust_ID uniqueidentifier
CONSTRAINT Guid_Default_1 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
FName varchar(20) not null,
LName varchar(20) not null,
Cust_Address varchar(40) null,
Company varchar(40) not null,
constraint PK_CUSTOMER primary key (Cust_ID)
)
go
No additional value constraints are needed, so we consider the script for creating the "Customer" dimension table complete.
The CREATE TABLE command for creating the "Employee" dimension table in the SQL dialect of the MS SQL Server DBMS family looks like this:
create table Employee (
Empl_ID uniqueidentifier
CONSTRAINT Guid_Default_2 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Empl_FName varchar(20) not null,
Empl_LName varchar(20) not null,
City varchar(20) not null,
Empl_Address varchar(40) null,
constraint PK_EMPLOYEE primary key (Empl_ID)
)
go
No additional constraints on the values are required, so we can consider the script for creating the "Salesperson" dimension table complete.
The CREATE TABLE command for creating the "Product" dimension table in the SQL dialect of the MS SQL Server family of DBMSs looks like this:
create table Product (
Prod_ID uniqueidentifier
CONSTRAINT Guid_Default_3 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Name varchar(80) not null,
Size varchar(20) not null,
Unit_Price numeric(8,2) not null,
constraint PK_PRODUCT primary key (Prod_ID)
)
go
The product name is a unique value, so let's apply a constraint to this column by changing the "Product name" column specification line to the following:
Name varchar(80) not null UNIQUE NONCLUSTERED,
The product price is a positive value, so it makes sense to introduce a check on the entered price value. Analysis of the domain shows that the prices of the goods sold by the company range from 15 to 1500 rubles, and a range check can be applied to this value. Let's change the line defining the "Unit price" (Unit_Price) column as shown below:
Unit_Price numeric(8,2) not null CHECK (Unit_Price >= 15 and Unit_Price <= 1500),
The CREATE TABLE command for creating the "Time" dimension table in the SQL dialect of the MS SQL Server family of DBMSs looks like this:
create table Time (
Time_ID uniqueidentifier
CONSTRAINT Guid_Default_4 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Year integer not null,
Quarter integer not null,
constraint PK_TIME primary key (Time_ID)
)
go
By decision of the company's management, data will be loaded into the data warehouse starting from 2007. Therefore, it makes sense to introduce a check on the values of the "Year" column. There are four quarters in a year. Let's introduce a check on the values of the "Quarter" column. The script lines defining the "Year" and "Quarter" columns now look as shown below.
Year integer not null CHECK (Year >= 2007),
Quarter integer not null CONSTRAINT Q_CHK CHECK (Quarter IN ('1', '2', '3', '4''),
The CREATE TABLE command for creating the "Sales" (Sale) fact table in the SQL dialect of the MS SQL Server family of DBMSs looks like this:
create table Sale (
Sale_ID uniqueidentifier
CONSTRAINT Guid_Default_5 DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Time_ID uniqueidentifier null,
Cust_ID uniqueidentifier null,
Prod_ID uniqueidentifier null,
Empl_ID uniqueidentifier null,
Amount numeric(9,2) not null,
Quantity integer not null,
constraint PK_SALE primary key (Sale_ID)
)
go
The values of the "Quantity" and "Payment amount" (Amount) columns cannot be zero. So let's impose the corresponding constraints on the values of these columns, as shown below.
Amount numeric(9,2) not null CHECK (Amount >= 15), Quantity integer not null CHECK (Quantity >= 1),
The "Time identifier" (Time_ID), "Customer identifier" (Cust_ID), "Product identifier" (Prod_ID), and "Salesperson identifier" (Empl_ID) columns are the primary key values of the dimension tables and can therefore serve as foreign keys in the fact table. Let's impose foreign key constraints on the values of these columns.
Note that constraints are specified either in the column specifications or key specifications when a table is created, or imposed after table creation using the SQL ALTER TABLE command. We already know how to add constraints to a table using the CREATE TABLE command.
Let's use the ALTER TABLE command to impose foreign key constraints on the fact table. The syntax of the ALTER TABLE command is given below. This command modifies a table definition by changing, adding, or removing columns and constraints.
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
| ADD
{ < column_definition > | < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column }
] }
< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ]
| IDENTITY [ ( seed , increment ) ]
]
[ROWGUIDCOL]
[ < column_constraint > ] [ ...n ] ]
< column_constraint > ::=
[ NULL | NOT NULL ]
[ CONSTRAINT constraint_name ]
{
| { PRIMARY KEY | UNIQUE }
| REFERENCES ref_table [ (ref_column) ]
[ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]
[ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
{ ( column [ ,...n ] ) }
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES ref_table [ (ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]
[ ON UPDATE { CASCADE | NO ACTION | SET DEFAULT |SET NULL } ]
}
Listing .We will not describe the arguments that are also present in the CREATE TABLE command. We will only describe the arguments specific to this command.
To add foreign key constraints to the "Sales" (Sale) fact table using the ALTER TABLE command, we can proceed as follows.
alter table Sale
add constraint FK_SALE_REFERENCE_TIME foreign key (Time_ID)
references Time (Time_ID)
go
alter table Sale
add constraint FK_SALE_REFERENCE_CUSTOMER foreign key (Cust_ID)
references Customer (Cust_ID)
go
alter table Sale
add constraint FK_SALE_REFERENCE_PRODUCT foreign key (Prod_ID)
references Product (Prod_ID)
go
alter table Sale
add constraint FK_SALE_REFERENCE_EMPLOYEE foreign key (Empl_ID)
references Employee (Empl_ID)
go
Now the data warehouse designer can move on to creating indexes.
When you define a PRIMARY KEY while creating a table, many relational DBMSs, including those in the MS SQL Server family, require a unique index to be created for the primary key. Indexes, like tables, are objects of the relational
An index is created using the CREATE INDEX command. This command generates a relational index or view for the specified table. An index can be created before any data appears in the table. Relational indexes for tables or views can be created in a different database if its fully qualified name is specified.
The syntax of the CREATE INDEX command is given below.
Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON
Listing .The values of the command's arguments are as follows.
The Database Engine component does not allow creating a unique index on columns that already contain duplicate values, even if the IGNORE_DUP_KEY parameter is set to ON. If an attempt is made to create such an index, the Database Engine component returns an error message. Before creating a unique index on such a column or columns, all duplicate values must be removed. Columns used in a unique index must have the NOT NULL property, since NULL values are treated as duplicates when creating an index.
продолжение следует...
Часть 1 The physical model of a data warehouse
Часть 2 Developing a script for creating the objects of the data
Часть 3 Constraints and their use in a relational database - The
Часть 4 Summary - The physical model of a data warehouse
Comments