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
