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