Download and Preview : Database Systems Design SUNY Oswego
Report CopyRight/DMCA Form For : Database Systems Design SUNY Oswego
Physical Database Design10 10 2012 ISC239 Isabelle Bichindaritz 1 Learning Objectives Purpose of physical database design How to map the logical database design to.a physical database design How to design base relations for target How to design enterprise constraints fortarget DBMS 10 10 2012 ISC239 Isabelle Bichindaritz 2. Learning Objectives How to select appropriate file organizationsbased on analysis of transactions When to use secondary indexes to improveperformance . How to estimate the size of the database How to design user views How to design security mechanisms to satisfyuser requirements 10 10 2012 ISC239 Isabelle Bichindaritz 3. Acknowledgments Some of these slides have been adapted fromThomas Connolly and Carolyn Begg10 10 2012 ISC239 Isabelle Bichindaritz 4 Comparison of Logical and.Physical Database Design Sources of information for physicaldesign process includes global logicaldata model and documentation thatdescribes model . Conceptual and logical database designare concerned with the what physicaldatabase design is concerned with the10 10 2012 ISC239 Isabelle Bichindaritz 5 Physical Database Design.Process of producing a description of theimplementation of the database onsecondary storage it describes the baserelations file organizations and indexesused to achieve efficient access to the.data and any associated integrityconstraints and security measures 10 10 2012 ISC239 Isabelle Bichindaritz 6 Overview of Physical DatabaseDesign Methodology. Step 4 Translate global logical data model fortarget DBMS Step 4 1 Design base relations Step 4 2 Design representation of derived data Step 4 3 Design enterprise constraints.10 10 2012 ISC239 Isabelle Bichindaritz 7 Overview of Physical DatabaseDesign Methodology Step 5 Design physical representation Step 5 1 Analyze transactions. Step 5 2 Choose file organizations Step 5 3 Choose indexes Step 5 4 Estimate disk space requirements10 10 2012 ISC239 Isabelle Bichindaritz 8 Overview of Physical Database.Design Methodology Step 6 Design user views Step 7 Design security mechanisms Step 8 Consider the introduction ofcontrolled redundancy. Step 9 Monitor and tune the operational10 10 2012 ISC239 Isabelle Bichindaritz 9 Step 4 Translate Global LogicalData Model for Target DBMSTo produce a relational database schema that can.be implemented in the target DBMS from theglobal logical data model Need to know functionality of target DBMS suchas how to create base relations and whether thesystem supports the definition of . PKs FKs and AKs required data i e whether system supports NOT domains relational integrity constraints enterprise constraints .10 10 2012 ISC239 Isabelle Bichindaritz 10 Step 4 1 Design Base RelationsTo decide how to represent base relationsidentified in global logical model in target For each relation need to define . the name of the relation a list of simple attributes in brackets the PK and where appropriate AKs and FKs a list of any derived attributes and how they should be referential integrity constraints for any FKs identified .10 10 2012 ISC239 Isabelle Bichindaritz 11 Step 4 1 Design Base Relations For each attribute need to define its domain consisting of a data type length and anyconstraints on the domain . an optional default value for the attribute whether the attribute can hold nulls 10 10 2012 ISC239 Isabelle Bichindaritz 12 DBDL for the PropertyForRent10 10 2012 ISC239 Isabelle Bichindaritz 13. Step 4 2 Design Representationof Derived DataTo decide how to represent any derived datapresent in the global logical data model in thetarget DBMS . Examine logical data model and datadictionary and produce list of all derivedattributes Derived attribute can be stored in database orcalculated every time it is needed .10 10 2012 ISC239 Isabelle Bichindaritz 14 Step 4 2 Design Representationof Derived Data Option selected is based on additional cost to store the derived data and.keep it consistent with operational data fromwhich it is derived cost to calculate it each time it is required Less expensive option is chosen subject toperformance constraints .10 10 2012 ISC239 Isabelle Bichindaritz 15 PropertyforRent Relation and Staff Relationwith Derived Attribute noOfProperties10 10 2012 ISC239 Isabelle Bichindaritz 16 Step 4 3 Design Enterprise.ConstraintsTo design the enterprise constraints forthe target DBMS Some DBMS provide more facilities thanothers for defining enterprise constraints .CONSTRAINT StaffNotHandlingTooMuchCHECK NOT EXISTS SELECT staffNoFROM PropertyForRentGROUP BY staffNoHAVING COUNT 100 .10 10 2012 ISC239 Isabelle Bichindaritz 17 Step 5 Design PhysicalRepresentationTo determine optimal file organizationsto store the base relations and the.indexes that are required to achieveacceptable performance that is the wayin which relations and tuples will be heldon secondary storage 10 10 2012 ISC239 Isabelle Bichindaritz 18. Step 5 Design PhysicalRepresentation Number of factors that may be used to measureefficiency Transaction throughput number of transactions processed in.given time interval Response time elapsed time for completion of a singletransaction Disk storage amount of disk space required to store database However no one factor is always correct Typically have.to trade one factor off against another to achieve areasonable10 10 2012 balance ISC239 Isabelle Bichindaritz 19 Step 5 1 Analyze TransactionsTo understand the functionality of the.transactions that will run on thedatabase and to analyze the importanttransactions Attempt to identify performance criteria transactions that run frequently and will have a.significant impact on performance transactions that are critical to the business times during the day week when there will be a highdemand made on the database called the peak10 10 2012 ISC239 Isabelle Bichindaritz 20. Step 5 1 Analyze Transactions Use this information to identify the parts of thedatabase that may cause performance To select appropriate file organizations andindexes also need to know high level.functionality of the transactions such as attributes that are updated in an update transaction criteria used to restrict tuples that are retrieved in a query 10 10 2012 ISC239 Isabelle Bichindaritz 21 Step 5 1 Analyze Transactions. Often not possible to analyze all expectedtransactions so investigate most important ones To help identify which transactions toinvestigate can use . transaction relation cross reference matrix showingrelations that each transaction accesses and or transaction usage map indicating which relationsare potentially heavily used 10 10 2012 ISC239 Isabelle Bichindaritz 22. Step 5 1 Analyze Transactions To focus on areas that may be problematic 1 Map all transaction paths to relations 2 Determine which relations are mostfrequently accessed by transactions . 3 Analyze the data usage of selectedtransactions that involve these relations 10 10 2012 ISC239 Isabelle Bichindaritz 23 Cross Referencing Transactionsand Relations.10 10 2012 ISC239 Isabelle Bichindaritz 24 Transaction Usage Map for Some SampleTransactions Showing Expected Occurrences10 10 2012 ISC239 Isabelle Bichindaritz 25 Example Transaction Analysis Form.10 10 2012 ISC239 Isabelle Bichindaritz 26 Step 5 2 Choose FileOrganizationsTo determine an efficient fileorganization for each base relation . File organizations include Heap Hash Indexed Sequential Access Method ISAM B Tree and Clusters 10 10 2012 ISC239 Isabelle Bichindaritz 27 Step 5 3 Choose Indexes.To determine whether adding indexeswill improve the performance of the One approach is to keep tuplesunordered and create as many secondaryindexes as necessary .10 10 2012 ISC239 Isabelle Bichindaritz 28 Step 5 3 Choose Indexes Another approach is to order tuples in therelation by specifying a primary or clustering In this case choose the attribute for ordering.or clustering the tuples as attribute that is used most often for join operations this makes join operation more efficient or attribute that is used most often to access the tuplesin a relation in order of that attribute .10 10 2012 ISC239 Isabelle Bichindaritz 29 Step 5 3 Choose Indexes If ordering attribute chosen is key of relation index will be a primary index otherwise indexwill be a clustering index . Each relation can only have either a primaryindex or a clustering index Secondary indexes provide a mechanism forspecifying an additional key for a base relationthat can be used to retrieve data more.efficiently 10 10 2012 ISC239 Isabelle Bichindaritz 30 Step 5 3 Choose Indexes Overhead involved in maintenance and use ofsecondary indexes that has to be balanced against.performance improvement gained whenretrieving data This includes adding an index record to every secondary index whenevertuple is inserted . updating a secondary index when corresponding tuple is increase in disk space needed to store the secondary index possible performance degradation during query optimizationto consider all secondary indexes 10 10 2012 ISC239 Isabelle Bichindaritz 31. Index Choices in Access MS Access uses a fixed file organization no choice to make here Indexes can be created PK is automatically indexed. FK s are automatically indexed Exception Memo Hyperlink OLE objectfields cannot be indexed10 10 2012 ISC239 Isabelle Bichindaritz 32 Index Choices in Access. Recommended indexes to create Field data type is Text Number Currency or Date Time Application searches for values in the field Application sorts values in the field. Many different values are stored in the field Index fields on both sides of a join orcreate a relationship on these fields10 10 2012 ISC239 Isabelle Bichindaritz 33 Step 5 3 Choose Indexes .Guidelines for Choosing Wish List 1 Do not index small relations 2 Index PK of a relation if it is not a key of the fileorganization 3 Add secondary index to a FK if it is frequently. 4 Add secondary index to any attribute that is heavilyused as a secondary key 5 Add secondary index on attributes that are involved in selection or join criteria ORDER BY GROUP BY andother operations involving sorting such as UNION or.DISTINCT 10 10 2012 ISC239 Isabelle Bichindaritz 34 Step 5 3 Choose Indexes Guidelines for Choosing Wish List 6 Add secondary index on attributes involved in built in functions . 7 Add secondary index on attributes that could result in an index only plan 8 Avoid indexing an attribute or relation that is frequently 9 Avoid indexing an attribute if the query will retrieve a significantproportion of the tuples in the relation . 10 Avoid indexing attributes that consist of long character strings 10 10 2012 ISC239 Isabelle Bichindaritz 35 Step 5 4 Estimate Disk SpaceRequirementsTo estimate the amount of disk space that.will be required by the database 10 10 2012 ISC239 Isabelle Bichindaritz 36 Step 6 Design User ViewsTo design the user views that wereidentified during the Requirements.Collection and Analysis stage of therelational database application lifecycle 10 10 2012 ISC239 Isabelle Bichindaritz 37 Step 7 Design SecurityTo design the security measures for the.database as specified by the users 10 10 2012 ISC239 Isabelle Bichindaritz 38 ACESS Features for PhysicalDatabase Design Domains not available but restrictions on field values.are available Constraints Not null available Data types available Default available . FK available through relationships FK indexed yes automatic Indexes field property Indexed can be set to No Yes Duplicates OK or Yes No Duplicates 10 10 2012 ISC239 Isabelle Bichindaritz 39.Comparison of Logical and Physical Database Design. Sources of information for physical design process includes global logical data model and documentation that describes model. Conceptual and logical . database design . are . concerned with the . what, physical database design is concerned with the . how.