Class Exercise: Many-to-Many Table Administration 1. Write create table statements for the Department and Employee relations: Department(deptNo int(2) primary key, dName char(10), location char(10), salary decimal(7,2)); Employee(empno int(4) primary key, eName char(10), job varchar(9), manager int(4), hireDate date, salary decimal(7,2), deptno int(2)); 2. Create a Many-to-Many(M:M) database from the 1:M database. The foreign key attribute EMPLOYEE.DEPTNO will not be needed. An associative entity named ASSIGNMENT will be needed to relate DEPARTMENT and EMPLOYEE. ASSIGNMENT is to have attributes for DEPTNO and EMPNO. 3. Alter ASSIGNMENT to specify that the composite of EMPNO and DEPTNO is the primary key. 4. Alter ASSIGNMENT to specify that DEPTNO is a foreign key that references the column DEPTNO in a table named DEPARTMENT, and EMPNO is a foreign key that references EMPNO in EMPLOYEE. 5. Load the DEPARTMENT, EMPLOYEE and ASSIGNMENT tables with data. Create data for ASSIGNMENT based on the relationships to be established. [load data local infile 'pathName' into table tableName fields terminated by 'delimiter';] 6. Create a join view named Management that includes DEPTNO, DNAME, EMPNO, ENAME for all matching DEPARTMENT and EMPLOYEE rows with job type 'MANAGER'. 7. Query the Management view. -------- Department data: 10,Accounting,New York,11637.50 20,Research,Dallas,14463.75 30,Sales,Chicago,12502.00 40,Operations,Boston,0.0 Employee: 7369,SMITH,CLERK,7902,1995-12-17,1064.00,20 7499,ALLEN,SALESMAN,7698,1996-02-20,2128.00,30 7521,WARD,SALESMAN,7698,1996-02-22,1662.50,30 7566,JONES,MANAGER,7839,196-04-02,4000.00,20 7654,MARTIN,SALESMAN,7698,1996-09-28,1662.50,30 7698,BLAKE,MANAGER,7839,1996-05-01,4000.00,30 7782,CLARK,MANAGER,7839,1996-06-09,4000.00,10 7788,SCOTT,ANALYST,7566,2002-04-19,3990.00,20 7839,KING,PRESIDENT,\N,17-1980-11-17,\N,10 7844,TURNER,SALESMAN,7698,1996-09-08,1995.00,30 7876,ADAMS,CLERK,7788,2002-05-23,1463.00,20 7900,JAMES,CLERK,7698,1996-12-03,1263.50,30 7902,FORD,ANALYST,7566,1996-12-03,3990.00,20 7934,MILLER,CLERK,7782,1997-01-23,1729.00,10