Physical database design-3.pptx

Physical database design

1. Physical Database Design(cont.) Lecture:28 Instructor: Irfan Ahmed

2. Vertical Partitioning Same table is split into different physical records; depending on the nature of accesses PK is repeated in all vertical partitions of a table to get the original table

3. Vertical Partitioning STD(stId, sName, sAdr, sPhone, cgpa, prName, school, mtMrks, mtSubs, clgName, intMarks, intSubs, dClg, bMarks, bSubs) STD(stId, sName, sAdr, sPhone, cgpa, prName) STDACD(sId, school, mtMrks, mtSubs, clgName, intMarks, intSubs, dClg, bMarks, bSubs)

4. Replication Final form of denormalization Data duplicated Increases the access speed and failure damage Entire table or part of table can be replicated

5. Replication Example Generally adopted where updation is not very frequent In PROJ-EMP we replicate the data with both the PROJ and EMP tables so on the cost of extra storage both tables have the related data

6. Clustering Files Clustering means to place records from different tables to place in adjacent physical locations, called clusters Increases efficiency since related records are placed close to each other

7. Clustering Files Clustering is also suitable to relatively static situations Define cluster, define the key of the cluster, include the tables into the cluster while creating associating the key with it

8. Other Related Issues To be discussed later during storage concepts discussion File structures Indexes RAID

9. Summary of Physical DB Design

10. THANK YOU!