E4.11 Consider the following relation with sample data. STUDENT INTERNSHIP TABLE StudentID StudentName EmployerID EmployerName PositionID PositionDescription 111 Joe A Axel Alarm 1 Sales Intern 222 Mike A Axel Alarm 2 IT Help Desk Intern 333 Sue B Banex Inc 2 Sales Intern 444 Pat B Banex Inc 1 Front Desk Intern 555 Bob B Banex Inc 1 Front Desk Intern 666 Joe C Calypso Corp 1 Marketing Specialist Intern • The STUDENT INTERNSHIP Table captures the data about student internships. • Each student has a unique Student ID and a Student Name. • Each employer has a unique Employer ID and an Employer Name. • Within each employer, there are positions available. • Each position has a unique Position ID within an employer (two positions from two different employers can have the same Position ID) and a Position Description. • Each student can work for only one employer and can hold only one position within that employer. • More than one student can work in the same position at the same employer. E4.11a Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional dependencies (if any) in the Student Internship Table. E4.11b Show the result of normalizing the Student Internship Table to 2NF. E4.11c Show the result of normalizing the Student Internship Table to 3NF.
E4.11 Consider the following relation with sample data.
STUDENT INTERNSHIP TABLE
StudentID StudentName EmployerID EmployerName PositionID PositionDescription
111 Joe A Axel Alarm 1 Sales Intern
222 Mike A Axel Alarm 2 IT Help Desk Intern
333 Sue B Banex Inc 2 Sales Intern
444 Pat B Banex Inc 1 Front Desk Intern
555 Bob B Banex Inc 1 Front Desk Intern
666 Joe C Calypso Corp 1 Marketing Specialist Intern
• The STUDENT INTERNSHIP Table captures the data about student internships.
• Each student has a unique Student ID and a Student Name.
• Each employer has a unique Employer ID and an Employer Name.
• Within each employer, there are positions available.
• Each position has a unique Position ID within an employer (two positions from two different employers can have the
same Position ID) and a Position Description.
• Each student can work for only one employer and can hold only one position within that employer.
• More than one student can work in the same position at the same employer.
E4.11a Depict full key functional dependencies, partial functional dependencies (if any), and transitive functional
dependencies (if any) in the Student Internship Table.
E4.11b Show the result of normalizing the Student Internship Table to 2NF.
E4.11c Show the result of normalizing the Student Internship Table to 3NF.
Trending now
This is a popular solution!
Step by step
Solved in 5 steps
