YOU CAN POST A PICTURE IF YOU WANT: A large corporation sends its Human Resource
ID: 3678216 • Letter: Y
Question
YOU CAN POST A PICTURE IF YOU WANT:
A large corporation sends its Human Resource personnel to outside vendors for training in specific employee-oriented skills. Because the corporation is spread out geographically, it uses vendors in different locations. Some of those vendors offer trainings in the same skill areas. The Human Resources Department of the company has the information on the trainers in the following relation with this data:
Training ID
Description
Vendor
Vndr_Location
Session Price
SUP20
Employee Supervision
WeManage
NYC, NY
$595
SUP20
Employee Supervision
People Power
Stamford, CT
$525
EMP90
Employment Law
Visionary Resources
NYC, NY
$600
EMP90
Employment Law
WeManage
NYC, NY
$695
EMP90
Employment Law
Labor League
Hoboken, NJ
$595
LAN40
Labor Negotiation
WeManage
NYC, NY
$495
1) List the functional dependencies of this relation;
2) Normalize this relation to Third Normal Form
Training ID
Description
Vendor
Vndr_Location
Session Price
SUP20
Employee Supervision
WeManage
NYC, NY
$595
SUP20
Employee Supervision
People Power
Stamford, CT
$525
EMP90
Employment Law
Visionary Resources
NYC, NY
$600
EMP90
Employment Law
WeManage
NYC, NY
$695
EMP90
Employment Law
Labor League
Hoboken, NJ
$595
LAN40
Labor Negotiation
WeManage
NYC, NY
$495
Explanation / Answer
1)Functional dependencies:
1) Training ID-> Description
2) Vendor-> Vndr_Location
3) Training ID Vendor-> Session Price
First Normal Form:
First Normal Form is defined in the definition of relations (tables) itself. This rule defines that all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.
Training ID
Description
Vendor
Vndr_Location
Session Price
SUP20
Employee Supervision
WeManage
NYC
$595
SUP20
Employee Supervision
People Power
Stamford
$525
EMP90
Employment Law
Visionary Resources
NYC
$600
EMP90
Employment Law
WeManage
NYC
$695
EMP90
Employment Law
Labor League
Hoboken
$595
LAN40
Labor Negotiation
WeManage
NYC
$495
SUP20
Employee Supervision
WeManage
NY
$595
SUP20
Employee Supervision
People Power
CT
$525
EMP90
Employment Law
Visionary Resources
NY
$600
EMP90
Employment Law
WeManage
NY
$695
EMP90
Employment Law
Labor League
NJ
$595
LAN40
Labor Negotiation
WeManage
NY
$495
Second Normal Form
Before apply the second normal form, we need to understand the following
If we follow second normal form, then every non-prime attribute should be fully functionally dependent on prime key attribute. That is, if X A holds, then there should not be any proper subset Y of X, for which Y A also holds true.
So we have three functional dependencies they are:
1) Training ID-> Description
2) Vendor-> Vndr_Location
3) Training ID Vendor-> Session Price
So divided the above schema into three parts
T1(Training ID, Description)
T2(Vendor-, Vndr_Location)
T3(Training ID,Vendor,price)
Third Normal Form
For a relation to be in Third Normal Form, it must be in Second Normal form and the following must satisfy
There is no trival dependencies in above schemas so it is in Third Normal Form
Final schema:
T1(Training ID, Description)
T2(Vendor-, Vndr_Location)
T3(Training ID,Vendor,price)
Training ID
Description
Vendor
Vndr_Location
Session Price
SUP20
Employee Supervision
WeManage
NYC
$595
SUP20
Employee Supervision
People Power
Stamford
$525
EMP90
Employment Law
Visionary Resources
NYC
$600
EMP90
Employment Law
WeManage
NYC
$695
EMP90
Employment Law
Labor League
Hoboken
$595
LAN40
Labor Negotiation
WeManage
NYC
$495
SUP20
Employee Supervision
WeManage
NY
$595
SUP20
Employee Supervision
People Power
CT
$525
EMP90
Employment Law
Visionary Resources
NY
$600
EMP90
Employment Law
WeManage
NY
$695
EMP90
Employment Law
Labor League
NJ
$595
LAN40
Labor Negotiation
WeManage
NY
$495
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.