A collection of information technology and consulting knowledge
Category: Technical Design
A technical specification describes the minute detail of either all or specific parts of a design, such as:
the signature of an interface, including all data types/structures required (input data types, output data types, exceptions);
detailed class models including all methods, attributes, dependencies and associations;
the specific algorithms that a component employs and how they work; and
physical data models including attributes and types of each entity/data type.
A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.
Facts About Foreign Keys
Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
Foreign keys are not enforced by all RDBMS
The concept of referential integrity is derived from foreign key theory
Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys
The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.
Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records. No Action does not mean to suppress the foreign-key constraint.
The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.
The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record
A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it. Like a Primary Key what a composite Primary Key is depends on the database. Essentially a Composite Primary Key:
Is a combination of Fields (columns) which uniquely identifies every row.
Is an index in database systems which use indexes for optimization
Is a type of table constraint
Is applied with a data definition language (DDL) alter command
And may define parent-Child relationship between tables
Microsoft doesn’t provide macro guidance for naming convention, however, sometimes it is useful to have a place to start. Also, there are times when flexibility with naming conventions are necessary. So, here is a quick set of SQL Server naming conventions, which may be helpful if you find yourself working with a customer who doesn’t have an established set of naming convention standards and you need to assemble a set fast.
Basic SQL Server Object Naming Convention Guidance
Each project will have its own schema.
Schema represents the project
First letter of each word in table/Column starts with Uppercase.
Put Underscore(_) between words of table/Column name
When a project is created, there is a default repository structure created for use in the DataStage designer client.
However, some additional organization will be required for most DataStage projects. Usually, this organization occurs in in these areas:
Addition of structure within the “Jobs” folder
Addition of a “Parameter Sets” folder
Addition of structure within the “Table Definitions” folder
Addition of a “Developer Work Area” folder
Repository Structure within the “Jobs” folder
Below is a sample of a folder structure for multiple applications that share a common Repository. Pattern includes, but does not illustrate all other delivered folders. In addition to the core folder structure, developers can create individual working, test, and in progress folders, which do not migrate, but keep work segregated.
Parameter Sets Folders
The parameter set folders or for two sets of information.
First, are the database parameters, which include data connections and the attached parameter sets.
The second, for job parameters, which may include parameter sets, for things like e-mail parameters, surrogate key file paths, etc.; which is a best practice, rather creating them as project level parameters.
The Tables Definition folder have folders added to segregate the imported meta data for source and target system and, in some case, may need folders to logically organize imported meta which may reside within the same database and/or schema, but belong to different logical layer.
Some recent research to eliminate some ETL Data Conversion issues, made me want to make an enhancement to the documentation provided by IBM, to prevent repeating the research.
Netezza data types and their equivalent InfoSphere DataStage data types
Netezza data types
InfoSphere DataStage data types (SQL types)
Numeric, decimal, double
Decimal and double are aliases of Numeric
TIME WITH TIME ZONE
The value of time with time zone will be returned without the time zone information.
You cannot load the interval data type from an external table.
Data type conversions from Netezza to DataStage
InfoSphere Information Server, InfoSphere Information Server 11.5.0, Connecting to data sources, Databases, Netezza Performance Server, Netezza connector, Designing jobs by using the Netezza connector, Defining a Netezza connector job, Data type conversions, Data type conversions from Netezza to DataStage