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:
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?
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.