WGU C170 Data Management - Applications

WGU C170 Data Management - Applications domain Correct Answer: In data modeling, the construct used to organize and describe an attribute's set of possible values superkey Correct Answer: An attribute or attributes that uniquely identify each entity in a table. Any composite key with this is also this. candidate key Correct Answer: A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey entity integrity Correct Answer: The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values referential integrity Correct Answer: A condition by which a dependent table's foreign key must have either a null entry or a matching entry in the related table data dictionary Correct Answer: A DBMS component that stores metadata. It contains the data definition & their characteristics and relationships; may also include data external to the DBMS system catalog Correct Answer: A detailed system data dictionary that describes all objects in a database homonym Correct Answer: The use of the same name to label different attributes, much like similar-sounding words with different meanings synonym Correct Answer: The use of different names to identify the same object, such as an entity, an attribute, or a relationship. These should generally be avoided. not null and unique Correct Answer: Any candidate key must have these two constraints enforced cardinality Correct Answer: Term for how many instances of one object are related to instances of another object (maximum) modality Correct Answer: Term for whether an instance of a specific entity is optional or mandatory in a relationship (minimum) connectivity Correct Answer: Term for the classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N CREATE SCHEMA AUTHORIZATION Correct Answer: SQL command to create a database schema DEFAULT Correct Answer: SQL option to define a default value for a column (when no value is given) CHECK Correct Answer: SQL command to validate data in an attribute, used during table column creation CREATE TABLE AS Correct Answer: SQL command to create a new table based on a query in the user's database schema HAVING Correct Answer: SQL option that restricts a selection of grouped rows based on a condition EXISTS Correct Answer: SQL option that checks whether a subquery returns any rows DECIMAL Correct Answer: SQL data type like the NUMBER specification, but the storage length is a minimum specification True Correct Answer: True or false: if an INSERT or SELECT command is embedded inside a program for later use, the attribute list should always be used, as the table may change over time inner join Correct Answer: A join operation in which only rows that meet a given criterion are selected. The most common type of join outer join Correct Answer: A join operation that produces a table in which all unmatched pairs are retained; unmatched values in the related table are left null cross join Correct Answer: A join operation that returns the Cartesian product of two sets or tables natural join Correct Answer: Join type that returns only the rows with matching values in the matching columns; the matching columns must have the same names and similar data types left, right, full Correct Answer: Name the three types of outer joins ALL Correct Answer: This operator can be used to compare something to every returned row of a subquery ANY Correct Answer: This operator can be used to compare something to a list of values and return any rows that satisfy the criteria correlated subquery Correct Answer: A subquery that executes once for each row in the outer query UNION Correct Answer: SQL statement that combines rows from two or more queries without including duplicate rows, as long as the queries return the same # of attributes and similar data types UNION ALL Correct Answer: SQL statement similar to UNION but retaining duplicate rows INTERSECT Correct Answer: SQL statement used to combine rows from two queries, returning only the rows that appear in both sets EXCEPT (MINUS) Correct Answer: SQL statement that combines rows from two queries and returns only the rows that appear in the first set but not in the second view Correct Answer: A virtual table based on a SELECT query that is saved as an object in the database base table Correct Answer: The table on which a view is based batch update routine Correct Answer: A routine that pools transactions into a single group to update a master table in a single operation updatable view Correct Answer: A view that can update attributes in base tables that are used in the view True Correct Answer: One easy way to determine whether a view can be used to update a base table is if the primary key columns of the base table you want to update still have unique values in the view, then the base table is updatable SQL performance tuning Correct Answer: Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end DBMS performance tuning Correct Answer: Activities to ensure that clients' requests are addressed as quickly as possible while making optimum use of existing resources extents Correct Answer: In a DBMS environment, refers to the ability of data files to expand in size automatically using predefined increments table space (file group) Correct Answer: In a DBMS, a logical storage space used to group related data SQL cache (procedure cache) Correct Answer: A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions automatic vs manual Correct Answer: Whether query optimization is done by the DBMS vs done by the programmer static vs dynamic Correct Answer: Whether query optimization is done at compilation vs run-time statistically based algorithm Correct Answer: A query optimization technique that uses statistical information about a database. The DBMS then uses these statistics to determine the best access strategy rule based algorithm Correct Answer: A query optimization technique that uses preset rules and points to determine the best approach to executing a query access plan Correct Answer: The result of parsing a SQL statement; it contains the series of steps a DBMS will use to execute the query and return the result set in the most efficient way query processing bottleneck Correct Answer: In query optimization, a delay introduced in the processing of an I/O operation that causes the overall system to slow down data sparsity Correct Answer: A column distribution of values or the number of different values a column can have hash index Correct Answer: An index based on an ordered list of hash values