Questions and Answers
What is the primary benefit of using a code look-up table according to the text?
Minimizing storage space
Which of the following is NOT mentioned in the text as a feature of a code look-up table?
Referential integrity
What is the main drawback of using a code look-up table?
It requires an additional lookup to obtain the actual value
What is the purpose of using triggers according to the text?
Signup and view all the answers
Which of the following is NOT mentioned in the text as a technique for handling missing data?
Signup and view all the answers
What is the primary benefit of denormalization according to the text?
Signup and view all the answers
What is the percentage of MANUFACTURED parts in the database?
Signup and view all the answers
How many suppliers are typically in the database?
Signup and view all the answers
What does denormalization introduce that could be a concern?
Signup and view all the answers
In denormalization, what is a possible issue of having a reference data scenario?
Signup and view all the answers
How many SUPPLIES instances are there from each SUPPLIER to give 6000 accesses?
Signup and view all the answers
What is a potential risk associated with denormalization?
Signup and view all the answers
What is the primary goal of physical database design?
Signup and view all the answers
Which of the following is NOT an input required for the physical database design process?
Signup and view all the answers
What is the smallest unit of application data recognized by the system software?
Signup and view all the answers
Which of the following is NOT a consideration when selecting a data type for a field?
Signup and view all the answers
What is the purpose of field design in physical database design?
Signup and view all the answers
Which of the following is NOT a decision made during the physical database design process?
Signup and view all the answers
What is the main purpose of horizontal partitioning?
Signup and view all the answers
Which two types of horizontal partitioning are mentioned in the text?
Signup and view all the answers
What is the key difference between range partitioning and list partitioning?
Signup and view all the answers
In the example provided, how many partitions are created for the sale_mast table based on the amount column?
Signup and view all the answers
What is the purpose of the VALUES LESS THAN operator used in the example for range partitioning?
Signup and view all the answers
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Study Notes
Physical Database Design and Performance
- Physical database design is the process of translating the logical description of data into technical specifications for storing and retrieving data.
- The goal of physical database design is to create a design that provides adequate performance and ensures database integrity, security, and recoverability.
Physical Design Process Inputs
- Normalized relations
- Volume of relations
- Response time expectations
- Efficiency and security needs
- Backup and recovery requirements
- Integrity format (attribute data types)
- Physical definitions (field definitions)
- DBMS (database management system)
- Storage estimates
- Attribute data needs
- Record descriptions (may not match logical design)
Designing Fields
- A field is the smallest unit of application data recognized by system software.
- Field design involves choosing data types, coding, compression, encryption, and controlling data integrity.
- Selecting a data type involves four objectives:
- Representing all possible values
- Supporting all data manipulations
- Reducing the number of necessary join queries
- Minimizing costs and wasted storage space
Designing File Organizations
- Three types of file organizations:
- Heap file organization
- Sequential file organization
- Index-sequential file organization
Indexes
- Indexes are used to improve query performance by providing quick access to specific data.
- Indexes can be used to enforce uniqueness constraints and improve data integrity.
Denormalization
- Denormalization is the process of transforming normalized relations into non-normalized physical record specifications.
- Benefits of denormalization include:
- Improved performance (speed) by reducing the number of table lookups
- Reduced number of necessary join queries
- Common denormalization opportunities:
- One-to-one relationships
- Many-to-many relationships with non-key attributes
- Reference data (1:M relationship where 1-side has data not used in any other relationship)
Denormalization Caution
- Denormalization can lead to increased errors and inconsistencies, reintroduce anomalies, and force reprogramming when business rules change.
- Alternative methods to improve performance of joins include:
- Organization of tables in the database (file organization and clustering)
- Proper query design and optimization
Partitioning
- Partitioning is the process of dividing a relation into multiple physical tables.
- Two types of partitioning:
- Horizontal partitioning (distributing rows of a logical relation into separate tables)
- Vertical partitioning (dividing columns of a table into separate tables)
- Horizontal partitioning can be used to:
- Minimize storage space
- Improve data integrity
- Examples of partitioning include:
- Range partitioning (partitioning rows based on column values that fall within a specified range)
- List partitioning (partitioning rows based on a list of specific values)
Handling Missing Data
- Methods for handling missing data include:
- Using a default value and not permitting missing (null) values
- Substituting an estimate of the missing value
- Constructing a report listing missing values
- Ignoring missing data unless the value is significant (sensitivity testing)
- Using triggers to perform these operations
