Chapter Eight Flashcards
(42 cards)
What are the advantages of partitioning in a database?
- Efficiency: records used together are grouped together
- Local optimization: each partition can be optimized for performance
- Security: data not relevant to users are segregated
- Recovery and uptime: smaller files take less back up time
- Load balancing: partitions stored on different disks, reduces contention
Advantages of partitioning help in managing large datasets effectively.
What are the disadvantages of partitioning in a database?
- Inconsistent access speed: slow retrievals across partitions
- Complexity: non-transparent partitioning
- Extra space or update time: duplicate data; access from multiple partitions
Disadvantages highlight the potential challenges in managing partitioned databases.
Define horizontal partitioning in a database.
Distributing the rows of a logical relation into several separate tables
Useful for situations where different users need access to different rows.
What are the three types of horizontal partitioning?
- Key Range Partitioning
- Hash Partitioning
- Composite Partitioning
Each type serves different use cases depending on data distribution needs.
Define vertical partitioning in a database.
Distributing the columns of a logical relation into several separate physical tables
Useful for situations where different users need access to different columns.
What is denormalization in database design?
Transforming normalized relations into non-normalized physical record specifications
While it can improve performance, it may introduce data integrity issues.
List the benefits of denormalization.
- Can improve performance (speed) by reducing number of table lookups
- Reduce number of necessary join queries
Benefits must be weighed against potential costs.
What are the costs associated with denormalization?
- Wasted storage space
- Data integrity/consistency threats
These costs highlight the trade-offs involved in denormalization.
What is a physical file in database design?
A named portion of secondary memory allocated for the purpose of storing physical records
Essential for managing data storage in relational databases.
What are the components of a tablespace in a database?
- Segment: a table, index, or partition
- Extent: contiguous section of disk space
- Data block: smallest unit of storage
Understanding these components is crucial for effective database management.
What are the types of file organizations?
- Heap: no particular order
- Sequential
- Indexed
- Hashed
Each type of organization has its own use cases and performance implications.
What factors should be considered when selecting file organization?
- Fast data retrieval and throughput
- Efficient storage space utilization
- Protection from failure and data loss
- Minimizing need for reorganization
- Accommodating growth
- Security from unauthorized use
These factors are essential for optimizing database performance.
What is the purpose of clustering files in a database?
To improve performance of join operations by storing related records from different tables together in the same disk area
Clustering can enhance efficiency in data retrieval.
Define indexed file organizations.
Storage of records sequentially or nonsequentially with an index that allows software to locate individual records
Indexes are crucial for fast data access.
What is the purpose of a query optimizer?
To optimize performance by selecting the most efficient query execution plan
Essential for improving database response times.
What are the common data types in databases?
- VARCHAR2: variable-length character data
- CHAR: fixed-length character data
- NUMBER: positive or negative numbers
- DATE: date representation
- TIMESTAMP: date with fractional seconds
- BLOB: binary large objects
Understanding data types is fundamental for effective database design.
What does the Sarbanes-Oxley Act aim to achieve?
To protect investors by improving accuracy and reliability of financial reporting
Compliance with SOX is crucial for organizations handling financial data.
What are common authentication schemes in database security?
- Passwords
- Two-factor authentication
- Three-factor authentication
Strong authentication measures are vital for protecting sensitive data.
What are the recovery facilities in database management?
- Backup Facilities
- Journalizing Facilities
- Checkpoint Facility
- Recovery Manager
These facilities are crucial for data integrity and recovery after loss.
What is the role of a recovery manager in a database?
To restore the database to a correct condition when a failure occurs and then resume processing user requests
This ensures minimal downtime and data loss.
When should indexes be used in a database?
- On larger tables
- For primary keys
- For frequently searched fields
- On fields in ORDER BY and GROUP BY commands
Proper indexing can significantly improve query performance.
What are the downsides of cloud-based data management services?
- Existing systems do not yet provide adaptive capacity
- Lack of full consistency guarantees in distributed environments
- Challenges in monitoring cloud providers’ SLA commitments
These downsides highlight the limitations of current cloud solutions.
What is the purpose of clustering in databases?
Improves performance of join operations by storing related records together
Clustering stores primary key records of the main table adjacent to associated foreign key records of the dependent table.
What command is used in Oracle to create a cluster?
CREATE CLUSTER
This command facilitates the clustering of tables in Oracle databases.
