Fichas
Databases Topic 6
Fichas del conjunto (35)
- 1Physical Database DesignTranslate logical DM for target DBMS
Design file organizations and indexes
Design user views
Design security mechanisms
Introduction of controlled redundancy
Monitor and tune operational system - 2Selecting a DBMSAim is to select a system that permits expansion and enables speedier, easier application development balanced against costs, that fulfills the requirements of the system.
- 3DBMS need to:Undertake analysis of user requirements
Define objectives and scope of study
Identify possible products
Shortlist products against criteria
Evaluate products more thoroughly
Recommend selection - 4Design base tablesFor each relation, need to define:
- Name of the table
- List of simple attributes in brackets
- PKs and where appropriate AKs and FKs
- RI Constraints for any FKs identified. - 5Data Dictionary, we have for each attribute:- Domain, a data type, length, and any constraints on the domain
- Optional default value for the attribute
- NULL or NOT NULL
- Whether it is derived and is so, how it should be computed. - 6Entity Integrity ConstraintsEntity Integrity Constraints: PK values are UNIQUE and NOT NULL, these are automatically enforced.
Eg. CONSTRAINT ArtistPK PRIMARY KEY(ArtistID) - 7RI Constraint - Oracle SQLOracle allows you to only specify 'on delete cascade' in the constraint definition:
CONSTRAINT ArtistFK FOREIGN KEY (ArtistID) REFERENCES Artist (ArtistID) ON DELETE CASCADE - 8Representation of derived dataExamine logical data model and data dictionary, and produce list of all derived attributes Eg. Age of individual artists.
Derived attribute can be stored in database or calculated every time it is needed. - 9Derived data- Additional cost to store the derived data and keep it consistent with operational data from which it is derived.
- Cost to calculate it each time it is required
- Less expensive option is chosen subject to performance constraints. - 10Remaining Integrity ConstraintsThis step is about implementation of constraints apart from entity and RI, required data and domain constraints.
Implementation in the DBMS results in a more consistent application of the constraint. - 11File Organizations and IndexesDetermine the optimal file organizations to store the base table.
Indexes that are required to achieve acceptable performance, that is , the way in which the data will be held on secondary storage. - 12Design File Organizations and Indexes- Analyze Transactions
- Choose file organizations
- Choose indexes
- Estimate disk space requirements - 13Analyze TransactionsUnderstanding of the trans the database will run has a MAJOR impact on physical design.
Include criteria such as:
- Frequency
- Business (or system) impact
- Peak load - 14Transaction FunctionalityNeed to know high level functionality of the trans such as:
- Attributes that are updated
- Search criteria used in a query - 15Transaction AnalysisIdentify parts of the database: Trans / Relation Cross Reference Matrix and Trans Usage Map (TUM)
-Shows Create (C), Read (R), Update (U), and Delete (D) data access. - 16A typical TransactionCreate an Invoice for a project
- Create Invoice details
Create Invoice
Check project exists
- Add Invoice Line details
Create invoice line
Check invoice exists - 17Transaction Usage Maps (TUM)Identifies major trans and analyze the usage pattern and paths required for each trans,
Eg. Data Volume
- Average
- Peak
- Expected growth - 18Transaction Analysis helps us to identify:Area of potential high load
Frequently queried tables/columns that may benefit from indexing
Tables that will grow rapidly
Information will help assist in decision making. - 19Physical VS Logical RecordsLogical records are our abstraction of how records are stored.
Physical records are how the records are stored electronically - a collection of bytes. - 20Heap (Unordered) FilesSimplest kind of file organization
- Records are placed in the file (physical record) in the order they are inserted.
- Insertion is efficient.
- Searching is problematic
- Deletion of records also causes problems. - 21Sequential (Ordered) FilesRecords are stored in order of the value of a chosen ( Ordering) field.
- Insertion and deletion can be problematic
- Searching (on the ordering field) is easier - 22Hash FileRecords are not written to disk in any order.
Hash file converts a key value into a physical record address by some mathematical function. - 23Choose IndexesAllow DBMS to locate particular records in a file more quickly.
- Reduces the need to search the file sequentially for the record.
- Can improve performance for searches.
- Can reduce performance for insertions as index needs to be maintained. - 24Types of Indexes- Primary: Data file is ordered by ordering key field
- Clustering: Data file is sequentially ordered on a non-key field.
- Secondary: An index is defined on a non-ordering field of the data file. - 25IndexesA file can have at most one Primary index or one Clustering index, and can have several Secondary indexes.
- 26Indexed Sequential FilesSorted data file with a Primary Index.
- Compromise between a sequential file and random file.
- Records can be processed sequentially or accessed using the search key value via the index. - 27Indexed Sequential Files (Pros and Cons)- The index can be made more sparse as the data file is sorted.
- The primary index would be typically stored in memory for fast access.
- Problem: this type of organisation is that both the data and index need to be updated on inserts and deletes. - 28Secondary IndexesCan improve the performance of queries that use attributes other than primary key.
- Need not contain unique values
- Trade-off is the increased overhead in maintaining the index - 29Creating Secondary IndexesSeveral ways can be created:
- Very dense index that maps to all data .
- Index for each unique value in data but having pointers for each repeated values.
- Index each unique value in data but having pointers to a bucket that contain pointers for each repeating value. - 30Multilevel Indexes- When an index becomes very large, there is reduced advantages in searching it.
- Multilevel indexes reduce search time/effort by splitting the index into smaller index. - 31Index Creation Guidelines-Do not index small relations
-Add a secondary index to a FK if frequently accessed.
-Add a secondary index to any attribute that is heavily used as secondary key.
- Add secondary index that are frequently involved in: WHERE ORDER BY GROUP BY. - 32Indexes to avoid-When attributes are frequently updated.
-An attribute if the query will retrieve a significant portion of the tuples in the relation.
-Attributes that consist of long character strings. - 33Index CreationSQL to create the indexes is CREATE INDEX
Eg. CREATE INDEX StdGPAIndex
ON Student (StdGPA) - 34Controlled redundancyAdditional choice in physical database design:
- Denormalization combines tables so that they are easier to query
- Denormalization may/may not lead to violations in a normal form. - 35Advantages of normalization designs-Have a better update performance
-Require less coding to enforce integrity constraints
-Support more indexes to improve query performance