Category: Data Modeling Relationships

Identifying Relationship

Leave a comment

How to create Identifying Relationship with Cardinality of many to many relationship:

Identifying relationship:

If a foreign key column/multiple foreign key columns referencing some table, becomes a part of the primary key, and then it is an identifying relationship.

Employee and PROJECTS Table association:

Since the relationship is many to many with Employee_No (Employee Table) and Project_Code (Project table), we go with Identifying Relationship. In Identifying relationship, primary key migrates from the parent table to child table and it is a part of the primary key in Child  table. Foreign Key relationship is a thicker line.

  • Create table EMPLOYEE_PROJECT with no column.
  • Click identifying relationship in the tool bar.
  • Click Employee table first and then EMPLOYEE_PROJECT table. The line is not dotted and thicker. Employee_No column is created in EMPLOYEE_PROJECT table.
  • Similarly click identifying relationship in the tool bar. Click Project table first and then EMPLOYEE_PROJECT table. The line is not dotted and thicker. Project_Code column is created as primary key in EMPLOYEE_PROJECT table.

The example above talks about many to many relationships and it involves two tables Employee and Projects to form a third table Employee_Projects.

In some scenarios, if a primary key of the table is not able to identify uniqueness of a record in a table, then they add one more column from another table as a primary key with identifying relationship.

Save the data model with a meaningful name and save it either in your local directory or in a shared directory.

This data model is stored as “Data_Modeling_Relationship.txl”. On the tool bar, you can see the save menu.

Finally an image of the completed data model “Data_Modeling_Relationship” looks like this.

Employee_Projects_Datamodel

 

Non – Identifying Relationship

Leave a comment

What is Non-Identifying relationship?

If a column (foreign key) / multiple columns (foreign key columns) referencing some table becomes a column in a table as a NON-PRIMARY KEY, and then it is non-identifying relationship.

How to create non-identifying relationship with cardinality of 1 to many relationship in Employee Table?

Example 1: Employee Table with Department Table:

Since the relationship is one to many with Department_No (Department Table) and Department_No (Employee table), we go with Non-Identifying Relationship. In Non-Identifying relationship, primary key migrates from the Department table to Employee table and it is not a part of the primary key in Employee table. Here Department_No becomes the foreign key column and generally Foreign Key relationship is represented by a dotted line.

  • Click Non-Identifying relationship in the toolbar.
  • Click first on Department table; you can see a dotted line and then click on Employee table. Now you can see a relationship(dotted line) created between the Department Table and Employee Table.
  • Double Click the relationship. Change the relationship name as required (EMPLOYEE_FK01).
  • Under section CARDINALITY, check Mandatory Parent and Mandatory Child, since NULL values are not allowed in both the primary key and foreign key columns. Now the cardinality is 1..1 to 1..n.
  • Cardinality describes the relationship between a parent table and a child table. When you edit properties of the relationship line, you will be requested check(enable) the check boxes for mandatory parent column, and mandatory child. We have explained various permutations and combinations below.

[ultimatetables 26 /]

  • If you want to change the default foreign key index name, then change the name (EMPLOYEE_IDX01) in “Index to Foreign Key” tab. You have to double click the relationship line and there is a tab named “Index to Foreign Key”.
  • Click apply and ok. You can see how tables are connected and a new column Department_No is included in the Employee table.

Example 2: Employee Table with Title Table:

Repeat the above same procedure and you can see how Title table and Employee table are connected. You can see a new column Title_Code is included in the Employee Table.

How to create SELF REFERENTIAL OR RECURSIVE RELATIONSHIP in Employee Table:

When a Foreign key references the parent key(Primary key) of the same table, then it is called a SELF REFERENTIAL OR RECURSIVE RELATIONSHIP. The column Manager_No in the Employee table is a Foreign key which takes its values from the column Employee_No of the same Employee table.

This relationship is created by non-identifying relationship with cardinality 1 to many relationship in Employee Table:

  • Click Non-Identifying relationship in the tool bar.
  • Click the employee table twice. You can see the relationship created and a new column “FK_EMPLOYEE_NAME” appearing within the table. Go to the properties of that column and change the name to “MANAGER_NO”. So this column ‘Manager_No” will reference the values from its primary key column ‘Employee_No”.

 

Create Data Modeling Objects in Toad

Leave a comment

How to create Data Modeling Objects in Toad?

Here in this section, we will create a physical data model instead of starting from logical data model, since we can cover most of the features of a data modeling tool in the physical data model. Please refer our sections listed under Data Modeling Topic Logical Data Modeling and Data Modeling Tool – Erwin for creating a logical data model.

Table Structures:

[ultimatetables 20 /]

Working with Toad:

  • Open Toad Data Modeler Software
  • Click Menu File → New → Model
  • A new window will appear which asks you to select the database on which you will be generating the physical model
  • Since we are doing it in Oracle, select Oracle 11g release 2. Click OK.
  • A new screen appears. On the left hands side top, you can see the database name (Oracle 11g release 2). Below that you can see Physical Model Explorer, which contain different objects. Physical Model looks similar to a windows explorer.
  • On the middle and right hand side, you can see work-space (work area), where we will be creating objects.

On the tool bar, you can see a list box which displays Captions (Logical Data Model, Names (Physical Data Model) and Full Names. Select Names.

How to create table ‘DEPARTMENT’?

  • Click entity icon displayed on the tool bar and drop it on the work area. A new entity is created. By default some name is provided by the tool.
  • Highlight the entity, right click and click edit from the sub menu.
  • A new window, which contains different tabs, is opened.
  • Under General Tab, change the name and captions to “DEPARTMENT”.
  • Click Apply and OKAY. Now in the work area, you can see the DEPARTMENT table.

NOTE: Give proper names to TABLES, COLUMNS, PRIMARY KEYS, FOREIGN KEYS, INDEXES etc. and all other database objects as per the standard maintained by the data modeling team and database team.

How to create columns, primary keys, data type to table?

1. DEPARTMENT Table:

[ultimatetables 21 /]

  • Highlight table DEPARTMENT, right click edit from the sub menu.
  • Click Tab ‘Attributes’. Click Add. By default, an attribute is created. Click edit and from the new window
    • Change name and caption to ‘Department_No’.
    • Change the data type to Integer.
    • Department_No is the primary key of the table. So double click the space under the key column and you can see the key symbol. Click apply and OK. Go to the keys tab and change the primary key name to “Department_PK’.
  • On the work area, you can see table DEPARTMENT with a column “Department_No” as the primary key.
  • Similarly create column ‘Department_Name” with data type as VARCHAR2 (30). It should not be the primary key and it should be a column in the table. When you create, you can see a check box under NOT NULL. Check that check box so that this column will be NOT NULL. Assign data type as VARCHAR2 (30).

Similarly create tables TITLE and PROJECT and add columns to those tables.

2. TITLE Table:

[ultimatetables 22 /]

3. PROJECT Table:

[ultimatetables 23 /]

Similarly create table EMPLOYEE with Employee_No as the primary key and Employee_Name as the column. Later we will add other columns.

4. EMPLOYEE Table:

[ultimatetables 24 /]

 

Identifying and Non-Identifying Relationships

Leave a comment

Analysis of Data and creating Tables & Relationships:

Usually in a data model, Tables (Entities) looks like rectangular or square type boxes, which contains columns(attributes) and these tables are connected by lines (relationships). Identifying relationship and Non-Identifying relationship are two types of relationships. Based on cardinality, relationship are broadly classified into one to one relationship, one to many relationship and many to many relationship. Occasionally, there may arise a situation where you need to create a foreign key column by connecting a relationship to primary key column of the same table, which is called as self-referential or recursive relationship.

With the following example, we will explain you about how you can learn and practice different types of relationships with Quest’s TOAD Software tool.

Primary Key:

If the attribute (column) has to be unique and should contain NON-NULL values, data modelers create primary key constraint on that particular attribute and it is called the primary key attribute of that entity.

Foreign Key:

Attributes within same entity or attributes from different entities will be connected by a connector (foreign key) to the primary key attribute.

Composite Primary key:

If more than one column is present as a primary key, then it is called as primary key.

Let us consider a software company, which is involved in implementing projects. Assume that the client has sent the following information to the software company and software company has to create logical and physical data models and implement that in a database.

Sample Data:

Employee_Projects_Sample_Data

Data Modeler analyzes data, talks to business analysts or smart management experts in the client and understands the requirements.

Following section describes business requirements.

Business Requirements:
  • One employee has only one manager and CEO doesn’t have any manager.
  • Manager Numbers are also Employee Numbers.
  • One employee can work in only one department and one or more employees can work in the same department.
  • One or more employees can have the same title.
  • One employee can work in one or more projects and one project can have one or more employees.

Following sections, how business requirements are transformed to a data model.

Analysis:

How many tables should be present in the data model?
  1. We have to create EMPLOYEE table and Employee_No should be the primary key of the table and later other stand alone columns and foreign key columns have to be added based on the relationship.
  2. Employee_No has one to many relationship with Department_No because one or more employees can work in the same department. So DEPARTMENT table should be created and Dept_No should be the primary key of the DEPARTMENT table. Dept_No has to be connected to EMPLOYEE table and Dept_No in EMPLOYEE table is a foreign key to DEPARTMENT table.
  3. Employee_No has one to many relationship with Title_Code because one or more employees can have the same title. So TITLE table should be created as a separate table and Title_Code should be the primary key of the TITLE table. Title_Code has to be connected to EMPLOYEE table and Title_Code in EMPLOYEE table is a foreign key to TITLE table.
  4. One or more employees can work in one or more projects. So a separate table PROJECT should be created first and Project_Code should be the primary key of the table. Since the relationship between EMPLOYEE and PROJECT table depicts a many-to-many relationship and this relationship cannot be resolved using these two tables alone. So you need to create one more table EMPLOYEE_PROJECT to resolve this.
  5. Employee_No has many to many relationship with Project_Code because one or more employees can work in one or more projects and one project can have one or more employees. So a separate table EMPLOYEE_PROJECT should be created which shows many to many relationship with employees and project. It should be connected to Employee table and Project Table. Here Employee_No and Project_Code are the composite primary key of this table.
  6. Since Employee_No and Manager_No are same, we should create a self referential or recursive relationship within the same EMPLOYEE table. So a separate foreign key column should be created by connecting with Employee_no and should be named as Manager_No.

Next section, explains how to create entities, attributes, primary keys, foreign keys, and how to implement these in a database by using TOAD SOFTWARE DATA MODELING TOOL.

 

Supertype and SubType

Leave a comment

SuperType and SubType in Data Modeling:

At times, few entities in a data model may share some common properties (attributes) within themselves apart from having one or more distinct attributes. Based on the attributes, these entities are categorized as Supertype and Subtype entities.

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

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

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

E.g. People, Bank Account, Insurance, Asset, Liability, Credit Card.

When designing a data model for PEOPLE, you can have a supertype entity of PEOPLE and its subtype entities can be vendor, customer, and employee. People entity will have attributes like Name, Address, and Telephone number, which are common to its subtypes and you can design entities employee, vendor, and consumer with their own unique attributes. Based on this scenario, employee entity can be further classified under different subtype entities like HR employee, IT employee etc. Here employee will be the superset for the entities HR Employee and IT employee, but again it is a subtype for the PEOPLE entity.

A person can open a savings account or a certificate deposit (fixed deposit) in a bank. These accounts have attributes like account number, account opening date, account expiry date, principal amount, maturity amount, account balance, interest rate, checks issued, pre-cancellation fee etc. While designing a data model, you can create supertype parent entity as “Account” and subtype entities as Savings Account and Certificate Deposit. Account entity will store attributes like account number, interest rate that are common to savings account and certificate deposit entity. Savings account entity will have attributes like account balance and checks issued. While fixed deposit entity will have attributes like account opening, account expiry date, principal amount, maturity amount, pre-cancellation fee etc. When you design a logical data model in this manner, it provides more meaning to the business and the attributes are not cluttered in one table.

Supertype & Subtype Example:

One good example for explaining this SuperType & SubType is describing the Tax Terms related to Employees. Here Employee is the SuperType or the Parent Entity whereas the two Child Entities “FULL TIME EMPLOYEE W2” & “HOURLY EMPLOYEE 1099” are the SubTypes.

More Examples:

Insurance entity can act as supertype and entities like health insurance, life insurance, auto insurance, liability insurance, malpractice insurance etc., can be subtype entities.

Asset entity can be the supertype and entities like bank, real estate, auto, retirement fund, stocks, and life insurance etc., can be subtype entities.

Liability entity may be the supertype and entities like real estate, auto loan, alimony, other debts etc., may be subtype entities.

Credit card entity may be the supertype and entities like balance transfer cards, cash back credit cards, business credit card, student credit card, secured credit card etc., may be the subtype entities.

 

Reach US!!!

 

  • We provide online training in advanced OLTP Data Modeling and DIMENSIONAL Data Modeling.
  • We also teach the data structures with Data Analytics Software “R”.
  • We provide online Data Modeling Project Support when you get struck with projects that you are involved.
  • We can analyse your Business Requirements, understand and suggest solutions to create OLTP Data Models and Dimensional Data models.
  • We provide Data Modeling Interview Preparation Sessions with a lot of Data Modeling Interview Questions/Answers, which will help you to clear any interview.

 

If you are interested, please reach us at Training@LearnDataModeling.com or 91-9080157239