Introduction to Database Management Systems

1. Introduction to Database Management Systems Dr. Adam Anthony Fall 2012

2. Lecture 4 Overview • Chapter 3, sections 1-4 • SQL language introduction • Data Definition Language • Data Query Language • Basic Query Practice

3. History of SQL • IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory • Renamed Structured Query Language (SQL) • ANSI and ISO standard SQL: • SQL-86, SQL-89, SQL-92 • SQL:1999, SQL:2003, SQL:2008 • Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. • Not all examples here may work on your particular system.

4. Data Definition Language • Converts a schema into a real thing! • New information: Domain Types • Managing data integrity • Other Security, efficiency features (take Database 2 class for more info) • Indexing (pre-sorting, basically) • Security and access privilege • How to store on disk

5. Common Data Types • Most systems have these types exactly, or something very close in name and usage: • char(n). Fixed length character string, with user-specified length n. • varchar(n). Variable length character strings, with user-specified maximum length n. • int. Integer (a finite subset of the integers that is machine-dependent). • smallint. Small integer (a machine-dependent subset of the integer domain type). • numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. • real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision. • float(n). Floating point number, with user-specified precision of at least n digits. • More are covered in Chapter 4.

6. Example Schema Performer Song Performer-Song Performer_ID Song_ID Performer_ID name Song_ID Title Genre Album

7. Create Table Statement • create table NAME ( A1D1, A2D2, ..., AnDn,(integrity-constraint1),...,(integrity-constraintk) ); • create table PERFORMER ( performer_idint, name varchar(30), primary key (performer_id) );

8. Integrity Constraints • Not Null • Primary Key • Foreign Key • create table PERFORMER-SONG ( Performer_IDint, Song_IDint, primary key (Performer_ID,Song_ID) foreign key (Performer_ID) references PERFORMER foreign key (Song_ID) references SONG ); **A primary key specification requires values to be not null!

9. Not Null Example • Create table SONG( Song_IDint, Title varchar(30) not null, Genre varchar (10), Album varchar(50) not null, primary key (Song_ID) );

10. Multi-Valued Keys • create tabletakes (IDvarchar(5),course_idvarchar(8),sec_idvarchar(8),semestervarchar(6),yearnumeric(4,0),gradevarchar(2),primary key (ID, course_id, sec_id, semester, year),foreign key (ID) references student,foreign key (course_id, sec_id, semester, year) references section ); • Note: sec_id can be dropped from primary key above, to ensure a student cannot be registered for two sections of the same course in the same semester

11. Drop and Alter Table • drop table student • Deletes the table and its contents • delete from student • Deletes all contents of table, but retains table • alter table • alter table r add A D • where A is the name of the attribute to be added to relation r and D is the domain of A. • All tuples in the relation are assigned null as the value for the new attribute. • alter table r drop A • where A is the name of an attribute of relation r • Dropping of attributes not supported by many databases

12. Data Manipulation Language • SQL features that allow us to query data in novel ways, and to update and delete specific values • Basic query construct: the Select clause: SELECT Name FROM PERFORMER WHEREPerformer_ID < 5000; • All SQL clauses are case insensitive—performer_id = PERFORMER_ID = Performer_ID, etc. • Use capitalization, line breaks to enhance readability • Connect each portion above to the relational algebra equivalent

13. Projection, Plus! • If you want everything: SELECT * FROM Student; • When we get to multi-table queries: SELECT Student.* FROM Student, Takes WHERE … • Expressions for columns: SELECTID, name, salary*1.10FROM instructor

14. INTERACTIVE LECTURE TIME! • Queries are best learned through doing! • Download and extract: • http://www.bw.edu/~apanthon/courses/CSC280/DataFiles/InClassLec4-5.zip

15. DISTINCT and ALL • Output of an SQL statement is a relation, but it MAY have duplicates! • Differs from theoretical underpinnings • Find all the courses that have ever been taken by a student: • Try: Then Try: SELECT course_id FROM takes; • SELECT DISTINCT course_id • FROM takes;

16. Using WHERE • The WHERE clause does most of the interesting work • True/False tests based on attribute values • Use AND, OR, NOT to combine tests just like you would in an IF statement • SQL provides many useful operators for WHERE clauses

17. Comparing Strings • Strings represented with singe quote: ‘Comp. Sci.’ • Comparisons are case-sensitive ‘Comp. Sci.’ != ‘comp. sci.’ • Upper(s) and Lower(s) to get all-upper or all-lower • Trim(s) to get rid of trailing white space • LIKE Wildcards: • % represents any number or characters • _ represents exactly one character • Ex: WHERE name LIKE ‘%Thompson’

18. Practice • Find all students with • A name more than 5 characters long • A name that starts with P or ends in S (case insensitive) • A name that is exactly five letters and starts with B

19. Range Parameters • Cool trick to save time • Find all students with total credits more than 3 and less than 10 • Inclusive range: SELECT * FROM Student WHERE tot_credBETWEEN 4 and 9 • This construct is completely optional • How to do this with >=, <= ?

20. Using more than one table • The FROM clause automatically produces a cartesian (cross) product: • Command .headers ON • Try: SELECT * FROM Student, Takes • Is this useful, ever? What should we add?

21. Manual Joins • Just filter out the junk! SELECT * FROM Student, Takes WHERE Student.ID = Takes.ID • Called a ‘Join’ because it results in a combined table that is true to the intent of relational database design • Depending on the design, a Join can still be HUGE and COSTLY to compute!

22. Natural Joins • IF the names of the columns you wish to join on match PERFECTLY you can let SQL join automatically: SELECT * FROM STUDENT NATURAL JOIN TAKES • If the names don’t match, or you need to do something clever, stick with a manual join • Just a time-saver

23. Natural Join Problems • Danger in natural join: beware of unrelated attributes with same name which get equated incorrectly • List the names of instructors along with the the titles of courses that they teach • Incorrect version (makescourse.dept_name= instructor.dept_name) • select name, titlefrom instructor natural join teaches natural join course; • Correct version • select name, titlefrom instructor natural join teaches, coursewhere teaches.course_id= course.course_id;