1.
Design and
carry out tests to check the following:
a. The business rule that requires that employees
have a job_id
targets:
"JOB_ID"
VARCHAR2(10) CONSTRAINT
"EMP_JOB_NN" NOT NULL ENABLE,
Test No.
101
Date
11/15/2016
Test Description
Confirm NOT
NULL constraint on job_id
Input
INSERT INTO
employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)
VALUES(300,'Hemant','Kumar','HKUMAR',NULL,TO_DATE('2016-11-16','yyyy-mm-dd'),NULL,17000,null,100,90 );
Expected Output
Should not be
able to insert NULL
Result/Discrepancy
ORA-01400:
cannot insert NULL into
("HKUMAR"."EMPLOYEES"."JOB_ID")
Action
None
b. The business rule that requires that the end
date of an employment is after a start date in the job history table.
targets:
CONSTRAINT "JHIST_DATE_INTERVAL" CHECK (end_date
> start_date) ENABLE,
Test No.
102
Date
11/15/2016
Test Description
End date must
be greater than start date
Input
INSERT INTO
job_history(employee_id,start_date,end_date,job_id,department_id)
VALUES(102,TO_DATE('11-15-2016','mm-dd-yyyy'),TO_DATE('06-17-1993','mm-dd-yyyy'),'AD_ASST',90
);
Expected Output
Should not be
able to insert since start_date is greater than end_date
Result/Discrepancy
ORA-02290:
check constraint (HKUMAR.JHIST_DATE_INTERVAL) violated
Action
None
c. The business rule that states that departments
can be closed down with employees in that department (resulting in the
department_id becoming unknown).
targets:
ALTER
TABLE "EMPLOYEES" ADD
CONSTRAINT "EMP_DEPT_FK"
FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "DEPARTMENTS"
("DEPARTMENT_ID") ENABLE
Test No.
103
Date
11/15/2016
Test Description
When department
is deleted from departments table, department_id in employees table is set to
NULL for the corresponding rows.
Input
DELETE FROM
departments WHERE department_id = 10;
(Why I chose 10:
SELECT CONSTRAINT_NAME , table_name from user_constraints
where r_constraint_name = (SELECT r_constraint_name from user_constraints where
CONSTRAINT_NAME= 'EMP_DEPT_FK');
select department_id from employees WHERE department_id not in ( select nvl(department_id , 0) from
job_history);
)
Expected Output
Department with
id 10 is deleted and employee with id 200 gets department_id as null.
Result/Discrepancy
ORA-02292:
integrity constraint (HKUMAR.EMP_DEPT_FK) violated - child record found
Action
Need to update the constraint, see problem 2
d. The minimum salary of an employee is 1000.
targets:
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0)
ENABLE,
Test No.
104
Date
11/15/2016
Test Description
The minimum salary
of an employee is 1000. Should not be able to edit salary to NULL or less than
1000.
Input
UPDATE
employees
SET salary =
NULL
WHERE
employee_id = 144;
UPDATE
employees
SET salary =
999
WHERE
employee_id = 144;
Expected Output
Both of the
update statements should fail.
Result/Discrepancy
“1
row(s) updated.” In both cases.
Action
Action
Need to apply/update the constraint, see problem 2
2. If one of the above tests fails, write out the
SQL statement(s) that would be needed to correct the test. With the permission
of your teacher, implement the change and then rerun the test with the same
input and confirm that it works.
Problem
1 c Action:
ALTER TABLE employees
DROP CONSTRAINT emp_dept_fk;
ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES
departments (department_id) ON DELETE SET NULL ;
Now execute
test id 103 again:
DELETE FROM
departments WHERE department_id = 10;
SELECT
department_id FROM employees WHERE employee_id = 200;
Problem
1 d action:
ALTER TABLE employees
MODIFY salary CONSTRAINT emp_salary_nn NOT NULL;
ALTER TABLE employees
DROP CONSTRAINT emp_salary_min;
UPDATE employees
SET salary = 1000 WHERE NVL(salary,0) < 1000;
ALTER TABLE employees
ADD CONSTRAINT emp_salary_min CHECK (salary >= 1000);
Execute test 104 again:
UPDATE
employees
SET salary =
NULL
WHERE
employee_id = 144;
ORA-01407:
cannot update ("HKUMAR"."EMPLOYEES"."SALARY") to
NULL
UPDATE
employees
SET salary =
999
WHERE
employee_id = 144;
ORA-02290:
check constraint (HKUMAR.EMP_SALARY_MIN) violated
