Question

Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy if appropriate. Two-BitDrilling Company keeps information on employees and their insurance dependents. Each employee has an employeenumber, name, date of hire, and title. If an employee is an inspector, then the date of certification and certification renewaldate should also be recorded in the system. For all employees, the Social Security number and dependent names shouldbe kept. All dependents must be associated with one and only one employee. Some employees will not have dependents,while others will have many dependents.

Answer 1

The business rules, cardinality and type of relationships, entities, attributes and ERD all are described and discussed.

BUSINESS RULES

One employee may or may not have insurance dependents.

One insurance dependent is associated with only one employee.

One employee may or may not be an inspector.

All the participants, employee, inspector, insurance dependent become entities and the interactions among these participants become the relationships in the ERD.

RELATIONSHIPS

The relationship between an employee and insurance dependent is 1 to Many and the relationship between an employee and the inspector is 1 to 1.

STRONG - WEAK ENTITIES

Both the relationships of EMPLOYEE entity with INSPECTOR and INS_DEPENDENT entities are optional.

Hence, both INSPECTOR and INS_DEPENDENT are weak entities; EMPLOYEE is a strong entity.

Entities

EMPLOYEE

INSPECTOR

INS_DEPENDENT

Entities - Attributes

EMPLOYEE ( SSN, empNum, empName, HireDate, Title )

PRIMARY KEY - SSN

SSN - Social Security number

empNum - employee number

empName - employee name

HireDate - date of hiring the employee

Title - job title of the employee

INSPECTOR ( SSN, certificationDate , certificationRenewalDate )

FOREIGN KEY - SSN

SSN references SSN from EMPLOYEE table.

certificationDate - date of certification of the inspector

certificationRenewalDate - date of renewal of certification of the inspector

INS_DEPENDENT ( depNum, depName, SSN )

PRIMARY KEY - depNum

FOREIGN KEY - SSN

SSN references SSN from EMPLOYEE table.

depName - name of the insurance dependent

Entity Relationship Diagram - ERD

Entities are shown as boxes with primary key as PK and/ or foreign key, FK. The other attributes are listed vertically.

The primary key is shown in bold and is underlined.

The foreign key is shown in italics.

The relationship between an employee and insurance dependent is optional and shown with a dotted line.

Not all employees are inspectors, hence, this relationship is also optional and shown using specialization hierarchy.