Database systems design - SUNY Oswego

Database Systems Design SUNY Oswego-ppt Download

  • Date:25 Jun 2020
  • Views:36
  • Downloads:0
  • Size:880.39 KB

Share Presentation : Database Systems Design SUNY Oswego

Download and Preview : Database Systems Design SUNY Oswego

Report CopyRight/DMCA Form For : Database Systems Design SUNY Oswego


Transcription:

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.

Related Presentations

Database systems design Oswego

Logical database design Process of constructing a model of information used in an enterprise based on a specific data model (e.g. relational), but independent of a particular DBMS and other physical considerations.

30 Views0 Downloads

Database systems design cs oswego edu

Fact-finding used throughout the database application lifecycle. Crucial to the early stages including database planning, system definition, and requirements collection and analysis stages. Enables developer to learn about the terminology, problems, opportunities, constraints, requirements, and priorities of the organization and the users of ...

8 Views0 Downloads

Eco 101 Chapter 1 notes SUNY Oswego

Eco 101: Chapter 1 notes Definition of economics the study of how individuals and societies use limited resources to satisfy unlimited wants. Fundamental economic problem scarcity. individuals and societies must choose among available alternatives. Economic goods, free goods, and economic bads economic good (scarce good) - the quantity demanded ...

18 Views0 Downloads

SAFE Act and the gun owner SUNY Oswego

Added the firearm certificate program to the use of shotguns. Banned gun ownership of former criminals. Firearms Amendment Act 1988. Outlawed Semi-Automatic Weapons. 1997 Firearms Act. Outlawed the private possession of handguns. Hungerford England shooting, 1987. Michael Ryan killed 16 people with 2 semi-automatic weapons and a pistol.

9 Views0 Downloads

PLA Saga SUNY Oswego

The Prior Learning Path @ SUNY Oswego. Jill Pippin. 2017 CEANY Conference. Genesee Grande Hotel, Syracuse NY. November 8, 2017. Good afternoon! In this session, we’ll discuss Prior Learning Assessment, otherwise known as PLA, why it’s needed and the specific tactical steps SUNY Oswego has taken to recognize the college-level learning acquired by our non-traditional students through non ...

10 Views0 Downloads

Public Justice Careers SUNY Oswego

Legal Attorney Legal Attorney Information Federal, State and County Prosecutor U.S. Attorney General / Assist State Attorney General / Assist County District Attorney / Assist Private Practice Civil Law Criminal Law Government Appointed Attorney Public Defenders Offices Government Funded Assigned Counsel Contracted Firms Criminologist ...

3 Views0 Downloads

Information systems Oswego

Uses best, appropriate modern technology. Programming, software, hardware, networking. Moves “core” applications to run “enterprise-wide” Standardizes core data & communications. Enhances the five major systems. Regis / eligibility / enrollment, health data, provider, management / financial, “e” commun/transactions. Enhances cross ...

10 Views0 Downloads

Building for the Future Net Zero Design Build Construction at SUNY Oneonta

About Zero-Net Carbon. Zero-Net Carbon (ZNC) means the project will result in a highly efficient building that eliminates use of all on-site fossil fuels and dramatically reduce the building’s energy consumption, then procurement of sufficient renewable energy to cover the reduced load may be satisfied by SUNY through separate, and ...

0 Views0 Downloads

Data Mining Oswego

Data mapping is: the process of creating data element mappings between semantic and representational terms residing in two distinct models. It is a first step in data integration. It involves combining terms residing in different sources. Provides users with a unified view of data.

25 Views0 Downloads

Data Mining cs oswego edu

Study of clinical and health care trends or patterns. Ability to recognize trends or patterns within large amounts of data. Health information management (HIM) professional: Conducts clinically based research studies. Studies specific HIM department functions. Overview of Research & Epidemiology

26 Views0 Downloads

Geometry Unit Test Review Oswego Community Unit School

Janine keeps her jewelry in a jewelry box like the figure below. Find the volume of Janine’s jewelry box. Round to the nearest tenth if necessary. Piper wants to buy enough potting soil to fill a flower box that is 36 inches long, 8 inches wide, and 10 inches tall. If one bag of potting soil contains 576 cubic inches, how many bags should she ...

34 Views0 Downloads

Chapter 2 Criminal Law Oswego Community Unit School

Criminal law describes conduct that is . forbidden, and conduct that is . expected. Elements Of A Crime. Criminal Act. Must describe the specific conduct the law forbids. Theft stealing another person’s property. Robbery Stealing another person’s property through violence or threat of violence.

12 Views0 Downloads