Download D426 WGU Data Management - Foundations and more Study Guides, Projects, Research Computer Science in PDF only on Docsity!
1 / 15
D426 WGU Data Management - Foundations
1. Data: Numeric, textual, visual, or audio information that describes real-world systems. It is collected and processed to aid in a variety of tasks.
2. Analog Data: Most common before computers. Audio was recorded as vibrations impressed on vinyl disks.
3. Digital Data: Most common today. Encoded as 0s and 1s on electronic magnetic data.
4. Database: A collection of data in structured format. In principle, can be stored on paper or even clay tablets.
5. Database System (DBMS): Software that reads and writes in a database.
6. Query: A request to retrieve or change data in a database. Typically inserts new data, retrieves data, updates, data, or deletes data from a database.
7. Query Language: Specialized programming language designed specifically for database systems. Reads and writes data eflciently.
8. Database Application: Software that HELPS business users interact with database systems.
9. Database Administrator: What role am I?
Responsible for securing the database system against unauthorized users. They enforce procedures for user access and database system availability.
10. Database Designer: What role am I?
Determines the format of each data element AND the overall database STRUCTURE. Must balance priorities like including storage, response time, and support for rules that govern the data.
11. Database Programmer: What role am I?
Develop programs that utilize a database and write applications that combine query languages and general purpose programming language.
12. Database User: What role am I?
A consumer of data in a database. They request, update, or use stored data to generate reports or information.
13. Transaction: A group of queries that MUST be completed or rejected as a whole.
14. Architecture: Describes the internal components AND the relationships between components.
2 / 15
15. Query Processor: Interprets queries, creates a plan to modify the database or retrieve the data, and returns query results to the application. Performs query
optimization.
16. Query Optimization: Ensures eflcient instructions are executed on the data from the Query Processor.
4 / 15 diagram.
36. Database Design - Physical Design: This phase adds indexes and specifies HOW tables are organized on storage media. Specified
with SQL statements.
5 / 15
37. Key: A column used to identify individual rows of a table. Used to refer to only one specific row or record.
38. Application Programming Language (API): A library of procedures or classes that links a host programming language to a database.
39. Schema: The structure of tables in the database.
40. Foreign Key: What a primary key from one table is called when it is referenced in another table. Important when starting to build relationships between
tables. Refers to a primary key.
41. One-to-Many: This is the most common relationship. Associates ONE record in ONE table with MULTIPLE records in another table.
42. One-to-One: Associates only ONE record on one table with ONE record on another table.
43. Data Manipulation Language (DML): What language am I?
Allows users to access and modify the data to MAKE queries, and GENERATE reports. Interacting with the data.
44. Data Definition Language (DDL): What language am I?
Allows a database administrator to DEFINE the database structure, schema, and subschema.
45. Data Control Language (DCL): What language am I?
Commands that CONTROL a database. Includes administering privileges and committing data.
46. MySQL Community/MySQL Server: Is a FREE EDITION. Includes a complete set of database services and tools, and is suitable
for non-commercial applications such as education.
47. MySQL Enterprise: PAID EDITION for managing commercial databases. Includes MySQL Server AND additional administrative
applications.
48. MySQL Command-Line Client: A text interface included in the MySQL Server download. Allows developers to connect to the
database server, perform administrative functions, and execute SQL statements.
49. MySQL Workbench: Installed with MySQL Server AND allows developers to EXECUTE SQL commands using an editor.
50. Database Model: A conceptual framework for database software containing 3 parts:
7 / 15 Data Structures Operations Rules
52. Set: A collection of values or elements with NO INHERENT order / unordered. Denoted with braces {}.
53. Domain: A named set of possible database values, such as integers, dictionary words, or logical values (TRUE and FALSE).
54. Tuple: A finite sequence of values, each drawn from a fixed domain. Ordered in parentheses.
55. Relation: A NAMED set of tuples, all drawn from the same sequence of domains.
56. Relational Rules/Integrity Rules: Logical constraints that ensure data is valid and conforms to business policy. Governs the data.
57. Structural Rules: Relational rules that govern data in every relational database.
58. Business Rules: Relational rules specific to a particular database AND application. Discovered during database design.
59. Table: A collection of data organized as columns and rows. Has a name, a fixed sequence of columns, and a varying set of rows.
60. Column: Has a name and a data type.
61. Row: An unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type.
62. Cell: A single column of a single row. In relational databases, each contains exactly one value.
63. Aggregate: Computes functions over multiple table rows, such as SUM and COUNT.
64. Join: A SELECT statement that combines data from two tables (left and right), into a single result. The tables are combined by comparing columns from the
left and right tables.
65. Data Query Language (DQL): What language am I?
Retrieves data from the database.
66. Data Transaction Language (DTL): What language am I?
Manages database transactions.
67. Database System Instance: A single executing copy of a database system. Personal computers usually run just one.
68. DROP TABLE: This statement deletes a table, along with all the table's rows, from a database.
8 / 15
69. Operator: A symbol that computes a value from one or more other values called operands.
10 / 15
91. RIGHT JOIN: Selects all RIGHT table rows, but only matching left table rows.
92. OUTER JOIN: ANY JOIN that selects unmatched rows, including, left, right, and full joins.
93. EQUIJOIN: Compares columns of two tables with the = operator.
94. NON-EQUIJOIN: Compares columns with an operator OTHER THAN =, such as < and >.
95. SELF-JOIN: Joins a table to ITSELF.
96. CROSS-JOIN: Combines two tables WITHOUT comparing columns.
97. DISTINCT clause: Used with a SELECT statement to return only unique or "distinct" values.
98. IN operator: This operator is used in a WHERE clause to determine if a value matches one of several values.
99. Subquery: Sometimes called a nested query or inner query, is a query within another SQL query.
100. Flattening: Replacing a subquery with an equivalent join.
101. Entity Relationship Model: A high-level representation of data requirements, ignoring implementa- tion details. It guides implementation in a
particular database system, such as MySQL.
102. Entity: A person, place, product, concept, or activity.
103. Relationship: A statement about two entities. It links them together.
104. Attribute: A descriptive property of an entity.
105. ER Diagram: A schematic picture of entities, relationships, and attributes.
106. Glossary: Documents additional detail in text format. Also known as a data dictionary or repository.
107. Cardinality: Refers to the maximum number of times INSTANCES in one entity can be related to INSTANCES in another entity.
108. Modality: Refers to the MINIMUM number of times that an instance in one entity can be related to an instance in another entity.
109. Indexes: Used by the storage manager to quickly locate data.
110. Relational Algebra: Operations that are the theoretical foundation of SQL.
111. Constraints: Implemented rules that are enforced by the database system.
112. ALTER: This statement adds, deletes, or modifies columns on an existing table.
11 / 15
113. Truth Tables: The value of logical expressions containing NULL operands.
114. Identifying Attribute: This attribute is unique, singular, and required.
115. Strong Entity: This entity has one or more identifying attributes.
116. Weak Entity: This entity doesn't have an identifying attribute. Usually has a relationship called an identifying relationship, to another entity, called an
identifying entity.
117. Tablespace: A database object that maps one or more tables to a single file.
118. Data Warehouse: A separate database optimized for analytics rather than operations.
119. Data Mart: This is a data warehouse designed for a specific business area likes sales, HR, or product development.
120. Data Lake: An analytic database of raw, unprocessed data copied from multiple data sources.
121. Nickname: A federated database name of a participating database object, such as tables and columns.
122. ALTER TABLE: This statement adds, deletes, or modifies columns on an existing table.
123. Materialized View: A view for which data is stored at ALL TIMES.
124. Base Table: A table specified in the view query's FROM clause.
125. Reflexive Relationship: Relates an entity to itself.
126. IsA Relationship: A supertype entity identifies its subtype entities.
127. Partition: A group of mutually exclusive subtype entities.
128. Partition Attribute: This is what each partition corresponds to of the supertype entity.
129. Artificial Key: A single-column primary key created by the database designer when no suitable single-col- umn or composite primary key exists.
130. First Normal Form (1NF): What normal form am I?
Every cell of a table contains exactly one value. Contains a primary key as well.
131. Second Normal Form (2NF): What normal form am I?
All non-key columns depend on the whole primary key.
13 / 15 An index on a sort column.
146. Secondary Index/Nonclustering Index: What type of index am I?
An index that is not on the sort column.
147. Binary Search: The database repeatedly splits the index in two until it finds the entry containing the search value.
148. Multi-Level Index: What type of index am I?
Stores column values and row pointers in a hierarchy.
149. B+Tree: All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level. Since some indexed values also appear in
higher levels, values are occasionally repeated in the index.
150. B-Tree: If an indexed value appears in a higher level, the value is not repeated at lower levels. Instead, a pointer to the corresponding table block appears in
the higher level along with the value.
151. Hash Index: What type of index am I?
Index entries are assigned to buckets.
152. Bitmap Index: What type of index am I?
This index is a grid of bits.
153. Physical Index: What type of index am I?
A single or multi-level index normally contains pointers to table blocks.
154. Logical Index: What type of index am I?
A single or multi-level index in which pointers to table blocks are replaced with primary key values.
155. Bucket: A block or group of blocks containing index entries.
156. Entity-Relationship Model: A high-level representation of data requirements, ignoring implementa- tion details.
157. Subtype Entity/Supertype Entity: A subset of another entity type.
14 / 15
158. Subtype Table: This is what a subtype entity becomes.
159. Weak Table: This is what a weak entity becomes.
160. Normal Forms: Rules for designing tables with less redundancy.
161. Non-key: This is a column that is NOT contained in a candidate key.
162. Transfer Rate: The speed at which data is read or written, following initial access.
163. Access Time: The time required to access the first byte in a read or write operation.
164. Volatile Memory: Memory that is lost when disconnected from power.
165. Non-volatile Memory: Memory that is retained without power.
166. Block: This is what databases and file systems use as a uniform size when transferring data between main memory and storage media.
167. Row-oriented Storage: To minimize block transfers, relational databases usually store an entire row within one block.
168. Column-oriented/Columnar Storage: Each block stores values for a single COLUMN only.
169. Table Structure: A scheme for organizing rows in blocks on storage media.
170. Hash Key: A column or group of columns, usually the primary key.
171. Hash Function: Computes the bucket containing the row from the hash key.
172. Dynamic Hash Function: Automatically allocates more blocks to the table, creates additional buckets, and distributes rows across all buckets.
173. Cluster Key: This is what table clusters have. It is a column that is available in all interleaved tables.
174. Table Clusters/Multi-Tables: Interleave rows of two or more tables in the same storage area.
175. Shard: Like a partition, this is a subset of table data, usually a subset of rows rather than columns. These are stored on ditterent computers of a distributed
database.
176. Vertical Partition: What type of partition am I?
A subset of table columns.
177. Horizontal Partition: What type of partition am I?
