Question

Two-Bit Drilling Company keeps information on employees and their 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 the renewal date for that certification should also be recorded in the system. For all employees, the SSN and department 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.

Create the 3NF tables and relationships in Microsoft Access. The Word file should provide your rationale for each normalized form. State your assumptions about business rules, if any. Submit the relationship diagram from Access on the Word document and the Access database file (.accdb). (100 points)

Did your 3NF tables accurately reflect the initial ERD? If not, modify the initial ERD.

Answer 1

ERD diagram

1NF:

1.All the cells should be atomic. The cells in the column Dependent memer name were non atomic ie it was multi value attribute column. So we decomposed it so that each cell has only one attribute value.

2.We ensured that all columns have alue of the same type

3.We inserted unique IDs ie the Employee IDs for rows.

4. We inserted rows to achieve cell atomicity

2NF:

1. We ensured that all non key attributes are dependent /can be derived from the key attributes.We split the table and inserted a junction table. <with employee ID as one column and certificate ID as other column>

3NF:

1. We further decomposed the table so that each column in the table can be derived only by the key column.

So after transforming into 3NF form we have :

1. Employee table with employee ID as the primary key .Other columns are Name, Date of hiring and title.

2. Certificate table which holds entries only when the employee title is =Inspector. The columns include certificate ID as the primary key, date of certification and date of renewal.

3. We have a junction table with columns as Employee ID and Certificate ID

4.We have a dependent table which contains the record of all dependents. Other columns are employee ID and SSN ID.

Relationships:

1. An employee may have 0 or more dependents.A dependent wll be mapped to one and only one employee

2.A certificate will be mapped to one and only one employee. An employee may have o or 1 certification depending on the title. Also a employee will be mapped to only one certificate when employee title=Inspector. This relationship is supported by the junction table.