Chapter 5 Relational Database Management Systems and SQL

1. Chapter 5Relational Database Management Systems and SQL Spring 2014

2. History of SQL • Proposed by E.F.Codd in his 1970 paper • Used in System R, IBM’s research relational database in early 1970s-D.D. Chamberlin et al at IBM Research Center, San Jose, California • Used in Oracle, released in late 1970s • Incorporated into IBM’s SQL/DS in 1981, and DB2 in 1983 • Also used in Microsoft SQL Server, MySQL, Informix, Sybase, PostGreSQL, Microsoft Access, and others

3. Standards • ANSI and ISO published SQL standards in 1986, called SQL-1 • Minor revision, SQL-89 • Major revision, SQL-2,1992 • SQL-3, 1999, 2003, 2006, 2008- multi-part revision, includes new data types, object-oriented (OO) facilities, user defined datatypes (UDTs), triggers, support for XML • Most vendors support standard, but have slight variations of their own

4. Components of SQL • Data definition language - DDL • Data manipulation language - DML • Authorization language – grant privileges to users

5. Relational Database Architecture • Separate external, logical, internal models • Logical level-base tables and indexes • Indexes, B+ or B trees – maintained by system • Relational views (external level) - derived from base tables • Users see views or base tables, or combination • Internal level - files • SQL supports dynamic database definition-can modify structures easily See Figure 5.1

6. Three level architecture for relational databases

7. DDL Commands CREATE TABLE CREATE INDEX ALTER TABLE RENAME TABLE DROP TABLE DROP INDEX Also – CREATE VIEW

8. CREATE TABLE CREATE TABLE base-table-name ( colname datatype [column constraints], [,colname datetype [column constraints …]] ... , [table constraints] [storage specifications]);

9. Identifiers • No SQL keywords • Table name unique within the database • Column name unique within the table • In Oracle, identifiers must be at most 30 characters long, begin with an alphabetic character, and contain only alphanumeric characters (but _, $ and # are permitted)

10. Datatypes • Each column must have a datatype specified • Available datatypes vary from DBMS to DBMS • Standards include various numeric types, fixed-length and varying-length character strings, bit strings, and user-defined types • Oracle types include CHAR(N), VARCHAR2(N), NUMBER(N,D), DATE, BLOB (binary large object) and others • SQL Server includes types of NUMERIC, BINARY, CHAR, VARCHAR DATETIME, MONEY, IMAGE, and others • Microsoft Access supports several types of NUMBER, as well as TEXT, MEMO, DATE/TIME, HYPERLINK, YES/NO, and others

11. MySQLDatatypes • YEAR • CHAR • TINYBLOB • BLOB • MEDIUMBLOB • MEDIUMTEXT • LONGBLOB • LONGTEST • ENUM • SET • BOOL • BINARY • VARBINARY • VARCHAR • TINYINT • TEXT • DATE • SMALLINT • MEDIUMINT • INT • BIGINT • FLOAT • DOUBLE • DECIMAL • DATETIME • TIMESTAMP • TIME

12. Constraints Column constraints – in-line constraints NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REF, CHECK, and DEFAULT Table constraints – out-of-line constraints all of the above except NOT NULL

13. Creating the Tables for the University Database CREATE TABLE Student ( stuId VARCHAR2(6), lastName VARCHAR2(20) NOT NULL, firstName VARCHAR2(20) NOT NULL, major VARCHAR2(10), credits NUMBER(3) DEFAULT 0, CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId), CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150))); CREATE TABLE Faculty ( facId VARCHAR2(6), name VARCHAR2(20) NOT NULL, department VARCHAR2(20), rank VARCHAR2(10), CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId)); CREATE TABLE Class ( classNumber VARCHAR2(8), facId VARCHAR2(6) NOT NULL, schedule VARCHAR2(8), room VARCHAR2(6), CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE SET NULL, CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room)); CREATE TABLE Enroll ( stuId VARCHAR2(6), classNumber VARCHAR2(8), grade VARCHAR2(2), CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId), CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) ON DELETE CASCADE, CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student (stuId) ON DELETE CASCADE);

14. Indexes • Can create any number of indexes for tables • Stored in same file as base table • Facilitate fast retrieval of records with specific values in a column • Keep track of what values exist for the indexed columns, and which records have those values • B+ trees or B trees used – see Appendix A for review of concepts • Overhead – system must maintain index

15. CREATE INDEX Command CREATE [UNIQUE] INDEX indexname ON basetablename (colname [order] [,colname [order]]...) [CLUSTER] ; Ex. CREATE INDEX Student_lastName_firstName_ndx ON Student (lastName, firstName); • UNIQUE specification enforces unique values for indexed column or combination of columns • Except when specified, column need not be unique • Order is ASC(default) or DESC • Can have major and minor orders • CLUSTER specification keeps records with same value for indexed field together (only one per table) • Oracle automatically indexes primary key columns

16. ALTER TABLE Command • To add a new column ALTER TABLE basetablename ADD columnnamedatatype; Ex. ALTER TABLE Student ADD COLUMN birthdate DATETYPE; • Cannot specify NOT NULL, since existing records have no value for this field • To drop a column ALTER TABLE basetablenameDROP COLUMN columnname; Ex. ALTER TABLE Student DROP COLUMN major; • To change a column’s properties ALTER TABLE basetablename MODIFY COLUMN colname [new specifications]; • To rename a column ALTER TABLE Student RENAME COLUMNcolname TO new-colname;

17. Other Changes to Tables • To add a constraint ALTER TABLE basetablename ADD CONSTRAINT constraint_defn; • To drop a constraint ALTER TABLE basetablename DROP CONSTRAINT constraint_name; • To rename a table: RENAME TABLE old-table-name TO new-table-name; Ex: RENAME TABLE FACULTY TO TEACHERS; • To drop a table: DROP TABLE basetablename; Ex. DROP TABLE CLASS; • To drop an index: DROP INDEX indexname; Ex. DROP INDEX Student_lastName_fristName_ndx;

18. SQL DML • Non-procedural, declarative language • Can be interactive, can be embedded in host language, or can be stand-alone programming language (SQL/PSMs) • Basic commands SELECT UPDATE INSERT DELETE

19. University Database

20. SELECT Statement SELECT [DISTINCT] col-name [AS newname], [,col-name..]… FROM table-name [alias] [,table-name]… [WHEREpredicate] [GROUP BY col-name [,col-name]…[HAVING predicate] or [ORDER BY col-name [,col-name]…]; • Powerful command – equivalent to relational algebra’s SELECT, PROJECT, JOIN and more… • Can be applied to one or more tables or views • Can display one or more columns (renaming if desired) • Predicate is optional, and may include usual operators and connectives • Can put results in order by one or more columns • Can group together records with the same value for column(s) • Can also use predefined functions • See list of examples, Section 5.4.1-5.4.3

21. Simple retrieval with condition • Get names, IDs, and number of credits of all Math majors. • Information appears in Student table SELECT lastName, first Name, stuId, credits FROM Student WHERE major = ‘Math’;

22. All columns • Get all information about CSC Faculty. • Information appears in Faculty table • Use asterisk for “all columns” SELECT * FROM Faculty WHERE department= ‘CSC’; May want to avoid the “*” because columns may be added later that may produce incorrect results

23. Retrieval without conditions • Get the course number of all courses in which students are enrolled SELECT classNumber FROM Enroll; SELECT DISTINCT classNumber FROM Enroll;

24. Entire Table • Retrieve entire table SELECT * FROM Student;

25. ORDER BY and AS • Get names and IDs of all Faculty members, arranged in alphabetical order by name. Call the resulting columns FacultyName and FacultyNumber SELECT name AS FacultyName, facId AS FacultyNumber FROM Faculty ORDER BY name, department; SELECT name AS FacultyName, facId AS FacultyNumber FROM Faculty ORDER BY name;

26. Multiple Conditions • Get names of all math majors who have more than 30 credits SELECT lastName, firstName FROM Student WHERE major = ‘Math’ AND credits > 30;

27. SELECT using multiple tables • Natural Join • Find IDs and names of all students taking ART103A SELECT Enroll.stuId, lastName, firstName FROM Student, Enroll WHERE classNumber = ‘ART103A AND Enroll.sutId = Student.stuId;

28. Natural Join with Ordering • Find stuId and grade of all students taking any course taught by Faculty member whose facId is F110. Arrange in order by stuId SELECT stuId, grade FROM Class, Enroll WHERE facId = ‘F110’ AND Class.classNumber = Enroll.classNumber ORDER BY stuId ASC;

29. Natural Join of three tables • Find course numbers and the names and majors of all students enrolled in the courses taught by Faculty member F110 SELECT Enroll.classNumber, lastName, firstName, major FROM Class, Enroll, Student WHERE facId = ‘F110’ AND Class.classNumber = Enroll.classNumber AND Enroll.stuId = Student.stuId;

30. Use of Aliases • Get a list of all courses that meet in the same room, with their schedules and room numbers SELECT Copy1.classNumber, COPY1.schedule, COPY1.room, COPY2.classNumber, COPY2.schedule FROM Class COPY1, Class COPY2 WHERE COPY1.room = COPY2.room AND COPY1.classNumber < COPY2.classNumber;

31. Join without Equality Condition • Find all combinations of students and Faculty where the student’s major is different from the Faculty member’s department SELECT stuId, lastName, firstName, major, facId, name, department FROM Student, Faculty WHERE Student.major <> Faculty.department

32. Subqueries with Equality SELECT classNumber FROM Class WHERE facId = (SELECT facId FROM Faculty WHERE name = 'Byrne' AND department = 'Math'); • Find the numbers of all the courses taught by Byrne of the Math department • This produces the same results Select classNumber FROM Class, Faculty WHERE name = 'Byrne' AND department = 'Math' AND Class.facId = Faculty.facId; • Can use a subquery in place of a join, provided the result to be displayed is contained in a single table and the data retrieved from the subquery consists of only one column. • When you write a subquery involving two tables, you name only one table in each SELECT.

33. Four Table Example • List each faculty, the course name and the names of students in each class. SELECT name, Class.classNumber, lastName, firstName FROM Faculty, Class, Student, Enroll WHERE Faculty.facId = Class.facId AND Enroll.stuId = Student.stuId AND Enroll.classNumber = Class.classNumber ORDER BY name, Enroll.classNumber, lastName;

34. Example • List class schedule for each professor SELECT name, classNumber, schedule, room FROM Faculty, Class WHERE Faculty.facId = Class.facId ORDER BY name, schedule

35. Subqueries using IN SELECT name, facId FROM Faculty WHERE facId IN (SELECT facId FROM Class WHERE room = ‘H221’); • Find the names and IDs of all Faculty members who teach a class in Room H221. • In the WHERE line in the main query we use IN instead of =, because the result of the subquery is a set of values rather than a single value. • Comparison operator (=, etc.) is restricted to single value, the IN can have multiple values. • NOT IN will evaluate to true if the record has a field value which is not in the set of values retrieved by the subquery.

36. Nested Subqueries SELECT lastName, firstName, stuId FROM Student WHERE stuId IN (SELECT stuId FROM Enroll WHERE classNumber IN (SELECT classNumber FROM Class WHERE facId = ‘F110’)); • Get an alphabetical lis tof names and IDs of all students in any class taught by F110. • Since values to be displayed appear on one table, Student, can use a subquery. • In execution, the most deeply nested SELECT is done first, and it is replaced by the values retrieved. Then the next most nested SELECT is done.

37. Queries using EXISTS SELECT lastName, firstName FROM Student WHERE EXISTS (SELECT * FROM Enroll WHERE Enroll.stuId = Student.stuId AND classNumber = ‘CSC201A’); • Find the names of all students enrolled in CSC201A. • We can do this with a join or subquery using IN. • Finds records in Enroll with stuId that is in Student. • Use name of main query table (Student) in subquery. Usually don’t do that, but in this case it is acceptable.

38. Queries using NOT EXISTS SELECT lastName, firstName FROM Student WHERE NOT EXISTS (SELECT * FROM Enroll WHERE Enroll.stuId = Student.stuId AND classNumber = ‘CSC201A’); • Find the names of all students not enrolled in CSC201A.

39. Queries using UNION SELECT facId FROM Faculty WHERE department = “History” UNION SELECT facId FROM Class WHERE room = “H221”; • Get IDs of all Faculty who are assigned to the history department or who teach in Room H221.

40. Queries using Functions SELECT COUNT (DISTINCT stuId) FROM Enroll WHERE classNumber= “ART103A”; DISTINCT used to eliminate duplicates before counting the number of students. • Find the total number of students enrolled in ART103A. Built-in Functions: • COUNT • SUM • AVG • MAX • MIN COUNT, MAX, MIN apply to both numeric and non numeric fields SUM, AVG apply to numeric only

41. Queries using Functions SELECT COUNT (DISTINCT department) FROM Faculty; • Find the number of departments that have faculty in them. • Find the average number of credits students have. SELECT Avg (credits) FROM Student; • Find the student with the largest number of credits. SELECT stuId, lastName, firstName FROM Student WHERE credits = (SELECT MAX (credits) FROM Student); • Find the ID of the student(s) with the highest grade in any course. SELECT stuId FROM Enroll WHERE grade = (SELECT MIN(grade) FROM Enroll);

42. Queries using Functions SELECT lastName, firstName, stuId FROM Student WHERE credits < (SELECT AVG (credits) FROM Student); • Find the names and IDs of students who have less than the average number of credits.

43. Query using Expression and a String Constant • Assuming each course is three credtis, list, for each student, the number of courses he or she has completed. SELECT stuId, ‘Number of courses = ‘, credits/3 FROM Student;

44. Queries using GROUP BY and HAVING • For each course, show the number of students enrolled. SELECT classNumber, COUNT(*) FROM Enroll GROUP BY classNumber; • Find all courses in which fewer than three students are enrolled. SELECT classNumber FROM Enroll GROUP BY classNumber HAVING COUNT(*) < 3;

45. Queries using LIKE • Get details of all MTH courses. SELECT * FROM Class WHERE classNumber LIKE ‘MTH%’; % character stands for any sequence of characters of an length >= 0. _ character stands for any single character. srtuId LIKE ‘S____’ means ther must be five charactes, the first must be S schedule LIKE ‘%9’ means any sequence of characters, of length at least one, with the last character a nine. name NOT LIKE ‘A% means the name cannot begin with an A.

46. Queries using NULL • Find stuId and classNumber of all students whose grades in that course are missing. SELECT classNumber, stuId FROM Enroll WHERE grade IS NULL; Cannot use WHERE grade = NULL – will return unknown

47. University Database

48. UPDATE Operator UPDATE tablename SET columnname = expression [columnname = expression]... [WHERE predicate]; • Used for changing values in existing records • Can update, zero, one, many, or all records in a table • For null value, use SET columnname = NULL • can use a sub-query to identify records to be updated

49. UPDATE Examples • Updating a single Field of One Record UPDATE Student SET major = ‘Music’ WHERE stuId = ‘S1020’; • Updating Several Fields of One Record UPDATE Faculty SET department = ‘MIS’ rank = ‘Assistant’ WHERE name = ‘Tanaka’;

50. UPDATE Examples • Updating Using NULL UPDATE Student SET major = NULL stuId = ‘S1013’ WHERE name = ‘McCarthy’; • Updating Several Records UPDATE Enroll SET grade = ‘A’ WHERE classNumber = ‘CSC201A’;