Class Note for EECS 647 with Professor Huan at KU 5
Class Note for EECS 647 with Professor Huan at KU 5
Popular in Course
Popular in Department
This 23 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 15 views.
Reviews for Class Note for EECS 647 with Professor Huan at KU 5
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 Swat 39 Full Q s v amp 7 946 2 Stating Points o A database 0 A database management system c A miniworld o A data model 0 Conceptual model a Relational model 2242009 Luke Huan Univ of Kansas So What Database Schemas A database schema is a description of a database using a given data model relational model by default External schemas user Views describe how users see the data View 1 View 2 View 3 Conceptual schema de nes i logical Strucmre Conceptual Schema Internal schema describes the f yz gltggage Strucmre Of Internal Schema DB 2242009 Luke Huan Univ of Kansas 3 Data Independence 0 Applications insulated from how data is structured and stored 0 Logical data independence the capacity to change the conceptual schema Without having to change the external schema 0 Physical data independence the capacity to change the internal schema Without having to change the conceptual schema 0 Q Why is this particularly importanifor DBMS OK So how to model a miniworld 0 Using conceptual model such as ER model 2242009 Luke Huan Univ of Kansas Where does a conceptual model lead us to o A tabular View 2242009 Luke Huan Univ of Kansas 6 Now you have a draft how do you improve your design 0 Using integrity constraints a Attributes value must come from its domain a Every relation mush have a primary key a The primary key value in a tuple can not be NULL 0 The foreign key value in a referenced tuple must exist or the foreign key value in the referencing tuple is NULL 2242009 Luke Huan Univ of Kansas How do you improve relational data base design cont 0 Using functional dependency o Nonkey FD always leads to redundancy 0 BCNF normal form 0 Pick up a nonkey FD do binary decomposition 0 First normal form no attribute may be composite or multiValued 0 2nd normal form and 3rd normal form are coming 2242009 Luke Huan Univ of Kansas What could we do about relations o Relational algebra expression 0 Using temporary variable 0 Identifying information source a Pay attention to non monotonic operation 2242009 Luke Huan Univ of Kansas Review 0 SELECT a list of attributes FROM a list of relations WHERE condition 0 Condition may have logical operators AND OR NOT 0 Condition may have comparison operators lt lt ltgt gt2 gt 33 0 String comparison may use exactly match or LIKE matching with regular expressions 9 9 o Arithmetic expressions of attributes are allowed 2242009 Luke Huan Univ of Kansas 10 Examples of bag operations Bagl Bag2 fruit fruit Apple Apple Apple Orange Orange Orange Bagl UNION ALL Bag2 Bagl INTERSECT ALL Bag2 fruit Apple frult Bagl EXCEPT ALL Bag2 Apple Apple Apple fruit Orange Apple Orange Orange Orange 2242009 Luke Huan Univ of Kansas 11 Exercise 0 SELECT Sid 2009 age FROM STUDENT WHERE name LIKE J0hn OR GPA gt 36 sid name age gpa 1234 John Smith 21 35 1123 Mary Carter 19 38 1011 Bob Lee 22 26 1204 Susan Wong 22 34 1306 Kevin Kim 18 29 asdfsadf 2242009 Luke Huan Univ of Kansas Aggregates 0 Standard SQL aggregate functions COUNT SUM AVG MIN MAX 0 Example number of students under 18 and their average GPA 0 SELECT COUNT AVGGPA FROM Student WHERE age lt 18 o COUNT counts the number of rows 2242009 Luke Huan Univ of Kansas 13 Aggregates with DISTINCT 0 Example HOW many students are taking classes 0 SELECT COUNT SID FROM Enroll a SELECT COUNTDISTINCT SID FROM Enroll 2242009 Luke Huan Univ of Kansas 14 GROUP BY 0 SELECT FROM WHERE GROUP BY list0fcolumns 0 Example nd the average GPA for each age group a SELECT age AVGGPA FROM Student GROUP BY age 2242009 Luke Huan Univ of Kansas 15 Operational semantics of GROUP BY SELECT FROM WHERE GROUP BY 0 Compute FROM x 0 Compute WHERE o 0 Compute GROUP BY group rows according to the values of GROUP BY columns 0 Compute SELECT for each group TE 0 For aggregation functions with DI ST INCT inputs rst eliminate duplicates Within the group Number of groups number of rows in the nal output 2242009 Luke Huan Univ of Kansas 16 Example of computing GROUP BY SELECT age AVGGPA FROM Student GROUP BY age sid name age gpa 1234 John Smith 21 35 1123 Ma Carter 19 38 W values of GROUP BY 1011 Bob Lee 22 26 c lumnq 1204 Susan Wong 22 3 4 Sid 1306 Kevin Kim 19 29 CmnmneGROUPBYgDup rows according to the Compute SELECT for each 2242009 Luke Huan Univ of Kansas 17 Aggregates with no GROUP BY 0 An aggregate query with no GROUP BY clause represent a special case Where all rows go into one group SELECT AVG GPA FROM Student Compute aggregate over the group sid name age gpa 1234 John Smith 21 35 1123 Mary Carter 19 38 1011 Bob Lee 22 26 1204 Susan Wong 22 34 1306 Kevin Kim 19 29 2242009 Luke Huan Univ of Kansas name age gpa Group all rows into one 18 Restriction on SELECT o If a query uses aggregation group by then every column referenced in SELECT must be either 0 Aggregated or o A GROUP BY column This restriction ensures that any SELECT expression produces only one value for each group 2242009 Luke Huan Univ of Kansas 19 Examples of invalid queries o SELECT Wage EROM Student GROUP BY age 0 Recall there is one output row per group 0 There can be multiple SID values per group 0 SELECT M MAX GPA FROM Student 0 Recall there is only one group for an aggregate query with no GROUP BY clause 0 There can be multiple SID values 0 Wishful thinking that the output SID value is the one associated with the highest GPA does NOT work 2242009 Luke Huan Univ of Kansas 20 HAVING 0 Used to lter groups based on the group properties eg aggregate values GROUP BY column values 0 SELECT FROM H WHERE GROUP BY HAVING condition 2242009 Compute FROM x Compute WHERE 6 Compute GROUP BY group rows according to the values of GROUP BY columns Compute HAVING another 6 over the groups Compute SELECT TE for each group that passes HAVING Luke Huan Univ of Kansas 21 HAVING exam ples c Find the average GPA for each age group over 10 0 SELECT age AVGGPA FROM Student GROUP BY age HAVING age gt 10 0 Can be written using WHERE Without table expressions 0 List the average GPA for each age group with more than a hundred students 0 SELECT age AVGGPA FROM Student GROUP BY age HAVING COUNT gt 100 0 Can be written using WHERE and table expressions 2242009 Luke Huan Univ of Kansas 22 A First Touch of Subqueries 0 Use query result as a table 0 In set and bag operations FROM clauses etc o A way to nest queries 0 Example names of students who are in more clubs than classes SELECT DISTINCT name FROM Student SELECT SID FROM ClubMember EXCEPT ALL SELECT SID FROM Enroll AS S WHERE StudentSID SSID 2242009 Luke Huan Univ of Kansas 23
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'