Physical Database Design

• Physical database design involves implementing a database on secondary storage, focusing on base relations, file organizations, indexes, integrity constraints, and security measures.
• Physical database design is highly dependent on the target DBMS, as different systems offer varying functionalities.

Chapter Objectives

• Physical database design aims to translate the logical data model into a physical database design.
• It involves designing base relations for the target DBMS.
• General constraints tailored for the target DBMS are designed.
• Selecting suitable file organizations is achieved through the analysis of transactions.
• The process includes deciding when to utilize secondary indexes for boosting performance.
• Estimating the database size is a key part of the planning process.
• Designing user views and security mechanisms to meet user requirements are part of this methodology.

Database Design Methodology

• Involves conceptual, logical, and physical database design phases
• Logical database design is implementation-independent, focusing on ER diagrams, relational schemas, and data dictionaries
• Physical database design is concerned, with knowing how the computer system and DBMS operate.
• Physical database design requires close knowledge of the target DBMS and isn't isolated, providing feedback to logical and application design.

Overview of Physical Database Design Methodology

• The physical database design methodology has six main steps, from 3 to 8, following conceptual and logical database design.
• Translate the logical data model for target DBMS is step 3.
• Design file organizations and indexes is step 4.
• Design user views is step 5.
• Design security mechanisms is step 6.
• Consider the introduction of controlled redundancy is step 7.
• Monitor and tune the operational system is step 8.

Translating the Logical Data Model

• Produces a relational database schema implementable in the target DBMS.
• Requires collating information from logical design, data dictionary, requirements collection, and systems specification.
• The designer also needs to know -How to create base relations. -If the system supports primary, foreign, and alternate keys. -If the system supports required data (NOT NULL). -If the system supports domain and integrity definitions, and general constraints.
• Three core activities: design base relations, decide how to represent derived data, and design general constraints.

Designing Base Relations

• Decision-making on how to represent base relations in the target DBMS.
• Involves collating information from logical database design, including data dictionary and DBDL (DataBase Description Language) definitions.
• Extended form of DBDL is used to define domains, default values, and null indicators.
• The process involves implementing base relations, dependent on the capabilities of the target DBMS.
• All design choices should be fully documented.

Design Representation of Derived Data

• Deciding how to show derived data within the target DBMS.
• Derived or calculated attributes have values computable from other attribute values.
• It involves weighing the cost of storing derived data versus calculating it on demand.
• Document all design choices and alternatives.

Design General Constraints

• The step is focused on designing general constraints for the target DBMS.
• Relational updates may be constrained by integrity constraints on transactions.
• Considers remaining general constraints beyond required data, domains, and integrity covered in Step 3.1.
• Design depends on DBMS choice, with some systems offering more facilities.
• The designer also needs to fully document the process.

File Organizations and Indexes

• Determines the optimal file organizations and indexes for acceptable performance.
• It should allow efficient storage and access ways that relations and tuples will be held on secondary storage.
• Needs full understanding of storage structures and the system's query optimizer, which is dependent on the target DBMS.
• Physical database design must align with the nature and intended use of data.
• It relies on workload knowledge, and understanding performance requirements specified during analysis and the requirements stage.

Analyzing Transactions

• The objective is to fully understand the functionality and important transactions.
• It's important to identify - -Frequently run transactions with a big impact on performance, -Transactions critical to operations.
• Peak load times.
• The database designer must identify performance criteria.

Using a Transaction Relation Cross-Reference Matrix

• There are processes that help identify transactions to investigate
• A transaction relation cross-reference matrix can be utilized, showing relations accessed by each transaction.
• Diagrammatically indicate/map which relations are potentially heavily used.
• Three step process:

1. map all transaction paths to relations.
2. determine which relations are most frequently accessed by transactions.
3. analyze the data usage of selected transactions that involve these relations.

Frequency Information

• The text presents that there needs to be a determination of frequency information
• It's important to determine not only the average and maximum number of times it runs per hour, but also the day and time that the transaction is run.

Data Usage

• How to analyze the data usage, and determine: -The relations and attributes accessed by the transaction -The type of access. -Predicates that may be used -If there can be candidates for access structure

File Organizations

• Efficiency is key, want to retrieve tuples in alphabetical order of name, sorting by staff name and salary range.
• Objective is to choose optimized approach that is relevant to the correct DBMS.
• DBMSs may offer little or no choice for file organizations.
• File types:
• Heap
• Hash
• Indexed Sequential Access Method (ISAM)
• B+-tree
• Clusters
• Choice of file orgs should be documented.

Types of Indexes

• Unordered tuples
• Specifying primary or clustering index, in this case, decide the attribute for best efficiency
• Key/relation, primary index, if not key, clustering index
• Each relation can have one primary or clustering index

Choosing Secondary Indexes

• Secondary indexes allow extra keys for a base relation to retrieve data better
• Example: PropertyForRent may hash on PropertyNo but have frequent access on rent attributes. Therefore can add rent as secondary index
• There is overhead, which includes:
• Adding index record
• Updating secondary index
• Increase in disk space
• Performance degradation

Index Guidelines

• Do not index small relations
• Use primary key of a relation if not a key of the file organization, but be aware this doesn't guarantee primary key is indexed
• Add secondary index to a foreign key if use is frequent. Some DBMSs will automate index of the foreign keys
• Add a secondary index to any attribute that is heavily used
• Secondary index on attributes that contain selection criteria, are ordered by, are grouped by, or contain other sorting (union or distinct)
• Attributes used in built in aggregated functions

Wish List Guidelines

• Can be inefficient to add to group by clause
• Can allow better performance from index only data, without having to access the data file
• Query will retrieve significant proportion of the tuples, so there is no point in indexing
• Avoid indexes on long character strings
• Consider impact on updates; are they going to slow things down too much?
• Experiment to determine. In the last case, clearly remove this index from the wish-list
• If there is little observed improvement with the addition of the index, further examination may be necessary
• Microsoft Office Access a Performance Analyzer and other products have "Query Execution Plans (QEP)" to inspect the optimizers strategy like Oracle EXPLAIN utility

Estimating Disk Space

• There needs to be documentation for any choices -Objective is to estimate disk space
• Physical database implementation is handled by hardware, but designer will need knowledge to assess how much space will need to be allocated for hardware procurement.
• Estimate based on size of each tuple and number of tuples in relation
• Latter estimate should be max number, to see how the relation will grow and potential disk size.

Designing User Views

• The objective is to design the user views by using the requirements provided by SQL or the database development lifecycle.
• First phase of the database design methodology involves a conceptual data model. This can be for a single view, combined, or other.
• The objective of this step is to design views previously identifies.
• Views are very versatile, they work very well for standardizing and simplifying the database requests in a DBMS on a PC. In a multi-user one, though, views are critical to define and enforce database structure.
• There is the ISO SQL standard that's relevant here.
• All individual user views should be fully documented.

Security Mechanisms

• The goal is to develop security system that are compliant to requirements of data specifications and life cycle.
• Has to comply to system security requirements. Also, database is essential corporate resource so it must be protected
• Designer must understand data and facilities
• Relational DBMS provide two types

Aspects Of Relational DBMS Security

• System Security: user and passwords.
• Data Security: Views and access

Document And Redundancy

• Redundancy should be documented
• Reasons for this must be present
• Then, update models for the data