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. 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. 1. Schema for 1NF

1.1 Underline the PK in the 1NF

1.2 Show partial dependencies (if any) and full dependencies

1. 2. Schema for 2NF
   3. 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)

 

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