Author: learndmdwbi

Create a .csv file named “Naming_Standards.csv” and store the following information in that csv file. The reason for creating this file is to avoid errors and typos in our naming conventions and data modeling naming convention standards can also be followed. We have eliminated VOWELS in the actual word so that physical object names will not exceed the actual length provided by the database. Later this file will be created in .nst format (embarcadero’s naming standards format) and has to be attached to the physical data model that we will create in the next section. This file (.nst) can be attached to all data models created across the enterprise.

Note: We are not providing naming standards for logical data model.

Step1: Creating .csv file and storing it as “Naming_Standards.csv”

Open your Microsoft Excel and type the following data. Then store it in .nsv format.

DEPARTMENT

DPRTMNT

NUMBER

NMBR

NAME

NM

ADDRESS

ADDRSS

IDENTIFIER

IDNTFR

EMPLOYEE

EMPLY

MANAGER

MNGR

SALARY

SLRY

DATE

DT

OF

OF

JOINING

JNNG

PHONE

PHN

NUMBER

NMBR

INCENTIVE

INCNTV

INDICATOR

INDCTR

Step2: Creating .nst file and storing it as “Enterprise_Naming_Standards.nst”

Open existing logical data model “02_LDM_creating_entity_version2”

Click menu Tools/Naming Standards Template Editor.

A new screen appears.

On top of the screen, there are four different tabs. Name, Logical, Physical, and Mapping.

Under name tab: type the file name as enterprise_naming_standards.

Under Logical tab, you can change max length of entity, attribute, view, key, relationship. Select case as upper for entity.

Under physical tab, you can change you can change max length of entity, attribute, view, key, relationship. You have to change the length as per the target database standards. Select case as upper for table.

Now, we are going to make changes to the previously created data model. So create another file LDM_creating_entity_version2. Open the previous data model “LDM_creating_entity_version1” and click menu file/save as “LDM_creating_entity_version2”.

How to add entity and attributes in ER Studio Data Architect:

Add the following attributes to “Employee” Entity. Place the cursor in attribute section of Employee Entity, right click edit entity. Click attributes tab and add the following attributes.

“Employee Name” with data type as Varchar(50); NOT NULL;

“Incentive Indicator” with datatype as Char(10); NOT NULL;

“Phone Number” with datatype as Char(12); NULL;

You can use UP or DOWN arrow keys to align the attributes.

Create another entity Address with Address Identifier as attribute, datatype as Integer and Address identifier as primary key.

Address ID should be a sequence number.

How to create a sequence number in ERStudio Data Architect:

When you add an attribute, you can see a section “Identity Property”. Click the check box in Identity column.

Type 1 for seed and 1 for increment. Seed means the starting number of the sequence and increment means how it has to be incremented.

Create another entity Employee-Address with no attributes. We will explain how to add attributes below.

How to add non-identifying relationship in ER studio data architect?

Department entity to Employee entity:

Many employees can work in one department. One-to-many relationship. Hence we can create this relationship by Non-Identifying Relationship.

Click menu Insert/Relationship/Non-Identifying mandatory:

Click the cursor on Department Entity and then on Employee Entity.

Now you can see the non-identifying relationship between department entity and employee identity.

A new attribute Department Number is added to employee entity.

How to add identifying relationship in ER studio data architect?

Employee entity and Address entity: One employee can stay in many addresses.

One address can contain many employees.

Many to many relationship: Identifying relationship

Create address entity with address id as the primary key.

Click menu Insert/Relationship/Identifying

Click the cursor on employee entity and employee address entity. Again click on address entity and then on employee-address entity.

Now you can see the identifying relationship in employee address entity.

Two new attributes employee number, and address identifier are added as primary keys to employee-address entity. Since two keys are there, it is called as composite primary keys.

How to add self-recursive relationship in ER studio data architect?

To connect relationship between an employee and a manager: There is no attribute which references the employee number in the employee entity. So we will create a new attribute Manager Number by using self-recursive relationship. When we want to create an attribute in an entity that references the same primary key attribute, we have to create role name, because, two attributes cannot have the same name in the same table.

In this example, the parent entity and child entity are same (employee entity).

Click menu Insert/Relationship/Non-Identifying mandatory

Click the cursor on employee entity twice. You can see a new screen. Type role name as manager number.

You can see a new attribute manager number in employee entity.

If you want to know more about the relationship, click each relationship line and you can see different options.

ER/Studio Data Architect is a powerful Data Modeling tool with several applications. You can create either relational data model or dimensional data model from Embarcadero’s ER/Studio Data Architect. Based on the usage, you can create conceptual, enterprise and sub models etc. After you finish your creation of logical relational data model, you can create physical relational data model.

How you will create a logical relational data model in ER/Studio Data Architect?

Open ER Studio Data Architect.

Click File New.

From the new screen that appears click “Draw a new data model” and select “Relational” from the drop down box. A new screen appears. By default, you will create a logical data model.

Either you can use menu options or icons present in the screen and we will follow menu approach.

E.F. Codd gave some rules to design relational databases and the rules were focused on removing data redundancy which helps to overcome normal data modeling problems. The process of removing data redundancy is known as normalization.

2. What are the types of normalization?

First normal form, Second normal form, third normal forms are three types of normalization used in practice. Beyond these normal forms, Boyce-Codd fourth and fifth normal forms are also available.

3. What is De-Normalization?

De-Normalization is a process of adding redundancy to the data. This helps to quickly retrieve the information from the database.

4. What is data model Meta data?

You can take a report of the entire data model, or subject or part of the data model. The data about various objects in the data model is called as data model Metadata. .Data Modeling Tools have options to create reports by checking the various options. Either you can create logical data model Meta data of physical model Meta data.

5. What is data model repository?

Data Model and its relevant data like entity definition, attribute definition, columns, data types etc. are stored in a repository, which can be accessed by data modelers and the entire team.

6. What is forward engineering in a data model?

Forward Engineering is a process by which DDL scripts are generated from the data model. Data modeling tools have options to create DDL scripts by connecting with various databases. With these scripts, databases can be created.

7. What is reverse engineering in a data model?

Reverse Engineering is a process useful for creating the data models from database or scripts. Data modeling tools have options to connect to the database by which we can reverse engineer a database into a data model.

8. What is a subtype and super type entity?

An entity can be split into many entities (sub-entities) and grouped based on some characteristics and each sub entity will have attributes relevant to that entity. These entities are called subtype entities. The attributes which are common to these entities are moved to a super (higher) level entity, which is called as supertype entity.

A data warehouse is a collection of integrated data from one or more sources, used for data analysis and reporting. Several years of data is stored in data warehouse. Data is static and not a transactional.

2. What is a data mart?

A data mart is a subset of data warehouse. Data mart gives a clear understanding of the small portion of a data warehouse. For viewing, analyzing, reporting, and documentation, data mart will be better.

3. What is the difference between a data warehouse and data mart?

Data Warehouse comprises of all subject areas, where data mart is focused on a specific subject area.

4. What is a Dimensional Data Model?

Dimensional Data Model contains one or more dimension tables and fact tables and is used for calculating the summarized data. Dimensional Data Model is used in data warehouse and data marts.

5. What is a dimension?

Dimension table is also called as lookup or reference table. The data (foreign keys) in the fact table refers to the data (primary key) in the dimension and is used for validation and calculation purpose.

6. What is a slowly changing dimension (SCD)? What are types of SCD?

Dimensions that change over time are called slowly changing dimensions. Type1, Type2 and Type3 are three types of slowly changing dimensions.

7. What is a star schema?

Star Schema is a database schema, which contains one or more dimensions and fact table representing multidimensional data. It is called as star schema because the relationship between the fact tables and dimensions looks like a star.

8. What is OLAP data modeling?

OLAP stands for ONLINE ANALYTICAL PROCESSING. The approach by which data models are constructed for analyzing data is called as OLAP data modeling. Example: Data Warehouse and Data Marts.

9. What is ETL?

ETL acronym stands for Extraction, Transformation and Loading. ETL is a process by which data stored in various sources are extracted, transformed and loaded into the Target Database.

10. What is a Fact table?

The centralized table in a star schema is called as Fact table. Fact table contains many columns referenced to dimension tables and standalone measure/fact columns. These facts or measure columns give useful and meaningful data based on some calculation.

11. What are the types of measure columns in a fact table?

Additive, Semi Additive and Non-Additive columns are three types of measure columns.

Additive means: Measures or facts that can be added across all columns.

Semi Additive means: Measures or fact that can be added across few columns.

Non Additive means: Measures that cannot be added across all dimensions.

12. What are the steps to create a Data Warehouse?

The various steps are: Analyzing the data from different sources, Data Modeling, creating databases, designing etl process, extracting data from various sources, transforming the data, loading the data into target data warehouse database/target Data Mart databases. From database, reports are generated as per the needs.

The following data modeling questions and answers are conceptual questions that are asked during the data modeler interview.

1. What is data modeling?

A data model is a conceptual representation of business requirement (logical data model) or database objects (physical) required for a database and are very powerful in expressing and communicating the business requirements and database objects. The approach by which data models are created is called as data modeling.

A logical data model is the version of a data model that represents the business requirements (entire or part of an organization). This is the actual implementation and extension of a conceptual data model. Logical Data Models contain Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition etc. The approach by which logical data models are created is called as logical data modeling.

4. What is a physical data model and physical data modeling?

Physical data model includes all required tables, columns, relationship, database properties for the physical implementation of databases. Database performance, indexing strategy, and physical storage are important parameters of a physical model. The important or main object in a database is a table which consists or rows and columns. The approach by which physical data models are created is called as physical data modeling.

Data stored in form of rows and columns is called as table. Each column has datatype and based on the situation, integrity constraints are enforced on columns.

7. What is a column (attribute)?

Column also known as field is a vertical alignment of the data and contains related information to that column.

8. What is a row?

Row also known as tuple or record is the horizontal alignment of the data.

9. What is ER (entity relationship) diagram or ERD?

ER diagram is a visual representation of entities and the relationships between them. In a data model, entities (tables) look like square boxes or rectangular boxes, which contain attributes and these entities, are connected by lines (relationship).

When more than one column is a part of the primary key, it is called as composite primary key constraint.

12. What is a surrogate key?

In normal practice, a numerical attribute is enforced a primary key which is called as surrogate key. Surrogate key is a substitute for natural keys. Instead of having primary key or composite primary keys, the data modelers create a surrogate key; this is very useful for creating SQL queries, uniquely identify a record and good performance.

13. What is a foreign key constraint?

Parent table has primary key and a foreign key constraint is imposed on a column in the child table. The foreign key column value in the child table will always refer to primary key values in the parent table.

14. What is a composite foreign key constraint?

When group of columns are in a foreign key, it is called as composite foreign key constraint.

15. What are the important types of Relationships in a data model?

Identifying, Non-Identifying Relationship, Self-Recursive relationship are the types of relationship.

16. What is identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a part of the primary key in the child table, relationship is drawn by thick lines by connecting these two tables, which is called as identifying relationship.

17. What is non-identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a not a part of the primary key and standalone column in the child table, relationship is drawn by dotted lines by connecting these two tables, which is called as non-identifying relationship.

18. What is self-recursive relationship?

A standalone column in a table will be connected to the primary key of the same table, which is called as recursive relationship.

19. What is cardinality?

One to One, One to many, and many to many are different types of cardinalities. In a database, high cardinality means more unique values are stored in a column and vice versa.

20. What is a conceptual data model and conceptual data modeling?

Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the initial planning phase. Data Modelers create conceptual data model and forward that model to functional team for their review. The approach by which conceptual data models are created is called as conceptual data modeling.

21. What is an enterprise data model?

Enterprise data model comprises of all entities required by an enterprise. The development of a common consistent view and understanding of data elements and their relationships across the enterprise is referred to as Enterprise Data Modeling. For better understanding purpose, these data models are split up into subject areas.

22. What is relational data modeling?

The visual representation of objects in a relational database (usually a normalized) is called as relational data modeling. Table contains rows and columns.

23. What is OLTP data modeling?

OLTP acronym stands for ONLINE TRANSACTIONAL PROCESSING. The approach by which data models are constructed for transactions is called as OLTP data modeling. Example: all online transactions, bank transactions, trading transactions.

24. What is a constraint? What are the different types of constraint?

Constraint is a rule imposed on the data. The different types of constraints are primary key, unique, not null, foreign key, composite foreign key, check constraint etc.

25. What is a unique constraint?

Unique constraint is imposed on the column data to avoid duplicate values, but it will contain NULL values.

26. How many null values can be inserted in a column that has unique constraint?

Many null values can be inserted in an unique constraint column because one null value is not equal to another null value.

27. What is a check constraint?

Check constraint is used to check range of values in a column.

28. What is index?

Index is imposed on a column or set of columns for fastest retrieval of data.