Examples and Illustrations
Scenario: Now let this pretend Employee entity type have a field with names as Employee_ID, Name, and Department. In other words, this will result in a more complex business entity type with extra data added about employee projects.
- Project_ID: Unique id that correspond to the project for which the employee is working.
- Project_Name: The project’s name, to which the employee works or has been assigned to.
- Role: The employee’s role.
- Start_Date: From the moment they started working on the project to when they finished it.
- End_Date: The date of the deadline the employee has to adhere to for completing the assignment.
Moreover, it is suitable to make the entity “Project” of type for saving information about intended projects. This entity type could have the following attributes:
This entity type could have the following attributes:
- Project_ID: The project proposed a new method for evaluating.
- Project_Name: The name of the project is “….”
- Project_Manager: The name of the project manager should be …
- Start_Date: The first date on which the project started.
- End_Date: The date of the completion of the project is the third item on the to-do list.
From this, we have the relation between the “Employee” and “Project” entities. The data might show which employees are assigned to a given project. Subsequently, employers may be able to make better use of resources.
After that, we will rewrite the business logic for the entity type “Employee“, and we will also add a new entity type “Project” to the existing entity type “Employee”. Then, we can update our database schema to reflect the new changes and make additional changes to the final product. We can do this by inserting information directly into the newly supplemented attribute fields.
Here is an example of how we could extend the entity type “Employee” in SQL:
ALTER TABLE Employee
ADD Project_ID INT NOT NULL,
ADD Project_Name VARCHAR(255) NOT NULL,
ADD Role VARCHAR(255) NOT NULL,
ADD Start_Date DATE NOT NULL,
ADD End_Date DATE NOT NULL;
We can then create the new entity type “Project” with the following SQL statement:
CREATE TABLE Project (
Project_ID INT NOT NULL AUTO_INCREMENT,
Project_Name VARCHAR(255) NOT NULL,
Project_Manager VARCHAR(255) NOT NULL,
Start_Date DATE NOT NULL,
End_Date DATE NOT NULL,
PRIMARY KEY (Project_ID)
);
Finally, we can create a relationship between the “Employee” and “Project” entity types with the following SQL statement:
ALTER TABLE Employee
ADD CONSTRAINT FK_employee_project
FOREIGN KEY (ProjectID) REFERENCES Project (Project_ID);
Now Adding new attributes in tables
INSERT INTO Employee (Employee_ID, Name, Department, Project_ID, Project_Name, Start_Date)
VALUES (1, 'John Doe', 'Sales', 1, 'New Website', 'Developer', '2023-03-08');
INSERT INTO Project (Project_ID, Project_Name, Project_Manager, Start_Date, End_Date)
VALUES ('1', 'New Website', 'Jane Doe', '2023-03-08', '2023-06-08');
Getting Data from Databases.
SELECT * FROM Employee WHERE Project_ID =1;
Output:
What is an Extension of Entity Type?
Entity Type Extensions are a key issue with a database management system (DBMS) that helps to develop the current entity types without messing with the main structure.
In this article, the definitions, examples, and the importance of the entity type extensions in the database design will be discussed presenting how they are essential to the maintenance of a non-contradictory and efficient data structure and the ability to adapt to the enterprise performance needs that are continuously changing.