New version page

UW CSE 444 - Lecture Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more

This preview shows page 1-2-3-4-5-6 out of 17 pages.

View Full Document
View Full Document

End of preview. Want to read all 17 pages?

Upload your study docs or become a GradeBuddy member to access this document.

View Full Document
Unformatted text preview:

One More Normal FormMultivalued Dependencies (and one last normal form)Querying the DatabaseRelational Algebra at a GlanceSet OperationsSelectionSlide 7ProjectionSlide 9Cartesian ProductSlide 11Join (Natural)Complex QueriesExercisesSlide 15Other Joins and RenamingOperations on Bags (and why we care)One More Normal Form•Consider the dependencies: Product Company Company, State Product• Is it in BCNF?Multivalued Dependencies (and one last normal form) Name SSN Phone Number Course Fred 123-321-99 (206) 572-4312 CSE-444Fred 123-321-99 (206) 572-4312 CSE-341Fred 123-321-99 (206) 432-8954 CSE-444Fred 123-321-99 (206) 432-8954 CSE-341The multivalued dependencies are: Name, SSN Phone Number Name, SSN Course4th Normal form: replace FD by MVD.Querying the Database•How do we specify what we want from our database? Find all the employees who earn more than $50,000 and pay taxes in New Jersey.•We design high-level query languages:–SQL (used everywhere)–Datalog (used by theoreticians and their students)•Relational algebra: a basic set of operations on relations that provide the basic principles.Relational Algebra at a Glance•Operators: sets as input, new set as output •Basic Set Operators–union, intersection, difference, but no complement. •Selection:•Projection:  •Cartesian Product: X•Joins (natural,equi-join, theta join, semi-join)•Renaming:Set Operations•Binary operations–Result is table(set) with same attributes•Watch our for naming of attributes in resulting relation.•Union: all tuples in R1 or R2•Intersection: all tuples in R1 and R2•Difference: all tuples in R1 and not in R2•No complement. Why?•Bags later.Selection•Produce a subset of the tuples in a relation which satisfy a given condition•Unary operation… returns set with same attributes, but ‘selects’ rows•Use and, or, not, >, <… to build condition•Find all employees with salary more than $40,000:Selection ExampleEmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000SSN Name DepartmentID Salary888888888 Alice 2 45,000Find all employees with salary more than $40,000.Projection•Unary operation, selects columns•Eliminates duplicate tuples•Example: project social-security number and names.Projection ExampleEmployeeSSN Name DepartmentID Salary999999999 John 1 30,000777777777 Tony 1 32,000888888888 Alice 2 45,000SSN Name999999999 John777777777 Tony888888888 AliceCartesian Product•Binary Operation•Result is tuples combining any element of R1 with any element of R2, for R1XR2•Schema is union of Schema(R1) & Schema(R2)Cartesion Product ExampleEmployeeName SSNJohn 999999999Tony 777777777DependentsEmployeeSSN Dname999999999 Emily777777777 JoeEmployee_DependentsName SSN EmployeeSSN DnameJohn 999999999 999999999 EmilyJohn 999999999 777777777 JoeTony 777777777 999999999 EmilyTony 777777777 777777777 JoeJoin (Natural)•Most important, expensive and exciting.•Combines two relations, selecting only related tuples•Equivalent to a cross product followed by selection•Resulting schema has all attributes of the two relations, but one copy of join condition attributesComplex QueriesProduct ( name, price, category, maker)Purchase (buyer, seller, store, product)Company (name, stock price, country)Person( name, phone number, city)Find phone numbers of people who bought gizmos from Fred.Find telephony products that somebody boughtExercises Product ( name, price, category, maker)Purchase (buyer, seller, store, product)Company (name, stock price, country)Person( name, phone number, city)Ex #1: Find people who bought telephony products.Ex #2: Find names of people who bought American productsEx #3: Find names of people who bought American products and did not buy French productsEx #4: Find names of people who bought American products and they live in Seattle.Ex #5: Find people who bought stuff from Joe or bought products from a company whose stock prices is more than $50.Join ExampleEmployeeName SSNJohn 999999999Tony 777777777DependentsEmployeeSSN Dname999999999 Emily777777777 JoeEmployee_DependentsName SSN DnameJohn 999999999 EmilyTony 777777777 JoeOther Joins and Renaming•Theta join: the join involves a predicate–R S•Semi-join: the attributes of one relation are included in the other.•Renaming:Operations on Bags (and why we care)Basic operations: Projection Selection Union Intersection Set difference Cartesian product Join (natural join, theta


View Full Document
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?