Category: Domains Defaults

Domain object, Default object and Check constraints

Leave a comment

Why do we need to create Domains, Defaults & Check Constraints?

When you create domains, defaults and attach them to the columns in a data model, you are actually speeding up the data model creation process by using the predefined datatypes for all the similar columns. Metadata/data in the database and data structures will be consistent across the data models so that conflicts can be reduced. Check constraints enforce some rules on columns in the data model as per your instructions.

In this section, you will see how you can create domain, defaults and check constraints by using TOAD Data Modeler for the sample data shown below. To know more about the definitions and meaning of a domain, defaults and check constraints, please refer our earlier section Data Modeling Objects listed under the category Data Modeling.

Sample Data:

[ultimatetables 18 /]

Sample Data Observation:

  • You see columns FULL_NAME, CITY_NAME, STREET_NAME, and USER_NAME with the same datatype of VARCHAR2 (30). So, first create a domain with datatype as VARCHAR2 (30). Then attach it to four of these columns.
  • For column GENDER, it can take only two values. Create a check constraint.
  • For column SSN, it should contain only numerical values. So create check constraint.
  • For column RECORD_DATE, it will store system date. So create a default.
  • For column USER_NAME, it will store the name of the user who inserted or updated that record. So create a default.

Steps to be followed in Toad:

  • Go to TOAD DATA MODELER and create a table “DOMAIN_RULE_DEFAULT” as mentioned in the section Create Data Modeling Objects
  • Domain:
    • Creating Domain: Go to menu “MODEL/DOMAIN”. In the new window, click add. By default, a domain is created by TOAD. Change caption and name to “NAME_DOMAIN” and change datatype to VARCHAR2(30).
    • Attaching Domains: You have to attach domain “NAME_DOMAIN” for FULL_NAME, CITY_NAME, STREET_NAME, and USER_NAME. While creating columns, you can see a list box for attaching DOMAINS. Select the domain “NAME_DOMAIN” and attach it to those columns.
  • Default:
    • Creating Default for column RECORD_DATE: Go to menu “MODEL/DEFAULT”. In the new window, click add. By default a domain created by TOAD. Edit in General tab and Change caption and name to “TODAY_DATE_DEFAULT”. In SQL tab, type the syntax “SYSDATE”.
    • Creating Default for column USER_NAME: For column USER_NAME, create another default RECORD_BY_USER_DEFAULT and in SQL tab, type the syntax “USER”.
    • Attaching columns to Defaults: You have to attach the Default “TODAY_DATE_DEFAULT” to column “RECORD_DATE”. While creating columns, you will see a list box for attaching Defaults. Select the default “TODAY_DATE_DEFAULT” and attach it to RECORD_DATE column. Do the same procedure for the other column USER_NAME to attach it to the default “RECORD_BY_USER_DEFAULT”.
  • CHECK Constraint:
    • Creating and attaching Check Constraint: While creating the column gender, you can see a tab “check constraints”. Click add. By default a check constraint is created by TOAD. Edit in General tab and change caption and name to “GENDER_CHECK”. In sql tab, type “gender in (‘M’, ‘F’)”.
    • Similarly do it for SSN column also. In sql tab, type SSN BETWEEN ‘000000001’ AND ‘999999999’.

This is how the table looks in a Data Model.

[ultimatetables 19 /]

Note: NN means NOT NULL.

In few columns, you see NAME_DOMAIN. The domain that you attached is displayed here instead of data type.

DDL Script for this table:

 

 

 

 

CREATE TABLE “DOMAIN_RULE_DEFAULT”(
“FULL_NAME” Varchar2(30 ) NOT NULL,
“STREET_NAME” Varchar2(30 ) NOT NULL,
“CITY_NAME” Varchar2(30 ) NOT NULL,
“GENDER” Char(1 ) NOT NULL CONSTRAINT “GENDER_CHECK” CHECK (GENDER IN (‘M’, ‘F’)),
“SSN” Char(9 ) NOT NULL CONSTRAINT “SSN_CHECK” CHECK (SSN BETWEEN (‘000000001’ AND ‘999999999’)),
“RECORD_DATE” Date DEFAULT Sysdate NOT NULL,
“USER_NAME” Varchar2(30 ) DEFAULT USER NOT NULL)

 

Data Modeling Subject Area, Default, Domain, Rules & Constraints

Leave a comment

Logical and Physical Data Modeling Objects:

To become a data modeler, you need to understand the concepts of a database. Before proceeding further, please visit our topics listed under the section “Database and Data Modeling” to get a fair knowledge about the database. The following table briefs about the objects used in constructing the data model especially in domains, rules, check constraints, and subject area.

What is a Logical Data Model?

This is a business presentation of objects in a database which represents the business requirements (entire or part) of an organization. Usually object names are very descriptive and Supertypes / Subtypes, relationships between different objects are shown, which is very easy for every one to understand the business of the organization.

What is a Physical Data Model?

Physical Data Model contains most of the objects present in the database. From Developers perspective, it shows the table name, column name,  data type, null, not null, unique constraint, primary key constraints, foreign key constraints, which helps them to code.

Objects used in a Data Model

Data Model Type: Logical

Data Model Objects: Subject Area or Work Space

Explanation: 

In a data model, there is one main subject area which comprises all objects present in all subject areas and other subject areas based on their processes or business domains. Each subject area contains objects, which are relevant to that subject area and the subject area is very useful in understanding the data model and to generate reports and PRINT OUTS based on main subject areas or other subject areas. In a telecommunication data model, there may be several subject areas like Service Request, Service Order, Ticketing and Main Subject Area. In a Mortgage data model, there may be several subject areas like borrower, loan, under writing and main subject area. Usually subject areas are created on main business processes. In Telecommunication (telephone service subscription by customer), service request is a process to get the request from the customer through phone, email, fax etc. Service Order is the next process to approve the service request and provide telephone line subscription to customers. Ticketing is a process by which complaints are gathered from the customer and problems are resolved.

Data Model Type: Physical

Data Model Objects: Subject Area or Work Space

Explanation: 

It is a copy of the logical subject area but some objects like supertype and sub types objects may not be AS IS like the logical subject area.

Data Model Type: Logical

Data Model Objects: Entity

Explanation: 

It is the business presentation of a table present in a database. Example: COUNTRY

Data Model Type: Physical

Data Model Objects: Table

Explanation: 

It is comprised of rows & columns, which stores data in a database. Example: CNTRY

Data Model Type: Logical

Data Model Objects: Attribute

Explanation: 

It is the business presentation of a column present in a database. Example: Country Code, Country Name.

Data Model Type: Physical

Data Model Objects: Column

Explanation: 

It is a data item, which stores data for that particular item. Example: CNTRY_CD, CNTRY_NM.

Data Model Type: Logical

Data Model Objects: Default

Explanation: 

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Default

Explanation: 

When user input is not present, default value attached with the particular column is inserted into that column.

Step:

  • First, you have to create a default object in the data model.
  • You have to attach the default object with the column.
  • When you generate scripts from the physical data model, automatically, default will be attached to the column.

Example 1:

You may need a situation, where system date and time should be inserted when the record is inserted. With Oracle, you can attach SYSDATE to that column.

Column name: TODAY;

Datatype: DATE;

Default Syntax : DEFAULT;

Oracle Default: SYSDATE TODAY DATE DEFAULT SYSDATE

Example 2:

You many need to know about the schema name who inserted that record.

Column name: SCHEMA_NAME;

Datatype: VARCHAR2(30);

Default Syntax: DEFAULT;

Oracle Default: USER SCHEMA_NAME VARCHAR2(30) DEFAULT USER

Data Model Type: Logical

Data Model Objects: Domain

Explanation: 

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Domain

Explanation: 

When you create a data model, there are several columns in tables, which are code, identifier, indicators, descriptive columns, date columns, NOT NULL columns, Primary key columns etc. To make it consistent across the data model, we can use domains.

Steps:

  • First, you have to create a domain object in the data model.
  • You have to attach the domain object with the column.

Example:

For a DESCRIPTION column, you can create a domain which has the following (NOT NULL, Datatype as VARCHAR2 (200)).

You can attach this domain to all descriptive columns present in tables. So every descriptive column present in the table will have NOT NULL as the constraint and datatype as VARCHAR2 (200).

Data Model Type: Logical

Data Model Objects: Check Constraint Rule

Explanation: 

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Check Constraint Rule

Explanation: 

Steps:

  • First, you have to create a rule object in the data model.
  • You have to attach the rule object with the column or domain.

Check Constraint Rule can be imposed on columns like:

  • Example 1: Indicator Columns: Yes or NO
  • Example 2: Gender Columns: Male or Female
  • Example 3: Marital Status: Married or Single

Data Model Type: Logical

Data Model Objects: NULL

Explanation:

There is no name for this NULL either in logical or physical data model. NULL is an option so that it allows NULL values for that column.

Data Model Type: Physical

Data Model Objects: NULL

Explanation:

Column allows NULL VALUES (Values can be empty).

Data Model Type: Logical

Data Model Objects: Not Null Constraint

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Not Null Constraint

Explanation:

Column should always contain data.

Data Model Type: Logical

Data Model Objects: Unique Constraint

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Unique Constraint

Explanation:

Non NULL Values should be different from each other.

Data Model Type: Logical

Data Model Objects: Primary Key Constraint

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Primary Key Constraint

Explanation:

Unique Constraint + Not Null Constraint.

Data Model Type: Logical

Data Model Objects: Foreign Keys

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Foreign Keys

Explanation:

This is a constraint imposed on the child table. Whatever values are present in the child table, their corresponding values should be present in the parent table. This constraint can be imposed on one column or group of columns and NULL values are allowed in child table.

Data Model Type: Logical

Data Model Objects: Relationships

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Relationships

Explanation:

Identifying, Non-Identifying, Self Relationship for Non Identifying relationship, M:N relationship.

Data Model Type: Logical

Data Model Objects: Sequence

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Sequence

Explanation:

To generate a unique number, sequence is used.

Data Model Type: Logical

Data Model Objects: Views, Synonyms

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Views, Synonyms

Explanation:

Usually development team and DBA team does that.

Data Model Type: Logical

Data Model Objects: Procedure, Function, Packages, Triggers, Materialized Views

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Procedure, Function, Packages, Triggers, Materialized Views

Explanation:

Usually developer write these and some times data modelers implement that in the new data model.

Data Model Type: Logical

Data Model Objects: Indexes and Unique Indexes

Explanation:

Same as Physical data model. Only the name changes.

Data Model Type: Physical

Data Model Objects: Indexes and Unique Indexes

Explanation:

Index is used for fastest retrieval of data from the database. Whenever a primary key constraint is created on a table, index is also created.  When we use a column in the where clause, data modelers index it after getting guidance from the development team and DBA team. Unique Index is created when there is a need for unique values in that column.

Data Model Type: Logical

Data Model Objects: SuperType and SubType

Explanation:

Supertype is an entity type that has got relationship (parent to child relationship) with one or more subtypes and it contains the attributes that are common to its subtypes.

Subtypes are subgroups of the supertype entity and have unique attributes, they will be different from each subtypes.

Supertypes and subtypes are parent and child entities respectively and the primary keys of supertype and subtype are always identical.

For detailed explanation, visit our website in Supertype and Subtype.

Data Model Type: Physical

Data Model Objects: SuperType and SubType

Explanation:

Visual representation of supertypes and subtypes will not be identical in logical and physical data model. In logical data model, it explains about the business, but the same cannot be incorporated in physical data model.