1 / 23

Introduction to Database Management Systems

Introduction to Database Management Systems. Dr. Adam Anthony Fall 2012. Lecture 4 Overview. Chapter 3, sections 1-4 SQL language introduction Data Definition Language Data Query Language Basic Query Practice. History of SQL.

alexis-long
Download Presentation

Introduction to Database Management Systems

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. 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;

More Related