Page 231 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes create a new table partition when a predetermined size is reached. It is somewhat complex and
requires meta data to identify what data is stored in each partition.

Partitioning Dimensions

In some data warehouses dimensions may contain large number of entries that may need to be
partitioned in the same as a fact table.

Example: Consider a large dimension which varies depending on time and you got to
store all the changes and variations of that dimension for business logics, that dimension may
become quite large.

It may affect query response time. In these situations the dimension should be partitioned
depending on the level in hierarchy. In general this technique is appropriate are unusual. So,
always check in the dimension table may not contain embedded facts that are causing unnecessary
rows to be added.

Using Round-robin Partitions

In this technique where the data warehouse is holding the complete historical complement of
information, a new partition is creating archiving and reusing the old partition for the latest
data.
Meta data is used to allow user to access tools to refer to the appropriate partition. Warehouse
manager creates a specific table names such as sales_last_month or sales_moth_to_date, which
represents the data content of a physical partition.

Table management facility is easy in the data warehouse with this technique by allowing the
system to refer to the same physical table partitions. The information can change periodically
but this can be managed by using appropriate meta data.

13.2.2 Vertical Partitioning

In this partitioning data splits vertically and the process is as shown in figure below. This
process takes two forms; they are normalization and row splitting.

Normal means making ‘right’, here it is concern to data placing in right position. Normalization
allows common fields to be collapsed into single rows by which reducing space usage.
Removal of redundancy is done through normalization of a table. Let us take PDPL company
produces drugs they keep track of daily sales in the form of a table which consist of p_id, qty,
rate, sale_on, vendor_id, vendorName, loc, region. Sample of data as given below:

P_id qty rate Sale_on Vendor_id vendorName Loc Region
898 5 9.90 24-JAN-03 1111 Radha Krishna GopalPatnam Vizag
987 4 89.00 24-1AN-03 2222 Raj shekhar ReinBazar Hyd
342 6 44.30 27-JAN-03 1111 Radha Krishna GopalPatnam Vizag
433 2 300.00 29-JAN-03 1111 Radha Krishna GopalPatnam Vizag

224 LOVELY PROFESSIONAL UNIVERSITY