376a. Database Design

1. 376a. Database Design Dept. of Computer Science Vassar College http://www.cs.vassar.edu/~cs376 Class 7: Domain Relational Calculus and beginning SQL Prof. Billibon Yoshimi

2. Find employees with no dependents using Domain Relation Calculus. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPENDENT(ESSN, DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP) Prof. Billibon Yoshimi

3. List names of all managers with at least one dependent. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) DEPENDENT(ESSN, DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP) Prof. Billibon Yoshimi

4. SELECT statement No relation to sigma SQL tables are not sets (they’re multi-sets). Use DISTINCT to regain set-like quality. Basically: SELECT <attribute list> FROM <list of tables> WHERE <condition list> Prof. Billibon Yoshimi

5. Get the birthdate and address of employees with the name “John B. Smith” R.A. SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=“JOHN” and MINIT=“B” and LNAME=“SMITH” Prof. Billibon Yoshimi

6. Can use SELECT to do join operation too SELECT FNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT=“RESEARCH” AND DNUMBER=DNO Print the firstname and address of all employees in the research department. Prof. Billibon Yoshimi

7. Resolving ambiguous names Relationships may have same named attributes. Use relation.attribute to disambiguate. When using multiple instances of a relation in a SELECT, use aliases.. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE as E, EMPLOYEE as S WHERE E.SUPERSSN = S.SSN Prof. Billibon Yoshimi

8. Can also create attribute aliases, EMPLOYEE AS E(FN,MI,LN,SSN,BD,ADDR,S,SAL,SSSN,DNO) Prof. Billibon Yoshimi

9. SELECT - FROM statements Unspecified WHERE is *. If multiple relations are specified in FROM then the tuples are formed from the cross product of all FROM relations. Prof. Billibon Yoshimi

10. Other modifiers to SELECT SELECT * FROM - WHERE * - Selects all attributes SELECT ALL x FROM WHERE - get all values, including duplicates SELECT DISTINCT * FROM WHERE - removes duplicates *EXPENSIVE* Prof. Billibon Yoshimi

11. UNION, EXCEPT and INTERSECT operations (SELECT *) UNION (SELECT *) Sub sets should be union compatible, same attribute tuples, same ordering. UNION gives the union of all tuples (duplicates eliminated). EXCEPT gives the set difference. INTERSECT gives the intersection of the tuples. Prof. Billibon Yoshimi

12. Comparing strings In WHERE statements use LIKE WHERE NAME LIKE “%ITH%” % - replaces arbitrary numbers of characters _ - replaces a single character “_____5_____” In MySQL, use REGEX too. ^ - match beginning of line $- match end of line [bB] - match any one char in bracket * - zero or one instances of preceding thing Match anywhere in the input, unlike LIKE Prof. Billibon Yoshimi

13. Operations on return values +,-,*,/, BETWEEN (SALARY BETWEEN X AND Y) || is string append Like this SELECT name||address, salary*1.3 You can also call functions using the select statement to do evaluations SELECT NOW( ), SIN(3.1415), 3*5 Prof. Billibon Yoshimi

14. SELECT FROM WHERE ORDER BY ORDER BY attribute ASC|DESC, attribute ASC|DESC By default it is in ascending order. Order on first attribute, then second,then third. Prof. Billibon Yoshimi

15. Nested queries SELECT FROM WHERE X IN (SELECT as argument); X is an attribute name to compare or is a tuple (attribute, attribute, etc.) In the case of ambiguity, attribute is associated with innermost nested SELECT. Prof. Billibon Yoshimi

16. Example SELECT DISTINCT PNUMBER FROM PROJECT WHERE PNUMBER IN (SELECT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’ ) OR (SELECT PNUMBER FROM WORKS_ON_EMPLOYEE WHERE ESSN=SSN AND LNAME=‘Smith’); Prof. Billibon Yoshimi

17. Other operators used like IN = ANY or = SOME > ANY < ANY, <= ANY, >= ANY, <> ANY (similar for SOME but only needs to find one case) Why isn’t there a NONE? Prof. Billibon Yoshimi

18. When nested query is correlated to outer query. For each tuple satisfying the outside query, Apply the nested query. Select all employees with the same first name as one of their progeny. E.g. SELECT * FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.FNAME=DEPEND_NAME) Prof. Billibon Yoshimi

19. All nested queries can ALWAYS be expressed as single block queries SELECT * FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.FNAME=DEPEND_NAME); We explicitly indicate the JOIN. SELECT * FROM EMPLOYEE AS E, DEPENDENT AS D WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPEND_NAME; Prof. Billibon Yoshimi

20. EXISTS Also used in WHERE clause SELECT * FROM EMPLOYEE AS E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN AND E.FNAME=DEPEND_NAME); SELECT * FROM EMPLOYEE AS E WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN); Prof. Billibon Yoshimi

21. General form of EXISTS EXISTS (Q) where Q is the query. Returns TRUE if the query returns at least one tuple. Returns FALSE if query returns no tuples. Can use multiple EXISTS in a WHERE statement e.g. WHERE EXISTS (employee who is a manager) AND EXISTS (EMPLOYEE HAS DEPENDENTS) What is same query without EXISTS (use one nested select) What is same query without any nesting? Prof. Billibon Yoshimi

22. Explicit sets Another way to write the argument for IN or similar functions Normally, WHERE X IN (SELECT …) Can also be written WHERE NAME IN (‘Bill’, ‘Bob’, ‘Jack’) Prof. Billibon Yoshimi

23. Comparison to NULL WHERE X IS NULL Compares the value of attribute X with NULL, returns TRUE when X is NULL. e.g. employees with no manager SELECT * FROM EMPLOYEE WHERE SUPERSSN IS NULL Prof. Billibon Yoshimi

24. Use AS to rename any attribute or relation Remember SELECT FROM EMPLOYEE AS E, PROJECT AS P; Can rename columns returned in relation too. SELECT FNAME AS FIRST_NAME, LNAME AS LAST_NAME Will output a relation with new attribute names. Prof. Billibon Yoshimi

25. Explicit JOINs • In the select statement there is an implict join whenever there is more than 1 relation in the FROM section. • Control the join explicitly by writing it in. SELECT * FROM EMPLOYEE JOIN DEPARTMENT AS D ON DNO=DNUMBER WHERE D.DNAME=‘RESEARCH’; By default any join is an INNER JOIN (added only if matching tuple is found in other relation.) Prof. Billibon Yoshimi

26. JOIN types • Remember different types again. • NATURAL JOIN - removes duplicate attribute (by name). Joins on all same named attributes. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) SELECT * FROM EMPLOYEE NATURAL JOIN (DEPARTMENT AS DEPT(DNAME,DNO,MSSN,MSDATE)) WHERE … Prof. Billibon Yoshimi

27. OUTER JOIN Show employee name and supervisor name for all employees. (some employees have null for superssn field!) SELECT E.FNAME| ‘ ‘ | E.LNAME, S.FNAME| ‘ ‘ | S.LNAME FROM EMPLOYEE AS E LEFT OUTER JOIN (EMPLOYEE AS S ON E.SUPERSSN = S.SSN); Why no WHERE clause? Prof. Billibon Yoshimi

28. Rest of joins. INNER JOIN (or just JOIN), LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT OUTER JOIN Note: in last 3 cases the OUTER can be left out. Prof. Billibon Yoshimi

29. Aggregate operations A few of these (many more) COUNT() - counts the number of tuples or values in the query SUM() - applied to set of multiset of attributes. MAX() - may be applied to any totally orderable domain. MIN() AVG() e.g. SELECT SUM(SALARY), MAX (SALARY), MIN(SALARY), AVG(SALARY FROM EMPLOYEE; Prof. Billibon Yoshimi

30. COUNT() SELECT COUNT (*) FROM EMPLOYEE; SELECT COUNT (SALARY) FROM EMPLOYEE; SELECT COUNT (DISTINCT SALARY) FROM EMPLOYEE; First 2 results are different from 3rd. Prof. Billibon Yoshimi

31. Use COUNT in nested SELECT Select all employees with 2 or more dependents SELECT * FROM EMPLOYEE WHERE ( SELECT COUNT (*) FROM DEPENDENT WHERE SSN=ESSN) >= 2; For each employee, if the number of dependents for that employee is > 2, include the tuple. Prof. Billibon Yoshimi

32. Use GROUP BY to group statistics SELECT DNO, COUNT(*), AVG(SALARY) FROM EMPLOYEE GROUP BY DNO; GROUP BY specifies the subgroups the statistics are applied to. What if GROUP BY is given a primary key? Prof. Billibon Yoshimi

33. What does the following do? SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME; In this case, the JOIN happens first, then the GROUP BY statistics are applied. Remember there is one WORKS_ON tuple for every project every person works on. Prof. Billibon Yoshimi

34. Use HAVING to filter GROUP BY results SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT(*)>2; Same as before except, after the group statistics are calculated, the troups are passed through the HAVING clause. Prof. Billibon Yoshimi

35. Be careful with the order though What does this do? SELECT DNAME, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO AND SALARY>4000 GROUP BY DNAME HAVING COUNT(*) > 5; Prof. Billibon Yoshimi

36. What if we really wanted Number of employees making over $40,000 in departments with greater than 5 employees…. Prof. Billibon Yoshimi

37. Result Query SELECT DNAME, COUNT(*) FROM EMPLOYEE, DEPARTMENT WHERE DNO IN ( SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT(*) >5 ) GROUP BY DNAME; Prof. Billibon Yoshimi

38. How to think about SELECT queries 1. FROM first 2. WHERE 3. GROUP BY 4. HAVING 5. ORDER BY 6. SELECT Prof. Billibon Yoshimi

39. INSERT command INSERT INTO EMPLOYEE VALUES (‘john’,’q’,’public’,’111223333’,’2000-02-02’,’address’,’m’,20000,NULL,3) Or a list of values separated by commas. Can also specify attribute mapping INSERT INTO EMPLOYEE(LNAME,FNAME,SSN) VALUES (same order). Prof. Billibon Yoshimi

40. INSERT constraints Values not specified map to NULL or default value if specified. All integrity checks are checked. - Entity integrity (no NULL primary key) - Referential integrity (Foreign key must be valid) Prof. Billibon Yoshimi

41. How to implement nested queries in MySQL. CREATE TABLE TEMPTBL (DNAME VARCHAR(15), NO_EMPL INT, TOTAL_SAL INT); INSERT INTO TEMPTBL (DEPT_NAME, NO_EMPL, TOTAL_SAL) SELECT DNAME, COUNT(*),SUM(SALARY) FROM EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER GROUP BY DNAME; Then use TEMPTBL where you’d normally use the nested SELECT. Prof. Billibon Yoshimi

42. DELETE command DELETE FROM <relation> WHERE <condition>; DELETE FROM EMPLOYEE WHERE SSN=‘11122333’; DELETE FROM EMPLOYEE; Beware of the last one, it deletes all tuples from the EMPLOYEE relations. Depending on referential triggers, references to deleted tuples can cascase or delete referers. Prof. Billibon Yoshimi

43. UPDATE command UPDATE <relation> SET <attribute=value pairs> WHERE <condition> Prof. Billibon Yoshimi

44. Views Virtual tables. Prof. Billibon Yoshimi

45. Project • Groups of 2. • Demonstrate projects instead of final exam.( Monday Dec 16. 9-11AM in class) • Deadlines: • Summary of system requirements • Entity-relation diagram • Relational database design • Application program design Prof. Billibon Yoshimi

46. Project • Document all design decisions and justifications for those decisions. • Include all instances used for the project in Appendix. • Oct 21 - Prof. Billibon Yoshimi

47. Prof. Billibon Yoshimi