Download WGU C170 - Applications - Data Management and more Exams Public Health in PDF only on Docsity!
WGU C170 - Applications - Data
Management
FROM Correct Ans - Clause that specifies the table or tables from which data is to be retrieved. Table Row Correct Ans - Also called a tuple; represents a single entity occurrence within the entity set Table Column Correct Ans - Has a distinct name and represents an attribute Superkey Correct Ans - An attribute or combination of attributes that uniquely identifies each row in a table Candidate key Correct Ans - A minimal superkey A key that does not contain a subset of attributes that is itself a superkey Based on a full functional dependency Primary Key Correct Ans - A candidate key selected to uniquely identify all other attribute values in any given row; cannot contain null entries Foreign Key Correct Ans - An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null Secondary Key Correct Ans - An attribute or combination of attributes used strictly for data retrieval purposes Inner Join Correct Ans - A join operation in which only rows that meet a given criterion are selected It is the most commonly used type of join. Only RETURNS MATCHED RECORDS from the tables that are being joined Right Outer Join Correct Ans - Returns all rows from the right table, and the matched rows from the left table. The result is NULL in the left side when there is no match.
Full Join Correct Ans - Returns all rows from the left table and from the right table. It combines the output of both left and right joins. Theta Join Correct Ans - Links tables using an inequality comparison operator It is considered to be an extension of natural join. Natural Join Correct Ans - Yields a new table composed of ONLY THE ROWS WITH COMMON VALUES in their common attributes. It is a three-stage process. Relational Select operation Correct Ans - Retrieves one or more rows of a table, depending on whether the search argument is a unique or non-unique attribute. Relational Project operation Correct Ans - Retrieves all rows and some attributes of a table without using a WHERE clause to limit which rows of the table are included Join operation Correct Ans - Integrates data from two or more tables or combines them. A join operation can be implemented in the FROM and WHERE clauses Entity integrity Correct Ans - Describes a condition in which all tuples within a table are uniquely identified by their primary key Referential integrity Correct Ans - Describes a condition in which a foreign key value has a match in the corresponding table or in which the foreign key value is null Composite Entity Correct Ans - An entity designed to transform an M:N relationship into two 1:M relationships. This entity's primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity or associative entity. Join Correct Ans - The real power behind the relational database, allowing the use of independent tables linked by common attributes. Codd's Nonsubversion Rule of Relational Databases Correct Ans - If the system supports low level access to the data, users must not be allowed to bypass the integrity rules of the database
How a Null is created Correct Ans - By pressing the Enter key or the Tab key to move to the next entry without making a prior entry of any kind Closure Correct Ans - A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations. Hononym Correct Ans - Indicates the use of the same attribute name to label different attributes Relation Correct Ans - Another name for a table E.F. Cobb used the two terms as synonyms The proper use of foreign keys is crucial to controlling ________ Correct Ans - Data Redundancy System Catalog Correct Ans - A detailed system data dictionary that describes all objects within the database, including data about table names, the table's creator and creation date, the number of columns in each table, the data type corresponding to each column, index filenames, index creators, authorized users, and access privileges SELECT Correct Ans - Operator that yields values for all rows found in a table that satisfy a given condition Also known as RESTRICT Attribute Domain Correct Ans - Specific range of values for each column Any key that uniquely identifies each row is called a ______ Correct Ans
- Superkey Bridge Entity Correct Ans - Another name for a composite entity Dr. Codd's Guaranteed Access rule of relational database Correct Ans
- States that every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name Synonym Correct Ans - Indicates the use of different names to describe the same attribute Candidate Key Correct Ans - A minimal superkey, (superkey without any unnecessary attributes)
Referential Integrity Correct Ans - Dictates that the foreign key must contain values that match the primary key in the related table, or must contain null DIFFERENCE operator Correct Ans - An operator used to yield all rows from one table that are not found in another union-compatible table. Codd's Logical Data Independence rule of relational databases Correct Ans - States that application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns) Index Key Correct Ans - Can have multiple attributes Proper data warehousing design requires... Correct Ans - Carefully defined and controlled data redundancies to function properly. Each Table Column Represents Correct Ans - An attribute Entity Integrity Correct 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. Keys Correct Ans - Used to ensure that each row in a table is uniquely identifiable. Index Correct Ans - An orderly arrangement used to logically access rows in a table Equijoin Correct Ans - Links tables on the basis of an equality condition that compares specified columns of each table. Character Data Correct Ans - Outer Joins Correct Ans - Especially useful when you are trying to determine what values in related tables cause referential integrity problems. To be consider minimally relational which key relation operators must be supported? Correct Ans - JOIN SELECT PROJECT Unique Index Correct Ans - Automatically created by the DBMS on the primary key columns when a primary key is defined
Prime attribute Correct Ans - A key attribute An attribute that is part of a key or is the whole key PROJECT operator Correct Ans - Used to select a subset of columns Referential Intergrity Correct 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. 8 Functions of Referential Algebra Correct Ans - SELECT PROJECT JOIN INTERSECTION UNION DIFFERENCE PRODUCT DIVIDE Relvar Correct Ans - A relation that holds a variable A container (variable) for holding relation data, not the relation itself UNION Correct Ans - Used to merge (append) two tables into a new table, dropping the duplicate rows Strong (identifying) relationship Correct Ans - A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity. Depicted with a solid line Recursive Relationship Correct Ans - When an entity is related to itself. Example: a course may be a prerequisite to a course Composite Key vs. Composite Attribute Correct Ans - A composite key consists of more than one attribute A composite attribute can be subdivided to yield meaningful attributes Derived Attribute Correct Ans - An attribute that does not physically exist within the entity and is derived via an algorithm
Database Requirements Correct Ans - 1. Design Elegance
- Information Requirements
- Operational speed requirements Single-Valued Attributes vs. Simple Attributes Correct Ans - A single-valued attribute is one that can have only one value A simple attribute is one that cannot be decomposed into its component pieces Cardinality Correct Ans - A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity Composite Attribute Correct Ans - An attribute that can be further subdivided to yield additional attributes. Phone Numbers are an example Existence-Dependent Entity Correct Ans - Identifiers Correct Ans - One more attributes that uniquely identify each entity instance Iterative Process Correct Ans - A process based on repetition of steps and procedures Mandatory Participation Correct Ans - A relationship in which one entity occurrence must have a corresponding occurrence in another entity Non-Identifying Relationship Correct Ans - A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity. Optional participation Correct Ans - In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship. Participants Correct Ans - An ER term for entities that participate in a relationship Relational Schema Correct Ans - The organization of a relational database as described by the database administrator
The GROUP BY clause does impact the rows that are returned by the query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate functions The two categories of SQL commands Correct Ans - Data Definition Language (DDL) Data Manipulation Language (DML) The basic data types Correct Ans - NUMBER NUMERIC INTEGER CHAR VARCHAR DATE The basic Data Definition (DDL) commands Correct Ans - CREATE TABLE CREATE INDEX ALTER TABLE DROP TABLE DROP INDEX DISTINCT Correct Ans - ... INSERT Correct Ans - Command used to add new rows to tables. UPDATE command Correct Ans - Used to modify data values in existing rows of a table COUNT Correct Ans - Aggregate function that outputs the number of rows containing not null values for a given column or expression, sometimes used in conjunction with the DISTINCT clause. SUM Correct Ans - Aggregate function that adds the values together and can only be applied to numeric values ALTER TABLE Correct Ans - Command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics. AND Correct Ans - Logical operator used to link multiple conditional expressions in a WHERE or HAVING clause. It requires that all conditional expressions evaluate to true.
AVG Correct Ans - Aggregate function that outputs the mean average for a specified column or expression BETWEEN Correct Ans - A special comparison operator used to check whether a value is within a range of specified values Cascading Order Sequence Correct Ans - A nested ordering sequence for a set of rows, such as a list in which all last names are alphabetically ordered and, within the last names, all first names are ordered. CREATE TABLE Correct Ans - Command that creates a table's structures using the characteristics and attributes given DISTINCT Correct Ans - Clause that produces only a list of values that are different from one another DROP Correct Ans - Command used to used to delete database objects such as tables, views, indexes, and users EXISTS Correct Ans - A comparison operator that checks whether a subquery returns any rows GROUP BY Correct Ans - Used to quickly and easily group rows into smaller collections HAVING Correct Ans - Clause applied to the output of a GROUP BY operation to restrict selected rows IN Correct Ans - Comparison operator used to check whether a value is among a list of specified values Inner Query Correct Ans - A query that is embedded or nested inside another query. Also known as a nested query or a subquery. INSERT Correct Ans - Command that allows the insertion of one or more data rows into a table IS NULL Correct Ans - Comparison operator used to check whether an attribute has a value. LIKE Correct Ans - Comparison operator used to check whether an attribute's text value matches a specified string pattern. MAX Correct Ans - Aggregate function that yields the maximum attribute value in a given COLUMN
DEFAULT Correct Ans - Constraint assigns a value to an attribute when a new row is added to a table This is enforced automatically when the primary key is specified in the CREATE TABLE command sequence. Correct Ans - Entity Integrity DISTINCT Correct Ans - String Comparisons are made in which direction? Correct Ans - Left to Right The _____ condition is generally composed of an equality comparison between the foreign key and the primary key of related tables. Correct Ans - Join SMALLINT Correct Ans - Must be used if integer values are relatively small In a 1:M relationship, the table for which side is created first Correct Ans - The one side The basic SQL vocabulary has less than _____ words Correct Ans - 100 In SQL, all ______ expressions evaluate to true or false Correct Ans - Conditional IS NULL Correct Ans - Special operator used to check whether an attribute value is null Schema Correct Ans - A logical group of database objects, such as tables and indexes, that are related to each other. CROSS JOIN Correct Ans - A join that performs a relational product (or Cartesian product) of two tables. Identical to the PRODUCT relational operator LEFT OUTER JOIN Correct Ans - Yields all rows with matching values in the join columns, plus all of the unmatched rows from the left table. (The left table is the first table named in the FROM clause.) RIGHT OUTER JOIN Correct Ans - Yields all rows with matching values in the join columns, plus all of the unmatched rows from the right table
FULL OUTER JOIN Correct Ans - yield all rows with matching values in the join columns, plus all the unmatched rows from both tables named in the FROM clause. Base Tables Correct Ans - The table on which a view is based Batch Update Routine Correct Ans - A routine that pools transactions into a single group to update a master table in a single operation Correlated Subquery Correct Ans - A subquery that executes once for each row in the outer query. CREATE VIEW Correct Ans - A SQL command that creates a logical, "virtual" table. The view can be treated as a real table. View Correct Ans - A virtual table based on a SELECT query that is saved as an object in the database. Subquery Correct Ans - A query (expressed as a SELECT statement) that is located inside another query What are the three types of results a subquery can return? Correct Ans
- A list of values
- A virtual table Regular Subquery vs. Correlated Subquerry Correct Ans - A regular, or uncorrelated subquery, executes before the outer query. It executes only once and the result is held for use by the outer query. A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query. Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query. Set-Oriented Correct Ans - Means that the commands work over entire tables at a time, not row-by-row. Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query? Correct Ans - MINUS queries are analogous to algebraic subtraction - it results in the value that existed in the first operand that is not in the second operand. UNION queries are
Extents Correct Ans - Refers to the ability of data files to expand in size automatically using predefined increments input/output (I/O) request Correct Ans - A low-level data access operation that reads or writes data to and from computer devices. Manual Query Optimization Correct Ans - An operation mode that requires the end user or programmer to define the access path for the execution of a query. Manual Statistic Generation Mode Correct Ans - In this mode, the DBA must periodically run a routine to generate the data access statistics Query Optimizer Correct Ans - A DBMS process that analyzes SQL queries and finds the most efficient way to access the data. The query optimizer generates the access or execution plan for the query. Query Processing Bottleneck Correct Ans - A delay introduced in the processing of an I/O operation that causes the overall system to slow down. Rule-based Query Optimization Algorithm Correct Ans - A query optimization technique that uses preset rules and points to determine the best approach to executing a query. SQL Cache Correct Ans - A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. Also called procedure cache. SQL Performance Tuning Correct 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. Static Query Optimization Correct Ans - A query optimization mode in which the access path to a database is predetermined at compilation time. PROJECT Correct Ans - Operator that yields all values for selected attributes from a single table (unary operator) Table Space Correct Ans - A logical storage space used to group related data. Also known as a file group.
DBMS Processes Correct Ans - Listener User Scheduler Lock Manager Optimizer Static Query Optimization Correct Ans - Takes place at compilation time; the best optimization strategy is selected when the query is compiled by the DBMS. This approach is common when SQL statements are embedded in procedural programming languages Processing DDL statements vs. Processing DML statements Correct Ans - DDL statements actually update the data dictionary tables or system catalog DML statements manipulate end-user data Query Processing Phases Correct Ans - Parsing - The most efficient access/execution plan is chosen Execution - The DBMS executes the SQL query using the chosen execution plan. Fetching - The DBMS fetches the data and sends the result set back to the client. Database Statistical Measurements Correct Ans - Tables Indexes Resources Focus of most performance tuning applications Correct Ans - Minimizing the number of I/O operations, because the I/O operations are much slower than reading data from the data cache. Bitmap Index Correct Ans - Works with bits to store their data rather than bytes Used in data warehouse applications, with large number of rows and small number of columns. B-Tree Index Correct Ans - Provides an ordered data structure organized in an upside direction. These are "self-balanced," which means that it takes the same time to access any given row in the index.
Second Normal Form (2NF) Correct Ans - 1NF No partial dependencies Third Normal Form (3NF) Correct Ans - 2NF No TRANSITIVE Dependencies Transitive Dependency Correct Ans - A condition in which an attribute is dependent on another attribute that is not part of the primary key. An attribute that cannot be further subdivided is said to display ______ Correct Ans - Atomicity What does data redundancy produce? Correct Ans - Data Anomalies Prime Attribute Correct Ans - Any attribute that is at least part of a key Key Attribute Correct Ans - An attribute that is a part of a key Primary Key requirements to ensure entity integrity Correct Ans -
- All of the values in the primary key must be unique
- No key attribute in the primary key can contain a null What are used to ensure referential integrity Correct Ans - Foreign Keys Referential Integrity vs. Entity Integrity Correct Ans - Entity integrity ensures all rows are uniquely identified by primary key Referential integrity ensures foreign keys have a match in corresponding tables The relational operators have the property of ________ Correct Ans - Closure To be considered minimally relational, the DBMS must support what key relational operators? Correct Ans - SELECT PROJECT JOIN
_____ are especially useful when you are trying to determine what values in related tables cause referential integrity problems. Correct Ans - Outer Joins Entity Relationship Diagram (ERD) Correct Ans - Represents the conceptual database as viewed by the user What kind of attribute is indicated by a dashed line in Chen notation? Correct Ans - Derived Complex _____ requirements may dictate data transformations, and they may expand the number of entities and attributes within the design Correct Ans - Information The _____ notation of entity-relationship modelling can be used for both conceptual and implementation modelling Correct Ans - UML Double-Walled entity rectangle in Chen notation Correct Ans - Identifies a weak entity Documentation Correct Ans - Helps database designers to stay on track during the design process, it also enables them to pick up the design thread when the time comes to modify the design
