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 a Initial canonical query tree for SQL query Q b Moving SELECT operations down the query tree d Replacing CARTESIAN PRODUCT and SELECT with JOIN operations e Moving PROJECT operations down the query tree 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 doesn t allow partial transactions to re ect on database 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 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 This way the transactions are carried out in isolation Durability Durability means that once a transaction has been committed it will remain that way even in the event of power loss crashes or errors Committed transactions are written to the Write Ahead Log After an interval of prede ned time all the transactions from the WAL are written in order of their occurrence to the persistent storage
View Full Document
Unlocking...