Oracle Notes
Some of my notes
Schedule: A list of actions (reading, writing, aborting, committing), from a set of transactions Transactions: A unit of work performed with a dbms and treated in a coherent and reliable way independent of the other transactions. Transactions must have ACID properties (atomic, consistent, isolated and durable). Must provide an "all or nothing" approach where each work unit performed in the database must complete in its entirety or have no effect whatsoever. Each transaction must be isolated from the other transactions. Types of schedules: Serial: Transactions are executed such that they are non-interlaced. All steps of the transaction occur consecutively. Serializable: A schedule that is equivalent to a serial schedule. Conflicting Actions: if the actions belong to different transactions at least one of the actions is a write operation the actions access the same object (read or write) These actions are conflicting T1: R(X), T2: W(X), T3: W(X) ************************ ******************************** Drawing Precedence Graphs - Precedence Graph Rules http://db.grussell.org/section012.html (confusing way of stating the rules there) If given a list, separate into the elements that are getting worked on and list the transactions. Then follow these rules A: r1, w2 draw an arrow from T1 to T2 A: w2, w1 draw an arrow from T2 to T1 A: w3, r1 draw an arrow from T3 to T1 Essentially, every time you do something to the transaction, except for read-reads, you just want to draw an arrow from the first transaction in the list to the second transaction. If the graph has a cycle then it is NOT conflict-serializable. If the graph is acyclic (no cycle) then the schedule is conflict-serializable. To find equivalent conflict serializable schedules (only in acyclic graphs), start at the T where there is no arrows pointing to it and then read the T's following the arrows. Can have many equivalent schedules. **************************** ******************************************** View: Virtual Table Views are used to create tables by pulling data from other tables together. You can do calculations on some data and display the calculations in a view. The view is automatically updated as the data changes. For example, a view could show the names of employees where the salary is < 40,000. Lets call this view 'cheapLabour'. Bob is making 20,000 so his name comes up in the view cheapLabour. Bob gets a raise to 45,000. Checking the view cheapLabour, will now no longer show Bob in it. A view does not exist until it is called to existence in a query. A view is not stored on disk, it only exists as a definition. This definition defines what to data to pull from tables and display it to the user as a view. A view looks just like a table. You can do almost anything you can do with a regular table in a view. However, you cannot update a view like a table. Since the view is does not exist on the disk, you cannot update the view. (SQL for Dummies, notes and my own checking) To create a view, CREATE VIEW cheapLabour AS SELECT name FROM employees WHERE salary<40000; To drop a view, DROP VIEW cheapLabour (notes) Materialized View: Similar to a view except can be stored and updated periodically. MySQL does not support this.