Note: Please use proper referencing (where necessary) when submitting your report. Report without
references shall not be acceptable. If you do not have software for drawing ERD, you can draw on paper
by hand and take picture and include that in your report. Do not forget to write your name and id on the
paper on which you hand draw ERD.
Question No. 1 (Marks 3)
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy
if appropriate. 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 an
employee is an inspector, then the date of certification and certification renewal date should
also be recorded in the system. For all employees, the Social 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, while others will have many dependents. Please
give reasoning for any selection that you make. Providing only ERD is not enough.
EMPLOYEE DEPENDENT
EMP_SSN (PK) DEP_NUM (PK)
EMP_NUM EMP_SSN (FK)
EMP_NAME DEP_NAME
EMP_HIREDATE
EMP_TITLE 1 M
INSPECTOR
1
In this scenario, a specialization hierarchy is appropriate because there is an EMP_SSN (PK, FK)
identifiable type or kind of employee (Inspectors), and additional attributes INSP_CERTDATE
are recorded that are specific to just that kind or type. It is worth not that if INSP_RENEWDATE
there is only a single subtype, the disjoint/overlapping designation may be
omitted – if there is only one subtype then then there is no other subtype to
overlap or be disjoint from. Also, when there is only a single subtype, the
completeness constraint is always partial completeness. If the completeness constraint were
identified as total completeness, that would mean that every employee must be an inspector, in
which inspector would be a synonym for employee not a kind of employee.
Question No. 2 (Marks 4)
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy
if appropriate. Granite Sales Company keeps information on employees and the departments in
which they work. For each department, the department name, internal mailbox number, and
Page 1 of 3
references shall not be acceptable. If you do not have software for drawing ERD, you can draw on paper
by hand and take picture and include that in your report. Do not forget to write your name and id on the
paper on which you hand draw ERD.
Question No. 1 (Marks 3)
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy
if appropriate. 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 an
employee is an inspector, then the date of certification and certification renewal date should
also be recorded in the system. For all employees, the Social 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, while others will have many dependents. Please
give reasoning for any selection that you make. Providing only ERD is not enough.
EMPLOYEE DEPENDENT
EMP_SSN (PK) DEP_NUM (PK)
EMP_NUM EMP_SSN (FK)
EMP_NAME DEP_NAME
EMP_HIREDATE
EMP_TITLE 1 M
INSPECTOR
1
In this scenario, a specialization hierarchy is appropriate because there is an EMP_SSN (PK, FK)
identifiable type or kind of employee (Inspectors), and additional attributes INSP_CERTDATE
are recorded that are specific to just that kind or type. It is worth not that if INSP_RENEWDATE
there is only a single subtype, the disjoint/overlapping designation may be
omitted – if there is only one subtype then then there is no other subtype to
overlap or be disjoint from. Also, when there is only a single subtype, the
completeness constraint is always partial completeness. If the completeness constraint were
identified as total completeness, that would mean that every employee must be an inspector, in
which inspector would be a synonym for employee not a kind of employee.
Question No. 2 (Marks 4)
Given the following business scenario, create a Crow’s Foot ERD using a specialization hierarchy
if appropriate. Granite Sales Company keeps information on employees and the departments in
which they work. For each department, the department name, internal mailbox number, and
Page 1 of 3
