For a assignment of MySQL/SQL, I need to create 2 different tables to show the difference between strong entities and weak entities.
Could someone show me an example of how I would do this?
I understand that a strong entity can exists without another entity, while the same is not true for a weak entity. So, for example, would a strong entity be as follows:
Employee(EmpNo, Name, EmpId)
But I am unsure how to create a table showing the differences.
Employee table with the following columns :
EmployeeID , EmpName, EmpDept,...
Managers table would be like :
Now , each Manager is a Employee , thus if at all there is a Manager in the
Manager table , there would be the same entry in
The "is a" relationship is maintained :
Each manager is a Employee but each Employee is not a Manager
The query would be something like :
CREATE TABLE Employee ( EmployeeID int NOT NULL, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (EmployeeID) ) CREATE TABLE Managers ( ManagerID int NOT NULL, EmployeeID int NOT NULL, .. ... FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) )
As you know Weak Entity is table which does not have a primary key but the primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity setâ€™s discriminator.
The relationship between weak entity and strong entity set is called as Identifying Relationship. In example mentioned in above image, loan-payment is the identifying relationship for payment entity. A weak entity set is represented by doubly outlined box and corresponding identifying relation by a doubly outlined diamond as shown in figure. Here double lines indicate total participation of weak entity in strong entity set it means that every payment must be related via loan-payment to some account. The arrow from loan-payment to loan indicates that each payment is for a single loan. The discriminator of a weak entity set is underlined with dashed lines rather than solid line.
Let us consider another scenario, where we want to store the information of employees and their dependents. The every employee may have zero to n number of dependents. Every dependent has an id number and name.
Now let us consider the following data base:
There are three employees having E# as 1, 2, and 3 respectively.
Employee having E# 1, has two dependents as 1, Rahat and 2, Chahat.
Employee having E# 2, has no dependents.
Employee having E# 3, has three dependents as 1, Raju; 2, Ruhi; 3 Raja.
Now, in case of Dependent entity id cannot act as primary key because it is not unique.
Thus, Dependent is a weak entity set having id as a discriminator. It has a total participation with the relationship "has" because no dependent can exist without the employees (the company is concerned with employees).
There are two tables need to created above e-r diagram. These are Employee having E# as single column which acts as primary key. The other table will be of Dependent having E#, id and name columns where primary key is the combination of (E# and id).