Re: Resiliency To New Data Requirements

After implementing the initial solutions, the following data requirement is added: Each employee can be in 0 to many departments. And add Bob, emp# 789, works in finance and personnel departments, has a salary of 15,000.

RMDB: (schema change, transfer data, update query) Add table named employee_dept (emp#, dept). Move John and Mary's dept to new table employee_dept. INSERT employee_dept (123, finance);
INSERT employee_dept (456, safety);
Remove dept field from employee table.

Add data for Bob.

INSERT employee (789, bob, 15000);
INSERT employee_dept (789, finance);
INSERT employee_dept (789, personnel);

Update original query to find Mary via table join. SELECT employee.*
FROM employee INNER JOIN employee_dept ON employee.[emp#] = employee_dept.[emp#]
WHERE ((employee_dept.dept)="safety") AND ((employee.salary)=20000);

DBD: (no changes, just add new data)
(new '789 'emp#)
(new '15000 'salary)
(new 'bob 'person)

(create bob emp# 789)
(create bob dept finance)
(create bob dept personnel)
(create bob salary 15000)

(; Original query still appropriate, returns Mary)
(and (select employee instance *) (select * dept safety) (select *
salary 20000)) Received on Wed Aug 02 2006 - 14:39:31 CDT