cheat sheet

Topics: Database normalization, Third normal form, Relational model Pages: 6 (1430 words) Published: March 11, 2015
RESIDES (PERSON_NAME, STREET, CITY, STATE)WORKS (PERSON_NAME, COMPANY_NAME, SALARY)ADDRESS (COMPANY_NAME, CITY, STATE, ZIP)MANAGES (PERSON_NAME, MANAGER_NAME) 3. Find all employees who live in the same city and street as their manager (3 pts) SELECT R.PERSON_NAME, M.MANAGER_NAME, R.STREET, R.CITY, R2.STREET, R2.CITY FROM RESIDES R, MANAGES M, RESIDES R2

WHERE R.PERSON_NAME = M.PERSON_NAME AND M.MANAGER_NAME =
R2.PERSON_NAME AND R.STREET = R2.STREET AND R.CITY = R2.CITY 4. Find all persons who do not work for any company (3 pts)
SELECT R.PERSON_NAME
FROM RESIDES R
EXCEPT
SELECT W.PERSON_NAME
FROM WORKS W
5. Find the number of employees for each of the managers (3 pts) SELECT M.MANAGER_NAME, COUNT(*) AS ‘# of Employees’
FROM MANAGES M
GROUP BY M.MANAGER_NAME
6. Give all employees of ‘Nutiva’ a salary raise of 10% (2 pts) UPDATE WORKS W SET SALARY = SALARY * 1.1
WHERE W.COMPANY_NAME = ‘Nutiva’
7. Layoff all employees of ‘LiveChat’ (3 pts)
DELETE FROM MANAGES
WHERE PERSON_NAME IN
(SELECT PERSON_NAME
FROM WORKS
WHERE COMPANY_NAME = 'LiveChat')
DELETE FROM WORKS
WHERE COMPANY_NAME = ‘LiveChat’
8. Give all managers of ‘Zubecon’ a 10 percent raise, unless their salary becomes greater than $120,000. In such cases, give only a 4% raise. (5 pts)
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.04
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 > 120000;
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.1
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 However, they do not satisfy the condition of minimality and hence are not used as a Key

Relational Design Anomalies Redundancy – Duplication of information unnecessarily  Update Anomalies – For a given INVENTORY_ID value some of the rows may be changed causing inconsistencies. Ambiguous which row has the correct data Deletion Anomalies – If all rows that refer to a particular INVENTORY_ID are deleted then we lose all information about

this...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • acct cheat sheet Essay
  • Accounting Cheat Sheet Essay
  • Essay about Accounting Cheat Sheet
  • Essay on The Balance Sheet
  • balance sheet Essay
  • Balance Sheet Essay
  • Balance Sheet Essay
  • Balance Sheet Essay

Become a StudyMode Member

Sign Up - It's Free