1-Describe situations in which denormalization of a database schema would be use
ID: 3604353 • Letter: 1
Question
1-Describe situations in which denormalization of a database schema would be used? Answer in your own words and give examples.
2-Give a brief explanation of why the following query may not be promoting the query optimizer to use any index and show how it can be improved to be able to use an index.
SELECT ESSN, FName, LName, Salary, Age
FROM EMPLOYEE
WHERE Age < 35 OR Salary < 2000
3-What is a schedule? How many number of serial schedules can be formed with 3 transactions? Write down all serial schedule.
4-Check whether the schedules is conflict serializable or not? If yes, then why?
Explain by drawing precedence graph.
a) S: R2(A); W2(A); R3(C); W2(B); W3(A); W3(C);
R1(A); R1(B); W1(A); W1(B)
b) S: R2(A); R3(C); W3(A); W2(A); W2(B); W3(C);
R1(A); R1(B); W1(A); W1(B)
Explanation / Answer
Answering only the first question since multiple questions are given.
1. Denormalization of a database schema is done primarily to improve performance of database queries. In other words it is used to decrease the running time of select queries. For example if it is found that the performance is hit due to non-availability of data for select quesries it may be a good choice to introduce some redundancies in the schema to make the queries faster.
In the process we may use extra attributes in an existing table, add new tables, or even create instances of existing tables. Sometimes having a duplicate extra table may help in maintaining history for several information. For example the value of an attribute in a table can change. Task details should contain values that were actual at the moment a task was generated. We wouldn’t be able to recreate past data correctly if this didn’t happen. This problem can be solved by adding a table containing the history of these changes.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.