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 reflect 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 first 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 predefined time, all the transactions from the WAL are written in order of their occurrence to the persistent storage.
View Full Document