Physical database design 1.pptx

Physical database design

1. Physical Database Design Lecture:26 Instructor : Irfan Ahmed

2. Objective Basic goal is data processing efficiency Transforms logical DB design into technical specifications for storing and retrieving data Does not include practically implementing the design however tool specific decisions are involved

3. Inputs Required Normalized relations Definitions of each attribute Descriptions of data usage Requirements for response time, data security, backup etc. Tool to be used

4. Decisions Involved Choosing data types Grouping attributes (although normalized) Deciding file organizations Selecting structures Preparing strategies for efficient access

5. Data Volume and Usage Analysis Statistics about the size and usage of data plays critical role in data processing efficiency Final step of logical DB design or first step in physical DB design

6. Data Volume and Usage Analysis Statistics collected during analysis phase from the users of the system May not be accurate; give the tentative and relevant figures

7. Composite Usage Map PART 1000 MANUFAC-TURED PART PURCHASED PART O QUOTATION SUPPLIER 2500 40 % 70 % 50 140 200 60 40 80 75 (50) 40 70 700 400

8. Designing Fields Field is smallest unit of application data; corresponds to a simple attribute Involves different decisions about fields

9. Choosing Data Type Data type is defined as set of values along with the operations that can be performed on them Precisely depends on the particular DBMS

10. Choosing Data Types Involves four objectives Minimize storage space Represent all possible values Improve data integrity Support all data manipulation

11. Coding Techniques Values of the attributes with small domains can be replaced by codes Codes can be stored in lookup table or can be hard coded, example

12. Coding Example stId stName hobby S1020 Sohail Dar Reading S1038 Shoaib Ali Gardening S1015 Tahira Ejaz Reading S1015 Tahira Ejaz Movies S1018 Arif Zia Reading STUDENT

13. Coding Example stId stName hobby S1020 Sohail Dar R S1038 Shoaib Ali G S1015 Tahira Ejaz R S1015 Tahira Ejaz M S1018 Arif Zia R code Hobby R Reading G Gardening M Movies STUDENT HOBBY

14. Controlling Data Integrity Concerns the possible values that a field can assume First such control is enforced by the data type Some others are…

15. Controlling Data Integrity Default value Range control Null values Referential integrity Handling missing data

16. THANK YOU!