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 reflect 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 ROLLBACKed 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 first obtains lock for that tuple and performs its operations while other operations are waiting to gain lock for the same tuple. Once the first 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 predefined time, all the transactions from the WAL are written in order of their occurence to the persistent storage.
View Full Document
Unlocking...