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