Oracle Database MySQL 5.6 Developer — Question 32

You have been tasked to create a database that will store a list of all managers and the employees who report directly to them. The following is stipulated:
✑ No manage is managing more than three people.
✑ No employee can work for more than one manage.
Which of these designs represents a normalized schema that meets the project requirements?

Answer options

• A. CREATE TABLE manager manager varchar (50) DEFAULT NULL, employee2 varchar (50) DEFAULT NULL, employee varchar (50) DEFAULT NULL, UNIQUE ( manager , employee1, employee2, employee3) )
• B. CREATE TABLE managers ( "id’ int(11) NOT NULL AUTO_INCREMENT, manager varchar (50) DEFAULT NULL , PRIMARY KEY (id) ) CREATE TABLE "employees’ ( id int(11) NOT NULL AUTO _INCREMENT, manager_id int(11) DEFAULT NULL, employee varchar (25) DEFAULT NULL, PRIMARY KEY (id) )
• C. CREATE TABLE manager ( manager varchar (50) DEFAULT NULL, employee_listvarchar (150) DEFAULT NULL, )
• D. CREATE TABLE message ( id int(11) NOT NULL AUTO_INCREMENT, manager varchar(50) DEFAULT NULL, PRIMARY KEY ("id’) ) CREATE TABLE employees ( id int (11) NOT NULL AUTO _INCREMENT, employees varchar(25) DEFAULT NULL,

Correct answer: A

Explanation

Option A correctly implements a table structure that allows for a maximum of three employees to be linked to a manager while enforcing uniqueness on the manager-employee relationship. Option B lacks the proper constraints for managing the employee relationships, while Option C does not adequately allow for multiple employees under a single manager. Option D is incorrectly named and also fails to establish the necessary relationships between managers and employees.