Podcast
Questions and Answers
Row-oriented storage is best suited for analytic applications rather than transactional applications.
Row-oriented storage is best suited for analytic applications rather than transactional applications.
False
Column-oriented storage performs best when the row size is small relative to the block size.
Column-oriented storage performs best when the row size is small relative to the block size.
True
Row-oriented storage wastes a few bytes per block, since rows do not usually fit evenly into the available space.
Row-oriented storage wastes a few bytes per block, since rows do not usually fit evenly into the available space.
True
Block size is dependent on the type of storage media being used.
Block size is dependent on the type of storage media being used.
Signup and view all the answers
Relational databases are not aware of page and sector sizes due to the internal conversion managed by controllers.
Relational databases are not aware of page and sector sizes due to the internal conversion managed by controllers.
Signup and view all the answers
Indexes are always stored in a separate tablespace from the indexed table.
Indexes are always stored in a separate tablespace from the indexed table.
Signup and view all the answers
MySQL and most relational databases partition tables vertically, not horizontally.
MySQL and most relational databases partition tables vertically, not horizontally.
Signup and view all the answers
A shard is similar to a partition in that both are subsets of table data.
A shard is similar to a partition in that both are subsets of table data.
Signup and view all the answers
A range partition associates each partition with a range of partition expression values using the VALUES LESS THAN keywords.
A range partition associates each partition with a range of partition expression values using the VALUES LESS THAN keywords.
Signup and view all the answers
Physical design affects query results.
Physical design affects query results.
Signup and view all the answers
Column-oriented storage brings benefits to analytic applications
Column-oriented storage brings benefits to analytic applications
Signup and view all the answers
Heap tables are not optimal for queries that read rows in a specific order
Heap tables are not optimal for queries that read rows in a specific order
Signup and view all the answers
Sorted tables are optimized for read queries and best suited for queries that read data in order of the sort column
Sorted tables are optimized for read queries and best suited for queries that read data in order of the sort column
Signup and view all the answers
Hash tables distribute rows evenly across blocks and allow fast access to specific rows using the hash key
Hash tables distribute rows evenly across blocks and allow fast access to specific rows using the hash key
Signup and view all the answers
A table scan reads table blocks directly, while an index scan reads index blocks to locate needed table blocks
A table scan reads table blocks directly, while an index scan reads index blocks to locate needed table blocks
Signup and view all the answers
Binary search is a method to find a specific value in a database by repeatedly splitting the index in half
Binary search is a method to find a specific value in a database by repeatedly splitting the index in half
Signup and view all the answers
A primary index, also called a clustering index, is an index on a sort column
A primary index, also called a clustering index, is an index on a sort column
Signup and view all the answers
A table can have only one sort column and therefore only one primary index
A table can have only one sort column and therefore only one primary index
Signup and view all the answers
Multi-level indexes can be balanced or imbalanced
Multi-level indexes can be balanced or imbalanced
Signup and view all the answers
Bitmap index is a grid of bits where 'one' indicates the presence and 'zero' indicates the absence of a value in a table
Bitmap index is a grid of bits where 'one' indicates the presence and 'zero' indicates the absence of a value in a table
Signup and view all the answers
Entities, relationships, and attributes are the three kinds of objects included in an entity-relationship model
Entities, relationships, and attributes are the three kinds of objects included in an entity-relationship model
Signup and view all the answers
In an entity-relationship diagram, relationships are drawn as rectangles connecting entities
In an entity-relationship diagram, relationships are drawn as rectangles connecting entities
Signup and view all the answers
A glossary, data dictionary, or repository documents additional detail in text format about entities, relationships, and attributes
A glossary, data dictionary, or repository documents additional detail in text format about entities, relationships, and attributes
Signup and view all the answers
A reflexive relationship in an entity-relationship model relates an entity to another entity of the same type
A reflexive relationship in an entity-relationship model relates an entity to another entity of the same type
Signup and view all the answers
Each relationship has two maxima and minima, specified as one or many, and zero or one.
Each relationship has two maxima and minima, specified as one or many, and zero or one.
Signup and view all the answers
A related entity is singular when the maximum is one and plural when the maximum is many.
A related entity is singular when the maximum is one and plural when the maximum is many.
Signup and view all the answers
Entity design involves determining entities, relationships, attributes, cardinality, strong and weak entities, and creating supertype and subtype entities.
Entity design involves determining entities, relationships, attributes, cardinality, strong and weak entities, and creating supertype and subtype entities.
Signup and view all the answers
ER modeling concepts can vary, such as allowing relationships between three or more entities.
ER modeling concepts can vary, such as allowing relationships between three or more entities.
Signup and view all the answers
Chen notation is a standardized model convention for database design.
Chen notation is a standardized model convention for database design.
Signup and view all the answers
In ER diagrams, intangible entities are distinguished with special notation.
In ER diagrams, intangible entities are distinguished with special notation.
Signup and view all the answers
One-one relationships must have the foreign key referring to the primary key on the 'one' side.
One-one relationships must have the foreign key referring to the primary key on the 'one' side.
Signup and view all the answers
Many-many relationships become new weak tables with a single foreign key.
Many-many relationships become new weak tables with a single foreign key.
Signup and view all the answers
Normalization is the process of eliminating redundancy in tables by decomposing them into two or more tables in higher normal forms.
Normalization is the process of eliminating redundancy in tables by decomposing them into two or more tables in higher normal forms.
Signup and view all the answers
In a BCNF table, if column A depends on column B, then B must be unique.
In a BCNF table, if column A depends on column B, then B must be unique.
Signup and view all the answers
Redundancy can be eliminated by normalization, the last step of logical design, and every table can be normalized to any higher normal form.
Redundancy can be eliminated by normalization, the last step of logical design, and every table can be normalized to any higher normal form.
Signup and view all the answers
A candidate key is a simple or composite column that is unique and minimal.
A candidate key is a simple or composite column that is unique and minimal.
Signup and view all the answers
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively, in a database.
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively, in a database.
Signup and view all the answers
In Logical Design, entities, relationships, and attributes are implemented as tables, columns, and keys.
In Logical Design, entities, relationships, and attributes are implemented as tables, columns, and keys.
Signup and view all the answers
The ER diagram and glossary are usually developed in parallel during the Analysis phase.
The ER diagram and glossary are usually developed in parallel during the Analysis phase.
Signup and view all the answers
Cardinality refers to the maximum and minimum relationships and attributes in entity-relationship modeling.
Cardinality refers to the maximum and minimum relationships and attributes in entity-relationship modeling.
Signup and view all the answers
Study Notes
-
Binary search is a method to find a specific value in a database by repeatedly splitting the index in half
-
A database first compares the search value to an entry in the middle of the index, then narrows the search area accordingly
-
The database continues this process until it finds the index block containing the search value
-
Indexes on a sorted table can be primary or secondary
-
A primary index, also called a clustering index, is an index on a sort column
-
A secondary index, also called a nonclustering index, is an index that is not on the sort column
-
A table can have only one sort column and therefore only one primary index
-
In some database systems, the primary index may be created on any column
-
Tables can have many secondary indexes
-
Indexes may also be dense or sparse
-
A dense index contains an entry for every table row
-
A sparse index contains an entry for every table block
-
Multi-level indexes are a type of index that stores column values and row pointers in a hierarchy
-
The bottom level of a multi-level index is a sorted single-level index
-
Each level above the bottom is a sparse sorted index to the level below
-
The top level always fits in one block
-
The number of levels in a multi-level index can be computed from fan-out, number of rows, and rows per block
-
Multi-level indexes can be balanced or imbalanced
-
Balanced multi-level index is called a B+tree
-
B+tree index has all indexed values in the bottom level and pointers to table blocks only in the bottom level
-
Hash index is an index where index entries are assigned to buckets
-
Bitmap index is a grid of bits where 'one' indicates the presence and 'zero' indicates the absence of a value in a table
-
Logical indexes are a type of index that contains primary key values instead of pointers to table blocks
-
Logical indexes are always secondary indexes and require a separate primary index on the same table
-
Function indexes are indexes where index entries contain the result of a function applied to column values rather than the column values themselves.
-
In entity-relationship modeling, a type is a set: an entity type is a collection of things (e.g., all employees in a company), a relationship type is a set of related things (e.g., Employee-Manages-Department), and an attribute type is a set of values (e.g., all employee salaries).
-
Entity, relationship, and attribute types usually become tables, foreign keys, and columns, respectively, in a database.
-
An instance is an element of a set: an entity instance is an individual thing (e.g., the employee Sam Snead), a relationship instance is a statement about entity instances (e.g., "Maria Rodriguez manages Sales"), and an attribute instance is an individual value (e.g., the salary $35,000).
-
Entity, relationship, and attribute instances usually become rows, foreign key values, and column values, respectively, in a database.
-
There are three phases in database design for complex databases: Analysis, Logical Design, and Physical Design.
-
In the Analysis phase, entities, relationships, and attributes are discovered through interviews with database users and managers and review of written documents.
-
During Analysis, entities, relationships, and attributes appear as nouns and verbs, and designers look for relationships that are not explicitly stated.
-
In Logical Design, entities, relationships, and attributes are implemented as tables, columns, and keys.
-
The Database design process includes Discovery, Cardinality, Distinguishing Strong and Weak Entities, Creating Supertype and Subtype Entities, Implementing Entities, Implementing Relationships, Implementing Attributes, and Applying Normal Form.
-
Cardinality refers to the maximum and minimum relationships and attributes in entity-relationship modeling.
-
Entities, relationships, and attributes are discovered through interviews with database users and managers and review of written documents.
-
In interviews and documents, entities, relationships, and attributes appear as nouns and verbs, and designers should ignore nouns that denote specific data or are not relevant to the database.
-
Entity names should be singular and easily understood by database users, relationship names have the form Entity-Verb-Entity, and attribute names have the form EntityQualifierType.
-
Synonyms and descriptions are documented in the glossary to avoid confusion.
-
The ER diagram and glossary are usually developed in parallel during the Analysis phase.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Learn about the fundamental principles of binary search and how it efficiently locates a specific value within a sorted dataset by continuously dividing the search range in half.
