Domain object, Default object and Check constraints

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 Tools – What to Learn? Part 2

Dimensional Data Model:

  • Is there any specific notation to identify a Data Warehouse/Data mart data models?

Subject Area:

  • How to create subject area and assign relevant entities to subject area?

Reports:

  • How to generate reports from data model and export to .XLS, .DOC, .XML file formats?

Naming Options:

  • Is there any method to change the entity/table, attribute/column name from
    upper case to lower case or lower case to upper case?

Import & Export:

  • How to create data models from .xls, .txt files etc.?
  • How to import and export meta data into ETL tools?

Abbreviation Document:

  • How to create/attach a standard abbreviation document(for naming tables, columns etc.)?

Print:

  • How to send data models to printer/plotter/Acrobat Reader?

Backup:

  • How to take backup of data model?

Others:

  • How to split a data model to logical and physical data model?
  • How to copy and paste objects within data model and across data models?
  • How to search an object within a data model?
  • How to change the font size and color of entities,attributes,relationship lines?
  • How to create a legend?
  • How to show a data model in different levels like entity level, attribute level, and definition level?

 

Data Modeling Tools: What to Learn?

Data modeling tools are the only way through which we can create powerful data models. Following are the various options that we have to know and learn in data modeling tools before start building data models.

Logical Data Model:

  • How to create entity and add definition, business rule?
  • How to create domains?
  • How to create an attribute and add definition, business rule, validation rules like default values and check constraint?
  • How to create supertypes, subtypes?
  • How to create primary keys, unique constraint, foreign key relationships, and recursive relationships?
  • How to create identifying and non-identifying relationship?
  • How to assign relationship cardinality?
  • How to phrase relationship connecting two tables?
  • How to assign role names?
  • How to create key groups?
  • How to create sequence no’s?

Physical Data Model:

  • How to rename a table?
  • How to rename a column,validation rules like default and check constraints?
  • How to assign NULL and NOT NULL to columns?
  • How to name foreign key constraints?
  • How to connect to databases like MS Access, Oracle, Sibase, Terradata etc?
  • How to generate sql code from data model to run against databases like MS Access, Oracle, Sibase, Terradata etc.?
  • How to create a data model from an existing database like MS Access, Oracle, Sibase, Terradata etc.?
  • How to add database related properties to tables, indexes?
  • How to check different versions of the data model?
  • How many data modelers can concurrently work on the same version of a data model?

Data Modeling Tools – What to learn? Part 2

Data Warehouse and Data Mart

A Data Warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical  data that is derived from transaction data. It separates analysis workload from transaction workload and enables a  business to consolidate data from several sources.

In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

There are three types of data warehouses: 

1. Enterprise Data Warehouse – An enterprise data warehouse provides a central database for decision support throughout the enterprise.

2. ODS (Operational Data Store) – This has a broad enterprise wide scope, but unlike the real Enterprise data warehouse, data is refreshed in near real time and used for routine business activity.

3. Data Mart – Datamart is a subset of data warehouse and it supports a particular region, business unit or business function.

Data warehouses and data marts are built on dimensional data modeling where
fact tables are connected with dimension tables. This is most useful for users to access
data since a database can be visualized as a cube of several dimensions. A data
warehouse provides an opportunity for slicing and dicing that cube along each of
its dimensions.

Data Mart:

A data mart is a subset of data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources.

Data Warehouse and Datamarts – Sample Diagram:

Data Warehouse Datamart

 

Data Modeling Interview Questions

General Questions:

  • Have you exported objects from the data model to different database? What is the use of it?
  • What is aggregated data and why do you need aggregated data?
  • What is the role of a logical data modeler and physical data modeler?
  • How do you gather requirements for creating data model?
  • What is the naming convention you follow?
  • What are the different notations that can be used?
  • What is Cardinality?
  • What is identifying and non identifying relationship?
  • Why do you need phrases in a data model?
  • Have you generated reports for data models? Why do you need reports from data model?
  • How you will recover if data modeling file is corrupted?
  • What is Meta data? What kind of Meta data do you capture in a data model?
  • Explain a difficult situation in data modeling and how did you resolve it?

Logical Data Modeling Questions:

  • What is logical data modeling?
  • What is conceptual data modeling? Why do you need that?
  • What is enterprise data modeling? Why do you need that?
  • What is relational data modeling? When you will use relational data modeling?
  • When you will go for relational data modeling?
  • What is OLTP?
  • What is OLAP data modeling?
  • What is dimensional data modeling?
  • What is a dimension?
  • What is a slowly changing dimension? What are the types of slowly changing dimension? Which one you will use it?
  • What is super type and sub type? Where you will use in logical or physical data model? If you generate the super type and sub type structure it in a database, what are the consequences?
  • What is Star Schema? When you use Star Schema?
  • What is Snow Flake? When you will use Snow Flake Schema?
  • What is the difference between Snow Flake and Star Schema?
  • Can you compare logical data model with data base? How?
  • How you will find out the difference between the logical data models created previous month and current month?
  • What is Data Ware House?
  • What is Data Mart?
  • What is the difference between data ware house and data mart?

Physical Data Modeling Questions:

  • What is physical data modeling?
  • What is the difference between logical and physical data modeling?
  • Can you compare physical data model with data base? How?
  • How version controlling is done in a data model?

 

Reverse Engineering a Data Model

What is Reverse Engineering a Data Model?

Reverse Engineering is a process or function to create a logical and physical data model by extracting information from an existing data source. You may either create a physical data model or logical data model or combination of both. Data Source may be oracle data base or MS SQL Server or IBM DB2 or .sql files etc.

Example: Oracle database may contain objects likes tables, views, indexes, sequences, triggers, rules, data type, owner, constraints like primary keys, foreign keys, comments etc. You can specify the objects that you are interested to create a model. If you are not interested in triggers, then there is no need for you to check that option.

All you have to know is how to connect to the data source and whether necessary privileges are allocated to you.

If you do have any problems, while connecting to the data source, please ask system administrator or database administrator to resolve the issues.

Reasons for Reverse Engineering:

  • Data base may be present but there may be no data models and hence no logical data model and physical data model print outs for which project team may not be sure about the objects(tables, index, constraints etc) and relationships between them in data base.
  • Data Models may be present but it may not be in sync with data base.
  • When you integrate different sources(some sources may not have data models) into one database to create a data warehouse, then you have to represent these sources as data models (original AS IS). During discussions within the project team, it will give more clarity about the original structure (AS IS) and future (data warehouse) data models.
  • You can give logical data models to the business community (SME, Business Analysts etc.) and physical data models to development community (developers and dbas)

Note:

You can modify/update the reverse engineered model as per your needs. Alignment of tables and relationship on the work place may not be proper and printing out the models will not be in a presentable format. So please align it.

Sources from which data model can be reverse engineered: Oracle, MS SQL, PostGreSql, DB2 UDB, MYSQL, MS ACCESS, Sybase, xml files, sql files etc.

The Reverse Engineering wizard for each data base modeling software may be different. Learn how to connect to the databases, how to check different database objects that you want to reverse engineer and see whether you can save these settings.

How to create a data model from a Database?

For example, if you would like to reverse engineer database object from Oracle Production Server. To connect to oracle, you will need user name, password, and connection string. Then you have to check/click all different database objects(tables, columns, indexes, views, synonyms etc) that your are interested to reverse. Try to find out whether you can save these settings with a “NAME” (PRODUCTION_ORACLE_SERVER). The reason is when you connect to the same database next time, then there is no need for you to check all those options once again and you can use the saved information with the name “PRODUCTION_ORACLE_SERVER”.

How to create a data model by reverse engineering from scripts?

In the section, we are using the DDL scripts which have been created earlier in the page DDL Scripts from Data Model. Generated.SQL is the file name that contains DDL scripts which is stored in your hard drive. Now we will explain how to reverse engineer and create data model from the above script.

Reverse Engineering – Creating a data model from DDL scripts using Toad Data Modeler:

  • Click File→Reverse Engineering.Click Next button on the bottom of the page.
  • Select Data Source: Under “Select Data Source”, you can see the options “Database RE” and “DDL Script RE”.
  • Since we have created this .sql script from “oracle 11g release 2” database, highlight “oracle 11g release 2” and click next.
  • Select Data Provider: By default, “DDL script file data provider” is chosen. Click next.
  • Connecting:Browse file from your folder and select the file “C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL”. Click next.
  • Select Data Migrator: By default, it chooses “Reverse Engineering from oracle 11g release 2 DDL script of database”. Click next.
  • What to reverse: Check the boxes against different objects like tables, sequences, triggers etc. Click next
  • Options: Check different options like check constraints, indexes, unique constraints etc. Click next
  • Alias: If you want to use the same kind of settings in future, then save your settings, click save and enter some alias name. Click next.
  • Tables: You can check the tables that are required. Click Execute.

Now you can see the created data model with all database objects, relationships, and their properties in the work-space.

 

DDL Scripts from a Data Model

How to create DDL Scripts from Data Model?

  • Open the physical data model “Data_Modeling_Relationship.txl”.
  • Click menu Model/Verify Model.
  • A new window will be opened.
  • Check the appropriate objects like entities, functions, packages, materialized views, relationship, synonyms, data types etc which are listed in the new window.
  • Click “Verify” button on the bottom of the page.
  • It shows the errors and warning in a data model. If there are no errors and warnings.
  • Click Menu/Generate DDL Script.
  • A new window will be opened. It has tabs like what to generate, detail settings, referential integrity, Select List.
  • Check the appropriate objects like directories, editions, entities, functions, java, materialized views, packages, permissions to objects, procedures, relationships, sequences, synonyms, user data types, user groups, views etc which are listed in the new window.
  • In the bottom of the window, you can see buttons like Generate, Show Code, Show Log.
    • Click the button ‘Generate’. It will save the DDL scripts in a file under a folder. You can see the location of the file in “what to generate tab”. Example: C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL
    • If you have access to the databases, then you can directly execute those scripts and create database objects in database or database schema.
  • Click Show. It will retrieve the information from that file “Generated.sql” and code is shown below.

— Create tables section —————–

— Table DEPARTMENT

CREATE TABLE “DEPARTMENT”(

“DEPARTMENT_NO” Integer NOT NULL,

“DEPARTMENT_NAME” Varchar2(30 ) NOT NULL

)

/
— Add keys for table DEPARTMENT

ALTER TABLE “DEPARTMENT” ADD CONSTRAINT “Department_PK” PRIMARY KEY (“DEPARTMENT_NO”)

/

 

— Table TITLE

CREATE TABLE “TITLE”(

“TITLE_CODE” Integer NOT NULL,

“TITLE_DESCRIPTION” Varchar2(30 ) NOT NULL

)

/

 

— Add keys for table TITLE

ALTER TABLE “TITLE” ADD CONSTRAINT “TITLE_PK” PRIMARY KEY (“TITLE_CODE”)

/

 

— Table PROJECT

CREATE TABLE “PROJECT”(

“PROJECT_CODE” Integer NOT NULL,

“SOFTWARE_USED” Varchar2(30 ) NOT NULL

)

/

 

— Add keys for table PROJECT

ALTER TABLE “PROJECT” ADD CONSTRAINT “PROJECT_PK” PRIMARY KEY (“PROJECT_CODE”)

/

— Table EMPLOYEE

CREATE TABLE “EMPLOYEE”(

“EMPLOYEE_NO” Integer NOT NULL,

“DEPARTMENT_NO” Integer NOT NULL,

“TITLE_CODE” Integer NOT NULL,

“EMPLOYEE_NAME” Varchar2(30 ) NOT NULL,

“MANAGER_NO” Integer

)

/

 

— Add keys for table EMPLOYEE

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_PK” PRIMARY KEY (“EMPLOYEE_NO”)

/

— Table EMPLOYEE_PROJECT

CREATE TABLE “EMPLOYEE_PROJECT”(

“EMPLOYEE_NO” Integer NOT NULL,

“PROJECT_CODE” Integer NOT NULL

)

/

 

— Add keys for table EMPLOYEE_PROJECT

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK” PRIMARY KEY (“PROJECT_CODE”,”EMPLOYEE_NO”)

/

 

— Create relationships section ————————————————-

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK01” FOREIGN KEY (“DEPARTMENT_NO”) REFERENCES “DEPARTMENT” (“DEPARTMENT_NO”)

/

 

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FKO2” FOREIGN KEY (“TITLE_CODE”) REFERENCES “TITLE” (“TITLE_CODE”)

/

 

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK02” FOREIGN KEY (“PROJECT_CODE”) REFERENCES “PROJECT” (“PROJECT_CODE”)

/

 

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK01” FOREIGN KEY (“EMPLOYEE_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/

 

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK03” FOREIGN KEY (“MANAGER_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/

 

Compare Data Models and create DDL scripts

During the development cycle of data model, several changes/updates may take place over time depending upon the needs and requirement of that project. As and when required, different data model versions will be created with little or more changes between them. At one point of time, one may require to know the exact differences between different versions(older version and newer version) of data models. Knowing these will help in assuring the updates in the newer version are correct and staying intact with the needs of the requirement. Also you can apply these changes to the underlying database by creating “ALTER” scripts. In that case, data modeling tools can help us to sort out the differences between data models.

With the help of a data modeling tool you can compare:

  • between two data models
  • data model with the script
  • data model with the underlying database

Significance of comparing data models:

  • To know the differences between them Or to know what changes have been taken place in the latest version.
  • Whatever differences are there, we can create scripts and the same can be implemented on the database.

In this article, we are using Toad software to compare between two different data model versions as well as to create ALTER scripts for database update.

Note: In Toad Data Modeler Software, data models are saved with file extension of .txp.

The following table represents a data model with a file name “Domain_Rule_Default_v1.txp” in the first stage of a development of a data model.

[ultimatetables 13 /]

In the second stage of a development of a data model, you are requested to add another column “COUNTRY_NAME VARCHAR2 (30) NOT NULL)”.

Follow the guidelines below to insert the requested column in the data model using Toad Software.

  • Save the existing data model “Domain_Rule_Default_v1.txp” as “Domain_Rule_Default_v2.txp”.
  • Add a column “COUNTRY_NAME VARCHAR2 (30)” and assign NOT NULL to it.
  • Save the data model.

The following table represents the data model “Domain_Rule_Default_v2.txp” created in the second stage of a development of a data model.

[ultimatetables 14 /]

For comparing the data models:

  • Open TOAD Data Modeler.
  • Click menu File/Sync & Convert/Sync & Convert Wizard.
  • Action: On the new window that is appearing, click the button “Compare Models and Generate Alter Report”. Click next.
  • Select Left Side: Click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v2.txp”. Click next.
  • Select Right Side: Similarly click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v1.txp”. Click next.
  • Settings: You can select the comparison rules (Ignore text case, Ignore Model Name, Resolve Application Variables, Ignore empty Primary Keys, etc). Click next.
  • Select Object Types: You have compare all, compare all without graphics, compare settings buttons. Click compare all and. Click next.
  • Select Items: A new window appears and you can see that COUNTRY_NAME doesn’t exist in “Domain_Rule_Default_v1.txp”.

How to generate Alter Script after comparing the data models?

  • Open TOAD Data Modeler.
  • Click menu File/Sync & Convert/Sync & Convert Wizard.
  • Action: On the new window that is appearing, click the button “Generate Alter Script”. Click next.

Finally, the script generated by TOAD as follows:


ALTER TABLE “DOMAIN_RULE_DEFAULT” ADD (“COUNTRY_NAME” Varchar2(30) NOT NULL)


 

Comparison of Relational and Dimensional Data Modeling

Relational | Dimensional Data Modeling:

Relational Data Modeling is used in OLTP systems which are transaction oriented and Dimensional Data Modeling is used in OLAP systems which are analytically based. In a data warehouse environment, staging area is designed on OLTP concepts, since data has to be normalized, cleansed and profiled before loaded into a data warehouse or data mart. In OLTP environment, lookups are stored as independent tables in detail whereas these independent tables are merged as a single dimension in an OLAP environment like data warehouse.

Relational vs Dimensional Data Modeling

[ultimatetables 12 /]

 

1 2 3 4 5 6