About physical design关于物理的设计.pptVIP

About physical design After you have provided your scripts Understand the problems Present a template that can be used to report on the physical design of a DB Context: Analysis of the full process of db development Earlier stages Additional constraints / requirements Volume Analysis Estimates of average and max values for object sizes Number of instances per entity Realistic storage requirements Day one Growth over time Headings: row max, row avg., record size, table max, table avg [characters] Usage analysis (internal constraints) Identify major transactions to hit each table and their frequency Insertions / updates / retrievals / deletes or mixtures of all 4 Eg: register a new student day one or year 1 into year 2… Compute Volatile vs non-volatile tables Turnover / population (eg over month or year) Bipolar distribution (1 or 100) Transactional analysis (other constraints) Documenting critical transactions to hit DB Impact on systems resources Objects accessed and type of access Any constraints placed on processing Eg: time Top 20% most important transactions Transactional map Example – students per course Query Access Requirements Type of access + type of usage = headache Eg: indexes both increase and decrease performance Conflict between reporting / retrieval and transaction processing (OLTPs) Time sharing or avoidance (forbidden queries) Integrity Analysis Traditional DB territory Entity constraints (primary key – no duplicates) Referential integrity (foreign key ref is not null and valid) Domain constraints (credit limit numeric + accepted value [1000, 5000, 15000]) Enforcing those = big hit on performance (cascading updates) Security analysis Outside frame of DB = physical access to building, logon procedures etc… Difficult to contemplate DB security on its own Eg: operating systems level Large scale DBMSs provide specific utilities Preventing unauthorised access to data Preventing usage of DBMS functions Use usage analysis to derive user profiles with asso