WGU C170 Data Management – Applications All Solutions Complete

domain Ans- In data modeling, the construct used to organize and describe an attribute's set of possible values superkey Ans- An attribute or attributes that uniquely identify each entity in a table. Any composite key ...

domain Ans- In data modeling, the construct used to organize and describe an attribute's set of possible values superkey Ans- An attribute or attributes that uniquely identify each entity in a table. Any composite key with this is also this. candidate key Ans- A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey entity integrity Ans- 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 Ans- 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 Ans- 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 Ans- A detailed system data dictionary that describes all objects in a database homonym Ans- The use of the same name to label different attributes, much like similar-sounding words with different meanings synonym Ans- 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 Ans- Any candidate key must have these two constraints enforced cardinality Ans- Term for how many instances of one object are related to instances of another object (maximum) modality Ans- Term for whether an instance of a specific entity is optional or mandatory in a relationship (minimum) connectivity Ans- Term for the classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N CREATE SCHEMA AUTHORIZATION Ans- SQL command to create a database schema DEFAULT Ans- SQL option to define a default value for a column (when no value is given) CHECK Ans- SQL command to validate data in an attribute, used during table column creation CREATE TABLE AS Ans- SQL command to create a new table based on a query in the user's database schema HAVING Ans- SQL option that restricts a selection of grouped rows based on a condition EXISTS Ans- SQL option that checks whether a subquery returns any rows DECIMAL Ans- SQL data type like the NUMBER specification, but the storage length is a minimum specification True Ans- 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 Ans- A join operation in which only rows that meet a given criterion are selected. The most common type of join outer join Ans- 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 Ans- A join operation that returns the Cartesian product of two sets or tables natural join Ans- 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 Ans- Name the three types of outer joins ALL Ans- This operator can be used to compare something to every returned row of a subquery ANY Ans- This operator can be used to compare something to a list of values and return any rows that satisfy the criteria correlated subquery Ans- A subquery that executes once for each row in the outer query UNION Ans- 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 Ans- SQL statement similar to UNION but retaining duplicate rows INTERSECT Ans- SQL statement used to combine rows from two queries, returning only the rows that appear in both sets EXCEPT (MINUS) Ans- 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 Ans- A virtual table based on a SELECT query that is saved as an object in the database base table Ans- The table on which a view is based batch update routine Ans- A routine that pools transactions into a single group to update a master table in a single operation updatable view Ans- A view that can update attributes in base tables that are used in the view True Ans- 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 Ans- 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 Ans- Activities to ensure that clients' requests are addressed as quickly as possible while making optimum use of existing resources extents Ans- In a DBMS environment, refers to the ability of data files to expand in size automatically using predefined increments table space (file group) Ans- In a DBMS, a logical storage space used to group related data SQL cache (procedure cache) Ans- A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions automatic vs manual Ans- Whether query optimization is done by the DBMS vs done by the programmer static vs dynamic Ans- Whether query optimization is done at compilation vs run-time statistically based algorithm Ans- 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 Ans- A query optimization technique that uses preset rules and points to determine the best approach to executing a query access plan Ans- 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 Ans- In query optimization, a delay introduced in the processing of an I/O operation that causes the overall system to slow down data sparsity Ans- A column distribution of values or the number of different values a column can have hash index Ans- An index based on an ordered list of hash values B-tree index Ans- An ordered data structure organized as an upside-down tree bitmap index Ans- An index that uses a bit array (0s and 1s) to represent the existence of a value or condition prime attribute Ans- A key attribute; that is, an attribute that is part of a key or is the whole key 1NF Ans- Normal form: No repeating groups, PK identified, all values depend on PK 2NF Ans- Normal form: 1NF and no partial dependencies 3NF Ans- Normal form: 2NF and no transitive dependencies BCNF (Boyce-Codd Normal Form) Ans- Normal form: every determinant is a candidate key (special case of 3NF) 4NF Ans- Normal form: 3NF and no independent multivalued dependencies full functional dependence Ans- If attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is said