Class Note for EECS 647 with Professor Huan at KU
Class Note for EECS 647 with Professor Huan at KU
Popular in Course
Popular in Department
This 27 page Class Notes was uploaded by an elite notetaker on Friday February 6, 2015. The Class Notes belongs to a course at Kansas taught by a professor in Fall. Since its upload, it has received 16 views.
Reviews for Class Note for EECS 647 with Professor Huan at KU
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 02/06/15
EECS 647 Introduction to Database Systems Instructor Luke Huan Spring 2009 Summary of SQL Features 0 Query 0 SELECTFROMWHERE statements 0 Set and bag operations a Table expressions subqueries o Aggregation and grouping 0 Ordering o Outerjoins 0 Table creation constraints and content updates 41 2009 Luke Huan Univ of Kansas Today s Topic o Subqueries 0 Active databases 0 View 0 Indexing 41 2009 Luke Huan Univ of Kansas Quantified subqueries A quanti ed subquery can be used as a value in a WHERE condition Universal quanti cation for all WHERE x 0p ALL subquery 0 True iff for all t in the result of subquery x 0p t Existential quanti cation exists WHERE x 0p ANY subquery 0 True iff there exists some t in the result of subquery such that x 0p t Beware oInwmmmpmMmq mWwd kwmwbmmmmms o h1SQLANYraMynwmm smn Also may use EXI STS 412009 NOT EXISTS Luke Huan Univ of Kansas IN NOT IN Examples of quantified subqueries 0 Which employees have the highest salary Employee Sid Name Salary 0 SELECT FROM Employee WHERE Salary gt ALL SELECT Salary FROM Employee 0 How about the lowest salary a SELECT FROM Employee WHERE Salary lt ALL SELECT salary FROM Employee 41 2009 Luke Huan Univ of Kansas More ways of getting the highest Salary 0 Who has the highest Salary 0 SELECT FROM Employee e WHERE NOT EXISTS SELECT FROM Employee WHERE Salary gt eSalary a SELECT FROM Employee WHERE Eid NOT IN SELECT elSID FROM Employee el Employee e2 WHERE elSalary lt e2Salary 41 2009 Luke Huan Univ of Kansas Nested Queries Scoping c To nd out which table a column belongs to a Start with the immediately surrounding query a If not found look in the one surrounding that repeat if necessary 0 Use tablename columnname notation and AS renaming to avoid confusion 41 2009 Luke Huan Univ of Kansas Anlixanu e SELECT FROM Student S WHERE EXISTS u SELECT FROM Enrol e WHERE SID SSID AND EXISTS SELECT FROM Enroll WHERE SID SSID AND CID ltgt eCI D Students who are taking at least two courses 41 2009 Luke Huan Univ of Kansas Nested Queries o Nested queries do not add expression power to SQL 0 For convenient 0 Write intuitive SQL queries 0 Can always use SQL queries Without nesting to complete the same task though sometime it is hard 41 2009 Luke Huan Univ of Kansas 9 Database Design Active database View Indexing Thinking about the following realworld situation You need to design a database for students to record student name gpa gt0 id email address home department and images Constraints student id is the primary key student gpa can not be negative a student may take up to 10 courses in a semester unless they are senior and whenever a new honor student is entered in the table this student is automatically enrolled a honor course BiolOl How J ayhawk y 41 2009 Luke Huan Univ of Kansas 10 Active Database o Constraints are ef cient ways to improve the quality of the database 0 We have covered domain constraint 0 We will talk about 0 Check 0 Assertion o Trigger 41 2009 Luke Huan Univ of Kansas 11 Check 0 Student GPA must be positive 0 CREATE TABLE Student SId charlO Sname char20 email charlO gpa float CHECK gpa gt 00 41 2009 Luke Huan Univ of Kansas 12 Assertion Make sure each student can take up to 10 course projects unless the student is a senior level gt4 CREATE ASSERTTON uplimitCourse CHECK SELECT MAX COUNT Sid FROM student enrollment WHERE level lt 4 GROUP BY Sid lt 10 41 2009 Luke Huan Univ of Kansas 13 Trigger example CREATE TRIGGER EECSl68AutoRecruit AFTER INSERT ON Student gtEvent REFERENCTNG NEW ROW AS newStudent FOR EACH ROW WHEN newStudent isHonor Condition INSERT INTO Enroll VALUES newStudent SID BIOlOl 1 Action 41 2009 Luke Huan Univ of Kansas 14 Views o A View is like a Virtual table 0 De ned by a query which describes how to compute the View contents on the y a DBMS stores the View de nition query instead of View contents 0 Can be used in queries just like a regular table 41 2009 Luke Huan Univ of Kansas 15 Why use views c To hide data from users 0 To hide complexity from users 0 Logical data independence 0 If applications deal with Views we can change the underlying schema Without affecting applications 0 Recall physical data independence change the physical organization of data Without affecting applications 0 To provide a uniform interface for different implementations or sources Real database applications use tons of Views 41 2009 Luke Huan Univ of Kansas 16 Creating and dropping views 0 Example EECS647roster o CREATE VIEW EECS647Roster AS SELECT SID name age GBLCalled basetables FROM Student WHERE SID IN SELECT SID FROM Enroll WHERE CID 39EECS647 c To drop a View 0 DROP VIEW viewname 41 2009 Luke Huan Univ of Kansas 17 Using views in queries 0 Example nd the average GPA of EECS647 students 0 SELECT AVGGPA FROM EECS647Roster a To process the query replace the reference to the View by its de nition a SELECT AVGGPA FROM SELECT SID name age GPA FROM Student WHERE SID IN SELECT SID FROM Enroll WHERE CID EECS647 41 2009 Luke Huan Univ of Kansas 18 Modifying views Does not seem to make sense since Views are Virtual But does make sense if that is how users see the database Goal modify the base tables such that the modi cation would appear to have been accomplished on the View Be careful 0 There may be one way to modify 0 There may be many ways to modify 0 There may be no way to modify 41 2009 Luke Huan Univ of Kansas 19 A simple case CREATE VIEW StudentGPA AS SELECT STD GPA FROM Student DELETE FROM StudentGPA WHERE STD 123 translates to DELETE FROM Student WHERE STD 123 41 2009 Luke Huan Univ of Kansas 20 An impossible case CREATE VIEW HighGPAStudent AS SELECT SID GPA FROM Student WHERE GPA gt 37 INSERT INTO HighGPAStudent VALUES987 25 o No matter What you do on Student the inserted row will not be in H ighGPAStudent 41 2009 Luke Huan Univ of Kansas 21 A case with too many possibilities CREATE VIEW AverageGPAGPA AS SELECT AVGGPA FROM Student 0 Note that you can rename columns in View de nition UPDATE AverageGPA SET GPA 25 0 Set everybody s GPA to 25 0 Adjust everybody s GPA by the same amount 0 Just lower Lisa s GPA 41 2009 Luke Huan Univ of Kansas 22 SQL92 updateable views o More or less just singletable selection queries 0 No join a No aggregation o No subqueries o Arguably somewhat restrictive 0 Still might get it wrong in some cases a See the slide titled An impossible case 0 Adding WITH CHECK OPTION to the end ofthe View de nition will make DBMS reject such modi cations 41 2009 Luke Huan Univ of Kansas 23 Indexes for Performance Tuning 0 An index is an auxiliary persistent data structure a Search tree eg Btree Rtree 0 Lookup table eg hash table 0 An index 011 RA can speed up accesses 0f the form 0 RA value lockup 0 RA gt value range query 0 An index 0nRA1 RA can speed up a RA1 value1 E E RA value 0 RA1 RAngt valuel value More on indexes in the second half of this course 41 2009 Luke Huan Univ of Kansas 24 Creating and dropping indexes o CREATE UNIQUE INDEX indexname ON tablename columnnamel columnnamen 0 With UNIQUE the DBMS will also enforce that columnnamel columnnamen is a key of tablename o eg CREATE INDEX nameindeX ON STUDENT SName 0 DROP INDEX indexname 0 Typically the DBMS will automatically create indexes for PRIMARY KEY and UNIQUE constraint declarations 41 2009 Luke Huan Univ of Kansas 25 Choosing indexes to create More indexes better performance 0 Indexes take space 0 Indexes need to be maintained when data is updated 0 Indexes have one more level of indirection Optimal index selection depends on both query and update workload and the size of tables 41 2009 Luke Huan Univ of Kansas 26 What s next 0 Database meets Concurrency 0 Transaction will be covered after we talk about transaction management 41 2009 Luke Huan Univ of Kansas 27
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'