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