2e59c88f-0398-4e4c-8482-e89b3f037864.pdf

WGU D426 V2 Study online at https://quizlet.com/_dajsjr 1. The relationship (within the relation) that describes Functional Depenhow the value of one attribute may be used to find the dency value of another attribute. 2. An attribute that can be used to find the value of another attribute in the...

WGU D426 V2 Study online at https://quizlet.com/_dajsjr 1. The relationship (within the relation) that describes Functional Depenhow the value of one attribute may be used to find the dency value of another attribute. 2. An attribute that can be used to find the value of another attribute in the relation. Determinant 3. Value can be used to find the value of every other non-key attribute in the table. Unique and minimal. Candidate Key 4. Define & manage database structures DDL 5. Adding, deleting, and modifying data - Manipulates data stored in a database. DML 6. Used to create user accounts, manage permissions DCL and access. 7. Includes CREATE, DROP, ALTER, TRUNCATE, RENAME 8. A view created and stored on the database. Under- Materialized View lying data is changed and also updated in this view. Uses memory space. 9. Not stored in memory. Only computed when used or Virtual View accessed. DDL 10. The data about the data. Describes elements such as Metadata columns, names, and data type. 11. Ordered collection of elements in parentheses. Tuple 12. % of table rows selected by a query Hit Ration 13. Reads blocks directly, without accessing an index. Table Scan 14. Reads index blocks sequentially in order to locate the Index Scan needed table blocks. 1 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 15. Database repeatedly splits the index in two until it finds a match. Binary Search 16. Database object that maps one or more tables to a single file. Tablespace 17. Specifies tables, columns, and keys. Implements database requirements in a specific database system. Also called a schema. Logical Design 18. Specifies indexes, table structures, and partitions. Physical Design Specifies how tables are organized on storage media. 19. Components of a computer system and the relation- Architecture ships between components. 20. Interact directly with database users and adminisTools trators and send queries to the query processor. Includes Connectors & APIs, Workbench, and utility programs. 21. Interprets queries. Compiles queries into low-level Query Processor instructions for the storage engine. Creates a plan to modify the database or retrieve data, and returns query results to the application. 22. Link between tools and query processor Connection 23. Creates connections and manages communication between tools and query parser. Connection Manager 24. Translates the query processor instructions into low-level file system commands that modify or retrieve data. Storage Engine 25. Accesses data on storage media. Data stored on stor- File System age media and organized in files. Contains 3 types of data: - User data (tables & indexes) - Log files (record of any changes) - Data dictionary (aka: catalog direc2 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr tory of tables, columns, keys, indexes in a relational database). One row for each database object. 26. Stores reusable information in main memory. Cache Manager 27. Retains data blocks from the file system for possible Buffer Manager reuse. 28. Arranged in hierarchy Multi-tier architecture 29. Multi-tier consisting of web browsers and web servers communicating over the internet. Web architecture 30. A software layer that emulates a complete, indepen- Virtual Machine dent computing environment. 31. A database offered as a PaaS cloud service. Cloud Database 32. A statement or proposition from which another state- Premise ment is inferred. 33. Multiple processors managed by a single operating Parallel Computer system instance. Shared memory - share the same memory and storage media. Shared storage - share storage media only. Shared nothing 34. Group of computers connected by LAN or WAN Node 35. Group of nodes connected by a LAN, managed by separate operating system instances. Similar to a parallel computer. Cluster 36. Runs on a parallel computer or cluster Parallel Database 37. Runs on multiple computers connected by a WAN Distributed Database 3 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 38. Updates data on multiple nodes of a distributed data- Distributed Transbase. Two-phase commit (nodes receive notice of actions update, store update in log, send confirmation, then receive commit). Either all nodes or no nodes must be successfully updated. 39. Updates data on a single node of a distributed data- Local Transaction base. Databases that use this are called "eventually consistent". 40. States that a distributed database cannot be ConCAP Theorem sistent, 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. 41. Maintains two or more replicas on separate storage Replicated datadevices. Advantages: High availability, fast concur- base rent reads, local reads. Disadvantages: Slow or inconsistent updates. 42. Entire catalog resides on a single node Central Catalog 43. Copy of catalog resides on each node Replicated Catalog 44. Volatile- Used to conduct daily business -Changes Operational Data in real time -Detailed Designed for specific business function -Concerned primarily with current data 45. AKA: Reporting Data or Decision Support Data -En- Analytic Data terprise-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 4 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 46. Data warehouse designed for specific business area Data Mart 47. Analytic database of raw, unprocessed data copied from multiple data sources. Data Lake 48. Optimized for analytics Data Warehouse 49. Five-step process - ETL: Extract-Transform Load 1) Data Warehouse Extract data 2) Cleanse data 3) Integrate data 4) Re- Refresh structure data 5) Load data - Open-source ETL tools: -PowerCenter -SQL Server Integration Services -Oracle Data Integrator 50. AKA: Star Schema - Consists of fact and dimension Dimensional Detables. Fact Table - Contains numeric data used to sign 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. 51. Sequence of columns in which each column has a one-many relationship to the next column. Dimension Hierarchy 52. A database that stores data in main memory, instead In-Memory Dataof or in addition to storage media. Ex: SQL Servbases er In-Memory OLTP - Oracle Database In-Memory MySQL 53. A database that is packaged with a programming language. AKA: In-Process Database (Database Architecture) - SQLite - SQL Server Compact Embedded Databases 54. Collection of two or more participating databases un- Federated Datader a coordinating software layer called middleware. base 55. Relationships are named and classified by their car- Maximum Cardidinalities, which means count (as in the number of nality items in a set). HAS-A relationship. Each of the three types of binary relationships has a different cardinal5 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr ity. Is the maximum number of entity instances that can participate in a relationship instance. 56. Minimum number of entity instances that must partic- Minimum Cardiipate in a relationship instance. These values typical- nality ly assume a value of zero (optional) or one (mandatory). 57. An entity whose instances cannot exist in the data- Weak Entity base without the existence of an instance of another entity. 58. Can exist in the database independently. Strong Entity 59. Can be exclusive or inclusive. Can be a part of an IS-A Subtype Entities relationship. 60. Depicted with square corners Tables 61. Indicate primary key columns Bullets 62. Depict foreign keys. Starts at the foreign key and Arrows 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. 63. States that every value of a foreign key must match a Referential Integrivalue of an existing primary key or be fully NULL. ty 64. Represents an intersection of the data sets AND 65. Represents a union of the data sets OR 66. Used to remove unwanted database objects from the DROP database. Will permanently remove the object and all associated data! 67. Description of the structure and format of the occur- Entity Class rences of the entity, similar to a recipe or architectural blueprints. 6 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 68. Eliminates redundancy by decomposing into two or Normalization more tables. To be a well-formed relation, every determinant must be a candidate key. Any relation that is not well-formed should be broken into two or more well-formed relations. As a general rule, a well-formed relation will not encompass more than one business concept. 69. Putting tables back together into a single table for Denormalization business needs. Used in place of a join to speed up a query, but it uses more space, data redundant, modification anomalies. 70. Multiple values removed. Only one value in each cell. First Normal Form 71. No partial dependencies. All non-key columns depend on the whole primary key. Second Normal Form 72. No transitive dependencies (Functional dependen- Third Normal cies on non-primary-key attributes. Called transitive Form because the primary key is a determinant for another attribute, which in turn is a determinant for a third. Solution: Non-key determinant with transitive dependencies goes into a new table; non-key determinant becomes primary key in the new table and remains as a foreign key in the old table). 73. Every determinate is also a candidate key during nor- Boyce-Codd Normalization. Optimal normal form for frequent inserts, mal Form updates, and deletes. One key is a super key. If Column A depends on Column B, then B must be unique. 74. Gets data from the left table that's not in the right table. Left Outer Join 75. Gets data from the right table that's not in the left table. Right Outer Join 76. Unmatched data from either table is included. Will result in empty fields. Full Outer Join 7 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 77. Returns only matching values. Inner Join 78. Combines two tables without comparing data. Cross Join 79. AKA: Clustering Index. Usually sparse. Index on a sort column. Primary Index 80. AKA: Nonclustering Index. Index that is not on the sort column. Always dense. Secondary Index 81. Entry for every table row Dense Index 82. Entry for every table block Sparse Index 83. Stores column values and row pointers in hierarchy Multi-Level Index 84. All branches are the same length Balanced Indexes 85. Index entries assigned to buckets Hash Index 86. Block or group of blocks containing rows/index en- Bucket tries. 87. Computes the bucket containing the row from the value of the indexed column. Hash Function 88. Grid of bits Bitmap Index 89. Stores table rows in each bucket Hash Table 90. Single or multi-level index that normally contains pointers to table blocks Physical Index 91. Single or multi-level index where pointers to table blocks are replaced with primary key values Logical Index 92. Database designer specifies a function on the column value consistent with a WHERE clause. Can be applied to any index type. Considered when the Function Index 8 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr WHERE clause format is different than the storage format. 93. A subset of table data. One table has many that do Partitions not overlap and, together, contain all table data. Must appear in all unique columns. May not contain foreign keys and foreign keys may not refer to table. 94. Subset of table rows. Horizontal Partition 95. Subset of table columns. Vertical Partition 96. Range of partition expression values (VALUES LESS Range Partition THAN, MAXVALUE) 97. List of partition expression values using VALUES IN List Partition keywords. 98. A partition expression with positive integer values. The partition number for each row is computed as (partition expression value)modulo N. Hash Partition 99. Similar to hash but partition expression determined Key Partition automatically by database. 100. Candidate Key - Composite Key - Primary Key - Sur- Unique rogate Key 101. Foreign Key Non-Unique 102. Used to commit, rollback changes. (called dtl in course definitions) TCL 103. Ensures transactions are properly executed. Transaction Manager 104. A file containing a complete record of all inserts, updates, and deletes processed by the database. Log 9 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 105. AKA: Data dictionary. A directory of tables, columns, Catalog and rows similar to a spreadsheet. 106. A conceptual framework for database systems with Database Model three parts: Data Structures (prescribe how data is organized), Operations (manipulates data structures), Rules (govern valid data). 107. An unordered collection of elements enclosed in braces. Set 108. Retrieves data from the database. DQL 109. A string of operators, operands, and parentheses that Expression evaluates to a single value. 110. A column, or group of columns, used to identify a row. Primary Key 111. Rejects an insert, update, or delete that violates ref- RESTRICT erential integrity. 112. A rule that governs allowable values in a database based on relational and business rules and implemented with special keywords. Constraint 113. Specifies an expression on one or more columns of Check a table. Violated when the expression is false and satisfied when the expression is true or null. 114. The LIKE operator performs case-insensitive pattern BINARY matching by default or case-sensitive pattern matching if followed by this keyword. 115. Used with GROUP BY to filter results. HAVING 116. Combines two query results into one table. UNION 117. Which operator is considered an equijoin? = 118. Correlated 10 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr _______________ is when a subquery's WHERE clause references a column from the outer query. 119. Replacing a subquery with an equivalent join is called Flattening what? 120. The table specified in the view query's FROM clause. Base Table 121. Converts a SQL query into a sequence of low-level Query Optimizer database actions, called the query execution plan, which specifies precisely how to process a SQL statement. 122. A high-level representation of data requirements, ig- Entity-Relationnoring implementation details. ship Model 123. A schematic picture of entities, relationships, and attributes. ER Diagram 124. Relates an entity to itself Reflexive Relationship 125. Develops an entity-relationship model capturing data Analysis requirements while ignoring implementation details. 126. Refers to the maxima and minima of relationships and attributes. Cardinality 127. Unique, singular, and required. Identifying Attribute 128. A group of related entities from a complex model. Subject Area 129. Created by a database designer when no suitable single-column or composite primary key exists. Artificial Key 130. Column A ______________________________ Col- Depends On umn B means that each B value is related to at most one A value. 11 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 131. Rules for designing tables with less redundancy. Normal Forms 132. The speed at which data is read or written, following Transfer Rate initial access. 133. Memory that is lost when disconnected from power. Volatile Memory 134. The primary memory used when computer programs Random-Access execute. aka: main memory Memory 135. AKA: Solid State Drive. Less expensive and higher capacity than main memory. Groups data in pages, usually between 2 kb and 16 kb per page. Flash Memory 136. AKA: Hard-Disk Drive. Used to share large amounts Magnetic Disk of data. Groups data in sectors, traditionally 512 bytes per sector, but 4 kb with newer disk formats. 137. A uniform size used when transferring data between Block main memory and storage media. To minimize transfers, relational databases usually store an entire row in one, which is called row-oriented storage. In column-oriented storage, each one stores values for a single column only. 138. A hash function with four steps Modulo Function 139. Automatically allocates more blocks to the table, cre- Dynamic Hash ates additional buckets, and distributes rows across Function all buckets. 140. AKA: Multi-tables. Interleave rows of two or more Table Clusters tables in the same storage area. They contain cluster keys. 141. A file containing column values, along with pointers Single-Level Index to rows containing the column value. 142. Multi-Column Index 12 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr Each index entry is a composite of values from all indexed columns. Behave exactly like indexes on a single column. 143. The number of index entries per block of a multi-level Fan-Out index. 144. The path from the top-level block to the bottom-level Branch block. 145. All indexed values appear in the bottom level B+Tree 146. If an indexed value appears in a higher level of mul- B-Tree ti-level index, the value is not repeated at lower levels in _____. 147. A subset of table data, usually a subset of rows rather Shard than columns. Stored on different computers of a distributed database. 148. File that records all long-running queries submitted Slow Query Log to the database. 149. Checks each query for syntax errors and converts valid queries to an internal representation. Query Parser 150. Conforms to all rules at all times. Consistent 151. Forms when a network error prevents nodes from communicating. Network Partition 152. Database that continues to function when a network Partition Tolerant partition occurs. 153. Manage replicas internally, without database intervention. Storage Arrays 154. Adding start and end foreign keys to the fact table for Type 2 Design slowly-changing dimensions. 13 / 14 WGU D426 V2 Study online at https://quizlet.com/_dajsjr 155. Operates independently of other participating data- Autonomous bases. Database 156. Either run under different database systems or have Heterogenous incompatible schema. Databases 157. A directory of participating database objects such as Global Catalog tables, columns, and indexes. 158. Converts the decomposed queries to the appropriate Database Wrapsyntax for each participating database. per 159. A federated database name for a participating data- Nickname base object, such as tables and columns. 160. Associates a federated database user with a partici- User Mapping pating database user. 14 / 14