CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. Last time • Relational Model • Relation • Relation implementation • Populating a table • Primary Keys • Foreign Keys

3. Enforcing Integrity Constraints

4. Enforcing Referential Integrity • Can only insert or update a tuple if the value of every foreign key in the tuple appears among the values of the primary key that it references Insert (40563, 1020, ‘Fall’, `2016’): Reject such an insertion

5. Can only delete or update a tuple if the value of its primary key does not appear among the values of any of the foreign keys that reference it Delete from Student where SID = 90421 Reject such a deletion

6. We can specify actions on a parent if referential integrity of a foreign key is violated SET NULL SET DEFAULT CASCADE • Specified as the following on the childON DELETE/UPDATE SET NULL/CASCADE/DEFAULT

7. Forcing Deletions • CASCADE Constraints • Remove referencing tuples before referenced tuples create table enrolled ( StudentID number(5), CourseID number(4), Quarter varchar(6), Year number(4), primary key (StudentID, CourseID), foreign key (StudentID) references student(SID) on delete cascade, foreign key (CourseID) references course(CID) on delete cascade );

8. Referential triggered action Example (CASCADE) CREATE TABLE dependent ( ... FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE CASCADE, ...) Example (SET NULL) CREATE TABLE studentgroup ( FOREIGN KEY (PresidentID) REFERENCES student(SID) ON DELETE SET NULL Example (SET DEFAULT) CREATE TABLE employee ( ... dno INT NOT NULL DEFAULT 1, ... FOREIGN KEY (dno) REFERENCES department(dnumber) ON DELETE SET DEFAULT ...)

9. Enforcing Integrity Constraints

10. Attribute-level Check create table enrolled ( StudentID number(5), CourseID number(4), Quarter varchar(6) CHECK(quarter in ('Fall','Winter','Spring')), Year number(4), …

11. Tuple-level CHECK create table course ( CID number(4), CourseName varchar(40), Department varchar(4), CourseNr char(3), primary key (CID), check (department <> 'CSC' OR CourseNR > 100) ); • same as attribute level check, just involves any number of attributes and different placement

12. Enforcing Integrity Constraints-Summary • Integrity constraints are specified when schema is defined • They must be true at all times • Must be checked when relations are modified • A DBMS must be responsible for checking them (as opposed to?) • Integrity constraints come from applications; a DB instance is never a good evidence to infer ICs

13. Relation Schema Modifications

14. ALTER TABLE ALTER TABLE TABLE_NAME …ADD Attribute DOMAIN; or DROP COLUMN Attribute CASCADE CONSTRAINTS; • Modifies an existing table schema

15. Examples ALTER TABLE student ADD age integer; Exercise: Add a (named) constraint that 0 <= age <= 120 ALTER TABLE studentgroup ADD FOREIGN KEY(PresidentID) REFERENCES Student(SID); ALTER TABLE studentgroup ADD CONSTRAINT fk_sg FOREIGN KEY(PresidentID) REFERENCES Student(SID); ALTER TABLE studentgroup DROP fk_sg;

16. Cyclic Dependencies • Most systems do not allow references to tables that do not exist yet. • Two solutions: • if no cyclical dependencies: create tables in right order (Example: university.sql) • in case of cyclical dependencies: create tables without f.k. constraints, and use ALTER TABLE to add these later

17. Today • SQL • Basic SQL on single table • Basic SQL on two tables • Nested subqueries

18. SQL: Structured Query Language

19. SQL Structured Query Language (SQL) is the industry standard for relational databases Used to be known as SEQUEL (Structured English Query Language), developed at IBM All major DBMSs support some version of SQL (SQL-99 is the one you are likely to see)

20. Classes of SQL Commands • Data Definition Language (DDL) • Create schemas, tables, constraints, views • Data Manipulation Language (DML) • Modify and update tables, retrieve information • Data Control Language (DCL) • Grant and revoke access to parts of database • Most users will only have access to the DML – we will use both the DDL and the DML

21. SQL-DDL • Create a table • Insert values into it create table student ( LastName varchar(40), FirstName varchar(40), SID number(5), SSN number(9), Career varchar(4), Program varchar(10), City varchar(40), Started number(4) ); insert into student values ( 'Brennigan', 'Marcus', 90421, 987654321, 'UGRD', 'COMP-GAM', 'Evanston', 2010 );

22. Classes of SQL Commands • Data Definition Language (DDL) • Create schemas, tables, constraints, views • Data Manipulation Language (DML) • Modify and update tables, retrieve information • Data Control Language (DCL) • Grant and revoke access to parts of database • Most users will only have access to the DML – we will use both the DDL and the DML

23. SQL is declarative SQL describes WHAT to do Not HOW to do it.

24. Student Table Query the table: Find all students who live in Chicago

25. Student Table Query the table: Find all students who live in Chicago SELECT * FROM Student WHERE city = ‘Chicago’ Star means all attributes

26. Student Table Query the table: Find IDs of students who live in Chicago SELECT SID, City FROM Student WHERE city = ‘Chicago’ This is the WHERE clause. The WHERE clause is evaluated for each row in the table

27. No No No No

28. Yes Yes Yes No Temporary Query Result Table

29. Query the table: Find Ids of students who live in Chicago SELECT SID, City FROM Student WHERE city = ‘Chicago’ Find Ids of students who live in Chicago SELECT SID, City FROM Student WHERE city = ‘Chicago’

30. Query the table: Find Id and Last Name of students who live in Chicago SELECT SID, City FROM Student WHERE city = ‘Chicago’ Query the table: Find Id and Last Name of students who live in Chicago SELECT SID, LName FROM Student WHERE city = ‘Chicago’

31. SQL: WHERE WHERE condition • Each tuple is tested against the condition, and only those that satisfy it are returned by the query • Condition expression can contain: • comparisons • expressions with wildcards (for strings) • boolean operations

32. Comparisons • Put numerical or string value on each side • Each comparison returns true or false = is equal to != or <> is not equal to > is greater than >= is greater than or equal to < is less than <= is less than or equal to

33. Wildcards • Using LIKE, we can compare character strings to strings that include wildcard characters that match anything: _ matches any single character % matches any consecutive set of characters • For example: • ‘b_d’ will match ‘bad’, ‘bed’, but not ‘band’ • ‘bat%’ will match ‘bat’, ‘bath’, ‘battery’…

34. Practice • Select all students who started after 2010

35. SQL: WHERE WHERE condition • Each tuple is tested against the condition, and only those that satisfy it are returned by the query • Condition expression can contain: • comparisons • expressions with wildcards (for strings) • boolean operations

36. Boolean Expressions • Select students whose last initial is ‘B’ or ‘Y’ • List students who live in ‘Evanston’ and started in ‘2010’

37. Yes AND Yes Yes AND No No AND No No AND No

38. No AND Yes No AND No No And No No AND No SELECT * FROM Student WHERE City = ‘Evanston’ AND Started = 2010

39. Boolean Expressions Temporary Query Result Table

40. Boolean Expressions • List students who live in ‘Evanston’ or started in ‘2010’ SELECT * FROM Student WHERE City = ‘Evanston’ OR Started = 2010

41. Boolean Expressions • List students in ‘COMP-GAM’ and ‘INFO-SYS’ SELECT * FROM Student WHERE Program = ‘COMP-GAM’ OR Program = ‘INFO-SYS’

42. Boolean Operators • Simple conditions can be combined into more complicated conditions • X AND Y is satisfied by a tuple if and only if both X and Y are satisfied by it • X OR Y is satisfied by a tuple if and only if at least one of X and Y is satisfied by it • NOT X is satisfied by a tuple if and only if X is not satisfied by it

43. SQL: SELECT FROM SELECT list of attributes FROM list of tables • SELECT gives which attributes to include • give a single attribute, or a list • * for all attributes • FROM gives the table(s) to get tuples from • for now, just a single table

44. Extensions to SELECT: Distinct SELECT City FROM Student WHERE city = ‘Chicago’ SELECT DISTINCT City FROM Student WHERE city = ‘Chicago’

45. Extensions to SELECT: Distinct • SQL does not remove duplicates by default • The first query does not eliminate duplicate rows from the answer. • The second query eliminates duplicate rows. • The query writer chooses whether duplicates are eliminated.

46. More SELECT Extensions • The SELECT clause list can also include simple arithmetic expressions using +, -, *, and /. • We can use aggregate operators in the SELECT clause: COUNT, SUM, MIN, MAX, and AVG • If one aggregate operator appears in the SELECT clause, then ALL of the entries in the select clause must be aggregate operators • Operators can be composed together

47. Examples • SELECT avg(started), max(started), min(started) FROM student; • SELECT max(started), min(started) FROM student WHERE career = ‘GRD’; • SELECT count(*) AS GraduateStudents FROM student WHERE career = ‘GRD’; • SELECT count(distinct presidentID) FROM studentgroup;

48. Rename Columns and Tables • SQL aliases are used to give a table, or a column in a table, a temporary name. • Aliases are often used to make column names more readable. • An alias only exists for the duration of the query. • SELECT column_name AS alias_nameFROM table_name; • SELECT column_name(s)FROM table_name AS alias_name;

49. Order Query Result • Ordering tuples SELECT SID, Started FROM Student WHERE city = ‘Chicago’ ORDER BY Started • By default ASC order • DESC for descending order

50. SQL Queries • General form of a query: 1. SELECT list of attributes to report 2. FROM list of tables 3. [WHERE tuple condition]4. [ORDER BY list of ordering attributes] ; • Result is an ordered set of ordered tuples