Question

Two-Bit Drilling Company keeps information on employees and their insurance dependents. Each employee has an employee number, name, date of hire, and title. If anemployee is an inspector, then the date of certification and the renewal date for that certification should also be recorded in the system. For all employees, theSocial
Security number and dependent names should be kept. All dependents must be associated with one and only one employee. Some employees will not have dependents, whileothers will have many dependents.

Answer 1

SOLUTION:-

SOLUTION:- Crow's Foot ERD using a specialization hierarchy: EMPLOYEE PK| Emp_Num DEPENDENT PK] Dep_Num Supports Emp_LName Emp_FName Emp_HireDate Emp-Title Emp_Is_Insp Dep_SSN Dep_LName Dep_FName FK1 Emp_Num Yes, Here, A Specialization Hierarchy is appropriate since there is an recognizable type or kind of employee (Inspector), and additional attributes are recorded that are particular to just that kind or type. It is noticeable that if there is only a single sub-type, the disjoint/overlapping designation may be excluded, if there is only one sub-type then there is no other sub-type in order to overlap or be disjoint through. As well, when there is only a single sub-type, the completeness constraint is eternally partial completeness. If the completeness constraint were recognized as total completeness, that will mean that every employee must be an inspector, in which inspector will be a synonym for employee not a kind of employee. Emp_Is_Insp INSPECTOR PK,FK1 Emp_Num Insp_CertDate Insp_CertRenewDate
===========================================================================

Answer 2

The Crow Foot ER diagram of the database design as described in the question is given below

Crow Foor ER Diagram of Two Bit Drill Company

Employees Dependents EmployeeNumber Name HireDate Tite SSN Dependentld Name EmployeeNumber Insurances RenewalDate

In the above ER diagram i have added three tables based on the description in question

• Employees
• Insurances
• Dependents

All these three tables are interconnected by EmployeeNumber, and based on the points below the ER diagram have been drawn

• one dependent should connected with only one employee
• where as employee may or may not have depended or they can have multiple dependeds
• Insurance is compulsory for the employees so that one insurance for only one employee
• Where as the Employees can have one or many insurances

The Crow Foot diagramatic symbol representation is given below, using this you can co-relate the above diagram relations

Summary of Crow's Foot Notation One or Zero One and only One Zero or Many One or Many