1 / 39

TDDD37 Database technology TDDD46 Database technology Lecture 2: Relational databases and SQL

TDDD37 Database technology TDDD46 Database technology Lecture 2: Relational databases and SQL. Jose M. Peña j ose.m.pena@liu.se. Database design process. Attributes. Relation schema. Tuples. EMPLOYEE ( FNAME, M, LNAME, SSN, BDATE, ADDRESS, S, SALARY, SUPERSSN, DNO).

hayes-chase
Download Presentation

TDDD37 Database technology TDDD46 Database technology Lecture 2: Relational databases and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. TDDD37 Database technologyTDDD46 Database technologyLecture 2: Relational databases and SQL Jose M. Peña jose.m.pena@liu.se

  2. Database design process

  3. Attributes ... ... Relation schema ... Tuples EMPLOYEE ( FNAME, M, LNAME, SSN, BDATE, ADDRESS, S, SALARY, SUPERSSN, DNO) Relational model concepts EMPLOYEE Relation: Set of tuples, i.e. no duplicates are allowed. Database: Collection of relations. 3

  4. Integer 400 < x < 8000 String shorter than 30 chars Character M or F Domain yyyy-mm-dd NULL value Relational model concepts EMPLOYEE 4

  5. Relational model constraints EMPLOYEE Entity integrity constraint 5

  6. Foreign keys Relational model constraints EMPLOYEE Referential integrity constraint DEPARTMENT 6

  7. Relational model constraints • (Atomic) domain (or NULL). • Key. • Entity integrity: A PK cannot take NULL values. • Referential integrity: A FK in a relation can only refer to the PK of another relation, and the domains of the FK and PK must coincide, and the FK takes NULL value or values that exist for the PK. 7

  8. SQL relational data modelSQL relation table attribute column tuple row • Used in many DBMSs. • Declarative (what data to get, not how). • DDL (Data Definition Language) • CREATE, ALTER, DROP • Queries • SELECT • DML (Data Manipulation Language) • INSERT, DELETE, UPDATE … 8

  9. COMPANY schema • EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) • DEPT_LOCATIONS (DNUMBER, DLOCATION) • DEPARTMENT (DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) • WORKS_ON (ESSN, PNO, HOURS) • PROJECT (PNAME, PNUMBER, PLOCATION, DNUM) • DEPENDENT (ESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) 9

  10. Create tables CREATE TABLE<tablename>( <colname> <datatype> [<constraint>], …, [<constraint>], … ); • Data types: Integer, decimal, number, varchar2, etc. • Constraints: Not null, primary key, foreign key, unique, etc. 10

  11. Create tables CREATE TABLEWORKS_ON ( ESSNinteger constraintfk_works_emp referencesEMPLOYEE(SSN), PNOinteger constraintfk_works_proj referencesPROJECT(PNUMBER), HOURS decimal(3,1), constraintpk_workson primary key (ESSN, PNO) ); 11

  12. Modify tables • Change the definition of a table: Add, delete and modify columns and constraints. ALTER TABLEEMPLOYEEADDJOB VARCHAR2(12); ALTER TABLEEMPLOYEEDROPCOLUMNADDRESS CASCADE; ALTER TABLE WORKS_ON DROPCONSTRAINTfk_works_emp; • Delete a table and its definition DROP TABLEEMPLOYEE; 12

  13. Query tables SELECT<attribute-list> FROM<table-list> WHERE<condition>; • Attribute list: A1, …, Ar Attributes whose values are required. • Table list: R1, …, RkRelations to be queried • Condition: Boolean expression It identifies the tuples that should be retrieved. It may includecomparison operators(=, <>, >, >=, etc.) and/or logical operators (and, or, not). 13

  14. SSN 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555 Simple query • List the SSN for all employees. SELECTSSN SELECT FROM FROMEMPLOYEE; 14

  15. Use of * • List all information about the employees of department 5. SELECT FROM WHERE SELECTFNAME, MINIT, LNAME,SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO FROMEMPLOYEE WHEREDNO = 5; or Comparison operators {=, <>, >, >=, etc.} SELECT* FROMEMPLOYEE WHEREDNO = 5; 15

  16. LNAME BDATE ADDRESS Zelaya 1968-07-19 3321 Castle, Spring, TX Simple query • List the last name, birth date and address for all employees whose name is `Alicia J. Zelaya‘. SELECT FROMEMPLOYEE WHERE LNAME, BDATE, ADDRESS FNAME = ‘Alicia’ ANDMINIT = ‘J’ ANDLNAME = ‘Zelaya’; Logical operators {and, or, not} 16

  17. LNAME BDATE ADDRESS Zelaya 1968-07-19 3321 Castle, Spring, TX Narayan 1962-09-15 975 Fire Oak, Humble, TX Pattern matching • List the birth date and address for all employees whose last name contains the substring ‘aya’. SELECTBDATE, ADDRESS FROMEMPLOYEE WHERE LNAME LIKE ‘%aya%’; LIKE comparison operator % replaces 0 or more characters _ replaces a single character 17

  18. SALARY 30000 40000 25000 43000 38000 25000 25000 55000 Tables as sets • List all salaries. SELECTSALARY FROMEMPLOYEE; 18

  19. Tables as sets • SQL considers a table as a multi-set (bag), i.e. tuples can occur more than once in a table. This is different in a relational model. • Why? • Removing duplicates is expensive. • User may want information about duplicates. • Aggregation operators. 19

  20. SALARY 30000 40000 25000 43000 38000 25000 25000 55000 SALARY 30000 40000 25000 43000 38000 55000 Example • List all salaries. SELECTSALARY FROMEMPLOYEE; • List all salaries without duplicates. SELECT DISTINCTSALARY FROMEMPLOYEE; 20

  21. E Set operations Duplicate tuples are removed. Queries can be combined by set operations: UNION, INTERSECT, EXCEPT (MySQL only supports UNION) • Retrieve the first names of all people in the database. SELECTFNAMEFROMEMPLOYEE UNION SELECTDEPENDENT_NAMEFROMDEPENDENT; • Which department managers have dependents? Show their SSN. SELECTMGRSSNFROMDEPARTMENT INTERSECT SELECT ESSNFROMDEPENDENT; D M DE 21

  22. Ambiguous names: Aliasing • What if the same attribute name is used in different relations ? • No alias SELECTNAME, NAMEFROMEMPLOYEE, DEPARTMENTWHEREDNO=DNUMBER; • Whole name SELECTEMPLOYEE.NAME, DEPARTMENT.NAMEFROMEMPLOYEE, DEPARTMENT WHEREEMPLOYEE.DNO=DEPARTMENT.DNUMBER; • Alias SELECTE.NAME, D.NAMEFROMEMPLOYEE E, DEPARTMENT DWHEREE.DNO=D.DNUMBER; 22

  23. LNAME DNAME Smith Research Wong Research Zelaya Research Wallace Research Narayan Research English Research Jabbar Research Borg Research Smith Administration Wong Administration Zelaya Administration Wallace Administration Narayan Administration English Administration Jabbar Administration Borg Administration Smith Headquarters Wong Headquarters Zelaya Headquarters Wallace Headquarters Narayan Headquarters English Headquarters Jabbar Headquarters Borg Headquarters EMPLOYEE LNAME DNO Smith Wong Zelaya Wallace Narayan English Jabbar Borg 5 5 4 4 5 5 4 1 Join: Cartesian product • List all employees and the namesof theirdepartments. SELECTLNAME, DNAME FROMEMPLOYEE, DEPARTMENT; DEPARTMENT DNAME DNUM 5 4 1 Research Administration headquarters 23

  24. Foreign key in EMPLOYEE Primary key in DEPARTMENT LNAME DNO DNAME DNUMBER Smith 5 Research 5 Wong 5 Research 5 Zelaya 4 Research 5 Wallace 4 Research 5 Narayan 5 Research 5 English 5 Research 5 Jabbar 4 Research 5 Borg 1 Research 5 Smith 5 Administration 4 Wong 5 Administration 4 Zelaya 4 Administration 4 Wallace 4 Administration 4 Narayan 5 Administration 4 English 5 Administration 4 Jabbar 4 Administration 4 Borg 1 Administration 4 Smith 5 Headquarters 1 Wong 5 Headquarters 1 Zelaya 4 Headquarters 1 Wallace 4 Headquarters 1 Narayan 5 Headquarters 1 English 5 Headquarters 1 Jabbar 4 Headquarters 1 Borg 1 Headquarters 1 Cartesian product Join: Equijoin • List all employees and the namesoftheirdepartments. SELECTLNAME, DNAME FROMEMPLOYEE, DEPARTMENT WHERE DNO = DNUMBER; Equijoin Thetajoin {=, <>, >, >=, =<, !=} 24

  25. Employee Boss Smith Wong Wong Borg Zelaya Wallace Wallace Borg Narayan Wong English Wong Jabbar Wallace Join: Self-join • List the last name for all employees together with the last names of their bosses. SELECTE.LNAME “Employee”,S. LNAME “Boss” FROMEMPLOYEEE, EMPLOYEES WHEREE.SUPERSSN = S.SSN; 25

  26. Join: Inner join • List the last name for all employees together with the last names of their bosses. SELECTE.LNAME “Employee”, S.LNAME “Boss” FROMEMPLOYEE E, EMPLOYEE S WHEREE.SUPERSSN = S.SSN; SELECTE.LNAME “Employee”, S.LNAME “Boss” FROMEMPLOYEE E INNER JOIN EMPLOYEE S ONE.SUPERSSN = S.SSN; 26

  27. List the last name for all employees and, if available, show the last names of their bosses. SELECTE.LNAME “Employee”, S. LNAME “Boss” FROMEMPLOYEE E LEFT JOIN EMPLOYEE S ONE.SUPERSSN = S.SSN; LEFT JOIN, RIGHT JOIN, FULL JOIN Employee Boss Smith Wong Wong Borg Zelaya Wallace Wallace Borg Narayan Wong English Wong Jabbar Wallace Borg NULL Join: Outer join 27

  28. Joins revisited A B Cartesian product SELECT * FROM a, b; Equijoin, natural join, inner join SELECT * from A, B WHERE A1=B1; Thetajoin SELECT * from A, B WHERE A1>B1; 28

  29. Outer joins revisited A B Right outer join SELECT * FROM A RIGHT JOIN B on A1=B1; Full outer join (union of right+left) SELECT * FROM A FULL JOIN b on A1=B1; Left outer join SELECT * FROM A LEFT JOIN B on A1=B1; 29

  30. Subqueries • List all employees that do not have any project assignment with more than 10 hours. • SELECTLNAMEFROMEMPLOYEE, WORKS_ON WHERESSN = ESSN AND HOURS <= 10.0; SELECTLNAME FROMEMPLOYEE WHERESSNNOT IN (SELECTESSNFROMWORKS_ONWHEREHOURS > 10.0); Or SELECTLNAME FROMEMPLOYEE WHERENOT EXISTS (SELECT * FROM WORKS_ON WHERESSN = ESSN AND HOURS > 10.0); {>, >=, <, <=, <>} + {ANY, SOME, ALL} EXISTS 30

  31. Extended SELECT syntax SELECT<attribute-list and function-list> FROM<table-list> [ WHERE<condition>] [GROUP BY<grouping attribute-list>] [HAVING<group condition> ] [ ORDER BY<attribute-list>]; 31

  32. Aggregate functions • Built-in functions: AVG(), SUM(), MIN(), MAX(), COUNT() • They appear only in SELECT and HAVING clauses. • NULL values are not considered in the computations. • List the total number of employees. SELECTCOUNT(*) FROMEMPLOYEE; AVG() 32

  33. DNO COUNT(*) AVG(SALARY) 5 4 33250 4 3 31000 1 1 55000 Grouping • Used to apply an aggregate function to subgroups of tuples in a relation. • GROUP BY: Grouping attributes. • HAVING: Condition that a group has to satisfy. • List, for each department with more than two employees, the department number, the number of employees and the average salary. SELECTDNO, COUNT(*), AVG(SALARY) FROMEMPLOYEE GROUPBYDNO HAVINGCOUNT(*) > 2; 33

  34. DNAME DLOCATION Administration Stafford Headquarters Houston Research Sugarland Research Houston Research Bellaire Sort query results • Show the department names and their locations in alphabetical order. SELECT DNAME, DLOCATION FROMDEPARTMENT D, DEPT_LOCATIONS DL WHERE D.DNUMBER = DL.DNUMBER ORDER BYDNAME ASC, DLOCATION DESC; 34

  35. ‘SUPERSSN = NULL’ and ‘SUPERSSN <> NULL’ will not return any matching tuples, because NULL is incomparable to any value, including another NULL. Null values • List all employees that do not have a boss. SELECTFNAME, LNAME FROMEMPLOYEE WHERESUPERSSN IS NULL; 35

  36. Insert data INSERT INTO<table>(<attr>,…)VALUES(<val>, …) ; INSERT INTO<table>(<attr>, …)<subquery>; • Store information abouthowmanyhours an employeeworks for the project ’1' into WORKS_ON. INSERT INTO WORKS_ON VALUES (123456789, 1, 32.5); Integrity constraint! Referential integrity constraint! 36

  37. Update data UPDATE<table>SET<attr>=<val>,…WHERE<condition>; UPDATE<table>SET(<attr>,….)=(<subquery>)WHERE<condition>; • Give all employees in the ‘Research’ department a 10% raise in salary. UPDATE EMPLOYEE SETSALARY = SALARY*1.1 WHEREDNO IN (SELECTDNUMBER FROMDEPARTMENT WHEREDNAME = ‘Research’); Integrity constraint! Referential integrity constraint! 37

  38. Foreign key DEPARTMENT DNAME DNUMBER MGRSSN Research 5 333445555 Administration 4 987654321 Headquarters 1 888665555 Delete data DELETE FROM<table>WHERE<condition> ; • Delete the employees having the last name ‘Borg’ from the EMPLOYEE table. DELETE FROMEMPLOYEE WHERELNAME = ‘Borg’; EMPLOYEE ON DELETE SET NUL / DEFAULT / CASCADE ? 38 Referential integrity constraint!

  39. Views • A virtual table derived from another (possibly virtual) tables, i.e. always up-to-date. • CREATE VIEWdept_viewAS SELECTDNO, COUNT(*), AVG(SALARY) FROMEMPLOYEE GROUPBYDNO; • Why? • Simplify query commands. • Provide data security. • Enhance programming productivity. • Update problems. 39

More Related