Friday, November 18, 2016

48 Testing ORACLE SQL Programming


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

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




No comments:

Post a Comment