SQL

The schema (PartID, WarehouseID, QuantityOnHand, WarehouseAddress) is likely to be in second normal form, where (PartID, WarehouseID) is a composite key.

False

The most common relationships in the ER model are binary relationships.

True

UNION ALL is a computationally less expensive operation than UNION.

True

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

The cardinality of an attribute fully specifies the set of permitted values for an attribute.

False

Which of the following is more likely to be an entity set? Date of Birth Name Employee Social Security Number

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 bitmap index is relatively inexpensive to update when changes are made to the underlying table's data one row at a time.

False

A many-to-many-to-many ternary relationship between three entity sets can be represented using three relations in the logical model.

False

All relations in a database should always be in 3rd normal form.

False

An attribute's cardinality determines what values are permitted for that attribute.

False

Database normalization often improves read performance by avoiding unnecessary join operations.

False

Entity sets with a one-to-one relationship should always be converted into a single relation when normalizing.

False

Entity sets with a one-to-one relationship should always be converted into a single relation.

False

The conceptual design is concerned with communicating understanding solely to the technical participants of the project.

False

The following statement will display the number of books stored in the books table. SELECT NUMBER(*) FROM books;

False

The modality of all entity sets in the same relationship set must be the same.

False

The most common relationships in the ER model are ternary relationships.

False

When joining multiple tables together, all joins must be of the same type.

False

When mapping a conceptual to logical design, a one-to-many relationship involves inventing a whole new key for both relational tables.

False

A B+-tree index includes pointers to link the leaf nodes for which reason(s)?

In order to improve range query performance.

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 database is considered "normalized" if only the structured query language (SQL) is used to retrieve data in normal ways.

True

If the primary key of one relation appears as an attribute in another relation, then this attribute is called a _____.

foreign key

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

Attributes define the rules of the business within the ER diagram.

False

Indexes are cost-free mechanisms that dramatically improve the performance of a database.

False

The UNION operator is likely to be more expensive than UNION ALL due to duplicate elimination.

True

The data dictionary is typically accessed during query and transaction processing.

True

The database writer processes (DBWn) write the contents of buffers to datafiles.

True

The degree of a relationship quantifies the number of entity sets involved, such as unary, binary, or ternary relationships.

True

The log writer (LGWR) sequentially writes small amounts of information to redo log files (a fairly fast operation).

True

Third normal form is violated when a non-key field is a fact about another non-key field.

True

In the entity-relationship (ER) model, a relationship set is an association between entity sets.

True

In the entity-relationship (ER) model, an entity is an object or "thing" that exists in the real world (and database) and is distinguishable from other objects.

True

Individual tablespaces can be brought online or offline to aid in data loads, object re-location, backups, and other administrative tasks.

True

Joins are among the most expensive database operations.

True

Many DBMSs incorporate a mechanism for providing direct guidance or "hints" to the optimizer.

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 system global area (SGA) is divided into separate shared memory sub-areas based on specific processes and usage characteristics.

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

Comparing a NULL value to another NULL value will result in which of the following?

Unknown / NULL

An entity set may have a relationship with itself.

True

Optimizers can use the following factor(s) to compare the costs of alternative execution plans.

A combination of disk I/O, CPU usage, and memory usage.

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 / intersection relation).

The database writer processes (DBWn) periodically write buffers to advance the checkpoint, which is the position in the redo log from which instance recovery is initiated.

True

A foreign key field in a relation may contain some null values.

True

An associative / intersection relation is created when we convert what type of relationships?

many-to-many

A relation is typically considered "normalized" if it is in third normal form.

True

Create a basic e-commerce database that handles order for products from customers. Products should be associated with suppliers (more than one supplier for a given product). Products should include a SKU, name, description and price. Products should be organized into categories to support sales analysis. Customer can place many orders and each order can include many products (or items). Orders should be associated with shippers that deliver orders. Customer information includes name, e-mail and physical address. Pick the best schema from the alternatives presented.

Categories(CategoryID [PK], CategoryName, Description) Products(ProductID [PK], SKU, ProductName, Description, Price, CategoryID [FK1]) Suppliers(SupplierID [PK], SupplierName, Email, Address, URL) ProductSuppliers(ProductID [PK/FK1], SupplierID [PK/FK2], IsPrimary) Customers(CustomerID [PK], CustomerName, Email, Address) Shippers(ShipperID [PK], ShipperName, Email, Address, URL) Orders(OrderID [PK], OrderDate, ShipDate, Priority, CustomerID [FK1], ShipperID [FK2]) OrderItems(OrderID [PK/FK1], ProductID [PK/FK2], Price, Quantity, Discount)

For binary relationships, one-to-one, one-to-many, and many-to-many are all possible cardinalities.

True

Good query optimization was a critical factor in the commercial success of relational database technologies.

True

An inner join is needed when joining two tables together and all records from one table should be displayed regardless of if a matching record is found in the other table.

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

Integrity constraints are automatically generated by the database management system.

False

Joins are the least expensive database operation.

False

Many-to-many relationships are represented in a relational model by storing the primary key of one relation as a foreign key in the other relation.

False

Normalization usually involves forming aggregated relations that summarize detailed data.

False

Relationships are restricted to a degree of one, two, or three.

False

SQL 3-valued logic means a result may be true, false, or possible. True

False

The schema (EmpIoyeeID, EmployeeName, DeptID, DeptLocation) is likely to be in third normal form, where EmployeeID is the primary key.

False

There is really no difference between conceptual and logical database designs.

False

Design a catalog database to track the book inventory for a small system of libraries. Information to be captured includes books, book authors, publishers and the libraries themselves. Of course, books can have many authors and libraries have many books. Books are grouped into basic literature categories. Authors are assigned roles to clarify their authorship contributions. Select the schema that best meets these requirements.

Libraries(LibraryID [PK], LibraryName, Address, ZIPCode, CityID [FK1]) Publishers(PublisherID [PK], PublisherName, Address, ZIPCode, CityID [FK1]) Categories(CategoryID [PK], CategoryName) Books(BookID [PK], BookTitle, ISBN, YearPublished, PublisherID [FK1], CategoryID [FK2]) LibraryBooks(LibraryID [PK/FK1], BookID [PK/FK2]) Authors(AuthorID [PK], AuthorName, DateOfBirth, DateOfDeath) Roles(RoleID [PK], RoleName) BookAuthors(BookID [PK/FK1], AuthorID [PK/FK2], RoleID [FK3]) Countries(CountryID [PK], CountryName) Cities(CityID [PK], CityName, Population, CountryID [FK1])

B+-tree indexes are balanced for which reason?

Query performance/retrieval is kept predictable.

Which of the following is the correct syntax for sorting the results on a specific column?

SELECT * FROM college.faculty ORDER BY EmpID;

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

A good database design allows as many important queries to be answered as possible.

True

A normalized design enhances the integrity of the data by minimizing redundancy and inconsistency.

True

A primary key is composed of one or more attributes that uniquely identifies all individual entities in an entity set.

True

A weak entity set depends on another entity set for help in forming a primary key that uniquely identifies specific entities.

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 a composite index called indexA was created on field1 and field2 (in that order) on table tableA. The query optimizer would consider using indexA in an execution plan given the following SQL: SELECT field2 FROM tableA WHERE field1 = 7;

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

Assume the following relation: authors_books (author_id, book_id). The following SQL statement will select only the IDs of authors that have published at least 10 books. SELECT author_id FROM authors_books GROUP BY author_id HAVING COUNT(*) >= 10;

True

B+-tree indexes are suitable for high cardinality attributes.

True

B+-tree indexes must be continually re-balanced as the underlying tables change.

True

Bitmap indexes are very useful in reporting databases and data warehouses.

True

Bitmap indexes support efficient processing of complex AND/OR statements in the WHERE clause of a query.

True

Both entities and relationships can have attributes in the entity-relationship (ER) model.

True

Collecting database statistics for optimization is a very inexpensive operation.

True

Composite (or multi-column) indexes can be used to improve the performance of targeted queries by avoiding computations of unions.

True

Cost-based optimization (CBO) relies on current statistics to produce good execution plans.

True

Data blocks are at the base of the "BEST" hierarchy and represent the fundamental I/O units manipulated by the database engine.

True

Database denormalization is the process of improving read performance by adding redundant data or by grouping data.

True

Database normalization is the process of organizing the fields and relations of a relational database to minimize redundancy and dependency.

True

Database optimizers often provide a choice of optimizing for throughput or response time.

True

Database servers are typically configured with large memory spaces for reading and writing database blocks to and from secondary storage subsystems.

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