Download Database Concepts: 200 Questions and Answers and more Exams Community Corrections in PDF only on Docsity!
D426 (v2) (200 Questions and
Answers).
The relationship that describes how one attribute's value can determine another's - Correct answer Functional Dependency An attribute determining the value of another attribute - Correct answer Determinant Unique and minimal attribute determining non-key attributes - Correct answer Candidate Key Defines and manages database structures - Correct answer DDL Manipulates data: adding, deleting, modifying - Correct answer DML Manages user accounts and permissions - Correct answer DCL Commands: CREATE, DROP, ALTER, TRUNCATE, RENAME - Correct answer DDL Stored view that updates when underlying data changes - Correct answer Materialized View Computed view not stored in memory - Correct answer Virtual View Information describing data elements - Correct answer Metadata Ordered collection of elements in parentheses - Correct answer Tuple Percentage of table rows a query selects - Correct answer Hit Ratio Directly reads blocks without using an index - Correct answer Table Scan Reads index blocks to locate table blocks - Correct answer Index Scan
Database splits the index until a match is found - Correct answer Binary Search Database object mapping tables to a file - Correct answer Tablespace Specifies tables and keys, also called a schema - Correct answer Logical Design Specifies how tables are stored on media - Correct answer Physical Design Components and relationships in a computer system - Correct answer Architecture Direct interface with database users, includes APIs - Correct answer Tools Interprets and compiles queries - Correct answer Query Processor Link between tools and query processor - Correct answer Connection Manages communication between tools and query parser - Correct answer Connection Manager Translates query instructions to file system commands - Correct answer Storage Engine Accesses data on storage media, includes logs and user data - Correct answer File System Stores reusable information in memory - Correct answer Cache Manager Retains file system data blocks for reuse - Correct answer Buffer Manager Hierarchical arrangement - Correct answer Multi-tier architecture Multi-tier with web browsers and servers over the internet - Correct answer Web architecture Software emulating a complete computing environment - Correct answer Virtual Machine
Enterprise-wide data for understanding and planning - Correct answer Analytic Data Data warehouse for a specific business area - Correct answer Data Mart Raw data from multiple sources - Correct answer Data Lake Database optimized for analytics - Correct answer Data Warehouse ETL process: Extract, Cleanse, Integrate, Restructure, Load - Correct answer Data Warehouse Refresh Fact and dimension tables in a star schema - Correct answer Dimensional Design Sequence of columns with one-many relationships - Correct answer Dimension Hierarchy Database storing data in main memory - Correct answer In-Memory Databases Database packaged with a programming language - Correct answer Embedded Databases Multiple databases coordinated by middleware - Correct answer Federated Database Relationship cardinalities - Correct answer Maximum Cardinality Minimum entities in a relationship - Correct answer Minimum Cardinality Attribute that's unique and singular - Correct answer Identifying Attribute Group of related entities in a complex model - Correct answer Subject Area Key created when no suitable primary key exists - Correct answer Artificial Key
Often represented with square corners in diagramming tools - Correct answer Tables Indicate primary key columns - Correct answer Bullets 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 - Correct answer Arrows States that every value of a foreign key must match a value of an existing primary key or be fully NULL - Correct answer Referential Integrity Represents an intersection of the data sets - Correct answer AND Represents a union of the data sets - Correct answer OR Used to remove unwanted database objects from the database. Will permanently remove the object and all associated data - Correct answer DROP Description of the structure and format of the occurrences of the entity, similar to a recipe or architectural blueprints - Correct answer Entity Class Eliminates redundancy by decomposing into two or 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 - Correct answer Normalization Putting tables back together into a single table for business needs. Used in place of a join to speed up a query, but it uses more space, data redundant, modification anomalies - Correct answer Denormalization Multiple values removed. Only one value in each cell - Correct answer First Normal Form No partial dependencies. All non-key columns depend on the whole primary key - Correct answer Second Normal Form
Index entries assigned to buckets - Correct answer Hash Index Block or group of blocks containing rows/index entries - Correct answer Bucket Computes the bucket containing the row from the value of the indexed column - Correct answer Hash Function Grid of bits - Correct answer Bitmap Index Stores table rows in each bucket - Correct answer Hash Table Single or multi-level index that normally contains pointers to table blocks - Correct answer Physical Index Single or multi-level index where pointers to table blocks are replaced with primary key values - Correct answer Logical Index Database designer specifies a function on the column value consistent with a WHERE clause. Can be applied to any index type. Considered when the WHERE clause format is different than the storage format - Correct answer Function Index A subset of table data. One table has many that do 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 - Correct answer Partitions Subset of table rows - Correct answer Horizontal Partition Subset of table columns - Correct answer Vertical Partition Range of partition expression values (VALUES LESS THAN, MAXVALUE)
- Correct answer Range Partition List of partition expression values using VALUES IN keywords - Correct answer List Partition
A partition expression with positive integer values. The partition number for each row is computed as (partition expression value) modulo N - Correct answer Hash Partition Similar to hash but partition expression determined automatically by database - Correct answer Key Partition Candidate Key - Composite Key - Primary Key - Surrogate Key - Correct answer Unique Foreign Key - Correct answer Non-Unique Used to commit, rollback changes (called dtl in course definitions) - Correct answer TCL Ensures transactions are properly executed - Correct answer Transaction Manager A file containing a complete record of all inserts, updates, and deletes processed by the database - Correct answer Log AKA: Data dictionary. A directory of tables, columns, and rows similar to a spreadsheet - Correct answer Catalog A conceptual framework for database systems with three parts: Data Structures (prescribe how data is organized), Operations (manipulates data structures), Rules (govern valid data) - Correct answer Database Model An unordered collection of elements enclosed in braces - Correct answer Set Retrieves data from the database - Correct answer DQL A string of operators, operands, and parentheses that evaluates to a single value - Correct answer Expression A column, or group of columns, used to identify a row - Correct answer Primary Key
Develops an entity-relationship model capturing data requirements while ignoring implementation details - Correct answer Analysis Refers to the maxima and minima of relationships and attributes - Correct answer Cardinality Unique, singular, and required - Correct answer Identifying Attribute Group of related entities from a complex model - Correct answer Subject Area Created by a database designer when no suitable single-column or composite primary key exists - Correct answer Artificial Key Column A depends on Column B - Correct answer Depends On Rules for designing tables with less redundancy - Correct answer Normal Forms The speed at which data is read or written, following initial access - Correct answer Transfer Rate Memory that is lost when disconnected from power - Correct answer Volatile Memory The primary memory used when computer programs execute - Correct answer Random-Access Memory Non-volatile memory technology used for storage - Correct answer Flash Memory Storage device for large data amounts, grouping data in sectors - Correct answer Magnetic Disk Uniform size for data transfers between memory and storage - Correct answer Block Hash function with four steps - Correct answer Modulo Function
Hash function that automatically allocates more blocks - Correct answer Dynamic Hash Function Tables interweaving rows in the same storage area - Correct answer Table Clusters File with column values and row pointers - Correct answer Single-Level Index Index on multiple columns - Correct answer Multi-Column Index Number of index entries per block in a multi-level index - Correct answer Fan-Out Path from the top to the bottom of a multi-level index - Correct answer Branch Tree structure with all indexed values in the bottom level - Correct answer B+Tree Tree structure where indexed values aren't repeated at lower levels if they appear higher - Correct answer B-Tree Subset of table data stored on different computers - Correct answer Shard File recording all long-running queries - Correct answer Slow Query Log Converts SQL queries to an internal format - Correct answer Query Parser Database always adhering to all rules - Correct answer Consistent Occurs when network errors prevent node communication - Correct answer Network Partition A characteristic of a database system that continues to function despite network partitions - Correct answer Partition-tolerance Devices managing replicas without database intervention - Correct answer Storage Arrays
A relation where each column contains atomic, indivisible values and has a unique primary key. - Correct answer 1NF A relation in 1NF where non-key attributes are fully functionally dependent on the primary key. - Correct answer 2NF A relation in 2NF where there are no transitive functional dependencies. - Correct answer 3NF A stricter version of 3NF where all dependencies are based on candidate keys. - Correct answer BCNF SQL sublanguage used to define and manage tables and the structure of the database. - Correct answer DDL SQL sublanguage used to retrieve, insert, update, and delete data in the database. - Correct answer DML SQL sublanguage used to manage access to the data and database. - Correct answer DCL SQL sublanguage used in conjunction with DML to manage transactions. - Correct answer TCL/DTL Functions in SQL that process multiple rows of a single column of a table and return a single value. - Correct answer Aggregate Functions Counts the number of rows in a table. - Correct answer COUNT Returns the smallest value in a column. - Correct answer MIN Returns the largest value in a column. - Correct answer MAX Returns the sum of all values in a column. - Correct answer SUM Returns the average of all values in a column. - Correct answer AVG Returns rows when there is a match in both tables. - Correct answer Inner Join
Returns all rows from the left table and the matching rows from the right table. - Correct answer Left Join Returns all rows from the right table and the matching rows from the left table. - Correct answer Right Join Returns rows when there is a match in one of the tables. - Correct answer Full Join Returns the Cartesian product of the two tables. - Correct answer Cross Join Defines the maximum number of times an instance in one entity can relate to instances of another entity. - Correct answer Cardinalities A relationship where one record in a table relates to one and only one record in another table. - Correct answer One-to-One A relationship where one record in a table can relate to multiple records in another table. - Correct answer One-to-Many A relationship where multiple records in one table relate to a single record in another table. - Correct answer Many-to-One A relationship where multiple records in one table can relate to multiple records in another table. - Correct answer Many-to-Many The initial stage in database design where the requirements of the database are collected and defined. - Correct answer Analysis Phase The stage where the conceptual design is transformed into a logical schema. - Correct answer Logical Design Phase The stage where the logical schema is transformed into detailed physical storage structures. - Correct answer Physical Design Phase Characteristics or properties that define an entity within a database. - Correct answer Attributes
