Advanced Database Systems

1. Sekolah Tinggi Ilmu Statistik (STIS) Advanced Database Systems

2. Main Topics • Denormalizing and introducing controlled redundancy • Meaning of denormalization. • When to denormalize to improve performance. • Monitoring the system to improve performance • Importance of monitoring and tuning the operational system. • How to measure efficiency. • How system resources affect performance. Dr. Said Mirza Pahlevi, M.Eng.

3. Denormalizing and introducing controlled redundancy First Topic Dr. Said Mirza Pahlevi, M.Eng.

4. Introduction of Controlled Redundancy To determine whether introducing redundancy in a controlled manner by relaxing normalization rules will improve the performance of the system. Dr. Said Mirza Pahlevi, M.Eng.

5. Introduction of Controlled Redundancy Sometimes a normalized database does not provide maximum processing efficiency. We may accept loss of some benefits of a fully normalized design in favor of performance. To determine whether introducing redundancy in a controlled manner by relaxing normalization rules will improve the performance of the system. Result of normalization is a design that is structurally consistent with minimal redundancy. Dr. Said Mirza Pahlevi, M.Eng.

6. Effect of Denormalization • Makes implementation more complex; • Often sacrifices flexibility; • May speed up retrievals but it slows down updates. Dr. Said Mirza Pahlevi, M.Eng.

7. Example: Data Redundancy Dr. Said Mirza Pahlevi, M.Eng.

8. What is Denormalization? Refers to a refinement to relational schema such that the degree of normalization for a modified relation is less than the degree of at least one of the original relations. Also use term more loosely to refer to situations where two relations are combined into one new relation, which is still normalized but contains more nulls than original relations. Dr. Said Mirza Pahlevi, M.Eng.

9. Denormalization Situation • Consider denormalization to speed up frequent or critical transactions: • Combining 1:1 relationships • Duplicating non-key attributes in 1:* relationships to reduce joins • Duplicating foreign key attributes in 1:* relationships to reduce joins • Duplicating attributes in *:* relationships to reduce joins • Introducing repeating groups • Creating extract tables Dr. Said Mirza Pahlevi, M.Eng.

10. Sample Relation Diagram Dr. Said Mirza Pahlevi, M.Eng.

11. Sample Relations Dr. Said Mirza Pahlevi, M.Eng.

12. 1. Combining 1:1 relationships When Client and Interview tables are frequently referenced together and infrequently referenced separately Dr. Said Mirza Pahlevi, M.Eng.

13. 2. Duplicating non-key attributes in 1:* relationships to reduce joins • When PropertyForRent table is accessed, it is very common for the owner’s name to be accessed at the same time. • SELECTp.*, o.lNameFROMPropertyForRentp, PrivateOwneroWHEREp.ownerNo=o.ownerNo AND branchNo=‘B003’ • SQL after denormalization (no name will be change) • SELECT p.* FROMPropertyForRent p WHEREbranchNo=‘B003’ Dr. Said Mirza Pahlevi, M.Eng.

14. Duplicating non-key attributes in 1:* relationships: Lookup Table • If lookup table is used frequent or critical queries, and the description is unlikely to change. Dr. Said Mirza Pahlevi, M.Eng.

15. After Duplicating non-key attributes • Note: the lookup table can still be used to validate user input Dr. Said Mirza Pahlevi, M.Eng.

16. 3. Duplicating FK attributes in 1:* relationship to reduce joins • When frequent query is to list all private prop. owners at a branch • SELECTo.lNameFROMPropertyForRent p, PrivateOwner o WHEREp.ownerNo= o.OwnerNo AND branchNo=‘B003’ • After duplicating FK. • SELECTo.lNameFROMPrivateOwner o WHEREbranchNo=‘B003’ Dr. Said Mirza Pahlevi, M.Eng.

17. 4. Duplicating attributes in *:* relationships to reduce joins • Sales staff should contact clients without comment on the properties and he needs only street of the property. • SELECTp.street, c.*, v.viewDateFROM Client c, Viewing v, PropertyForRent p WHEREv.propertyNo=p.propertyNo AND c.clientNo=v.clientNo AND comment IS NULL • After duplicating the attributes. • SELECTv.street, c.*, v.viewDateFROM Client c, Viewing v WHEREc.clientNo=v.clientNo AND comment IS NULL Dr. Said Mirza Pahlevi, M.Eng.

18. 5. Introducing repeating groups • When access to tel. no is important and frequent. • Use this when: • The absolute number of items in the group is known • The number is static • The number is not very large (less than 10) Dr. Said Mirza Pahlevi, M.Eng.

19. 6. Creating extract tables Reports can access derived data and perform multi-relation joins on same set of base relations. However, data the report is based on may be relatively static or may not have to be current. Possible to create a single, highly denormalized extract table based on relations required by reports, and allow users to access extract table directly instead of base relations. Dr. Said Mirza Pahlevi, M.Eng.

20. Advantages and disadvantages of denormalization Dr. Said Mirza Pahlevi, M.Eng.

21. Monitoring the System to Improve Performance Second Topic Dr. Said Mirza Pahlevi, M.Eng.

22. Monitor & Tune Operational System To monitor operational system and improve performance of system to correct inappropriate design decisions or reflect changing requirements. Dr. Said Mirza Pahlevi, M.Eng.

23. Monitor & Tune Operational System • Number of factors may be used to measure efficiency: • Transaction throughput: number of transactions processed in given time interval. • Response time: elapsed time for completion of a single transaction. • Disk storage: amount of disk space required to store database files. • Have to trade each off against another to achieve reasonable balance. • Need to understand how the various hardware components interact and affect database performance. Dr. Said Mirza Pahlevi, M.Eng.

24. Consideration Points • It is sensible always to have a minimum of 5% of main memory available. • It is necessary to understand the typical workload through 24 hour period to ensure sufficient resources are available • Storage should evently distributed to reduce disk contention • Separate OS, main database files, index files and recovery log files. Dr. Said Mirza Pahlevi, M.Eng.