Denormalization to Maintain History - Assignment Example

Database Systems YourFirst YourLast CH20: PHYSICAL DATABASE DESIGN AND TUNING #20.5. Denomalization can be used when there is need for improved performance, when there is need for fewer joins and there is need to maintain history information Examples: Using the following normalized design Job(JobId, JobName, CustomerId) JobDetails(JobId, ItemId, ActivityId, EmployeeId, WorkDate, TimeSpent) Customers(CustomerId, CustomerName) Activities(ActivityId, ActivityName) Employees(EmployeeId, EmployeeName) Denormalization to improve performance For instance you might have a problem with the report that calculates total hours spent on a Job. To improve performance, you combine the Job details row. This is done by adding a new column to hold the total time spent in Jobs table. Hence add a column called TotalTimeSpent. Job(JobId, JobName, CustomerId, StartDate, CustomerName, TotalTimeSpent) Denormalization to Maintain History For instance, when you are told to maintain a customer name since you started a job on the customer and the customer might change their name. Since you maintain only the customer’s current name in the customers table, you have to add the customer name to the Job table. You also add a start date to record when the name became valid Job(JobId, JobName, CustomerID, StartDate, CustomerName) #20.8 – Illustrate the types of changes to SQL queries that may be worth considering for improving the performance during database tuning. Answer: 1.Minimize the use of GROUP BY and HAVING: Example: Instead of: SELECT MIN (E.age) FROM Employee E GROUP BY E.dno HAVING E.dno=130 Rewrite the query as: SELECT MIN (E.age) FROM Employee E WHERE E.dno=130 2. Avoid using intermediate relations. Instead of using two queries like these SELECT * INTO Temp FROM Employee E, Department D WHERE E.departmentNo=D.departmentNo AND D.managername=‘Tony’ and SELECT T.departmentNo, AVG(T.salary) FROM Temp T GROUP BY T.departmentNo You can write the query as SELECT E.departmentno, AVG(E.salary) FROM Employee E, Department D WHERE E.departmentNo=D.departmentNo AND D.managername=‘Tony’ GROUP BY E.departmentNo 3. Use only one query block, if possible .Avoid sub-queries. Instead of: SELECT DISTINCT * FROM Sailors S WHERE S.sname IN (SELECT Y.sname FROM YoungSailors Y) You can rewrite the query as: SELECT DISTINCT S.* FROM Sailors S, YoungSailors Y WHERE S.sname = Y.sname 4. Minimize the use of DISTINCT keyword. You don’t need it if duplicates are acceptable, or if answer contains a key. 5. If multiple options for a join condition are possible, choose one that uses a clustering index and avoid those that contain string comparisons. For example, assuming that the Name attribute is a candidate key in EMPLOYEE and STUDENT, it is better to use EMPLOYEE.Ssn = STUDENT.Ssn as a join condition rather than EMPLOYEE.Name = STUDENT.Name if Ssn has a clustering. In some situations involving the use of correlated queries, temporaries are useful. Consider the following query, which retrieves the highest paid employee in each department: SELECT Ssn FROM EMPLOYEE E WHERE Salary = SELECT MAX (Salary) FROM EMPLOYEE AS M WHERE M.Dno = E.Dno; This has the potential danger of searching all of the inner EMPLOYEE table M for each tuple from the outer EMPLOYEE table E. To make the execution more efficient, the process can be broken into two queries, where the first query just computes the maximum salary in each department as follows: SELECT MAX (Salary) AS High_salary, Dno INTO TEMP FROM EMPLOYEE GROUP BY Dno; SELECT EMPLOYEE.Ssn FROM EMPLOYEE, TEMP WHERE EMPLOYEE.Salary = TEMP.High_salary AND EMPLOYEE.Dno = TEMP.Dno; CH 21: INTRODUCTION TO TRANSACTION PROCESSING CONCEPTS AND THEORY #21.14 - Change transaction T 2 in Figure 21.2b to read: read_item(X); X:= X+M; if X > 90 then exit else write_item(X);   Discuss the final result of the different schedules in Figure 21.3 (a) and (b), where M = 2 and N = 2, with respect to the following questions. Does adding the above condition change the final outcome? Does the outcome obey the implied consistency rule (that the capacity of X is 90)? Answer: The given condition in the transaction does not cause any change in the outcome result (It changes the outcome only when the initial value of X is greater than 88). On the other hand, the outcome obeys the implied consistency rule that X< 90, because the value of X is not updated when it’s greater than 90.  #21.18 - How many serial schedules exist for the three transactions in Figure 21.8 (a)? What are they? What is the total number of possible schedules? Answer: Number of serial schedules for n transactions is n! T1 T2 T3 T3 T2 T1 T2 T3 T1 T2 T1 T3 T3 T1 T2 T1 T3 T2 Total number of serial schedules for the three transactions = 3*2*1= 6 #21.20 - Why is an explicit transaction end statement needed in SQL but not an explicit begins statement? Answer: Transaction is any atomic operation in the database management. There is only one way to begin a transaction which is “Begin Transaction” command. However, a transaction could end in two ways, When it “Commits” that is all changes to the database are registered or “Rollback” when the chances are ignored and database returns to original state. With these two ways, the database may never know when a transaction ends hence the need to specify the “End” command. CH 22: CONCURRENCY CONTROL TECHNIQUES #22.22 - Prove that strict two-phase locking guarantees strict schedules Answer: Since no other transaction can read or write an item written by a transaction T until transaction T has committed, the condition for a strict schedule is satisfied. #22.26 - Repeat Exercise 22.25, but use the multi-version timestamp ordering method. Answer: Let us assume a clock with linear time points 0, 1, 2, 3, ..., and that the original read and write timestamps of all items are 0 read_TS(X) = read_TS(Y) = read_TS(Z) = 0 write_TS(X) = write_TS(Y) = write_TS(Z) = 0 Let the schedules in Figure 21.8(b) Schedule E or SE, and that in Figure 21.8(c) Schedule F or SF. The two schedules can be written as follows in shorthand notation: SE: r2(Z); r2(Y); w2(Y); r3(Y); r3(Z); r1(X); w1(X); w3(Y); w3(Z); r2(X); r1(Y); w1(Y); w2(X); 1 2 3 4 5 6 7 8 9 10 11 12 13 SF: r3(Y); r3(Z); r1(X); w1(X); w3(Y); w3(Z); r2(Z); r1(Y); w1(Y); r2(Y); w2(Y); r2(X); w2(X); 1 2 3 4 5 6 7 8 9 10 11 12 13 Assume that each operation takes one time unit, so that the numbers under the operations indicate the time when each operation occurred. Also assume that each transaction timestamp corresponds to the time of its first operations in each schedule, so the transaction timestamps are as follows Schedule E Schedule F TS(T1) = 6 TS(T1) = 3 TS(T2) = 1 TS(T2) = 7 TS(T3) = 4 TS(T3) = 1 To refer to versions, use X, Y, Z to reference the original version (value) of each item, and then use indexes (1, 2, ...) to refer to newly written version (for example, X1, X2, ...). (a) Applying multiversion timestamp ordering to Schedule E: Initial values (new values are shown after each operation): read_TS(X)=0,read_TS(Y)=0,read_TS(Z)=0,write_TS(X)=0,write_TS(Y)=0,write_TS(Z)=0 TS(T1)=6, TS(T2)=1, TS(T3)=4 (These do not change) T2: read_item(Z) Execute read_item(Z) Set read_TS(Z) Read More