HIM6217: Health Data Management
How do we convert a "many-to-many" relationship to a set of relations?
Combine the two primary keys in a new relation (an associative relation).
Which of the following is more likely to be an entity set?
Employee
A CROSS JOIN is commonly used to join two tables that match a specified condition.
False
A GROUP BY clause should always be included when using an aggregation function (e.g., SUM, MIN).
False
A RIGHT OUTER JOIN displays all tuples from two tables, regardless of whether corresponding tuples are found in either table.
False
A SELECT statement must contain SELECT and WHERE clauses
False
A database is considered "normalized" if only the structured query language (SQL) is used to retrieve data in normal ways.
False
A database is considered normalized if only the "normal" data types are used to define attributes.
False
A logical database design focuses on the detailed physical configuration of database objects on the underlying storage media.
False
A table is in second normal form if it is in first normal form and no non-key attribute in the table is functionally dependent on a proper subset of any candidate key.
False
All columns in a table are not null, unless otherwise specified
False
Any database must have at least two I/O processes, such as the database writer (DBWn) and redo log writer (LGWR), to move a data block safely to disk.
False
Given a books table with columns named book_title and year_released, the following SQL statement will select the number of books released each year. SELECT book_title, year_released FROM books GROUP BY year_released
False
Given an authors table with the column last_name, the following will only obtain authors with a last name starting with 'J.' SELECT*FROM authors WHERE last_name LIKE '%J';
False
Given an employees table with the column supervisor, the following will obtain all employees that do not have a null value for supervisor. SELECT*FROM employees WHERE supervisor <> NULL;
False
Indexes are cost-free mechanisms that dramatically improve the performance of a database
False
Indexes are mandatory structures and must be associated with all database tables
False
Read performance is typically improved in highly normalized database designs.
False
SQL 3-valued logic means a result may be true, false, or possible
False
SQL is only capable of selecting, changing, removing, and updating data in a database
False
Selectivity refers to the process by which the optimizer determines which users have access to various database objects
False
Sorting is the least expensive operation
False
Sorting the results of a SELECT statement in ascending order requires specifying ASC
False
The UNION ALL operator is likely to be more expensive than UNION since it processes "all" the rows
False
The cardinality of an attribute fully specifies the set of permitted values for an attribute.
False
The following statement will display the number of books stored in the books table SELECT NUMBER(*) FROM books;
False
The following statement will display the number of books stored in the books table. SELECT NUMBER(*) FROM books;
False
There can be only one secondary index on a table
False
Thie first query is computationally less expensive than the second query. (Assume no indexes exist on release_date). Query 1: SELECT DISTINCT(release_date) FROM books; Query 2: SELECT release_date FROM books;
False
When joining multiple tables together, all joins must be the same type.
False
An associative relation is created when we convert what types of relationships?
Many-to-many
Which of the following is the correct syntax for sorting the results on a specified column?
SELECT*FROM college.faculty ORDER BY EmpID;
How do we convert a "one-to-many" relationship to a set of relations?
Take the primary key of the one side relation, and put it into the many side relation.
A deletion anomaly can occur when data representing certain facts necessitates the removal of data representing completely different facts.
True
A normalized design enhances the integrity of the data by minimizing redundancy and inconsistency.
True
A physical database design specifies the detailed physical configuration of database objects on the underlying storage media.
True
A primary key is composed of one or more attributes that uniquely identifies all individual entities in a set.
True
A record is in third normal form if it is in second normal form and every field is either part of the key or provides a single-valued fact about exactly the whole key and nothing else.
True
A subquery may be evaluated for each row in the main query
True
A weak entity set depends on another entity set for help in forming a primary key that uniquely identifies specific entities.
True
An entity-relationship diagram (ERD) allows attributes to be associated with relationships.
True
An indication of whether an index is used can be found in an execution plan
True
As an underlying table's data changes, any associated indexes may also require changes
True
Assume the SQL statement below is valid (all tables and columns exist). This SELECT statement will show all book titles regardless of whether a movie based on that book is also present in the movies table. SELECT book_title, movie_title FROM books LEFT OUTER JOIN movies ON (books.book_id = movies.book_id);
True
B+-tree indexes are typically the default index structure in most database systems
True
Both entities and relationships can have attributes in the entity-relationship (ER) model.
True
Composite (or multi-column) indexes can be used to improve the performance of targeted queries by avoiding computations of unions
True
Database normalization often improves read performance by avoiding unnecessary join operations.
True
Database servers are typically configured with large memory spaces for reading and writing database blocks to and from secondary storage subsystems.
True
Database updates can only be considered persistent when the information is written to external data files (safely on secondary storage)
True
Duplicate elimination through SELECT DISTINCT can be an expensive operation
True
Each column specified in an ORDER BY clause may be sorted independently in either ascending or descending order.
True
Enabling and disabling constraints provides some control over when a constraint is verified, while still keeping a record of the business rule
True
Extents are storage structures made up of many contiguous data blocks, which can improve disk I/O performance
True
Given a books table with the column genre, the following will obtain all books with a genre of 'Horror' or 'True Crime.' SELECT*FROM books WHERE genre IN ('Horror,' 'True Crime');
True
Given a books table with the column year_released, the following will obtain all books released from 2000 to 2010. SELECT*FROM books WHERE year_released BETWEEN 2000 and 2010;
True
In a SELECT statement, tuples (rows) from the tables specified in the FROM clause are examined before projecting any columns.
True
In the Oracle DBMS, function-based indexes can provide an efficient means of accessing calculated results
True
In the entity relationship (ER) model, a relationship set is an association between entity sets.
True
Integrity constraints provide a rich mechanism for expressing business rules in a database, thereby protecting data quality
True
Log Writer (LGWR) sequentially writes small amounts of information to redo log files (a fairly fast operation)
True
Many DBMSs incorporate a mechanism for providing direct guidance or "hints" to the optimizer
True
One of the most important decisions an optimizer makes is the selection of index-based access methods
True
One reasonable way to improve database performance is to add memory to the server and extend the database buffer cache
True
Pipelining is a natural fit with "first rows" (or response time) oriented optiization
True
Selectivity is a measure representing the fraction of rows from a row set that may be returned by a database operation
True
The Oracle logical storage structures are arranged in the "BEST" hierarchy, with blocks, extents, segments, and tablespaces representing increasingly higher levels of abstraction.
True
The Structure Query Language (SQL) is a declarative language
True
The cardinality of a relationship restricts the number of entities that can be related to a specific entity from each entity set.
True
The cardinality of an attribute fully specifies the set of permitted values for an attribute.
True
The database writer processes (DBWn) write the contents of buffers to datafiles.
True
The decision of whether to use constraints can have an impact on data quality.
True
The degree of a relationship quantifies the number of entity sets involved, such as unary, binary, ternary relationships.
True
The entity-relationship (ER) model is a widely used data model for database design.
True
The following two SELECT statements will display the same result set. SELECT book_title, publisher FROM books, publishers WHERE books.publisher_id = publishers.publisher_id; SELECT book_title, publisher FROM books INNER JOIN publishers ON (books.publisher_id = publishers.publisher_id);
True
The more indexes there are, the more overhead is incurred as the underlying table is modified
True
The most common relationships in the ER model are binary relationships
True
The most common relationships in the ER model are binary relationships.
True
The redo log buffer (a write-ahead log) is a circular buffer that holds after image information about changes made to the database
True
The redo log buffer (or write-ahead log) is used to reconstruct changes made by recent transactions during database recovery
True
The unique constraint allows null values.
True
There can be only one primary (or clustering) index on a table
True
Third normal form is violated when a non-key field is a fact about another non-key field.
True
UNION ALL is a computationally less expensive operation than UNION
True
When mapping a conceptual to logical design, a many-to-many relationship involves creating a new associative relational table with keys drawn from both related tables.
True
When rebuilding an index, storage characteristics can be changed and fragmentation may be reduced
True
Comparing a NULL value to another NULL value will result in which of the following?
Unknown/NULL
If the primary key of one relation appears as an attribute in another relation, then this attribute is called a _____.
foreign key
An associative relation is created when we convert what type of relationships?
many-to-many
