Categories: Domains Defaults

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

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.

learndmdwbi