Employees database only - https://dev.mysql.com/doc/employee/en/
1. Create a procedure that adds a user. The procedure takes the five parameters of dept_no,
birth_Date, first_name, last_name, and gender, as defined below.
The procedure consists of the steps:
- Generate a new employee number finding the highest emp_no and add one. I.e.,
new_emp_no = select max(emp_no) + 1 from employees
- Insert the new user into Table employees, where use CURDATE() for hire_date.
- Insert (new_emp_no, dept_no) into dept_emp, where use CURDATE() for from_date
and '9999-01-01' for to_date.
You can test the procedure like "call addUser('d001', '1998-01-01', 'John', 'Doe', 'M');". Then
check Tables employees and dept_emp whether the new user is added.
Evaluation (1) SQL query for the procedure and (2) Screen capture that shows that new user is added on Tables employees and dept_emp.
2. Add an attribute, "emp_num" (integer), on departments. The attribute will show the total
number of employees on the department. Then, update the field with the following SQL.
Check whether Table departments has correct total numbers of employees.
Evaluation: (1) SQL query for altering the table and (2) Screen capture that shows Table departments after updating.
3. Create a trigger, "add_emp_num", that update emp_num on department whenever a new
user is added. You can check the trigger by the query: SHOW TRIGGERS;
Evaluation: (1) SQL query for the trigger and (2) Screen capture that shows the result of "SHOW TRIGGERS"
4. Add a new user by using the procedure like "call addUser('d001', 1998-01-01', 'John',
'Smith', 'M');". Then check whether emp_num is automatically increased by the trigger.
Evaluation: Screen capture that shows Table departments before and after adding a new user.