ERD

All the business rules have been incorporated in the diagram.

Primary key and foreign keys have been shown.

CHANGE 1 IN BUSINESS RULE

It is said that data about marriage of only those employees have been stored who are married to other employee of the same company.

MODIFICATION

The company wishes to offer gifts to employees on their marriage anniversaries.

New Rule

The data about marriage, i.e., marriage date of all married employees is to be stored in the company database. No data about their spouse is to be saved.

CHANGES IN ENTITY

The entity MARRIED_TO_EMP becomes EMP_MARRIAGE.

The foreign key, marriedToEmpID is removed.

Only two attributes, empID and mrgDate are kept in this table as per the modified rule.

CHANGES IN RELATIONSHIP

The relationship between EMPLOYEE and EMP_MARRIAGE entities changes to ONE – ONE.

The original relationship between EMPLOYEE and MARRIED_TO_EMP was ONE – OPTIONAL ONE.

CHANGE 1 IN ERD

The modified ERD after the modification to the business rule about storage of information about employee marriages, is as follows.

CHANGE 2 IN BUSINESS RULE

It is said that only IT job class is further divided into many positions.

Each position requires a certification.

The date of certification for only these employees is to be kept in the database.

MODIFICATION

The company wants all its employees to enhance their skills and/or knowledge pertaining to their position.

Thus, the company wants all employees to undergo certification related to their position.

New Rule

Each job class has 1 or many positions.

Each position requires a certification.

Each employee should have the certification pertaining to their position.

CHANGE IN ENTITY

The entity IT_POSITION_CERTIFICATION becomes POSITION_CERTIFICATION.

No changes are made to its attributes.

CHANGE IN RELATIONSHIP

The relationship between EMPLOYEE and JOB_CLASS entities is removed.

The original relationship between EMPLOYEE and JOB_CLASS was ONE – ONE.

The relationship between POSITION_CERTIFICATION and EMPLOYEE becomes ONE – MANY.

The original relationship between POSITION_CERTIFICATION and EMPLOYEE was ONE – OPTIONAL MANY.

CHANGE 2 IN ERD

The modified ERD after the modification to the business rule about employee’s certification is as follows.