IT ms access

1. 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.

5. Tiny College wants to keep track of the history of all its administrative appointments,

including dates of appointment and dates of termination. (Hint:

Time-variant data is at work.) The Tiny College chancellor may want to know

how many deans worked in the College of Business between January 1, 1960, and

January 1, 2016, or who the dean of the College of Education was in 1990. Given

that information, create the complete ERD that contains all primary keys, foreign

keys, and main attributes.

3.2. Chapter 6. Problems 3

3. Using the INVOICE table structure shown in Table P6.3, do the following:

TABLE P6.3

a. Write the relational schema, draw its dependency diagram, and identify all

dependencies, including all partial and transitive dependencies. You can assume

that the table does not contain repeating groups and that an invoice number references

more than one product. (Hint: This table uses a composite primary key.)

b. Remove all partial dependencies, write the relational schema, and draw the new

dependency diagrams. Identify the normal forms for each table structure you

created.

c. Remove all transitive dependencies, write the relational schema, and draw the

new dependency diagrams. Also identify the normal forms for each table structure

you created.

d. Draw the Crow’s Foot ERD.