Controlled Redundancy redundancy 1 Database Design Methodology l

Controlled Redundancy redundancy 1

Database Design Methodology l l l l Create and check ER model Map ER model to tables Translate logical database design for target DBMS Choose file organizations and indexes Design user views Design security mechanisms Consider the introduction of controlled redundancy Monitor and tune the operational system redundancy 2

Objectives l l l Understanding meaning of denormalization. When to denormalize to improve performance. Importance of monitoring and tuning the operational system. redundancy 3

Denormalization l l Refinement to relational schema such that the degree of normalization for a modified table is less than the degree of at least one of the original tables. Also use term more loosely to refer to situations where two tables are combined into one new table, which is still normalized but contains more nulls than original tables. redundancy 4

Controlled Redundancy l l l Determine whether introducing redundancy in a controlled manner by relaxing the normalization rules will improve system performance. Normalization results in a logical database design that is structurally consistent and has minimal redundancy. However, sometimes a normalized database design does not provide maximum processing efficiency. May be necessary to accept loss of some of the benefits of a fully normalized design in favor of performance. Also consider that denormalization: l makes implementation more complex; l often sacrifices flexibility; l may speed up retrievals but it slows down updates. redundancy 5

Controlled Redundancy (cont. ) l Consider denormalization in following situations, specifically to speed up frequent or critical transactions: Step 7. 1 Combining 1: 1 relationships Step 7. 2 Duplicating nonkey columns in 1: * relationships to reduce joins Step 7. 3 Duplicating FK columns in 1: * relationships to reduce joins Step 7. 4 Duplicating columns in *: * relationships to reduce joins Step 7. 5 Introducing repeating groups Step 7. 6 Creating extract tables Step 7. 7 Partitioning tables. redundancy 6

Combining 1: 1 relationships redundancy 7

Duplicating nonkey columns in 1: * relationships to reduce joins SELECT vfr. *, v. daily. Rental FROM Video. For. Rental vfr, Video v WHERE vfr. catalog. No = v. catalog. No AND branch. No = ‘B 001’; redundancy SELECT vfr. * FROM Video. For. Rental vfr WHERE branch. No = ‘B 001’; 8

Duplicating Lookup Table Column redundancy 9

Duplicating FK columns in 1: * relationship to reduce joins SELECT ra. * FROM Rental. Aggrement ra, Video. For. Rental vfr WHERE ra. video. No = vfr. video. No AND vfr. branch. No = ‘B 001’; SELECT * FROM Rental. Aggrement WHERE branch. No = ‘B 001’; l redundancy This only works because the new relationship between Branch and Rental. Aggrement is 1: *. 10

Cannot Duplicate FK columns in *: * relationship Video Is 1. . 1 1. . * Video. For. Rent Is. Allocated 1. . * l l l Branch 1. . 1 List the video titles in stock at a branch. SELECT v. title FROM Video v, Video. For. Rent vfr WHERE v. catalog. No = vfr. catalog. No AND vfr. branch. No = ‘B 001’; Cannot add the branch. No column to the Video table. But we could consider duplicating the title column of the Video table in the Video. For. Rent table, although the increased storage may be more significant. redundancy 11

Duplicating columns in *: * relationships to reduce joins l Lists the video titles and roles that each actor has starred in. SELECT v. title, a. *, r. * FROM Video v, Role r, Actor a WHERE v. catalog. No = r. catalog. No AND r. actor. No = a. actor. No; SELECT a. *, r. * FROM Role r, Actor a WHERE r. actor. No = a. actor. No; redundancy 12

Introducing repeating groups Branch(branch. No, street, city, state, zip. Code, mgr. Staff. No) Telephone(tel. No, branch. No) Duplicating Tel. No columns in Branch: Branch(branch. No, street, city, state, zip. Code, tel. No 1, tel. No 2, tel. No 3, mgr. Staff. No) l redundancy 13

Creating extract tables l l Reports can access derived data and perform multi-table joins on same set of base tables. However, data report based on may be relatively static or may not have to be current. Can create a single, highly denormalized extract table based on tables required by reports, and allow users to access extract table directly instead of base tables. redundancy 14

Partitioning tables l l Rather than combining tables, could decompose a table into a smaller number of partitions. Horizontal partition: distribute records across a number of (smaller) tables. Vertical partition: distribute columns across a number of (smaller) tables. PK duplicated to allow reconstruction. Partitions useful for applications that store and analyze large amounts of data. redundancy 15

Partitioning tables (cont. ) redundancy 16

Partitioning tables (cont. ) l Advantages: l l l Improved load balancing Improved performance Increased availability Improved recovery Security. Disadvantages: l l l redundancy Complexity Reduced performance Duplication. 17