39 Questions
Which statement best describes a candidate key in a relation?
An attribute that can be used to find the value of another attribute in the relation.
What database manipulation language is used for adding, deleting, and modifying data stored in a database?
DML
What does a materialized view store and how does it differ from a virtual view?
A materialized view stores actual data, while a virtual view only computes data when accessed.
What is the purpose of binary search in a database?
To repeatedly split the index in two until it finds a match.
What is logical design in database design?
Specifies table structures, keys, and indexes, and is called a schema
What is the function of the storage engine in a database system?
Accesses data on storage media, which is organized in files
What does a cloud database offer as a service?
A database offered as a cloud service
Which component of a database system compiles queries into low-level instructions?
Query processor
What type of configuration does parallel computing systems include?
Shared nothing
What is the purpose of a replicated database?
Provide advantages like high availability and fast reads but slow updates
What is the main difference between operational data and analytic data?
Operational data is used for daily business transactions, while analytic data is used for understanding business trends and planning
What is the purpose of a data mart?
A data warehouse designed for a specific business area
What does ETL stand for in the context of data integration?
Extract Transform Load
What does a federated database consist of?
Collection of participating databases
What does cardinality represent in a relationship instance?
The maximum number of entity instances that can participate in a relationship instance
Which type of entity cannot exist without another entity's existence in the database?
Weak Entity
In database normalization, which form aims for well-formed relations by eliminating redundancy?
First Normal Form
Which join type combines tables without comparing data?
Cross Join
What does the Boyce-Codd Normal Form ensure during normalization?
Every determinate is also a candidate key
Which index type stores column values and row pointers in hierarchy?
Multi-Level Index
What does the term 'Denormalization' in databases involve?
Putting tables back together for business needs
Which query language retrieves data from the database?
DQL
Which index structure stores table rows in each bucket?
Hash Table
What does the Third Normal Form aim to eliminate during normalization?
Transitive dependencies
Which type of join returns matching values?
Inner Join
What does the term 'Normalization' aim to achieve in a database?
Reduce redundancy by organizing data into well-formed relations
Which type of index structures contains pointers to table blocks?
Physical Index
What is a Primary Key in a database?
A key that uniquely identifies a row in a database
What does the LIKE operator do in SQL?
Performs pattern matching
What is the purpose of the HAVING clause in SQL?
To filter results based on conditions
What does a correlated subquery refer to in SQL?
A subquery that references a column from the outer query
What is the purpose of entity-relationship modeling?
To represent data requirements at a high level
What is the defining characteristic of an identifying attribute?
It is a singular, required attribute for identifying an entity
What does a slow query log record in a database?
Long-running queries submitted to the database
What is the primary function of hash functions in databases?
To distribute data evenly across a database
What is the characteristic of volatile memory?
Retains data when disconnected from power
What best describes the function of storage arrays?
Managing replicas internally
What characterizes row-oriented storage in databases?
Stores an entire row in one block
What does dynamic hash function do in databases?
Automatically allocates more blocks and buckets as needed and distributes rows across all buckets
Study Notes
- A Primary Key is a column or group of columns that uniquely identifies a row in a database.
- A constraint is a rule that governs allowable values in a database based on relational and business rules.
- A check constraint specifies an expression on one or more columns that is violated when the expression is false and satisfied when the expression is true or null.
- The LIKE operator performs pattern matching and can be case-insensitive or case-sensitive.
- HAVING is used with GROUP BY to filter results.
- UNION combines two query results into one table.
- An equijoin is a join where the join condition is an equality condition.
- A correlated subquery is a subquery whose WHERE clause references a column from the outer query.
- Flattening is the process of replacing a subquery with an equivalent join.
- A base table is the table specified in the view query's FROM clause.
- A query execution plan converts a SQL query into a sequence of low-level database actions.
- Entity-Relationship modeling is a high-level representation of data requirements.
- A reflexive relationship is a relationship between an entity and itself.
- Cardinality refers to the maxima and minima of relationships and attributes.
- An identifying attribute is a singular, required attribute used to identify an entity.
- A subject area is a group of related entities from a complex model.
- An artificial key is a column or group of columns used as a primary key when no suitable natural key exists.
- Dependency is a relationship between columns where one column depends on another.
- Normal forms are rules for designing tables with less redundancy.
- Volatile memory is memory that is lost when disconnected from power.
- Main memory, also known as random-access memory, is the primary memory used when computer programs execute.
- Flash memory is less expensive and higher capacity than main memory, and it groups data in pages.
- A magnetic disk, also known as a hard-disk drive, is used to share large amounts of data and groups data in sectors.
- Row-oriented storage stores an entire row in one block, while column-oriented storage stores values for a single column only.
- A hash function, such as a modulo function, is used to distribute data evenly across a database.
- Dynamic hash functions automatically allocate more blocks and buckets as needed and distribute rows across all buckets.
- Table clusters interleave rows of two or more tables in the same storage area.
- A single-level index is a file containing column values and pointers to rows containing the column value.
- A multi-column index is an index that contains composite values from all indexed columns.
- A fan-out index is a multi-level index that has a fixed number of index entries per block.
- The branch block is the bottom level of a B-Tree, where all indexed values appear.
- A shard is a subset of table data, usually a subset of rows, stored on different computers in a distributed database.
- A slow query log records all long-running queries submitted to the database.
- A query parser checks queries for syntax errors and converts valid queries to an internal representation.
- A consistent database conforms to all rules at all times.
- A network partition occurs when nodes in a database cannot communicate due to a network error.
- A partition-tolerant database continues to function when a network partition occurs.
- Storage arrays manage replicas internally, without database intervention.
- Type 2 design for slowly-changing dimensions involves adding start and end foreign keys to the fact table.
- An autonomous database operates independently of other participating data sources.
- Heterogeneous databases either run under different database systems or have incompatible schemas.
- A global catalog is a directory of participating database objects such as tables, columns, and indexes.
- Database wrappers convert decomposed queries to the appropriate syntax for each participating database.
- A federated database user mapping associates a federated database user with a participating database user.
Test your knowledge of entity relationship model concepts with this quiz! Topics covered include entity instances, weak entities, strong entities, and subtype entities.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
