1 Optimize the following SQL query on the Company Database to find names of employees earning over 80 000 per year names of projects in which they work more than 30 hours where the project is located in Chicago Select Lname Fname Pname Hours From Project P Employee E Department D Works on W Where E Ssn W Essn and P Dnum D Dnumber and W Pno P Pnumber and Plocation Chicago and Hours 30 and Salary 80000 Show the query tree after each major step of the algorithm 2 Define the following terms Catastrophic failure System log ACID properties Catastrophic failure Catastrophic failure is a complete sudden often unexpected breakdown in a machine electronic system computer or network Sometimes a single component in a critical location fails resulting in downtime for the entire system System log System maintains a log file to keep track of all the transactions that occur In case of system failure or if the database is found in inconsistent state then the database uses these logs to roll back the changes made by the uncommitted transactions and bring the database to the consistent state ACID Atomicity A series of transactions occur or nothing occurs This property of the RDBMS doesnt allow partial transactions to re ect on database If the series of transactions is carried out without interruption then the transactions return with COMMIT message indicating the success of the operation If an interruption is encountered then the operations till that point are ROLLBACK ed ensuring the atomicity Consistency Database consistency is maintained by enforcing constraints If accessing a value is assured to return a valid value to user then the database is said to be consistent Consistency is another form of RDBMS to maintain Data Integrity Isolation If concurrent transactions are going on the same tuple then the resultant is not predictable Hence the concurrent transactions are performed one after the other such that their resultant is the same as the result if they were executed serially The transaction rst obtains lock for that tuple and performs its operations while other operations are waiting to gain lock for the same tuple Once the rst transaction releases the lock second transaction gains the lock and continues its operations Durability Durability means that once a transaction has been committed it will remain to even in the event of power loss crashes or errors Committed transactions are written to the Write Ahead Log After a interval of prede ned time all the transactions from the WAL are written in order of their occurence to the persistent storage
View Full Document
Unlocking...