Re: Resiliency To New Data Requirements
Date: 2 Aug 2006 12:39:31 -0700
Message-ID: <1154547571.725368.261540_at_i3g2000cwc.googlegroups.com>
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 - 21:39:31 CEST