chapter 5 Flashcards
(15 cards)
Q: What is physical database design?
A: Physical database design translates a logical database design into a technical specification for how data will be stored and accessed on a specific database management system (DBMS).
Q: What are the inputs to the physical database design process?
Tables from logical design (normalized tables).
Business requirements (response time, throughput).
Data characteristics (volume, volatility).
Application characteristics (data usage, priorities).
Operational requirements (security, backup, recovery).
Q: What are the three types of data storage in databases?
Primary Storage: RAM and cache (fast, limited, volatile).
Secondary Storage: HDDs and SSDs (large capacity, slower).
Tertiary Storage: CDs, DVDs, magnetic tapes (archival use).
What is the difference between persistent data and temporary data?
A:
Persistent Data: Stored for long-term use, like employee records.
Temporary Data: Used temporarily during operations, like intermediate results.
Q: What are the types of file organization in databases?
Heap File (Unordered): Records stored in no specific order.
Sorted File (Sequential): Records stored in a specific order based on a key.
Hashed File: Records placed based on a hash function for quick searches.
Q: What is a dense index, and how is it different from a sparse index?
Dense Index: Every record has an entry in the index (faster but larger).
Sparse Index: Only some records have an index entry (smaller but slower).
Q: What is the purpose of indexing in databases?
A: To speed up data retrieval by creating pointers to records in a table.
Q: What are the common types of indexes?
Single-Level Indexes: Primary, Secondary, and Clustering indexes.
Multi-Level Indexes: B-Trees and B+ Trees for large datasets.
Q: What is the difference between clustered and non-clustered indexes?
Clustered Index: The table’s physical order matches the index order (one per table).
Non-Clustered Index: The table’s physical order is independent of the index (multiple possible).
Q: What is the purpose of query optimization?
A: To improve the efficiency of database queries by determining the best way to access data.
Q: What is a hash function in file organization?
A: A mathematical function that determines the location of records on a disk.
Q: What is the difference between primary storage and secondary storage?
Primary Storage: Faster, more expensive, volatile (e.g., RAM).
Secondary Storage: Slower, cheaper, non-volatile (e.g., HDDs, SSDs).
Q: What is a blocking factor?
A: The number of physical records stored per block of data.
Q: What are the three types of file operations?
Retrieval: Locate and read specific records.
Insertion: Add new records.
Deletion/Modification: Remove or update records.
Q: What is the difference between file organization and access method?
File Organization: How data is physically arranged on the disk.
Access Method: Operations used to retrieve or manipulate files.
