WGU D426 V2 with complete verified solutions.

The relationship (within the relation) that describes how the value of one attribute may be used to find the value of another attribute. Functional Dependency 3 multiple choice options An attribute that can be used to find the value of another attribute in the relation. Determinant 3 multiple choice options Value can be used to find the value of every other non-key attribute in the table. Unique and minimal. Candidate Key 3 multiple choice options Define & manage database structures DDL 2 multiple choice options Adding, deleting, and modifying data - Manipulates data stored in a database. DML 2 multiple choice options Used to create user accounts, manage permissions and access. DCL 2 multiple choice options Includes CREATE, DROP, ALTER, TRUNCATE, RENAME DDL 2 multiple choice options A view created and stored on the database. Underlying data is changed and also updated in this view. Uses memory space. Materialized View 2 multiple choice options Not stored in memory. Only computed when used or accessed. Virtual View 2 multiple choice options The data about the data. Describes elements such as columns, names, and data type. Metadata 3 multiple choice options Ordered collection of elements in parentheses. Tuple 3 multiple choice options % of table rows selected by a query Hit Ration 3 multiple choice options Reads blocks directly, without accessing an index. Table Scan 3 multiple choice options Reads index blocks sequentially in order to locate the needed table blocks. Index Scan 2 multiple choice options Database repeatedly splits the index in two until it finds a match. Binary Search 3 multiple choice options Database object that maps one or more tables to a single file. Tablespace 3 multiple choice options Specifies tables, columns, and keys. Implements database requirements in a specific database system. Also called a schema. Logical Design 3 multiple choice options Specifies indexes, table structures, and partitions. Specifies how tables are organized on storage media. Physical Design 3 multiple choice options Components of a computer system and the relationships between components. Architecture 3 multiple choice options Interact directly with database users and administrators and send queries to the query processor. Includes Connectors & APIs, Workbench, and utility programs. Tools 3 multiple choice options Interprets queries. Compiles queries into low-level instructions for the storage engine. Creates a plan to modify the database or retrieve data, and returns query results to the application. Query Processor 3 multiple choice options Link between tools and query processor Connection 3 multiple choice options Creates connections and manages communication between tools and query parser. Connection Manager 3 multiple choice options Translates the query processor instructions into low-level file system commands that modify or retrieve data. Storage Engine 3 multiple choice options Accesses data on storage media. Data stored on storage media and organized in files. Contains 3 types of data: - User data (tables & indexes) - Log files (record of any changes) - Data dictionary (aka: catalog directory of tables, columns, keys, indexes in a relational database). One row for each database object. File System 2 multiple choice options Stores reusable information in main memory. Cache Manager 3 multiple choice options Retains data blocks from the file system for possible reuse. Buffer Manager 3 multiple choice options Arranged in hierarchy Multi-tier architecture 3 multiple choice options Multi-tier consisting of web browsers and web servers communicating over the internet. Web architecture 3 multiple choice options A software layer that emulates a complete, independent computing environment. Virtual Machine 3 multiple choice options A database offered as a PaaS cloud service. Cloud Database 3 multiple choice options A statement or proposition from which another statement is inferred. Premise 3 multiple choice options Multiple processors managed by a single operating system instance. Shared memory - share the same memory and storage media. Shared storage - share storage media only. Shared nothing Parallel Computer 3 multiple choice options Group of computers connected by LAN or WAN Node 3 multiple choice options Group of nodes connected by a LAN, managed by separate operating system instances. Similar to a parallel computer. Cluster 3 multiple choice options Runs on a parallel computer or cluster Parallel Database 3 multiple choice options Runs on multiple computers connected by a WAN Distributed Database 3 multiple choice options Updates data on multiple nodes of a distributed database. Two-phase commit (nodes receive notice of update, store update in log, send confirmation, then receive commit). Either all nodes or no nodes must be successfully updated. Distributed Transactions 3 multiple choice options Updates data on a single node of a distributed database. Databases that use this are called "eventually consistent". Local Transaction 3 multiple choice options States that a distributed database cannot be Consistent, Available, and Petition-tolerant simutaneously. Availability refers to individual nodes. Consistent database - confirms to all rules at all times. Network partition - forms when a network error prevents nodes from communicating. Petition-tolerant - database that continues to function when a network partition occurs. CAP Theorem 3 multiple choice options Maintains two or more replicas on separate storage devices. Advantages: High availability, fast concurrent reads, local reads. Disadvantages: Slow or inconsistent updates. Replicated database 3 multiple choice options Entire catalog resides on a single node Central Catalog 1 multiple choice option Copy of catalog resides on each node Replicated Catalog 1 multiple choice option Volatile- Used to conduct daily business -Changes in real time -Detailed Designed for specific business function -Concerned primarily with current data Operational Data 3 multiple choice options AKA: Reporting Data or Decision Support Data -Enterprise-wide- Used to understand, manage, and plan the business- Summary Updated at fixed intervals and summarized by time period -Combines data from many business functions -Usually contains current and historic data Analytic Data 3 multiple choice options Data warehouse designed for specific business area Data Mart 2 multiple choice options Analytic database of raw, unprocessed data copied from multiple data sources. Data Lake 2 multiple choice options Optimized for analytics Data Warehouse 2 multiple choice options Five-step process - ETL: Extract-Transform Load 1) Extract data 2) Cleanse data 3) Integrate data 4) Restructure data 5) Load data - Open-source ETL tools: -PowerCenter -SQL Server Integration Services -Oracle Data Integrator Data Warehouse Refresh 3 multiple choice options AKA: Star Schema - Consists of fact and dimension tables. Fact Table - Contains numeric data used to measure business performance.. Primary key is the composite for all foreign keys referencing dimension tables. Dimension Table - Contains textual data that describes the fact data. The relationship (within the relation) that describes how the value of one attribute may be used to find the value of another attribute. Functional Dependency 3 multiple choice options An attribute that can be used to find the value of another attribute in the relation. Determinant 3 multiple choice options Value can be used to find the value of every other non-key attribute in the table. Unique and minimal. Candidate Key 3 multiple choice options Define & manage database structures DDL 2 multiple choice options Adding, deleting, and modifying data - Manipulates data stored in a database. DML 2 multiple choice options Used to create user accounts, manage permissions and access. DCL 2 multiple choice options Includes CREATE, DROP, ALTER, TRUNCATE, RENAME DDL 2 multiple choice options A view created and stored on the database. Underlying data is changed and also updated in this view. Uses memory space. Materialized View 2 multiple choice options Not stored in memory. Only computed when used or accessed. Virtual View 2 multiple choice options The data about the data. Describes elements such as columns, names, and data type. Metadata 3 multiple choice options Ordered collection of elements in parentheses. Tuple 3 multiple choice options % of table rows selected by a query Hit Ration 3 multiple choice options Reads blocks directly, without accessing an index. Table Scan 3 multiple choice options Reads index blocks sequentially in order to locate the needed table blocks. Index Scan 2 multiple choice options Database repeatedly splits the index in two until it finds a match. Binary Search 3 multiple choice options Database object that maps one or more tables to a single file. Tablespace 3 multiple choice options Specifies tables, columns, and keys. Implements database requirements in a specific database system. Also called a schema. Logical Design 3 multiple choice options Specifies indexes, table structures, and partitions. Specifies how tables are organized on storage media. Physical Design 3 multiple choice options Components of a computer system and the relationships between components. Architecture 3 multiple choice options Interact directly with database users and administrators and send queries to the query processor. Includes Connectors & APIs, Workbench, and utility programs. Tools 3 multiple choice options Interprets queries. Compiles queries into low-level instructions for the storage engine. Creates a plan to modify the database or retrieve data, and returns query results to the application. Query Processor 3 multiple choice options Link between tools and query processor Connection 3 multiple choice options Creates connections and manages communication between tools and query parser. Connection Manager 3 multiple choice options Translates the query processor instructions into low-level file system commands that modify or retrieve data. Storage Engine 3 multiple choice options Accesses data on storage media. Data stored on storage media and organized in files. Contains 3 types of data: - User data (tables & indexes) - Log files (record of any changes) - Data dictionary (aka: catalog directory of tables, columns, keys, indexes in a relational database). One row for each database object. File System 2 multiple choice options Stores reusable information in main memory. Cache Manager 3 multiple choice options Retains data blocks from the file system for possible reuse. Buffer Manager 3 multiple choice options Arranged in hierarchy Multi-tier architecture 3 multiple choice options Multi-tier consisting of web browsers and web servers communicating over the internet. Web architecture 3 multiple choice options A software layer that emulates a complete, independent computing environment. Virtual Machine 3 multiple choice options A database offered as a PaaS cloud service. Cloud Database 3 multiple choice options A statement or proposition from which another statement is inferred. Premise 3 multiple choice options Multiple processors managed by a single operating system instance. Shared memory - share the same memory and storage media. Shared storage - share storage media only. Shared nothing Parallel Computer 3 multiple choice options Group of computers connected by LAN or WAN Node 3 multiple choice options Group of nodes connected by a LAN, managed by separate operating system instances. Similar to a parallel computer. Cluster 3 multiple choice options Runs on a parallel computer or cluster Parallel Database 3 multiple choice options Runs on multiple computers connected by a WAN Distributed Database 3 multiple choice options Updates data on multiple nodes of a distributed database. Two-phase commit (nodes receive notice of update, store update in log, send confirmation, then receive commit). Either all nodes or no nodes must be successfully updated. Distributed Transactions 3 multiple choice options Updates data on a single node of a distributed database. Databases that use this are called "eventually consistent". Local Transaction 3 multiple choice options States that a distributed database cannot be Consistent, Available, and Petition-tolerant simutaneously. Availability refers to individual nodes. Consistent database - confirms to all rules at all times. Network partition - forms when a network error prevents nodes from communicating. Petition-tolerant - database that continues to function when a network partition occurs. CAP Theorem 3 multiple choice options Maintains two or more replicas on separate storage devices. Advantages: High availability, fast concurrent reads, local reads. Disadvantages: Slow or inconsistent updates. Replicated database 3 multiple choice options Entire catalog resides on a single node Central Catalog 1 multiple choice option Copy of catalog resides on each node Replicated Catalog 1 multiple choice option Volatile- Used to conduct daily business -Changes in real time -Detailed Designed for specific business function -Concerned primarily with current data Operational Data 3 multiple choice options AKA: Reporting Data or Decision Support Data -Enterprise-wide- Used to understand, manage, and plan the business- Summary Updated at fixed intervals and summarized by time period -Combines data from many business functions -Usually contains current and historic data Analytic Data 3 multiple choice options Data warehouse designed for specific business area Data Mart 2 multiple choice options Analytic database of raw, unprocessed data copied from multiple data sources. Data Lake 2 multiple choice options Optimized for analytics Data Warehouse 2 multiple choice options Five-step process - ETL: Extract-Transform Load 1) Extract data 2) Cleanse data 3) Integrate data 4) Restructure data 5) Load data - Open-source ETL tools: -PowerCenter -SQL Server Integration Services -Oracle Data Integrator Data Warehouse Refresh 3 multiple choice options AKA: Star Schema - Consists of fact and dimension tables. Fact Table - Contains numeric data used to measure business performance.. Primary key is the composite for all foreign keys referencing dimension tables. Dimension Table - Contains textual data that describes the fact data.