WGU D426 V2 with complete verified solutions 2024

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. Dimensional Design 3 multiple choice options Sequence of columns in which each column has a one-many relationship to the next column. Dimension Hierarchy 3 multiple choice options A database that stores data in main memory, instead of or in addition to storage media. Ex: SQL Server In-Memory OLTP - Oracle Database In-Memory - MySQL In-Memory Databases 3 multiple choice options A database that is packaged with a programming language. AKA: In-Process Database (Database Architecture) - SQLite - SQL Server Compact Embedded Databases 3 multiple choice options Collection of two or more participating databases under a coordinating software layer called middleware. Federated Database 3 multiple choice options Relationships are named and classified by their cardinalities, which means count (as in the number of items in a set). HAS-A relationship. Each of the three types of binary relationships has a different cardinality. Is the maximum number of entity instances that can participate in a relationship instance. Maximum Cardinality 2 multiple choice options Minimum number of entity instances that must participate in a relationship instance. These values typically assume a value of zero (optional) or one (mandatory). Minimum Cardinality 2 multiple choice options An entity whose instances cannot exist in the database without the existence of an instance of another entity. Weak Entity 2 multiple choice options Can exist in the database independently. Strong Entity 2 multiple choice options Can be exclusive or inclusive. Can be a part of an IS-A relationship. Subtype Entities 2 multiple choice options Depicted with square corners Tables 3 multiple choice options Indicate primary key columns Bullets 3 multiple choice options Depict foreign keys. Starts at the foreign key and points to the referenced table. RESTRICT, CASCADE, or SET NULL appears on the arrow, on top read left-to-right, on bottom read right-to-left. Arrows 2 multiple choice options States that every value of a foreign key must match a value of an existing primary key or be fully NULL. Referential Integrity