Table of attributes for ‘Weekly Employee Assignment and Hours Rendered’ (with sample data)

Question

Table of attributes for ‘Weekly Employee Assignment and Hours Rendered’ (with sample data)

Emp ID Emp Name Agency Hourly rate Company code Company name Hours Rendered Year-Work
E1 Cruz A1 50 C1 Makati Inn 16 2019-W1
C2 Golden In 24 2019-W1
C3 Shang Inn 8 2019-W1
E2 David A1 45 C1 Makati Inn 8 2019-W1
C3 Shang Inn 32 2019-W1
E3 Ramos A2 60 C1 Makati Inn 40 2019-W1

 

Business Rules

    1. An employee can work in several companies per week depending upon where his agency assigns him.
    2. A company may get workers from 1 or more agencies depending on the contract with the agencies and the workload which needs to be filled in the company.

Create the following. Make sure to provide the name of each table.

    1. Schema for 1NF

1.1 Underline the PK in the 1NF

1.2 Show partial dependencies (if any) and full dependencies

    1. Schema for 2NF
    2. Schema for 3NF

3.1 Underline all relational keys in 3NF

3.2 Show relationships between tables using lines with arrows.

Summary

The given schema is already in 1NF since all the attributes are atomic.

Generally, year_week is considered to be atomic, sometimes it might not be atomic.

Here, I considered it as atomic, as it just gives the current week and year.

The primary key for the Employee table is EmpID.

Here, there is a partial dependency between EmpID, Agency, and CompanyCode.

To convert the given table into 2NF, the table Employee is divided into two tables namely Employee and Company.

The converted 2NF is also in 3NF. So we have to write a schema for 1NF, 2NF, 3NF.

Explanation

Schema 1NF, 2NF, 3NF:

1) Schema for 1NF:

Here all the values are atomic.

1NF (schema):

Employee (EmpID, EmpName, Agency, HourlyRate, CompanyCode, CompanyName, HoursRendered, Year-Week)

1.1) A primary key should have unique values and should not be null. Here, Emp ID is unique and may not be null. Therefore, Primary key=Emp ID

Employee (EmpID, EmpName, Agency, HourlyRate, CompanyCode, CompanyName, HoursRendered, Year-Week)

1.2) Partial Dependencies: –

A functional dependency x   →  y is a partial dependency if some attribute AEX can be removed from ‘x’ and dependency still holds.

Here, functional dependencies are: –

EmpID    →     EmpName   →   Agency,

EmpName    →    Agency,

EmpID     →      Agency        →    CompanyName,

EmpID  →  HourlyRate

Here, EmpID, Agency together determine. Company Name. But even if we remove Agency, the reaction still holds.

Therefore, it is partial dependency.

 

2) 2NF:

Employee (EmpID, EmpName, Agency, HourlyRate, CompanyCode,  HoursRendered, Year-Week)

Company (Company code, company name)

Divided into two tables after removing partial dependencies.

        • The above 2NF relational schema is 3NF too.

 

3.1) Relational keys are EmpID and Company code.

3.2)

Schema for 1NF 2NF and 3NF

 

Also, read the Vehicle management system in c++.

 

Share this post

Leave a Reply

Your email address will not be published.