StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Denormalization to Maintain History - Assignment Example

Cite this document
Summary
The paper "Denormalization to Maintain History" states that 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 the Jobs table…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER95.2% of users find it useful
Denormalization to Maintain History
Read Text Preview

Extract of sample "Denormalization to Maintain History"

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
Cite this document
  • APA
  • MLA
  • CHICAGO
(“Assignment #3 Example | Topics and Well Written Essays - 500 words”, n.d.)
Assignment #3 Example | Topics and Well Written Essays - 500 words. Retrieved from https://studentshare.org/information-technology/1635700-assignment-3
(Assignment #3 Example | Topics and Well Written Essays - 500 Words)
Assignment #3 Example | Topics and Well Written Essays - 500 Words. https://studentshare.org/information-technology/1635700-assignment-3.
“Assignment #3 Example | Topics and Well Written Essays - 500 Words”, n.d. https://studentshare.org/information-technology/1635700-assignment-3.
  • Cited: 0 times

CHECK THESE SAMPLES OF Denormalization to Maintain History

Relational Database System Design

ECT is a project for an organization that deals with the stocking and selling of clothing products.... The paper "Relational Database System Design" discusses details about the companies that use the ECT strategy and the challenges, which the organizations face.... ... ... ... ECT uses the keywords to create and to manage the data elements and the data about its products and relevant transactions....
4 Pages (1000 words) Admission/Application Essay

Equality and Diversity Are Mutually Exclusive

The paper "Equality and Diversity Are Mutually Exclusive" describes that diversity can be utilized as a means of impacting equity in treatment irrespective of individual differences.... Utilizing the basic tenets of diversity, one is made cognizant of the differences in individuals.... ... ... ... Coordinating the attention to diversity management would imply reorganization of the mentoring system, but only after systematic attention is given to promotional career lad­ders for women....
8 Pages (2000 words) Essay

Normalization of Group Homes

The paper "Normalization of Group Homes" discusses that I have attempted to chart the development of social and educational practice in relation to persons with special needs due to disabilities and other backgrounds such as social, linguistic, and economic experiences.... ... ... ... Support services which facilitate normal life opportunities for people with disabilities – such as special education services, advocacy, and housing support – are not incompatible with normalization, although some particular services (such as special schools) may actually detract from rather than enhance normal living....
16 Pages (4000 words) Essay

Personalized Web Searching

In addition, these days search history can be also maintained by search engines, which users can see on later days.... In this section, initially, a definition and benefits from the personalized search will be presented than after history and preliminary analysis of some personalized search engines (Google, Yahoo, and MSN) will be presented....
8 Pages (2000 words) Case Study

Demoralization, Disillusionment and the End of Feudalism

The Black Death hails from the Bubonic Plague, which is a fatal disease that kills almost as fast as lightning (Hayden, 'history of The Black Death').... The spread of the disease was fast and crossed countries and continents within months as rat fleas feeding on infected black rats, causing the fleas to hunger for more sources of blood, were transported through ships that sailed from the East to the West (Hayden, 'history of The Black Death')....
5 Pages (1250 words) Essay

Implementation of Database Design in MS Access 2010

Like the table, 'ProductTypes' was extracted out from the table 'Products' to maintain the storage space and avoid redundancy in data.... This paper "Implementation of Database Design in MS Access 2010" presents the database that can be expanded with the induction of additional tables like purchase records, supplier records, and elaborated account details....
3 Pages (750 words) Assignment

Civil war and reconstruction

In the North, there were efforts to limit the spread of slavery by abolishing it, while in the South, people wanted to maintain and expand the institution, hence making slavery a focal point of political crisis, in fact, during 1800-1860, the Southern economy History and Political Science Civil War and Reconstruction There was a significant crisis faced by United s, whereby the Northern and Southern states had become less social, economical and political.... In the North, there were efforts to limit the spread of slavery by abolishing it, while in the South, people wanted to maintain and expand the institution, hence making slavery a focal point of political crisis, in fact, during 1800-1860, the Southern economy built on "the backs of slaves"....
2 Pages (500 words) Term Paper

The Sociology of Deviance

This paper "The Sociology of Deviance" focuses on the fact that one of the attributes of criminology is the exploration for the explanations of crime and deviancy.... The dilemma arises in relation to the core notion of a cause because the term 'cause' was used to a broad array of various concepts....
6 Pages (1500 words) Assignment
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us