DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-3-18-19-36-37-38 out of 38 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 38 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Transactions Relational Algebra XML February 11th 2004 Transactions Transaction group of statements that must be executed atomically Transaction properties ACID ATOMICITY all or nothing CONSISTENCY leave database in consistent state ISOLATION as if it were the only transaction in the system DURABILITY store on disk Transactions in SQL In ad hoc SQL Default each statement one transaction In embedded SQL BEGIN TRANSACTION SQL statements COMMIT or ROLLBACK ABORT Transactions Serializability Serializability the technical term for isolation An execution is serial if it is completely before or completely after any other function s execution An execution is serializable if it equivalent to one that is serial DBMS can offer serializability guarantees Serializability Enforced with locks like in Operating Systems But this is not enough User 1 LOCK LOCKAA write writeA 1 A 1 UNLOCK UNLOCKAA LOCK LOCKBB write writeB 2 B 2 UNLOCK UNLOCKBB User 2 LOCK LOCKAA write writeA 3 A 3 UNLOCK UNLOCKAA LOCK LOCKBB write writeB 4 B 4 UNLOCK UNLOCKBB What is wrong time Serializability Solution two phase locking Lock everything at the beginning Unlock everything at the end Read locks many simultaneous read locks allowed Write locks only one write lock allowed Insert locks one per table Isolation Levels in SQL 1 Dirty reads SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 2 Committed reads SET TRANSACTION ISOLATION LEVEL READ COMMITTED 3 Repeatable reads SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 4 Serializable transactions default SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Reading assignment chapter 8 6 Relational Algebra Formalism for creating new relations from existing ones Its place in the big picture Declartive Declartive query query language language SQL relational calculus Algebra Algebra Relational algebra Relational bag algebra Implementation Implementation Relational Algebra Five operators Union Difference Selection Projection Cartesian Product Derived or auxiliary operators Intersection complement Joins natural equi join theta join semi join Renaming 1 Union and 2 Difference R1 R2 Example ActiveEmployees RetiredEmployees R1 R2 Example AllEmployees RetiredEmployees What about Intersection It is a derived operator R1 R2 R1 R1 R2 Also expressed as a join will see later Example UnionizedEmployees RetiredEmployees 3 Selection Returns all tuples which satisfy a condition Notation c R Examples Salary 40000 Employee Employee name Smithh The condition c can be Selection Example Employee SSN 999999999 777777777 888888888 Name John Tony Alice DepartmentID 1 1 2 Salary 30 000 32 000 45 000 Find all employees with salary more than 40 000 Employee Salary 40000 SSN Name 888888888 Alice DepartmentID 2 Salary 45 000 4 Projection Eliminates columns then removes duplicates Notation A1 An R Example project social security number and names SSN Name Employee Output schema Answer SSN Name Projection Example Employee SSN 999999999 777777777 888888888 Name John Tony Alice DepartmentID 1 1 2 SSN Name Employee SSN 999999999 777777777 888888888 Name John Tony Alice Salary 30 000 32 000 45 000 5 Cartesian Product Each tuple in R1 with each tuple in R2 Notation R1 R2 Example Employee Dependents Very rare in practice mainly used to express joins Cartesian Product Example Employee Name John Tony SSN 999999999 777777777 Dependents EmployeeSSN 999999999 777777777 Dname Emily Joe Employee x Dependents Name SSN EmployeeSSN John 999999999 999999999 John 999999999 777777777 Tony 777777777 999999999 Tony 777777777 777777777 Dname Emily Joe Emily Joe Relational Algebra Five operators Union Difference Selection Projection Cartesian Product Derived or auxiliary operators Intersection complement Joins natural equi join theta join semi join Renaming Renaming Changes the schema not the instance Notation B1 Bn R Example LastName SocSocNo Employee Output schema Answer LastName SocSocNo Renaming Example Employee Name John Tony SSN 999999999 777777777 LastName SocSocNo Employee LastName John Tony SocSocNo 999999999 777777777 Natural Join Notation R1 R2 Meaning R1 R2 A C R1 R2 Where The selection C checks equality of all common attributes The projection eliminates the duplicate common attributes Natural Join Example Employee Name John Tony SSN 999999999 777777777 Dependents SSN 999999999 777777777 Dname Emily Joe Employee Dependents Name SSN Dname SSN SSN2 Employee x SSN2 Dname Dependents Name John Tony SSN Dname 999999999 Emily 777777777 Joe Natural Join R S A B B C X Y Z U X Z V W Y Z Z V Z V R S A B C X Z U X Z V Y Z U Y Z V Z V W Natural Join Given the schemas R A B C D S A C E what is the schema of R S Given R A B C S D E what is R S Given R A B S A B what is R S Theta Join A join that involves a predicate R1 R2 R1 R2 Here can be any condition Eq join A theta join where is an equality R1 A B R2 A B R1 R2 Example Employee SSN SSN Dependents Most useful join in practice Semijoin R S A1 An R S Where A1 An are the attributes in R Example Employee Dependents Semijoins in Distributed Databases Semijoins are used in distributed databases Dependents Employee SSN Name SSN network Dname Age Employee Dependents Employee ssn ssn age 71 ssn ssn age 71 Dependents R Employee T T SSN age 71 Dependents Answer R Dependents Complex RA Expressions name buyer ssn ssn pid pid seller ssn ssn ssn Person Purchase pid name fred name gizmo Person Product Operations on Bags A bag a set with repeated elements All operations need to be defined carefully on bags a b b c a b b b e f f a a b b b b b c e f f a b b b c c b b c c c d a b C R preserve the number of occurrences A R no duplicate elimination Cartesian product join no duplicate elimination Important Relational Engines work on bags not sets Reading assignment 5 3 5 4 Finally RA has Limitations Cannot compute transitive closure Name1 Name2 Relationship Fred Mary Father Mary Joe Cousin Mary Bill Spouse Nancy Lou Sister Find all direct and indirect relatives of Fred Cannot express in RA Need to write C program XML XML eXtensible Markup Language XML 1 0 a recommendation from W3C 1998 Roots SGML a very nasty language After the roots a format for sharing data Why XML is of Interest to Us XML is just syntax for data Note we have no syntax for relational data But XML is not relational semistructured This is exciting because Can translate any data to XML Can ship XML over the Web HTTP Can input XML into any application Thus data sharing and exchange on the Web XML Data Sharing and Exchange application application


View Full Document

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
Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
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 2 2 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?