WGU D426 Web Development Foundations Terms Explained

WGU D426 Web Development Foundations Terms Explained WGU D426 Web Development Foundations Terms Explained Data << correct answer >> Numeric, textual, visual, or audio information that describes real-world systems. Analog << correct answer >> Historically, data was mostly analog, encoded as continuous variations on various physical media. Digital << correct answer >> Today, data is mostly digital, encoded as zeros and ones on electronic and magnetic media. Database << correct answer >> A collection of data in a structured format. Database system << correct answer >> Software that reads and writes data in a database, ensuring data is secure, consistent, and available. Query language << correct answer >> A specialized programming language designed for database systems. It is a computer programming language for writing database queries. Database application << correct answer >> Software that helps business users interact with database systems. Database administrator << correct answer >> Is responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability. Database designer << correct answer >> Determines the format of data elements and the overall database structure. It is a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification. Database programmer << correct answer >> Develops computer programs that utilize a database. Database user << correct answer >> A consumer of data in a database who requests, updates, or uses stored data. Transaction << correct answer >> A group of queries that must be completed or rejected as a whole. Database Architecture << correct answer >> The internal components and relationships of a database system. Query processor << correct answer >> Interprets queries, creates a plan to modify or retrieve data, and returns query results. The query processor performs query optimization to ensure the most efficient instructions are executed on the data. Storage manager << correct answer >> Translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data. Indexes << correct answer >> Used by the storage manager to quickly locate data. Transaction manager << correct answer >> Ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure. Log << correct answer >> A file containing a complete record of all database changes. Catalog << correct answer >> A directory of tables, columns, indexes, and other database objects. Relational database << correct answer >> Stores data in tables, columns, and rows. A type of database that uses a structure called a relational model to organize and manage data. Data is organized into tables, where each table consists of rows and columns. SQL << correct answer >> Structured Query Language, the standard query language of relational databases. It was first developed at IBM in the 1970s as an experimental query language for a prototype relational database. Structured Query Language << correct answer >> The standard query language of relational databases. A high-level language for storing, manipulating, and retrieving data. Big data << correct answer >> Massive volumes of online data, often with poorly structured or missing information. NoSQL << correct answer >> Non-relational systems optimized for big data. Open source << correct answer >> Software that can be inspected, copied, and modified with no licensing fee. Query << correct answer >> A command for a database that inserts, retrieves, updates, or deletes data. CRUD << correct answer >> Create, Read, Update, and Delete data. Statement << correct answer >> A database command, such as a query or update. A SQL statement is a complete command composed of one or more clauses. Data type << correct answer >> A named set of values, from which column values are drawn. The format of column values. Each column is assigned a data type that indicates the format of column values. ____ _____ can be numeric, textual, or complex. A ____ ____ is a named set of values from which column values are drawn. Database design << correct answer >> The specification of database objects and the process used to develop the specification. Application programming interface << correct answer >> Links a programming language to a database. API (Application Programming Interface) << correct answer >> A library of procedures or classes that links a host programming language to a database. The host language calls library procedures, which handle details such as connecting to the database, executing queries, and returning results. Analysis << correct answer >> The phase that specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities. An attribute is a descriptive property of an entity. term-38 It has many alternative names, such as conceptual design, entity-relationship modeling, and requirements definition. ER diagrams << correct answer >> Depict entities, relationships, and attributes. Rectangles represent entities. Entity names appear at the top of rectangles. Lines between rectangles represent relationships. Text inside rectangles and below entity names represent attributes. ER diagrams are usually supplemented by textual descriptions of entities, relationships, and attributes. Logical design << correct answer >> Implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns. It also affects the query result. Specifies tables, columns, and keys. The logical design process is described elsewhere in this material. Key << correct answer >> A column used to identify individual rows of a table. Refers to a field or set of fields in a database table that is used to uniquely identify a record in that table. ___ play a crucial role in organizing and accessing data efficiently. Table diagram << correct answer >> A graphical representation of the logical design. Refers to a visual representation of the structure of a relational database table. These diagrams are often part of the database design process and are used to illustrate the various components and relationships within a table. Schema << correct answer >> The logical design specified in SQL and depicted in a table diagram. Refers to the structure or blueprint that defines the organization of data within a database. It provides a logical framework for how the database is designed and how the data is organized and related to one another. The ______ defines the tables, fields, relationships, and constraints that make up the database. Physical design << correct answer >> Adds indexes and specifies how tables are organized on storage media. It is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system. It can be depicted in diagrams. However, logical design is more important for database users and programmers, so physical design diagrams are not commonly used. It also affects query processing speed but never affects the query result. Specifies indexes, table structures, and partitions. Physical design affects query performance but never affects query results. Data independence << correct answer >> The principle that physical design never affects query results. It allows database designers to tune query performance without changes to application programs. The term information independence is occasionally used instead. These terms are synonymous. Rule 4 is called data independence. MySQL << correct answer >> A leading relational database system sponsored by Oracle. Based on the DB-Engines survey of internet references in May 2020, MySQL is the second most popular database system. MySQL Community << correct answer >> A free edition of MySQL. Also commonly called MySQL Server. It includes a complete set of database services and tools, and is suitable for non-commercial applications such as education. MySQL Enterprise << correct answer >> A paid edition for managing commercial databases. It includes MySQL Server and additional administrative applications. Root account << correct answer >> The administrative account that has full control of MySQL. MySQL Command-Line Client << correct answer >> A text interface for connecting to the database server and executing SQL statements. The Command-Line Client allows developers to connect to the database server, perform administrative functions, and execute SQL statements. Error code << correct answer >> A code and description returned when an SQL statement is incorrect or cannot be executed. MySQL Workbench << correct answer >> A tool for executing SQL commands using an editor. Relational model << correct answer >> A database model based on a tabular data structure. A table-based way of organizing data in a database. The model was published in 1970 by E. F. Codd of IBM and released in commercial products around 1980. The data structure, operations, and rules are standardized in SQL, the universal query language of relational databases. Were initially designed for transactional data, such as bank transactions and airline reservations. Set << correct answer >> An unordered collection of elements enclosed in braces. Tuple << correct answer >> An ordered collection of elements enclosed in parentheses. An ordered set of values, typically representing a single record in a database table. Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered. Column << correct answer >> A named element of a table. A ______ has a name and a data type. Row << correct answer >> An unnamed collection of values in a table. Each value corresponds to a column and belongs to the column's data type. Relational Algebra << correct answer >> Operations for manipulating tables. A theoretical framework and set of operations used to manipulate data stored in relational databases. Since the result of relational operations is always a table, the result of an SQL query is also a table. It provides a mathematical foundation for expressing queries and transformations on relational database tables. These operations are collectively called relational algebra and are the theoretical foundation of the SQL language. Select selects a subset of rows of a table. Project eliminates one or more columns of a table. Product lists all combinations of rows of two tables. Join combines two tables by comparing related columns. Union selects all rows of two tables. Intersect selects rows common to two tables. Difference selects rows that appear in one table but not another. Rename changes a table name. Aggregate computes functions over multiple table rows, such as sum and count. Relational Rules << correct answer >> Governing rules for data in a relational database to ensure data is valid. Typically refer to the principles and constraints that govern the relationships between tables and the data they contain. These rules help maintain the integrity and consistency of the database. They are part of the relational model and govern data in every relational database. Unique primary key. All tables have a primary key column, or group of columns, in which values may not repeat. Unique column names. Different columns of the same table have different names. No duplicate rows. No two rows of the same table have identical values in all columns. Business rules << correct answer >> Rules based on business policy specific to a particular database. Guidelines or constraints that are specific to an organization's policies, practices, and requirements. These rules define how data should be handled, processed, stored, and managed within a business environment. Constraints << correct answer >> Implemented as SQL constraints and enforced by the database system. Refer to rules or conditions applied to the data within a database to ensure data integrity, accuracy, and consistency. Constraints are used to enforce specific business rules or requirements at the database level, preventing the insertion, updating, or deletion of data that would violate these rules. Constraints help maintain the quality and reliability of the data stored in a database. Clause << correct answer >> Groups SQL keywords with table and column names. This groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like "Name", and conditions like "Population > 100000". Data Definition Language << correct answer >> Defines the structure of the database. Creates, alters, and drops tables. Data Query Language << correct answer >> Retrieves data from the database. Data Manipulation Language << correct answer >> Manipulates data stored in the database. Inserts, updates, and deletes data in a table. Data Control Language << correct answer >> Controls database user access. Grants and revokes permissions to and from users. Data Transaction Language << correct answer >> Manages database transactions. Commits data to a database, rolls back data from a database, and creates savepoints. Database system instance << correct answer >> A single executing copy of a database system. Refers to a snapshot or specific state of a database system at a particular moment in time. It represents the actual data and structural configuration of a database at a given point. Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system. CREATE DATABASE << correct answer >> Creates a new database. DROP DATABASE << correct answer >> Deletes a database. USE Database Name << correct answer >> Selects a default database for use in subsequent SQL statements. SHOW DATABASES << correct answer >> Lists all databases in the database system instance. SHOW TABLES << correct answer >> Lists all tables in the default database. SHOW COLUMNS FROM << correct answer >> Lists all columns in a table. SHOW CREATE TABLE << correct answer >> Shows the CREATE TABLE statement for a table. Table << correct answer >> A named sequence of columns and rows. Has a name, a fixed tuple of columns, and a varying set of rows. Refers to a fundamental structure used to organize and store data in a relational database. _____ are part of the relational database model, which is a widely used method for structuring and managing data. Cell << correct answer >> A single column of a single row. Empty table << correct answer >> A table without rows. Column constraint << correct answer >> Governs values in a single column. Are rules or conditions applied to individual columns within a database table. These constraints define the acceptable values, relationships, and properties for specific fields of data. Refer to constraints that are applied to specific columns within a database table. These constraints enforce rules and conditions on the data within individual columns, ensuring the integrity and consistency of the stored information. Table constraint << correct answer >> Governs values in one or more columns. Are rules or conditions applied to a database table to ensure the accuracy, integrity, and consistency of the data stored within it. These constraints define the acceptable values and relationships between columns, rows, and tables. Refer to rules or conditions applied to the entire table, influencing the data stored within the table as a whole. These constraints help ensure data integrity, consistency, and accuracy across all rows and columns of the table. Constraint << correct answer >> A rule that governs allowable values in a database. Constraints are based on relational and business rules, and implemented with special keywords in a CREATE TABLE statement. The database automatically rejects insert, update, and delete statements that violate a constraint. Unique << correct answer >> A constraint that ensures values in a column or group of columns are unique. Check << correct answer >> A constraint that specifies an expression on one or more columns of a table. Refers to a constraint that is applied to a column or set of columns in a database table. The CHECK constraint specifies a condition that data in the column(s) must satisfy, and it ensures that only values meeting the specified criteria are allowed in the table. The purpose of the CHECK constraint is to enforce specific business rules or conditions on the data within the column. Query Language << correct answer >> A computer programming language for writing database queries. Refers to a specialized language used to interact with and manipulate databases. The primary purpose of a _____ ________ is to facilitate the retrieval, insertion, updating, and deletion of data in a database. Users, database administrators, and developers can use _____ ________ to communicate with a database management system (DBMS) and perform various operations on the data. One common type of query language used in relational databases is SQL (Structured Query Language). INSERT << correct answer >> Inserts row(s) into a table SELECT << correct answer >> Retrieves data from a table UPDATE << correct answer >> The ______ modifies existing rows in a table DELETE << correct answer >> The ______ statement deletes existing rows in a table CREATE TABLE << correct answer >> Statement creates a new table by specifying the table and column names. Each column is assigned a data type that indicates the format of column values. Data types can be numeric, textual, or complex. Ex: INT - stores integer values. DECIMAL - stores fractional numeric values. VARCHAR - stores textual values. DATE - stores year, month, and day. Analysis Logical design Physical design Data Type << correct answer >> Each column is assigned a ____ ____ that indicates the format of column values. Data types can be numeric, textual, or complex. A named set of values, from which column values are drawn. Refers to the classification or category of data that specifies the kind of values a particular variable can hold. Defines the nature of the data and how it should be interpreted by the computer or database system. Different programming languages, databases, and systems support various data types, each with its own characteristics and storage requirements. Database Design << correct answer >> A specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification. A critical aspect of data management that involves the process of creating a structured and efficient database to meet the information needs of an organization or application. The goal of ________ ______ is to organize data in a way that supports accurate and efficient data storage, retrieval, and manipulation. A well-designed database ensures data integrity, minimizes redundancy, and provides a solid foundation for scalable and maintainable information systems. Database Model << correct answer >> A conceptual framework for database systems, with three parts: Data structures that prescribe how data is organized. Operations that manipulate data structures. Rules that govern valid data. Big Data << correct answer >> The rise of the internet in the 1990s generated big data, characterized by unprecedented data volumes and rapidly changing data structures. DROP TABLE << correct answer >> This statement deletes a table, along with all the table's rows, from a database. ALTER TABLE << correct answer >> This statement adds, deletes, or modifies columns on an existing table. Integer << correct answer >> _______ data types represent numbers with negative integers. Decimal << correct answer >> _______ data types represent numbers with fractional values Character << correct answer >> Date and time data types represent date, time, or both. Some date and time data types include a time zone or specify a time interval. Binary << correct answer >> ______ data types store data exactly as the data appears in memory or computer files, bit for bit. Spatial << correct answer >> _______ data types store geometric information, such as lines, polygons, and map coordinates. Document << correct answer >> ________ data types contain textual data in a structured format such as XML or JSON. Signed << correct answer >> A ______ number may be negative. Unsigned << correct answer >> An ________ number cannot be negative. Operator/Operands << correct answer >> An operator is a symbol that computes a value from one or more other values, called operands: Arithmetic operators compute numeric values from numeric operands. Comparison operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types. Logical operators compute logical values from logical operands. Unary Operator << correct answer >> A _____ operator has one operand Binary Operator << correct answer >> A ______ operator has two operands. Expression << correct answer >> An __________ is a string of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. The value of an expression may be any data type. Operator Precedence << correct answer >> Operators in an expression are evaluated in the order of operator precedence, shown in the table below. Operators of the same precedence are evaluated from left to right. Regardless of operator precedence, expressions enclosed in parentheses are evaluated before any operators outside the parentheses are applied. SELECT / FROM << correct answer >> The SELECT statement selects rows from a table. The statement has a SELECT clause and a FROM clause. The FROM clause specifies the table from which rows are selected. The SELECT clause specifies one or more expressions, separated by commas, that determine what values are returned for each row. Result Table << correct answer >> The SELECT statement returns a set of rows, called the result table. LIMIT << correct answer >> MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement. Condition << correct answer >> A _________ is an expression that evaluates to a logical value. WHERE << correct answer >> A SELECT statement has an optional WHERE clause that specifies a condition for selecting rows. A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL. NULL << correct answer >> ____ is a special value that represents either unknown or inapplicable data. Is a special marker used to indicate that a data value in a database column does not exist in the database. It represents the absence of a value or the unknown status of a data item. In a relational database, ____ is not the same as an empty string, zero, or any other specific value—it specifically signifies the lack of a value. NOT NULL << correct answer >> The ___ ____ constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected. ___ ____ follows the column name and data type in a CREATE TABLE statement. Is a constraint applied to a database column to ensure that the column cannot contain NULL values. When a column is defined as ___ ____, it means that every row in the table must have a value for that particular column, and that value cannot be NULL. IS NULL / IS NOT NULL << correct answer >> Instead, the IS NULL and IS NOT NULL operators must be used to select NULL values. Value IS NULL returns TRUE when the value is NULL. Value IS NOT NULL returns TRUE when the value is not NULL. Truth Tables << correct answer >> The value of logical expressions containing NULL operands is defined in truth tables. INSERT INTO << correct answer >> The ______ ___ clause names the table and columns where data is to be added. The keyword INTO is optional. VALUES << correct answer >> The ______ clause specifies the column values to be added. DEFAULT << correct answer >> The optional _______ keyword and default value follow the column name and data type in a CREATE TABLE statement. The column is assigned the default value, rather than NULL, when omitted from an INSERT statement. SET << correct answer >> The UPDATE statement uses the SET clause to specify the new column values FROM << correct answer >> The ____ keyword is followed by the table name whose rows are to be deleted. TRUNCATE << correct answer >> The ________ statement deletes all rows from a table. Primary Key << correct answer >> Is a column, or group of columns, used to identify a row. Simple Primary Key << correct answer >> Consists of a single column. Composite Primary Key << correct answer >> Consists of multiple columns. Minimal << correct answer >> All primary key columns are necessary for uniqueness. When any column is removed, the resulting simple or composite column is no longer unique. PRIMARY KEY Command << correct answer >> The _______ ___ constraint in a CREATE TABLE statement names the table's primary key. The _______ ___ constraint ensures that a column or group of columns is always unique and non-null. Auto-Increment Column << correct answer >> Is a numeric column that is assigned an automatically incrementing value when a new row is inserted. AUTO_INCREMENT << correct answer >> The AUTO_INCREMENT keyword defines an auto-increment column. AUTO_INCREMENT follows the column's data type in a CREATE TABLE statement. Foreign Key << correct answer >> Is a column, or group of columns, that refer to a primary key. Is a column or a set of columns in a database table that refers to the primary key of another table. The foreign key establishes a relationship between the two tables, linking the data in one table to the data in another. This relationship is crucial for maintaining referential integrity in a relational database. Referential integrity << correct answer >> Is a relational rule that requires foreign key values must either be NULL or match some value of the referenced primary key. FOREIGN KEY / REFERENCES << correct answer >> A foreign key constraint is added to a CREATE TABLE statement with the FOREIGN KEY and REFERENCES keywords. Fully NULL << correct answer >> A fully NULL foreign key is a simple or composite foreign key in which all columns are NULL. RESTRICT << correct answer >> ________ rejects an insert, update, or delete that violates referential integrity. SET NULL << correct answer >> Sets invalid foreign keys to NULL. SET DEFAULT << correct answer >> Sets invalid foreign keys to the foreign key default value. CASCADE << correct answer >> Propagates primary key changes to foreign keys. ON UPDATE / ON DELETE << correct answer >> Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint. ON UPDATE and ON DELETE are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE. Column Constraint << correct answer >> A ______ __________appears after the column name and data type in a CREATE TABLE statement. ______ ___________ govern values in a single column. Table Constraint << correct answer >> A _____ __________ appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns. UNIQUE << correct answer >> The ______ constraint ensures that values in a column, or group of columns, are unique. CHECK << correct answer >> The _____ constraint specifies an expression on one or more columns of a table. The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL. CONSTRAINT Command << correct answer >> Table constraints may be named using the optional CONSTRAINT keyword, followed by the constraint name and declaration. VARCHAR << correct answer >> Stores textual values. DATE << correct answer >> Stores year, month, and day. Project << correct answer >> Eliminates one or more columns of a table. Product << correct answer >> Lists all combinations of rows of two tables. Join << correct answer >> Combines two tables by comparing related columns. A SELECT statement that combines data from two tables, known as the left table and right table, into a single result. The tables are combined by comparing columns from the left and right tables, usually with the = operator. The columns must have comparable data types. Usually, a ____ compares a foreign key of one table to the primary key of another. However, a ____ can compare any columns with comparable data types. Union << correct answer >> Selects all rows of two tables. Intersect << correct answer >> Selects rows common to two tables. Difference << correct answer >> Selects rows that appear in one table but not another. Rename << correct answer >> Changes a table name. Aggregate << correct answer >> Computes functions over multiple table rows, such as sum and count. Refers to a function or operation that combines multiple values into a single value, typically summarizing or transforming the data in some way. Aggregates are commonly used in database queries to perform calculations, obtain summary statistics, or derive meaningful insights from large sets of data. Unique Primary Key << correct answer >> All tables have a primary key column, or group of columns, in which values may not repeat. Unique Column Names << correct answer >> Different columns of the same table must have different names. No Duplicate Rows << correct answer >> No two rows of the same table have identical values in all columns. IN << correct answer >> The IN operator is used in a WHERE clause to determine if a value matches one of several values. BETWEEN << correct answer >> The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue. LIKE << correct answer >> The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. BINARY << correct answer >> The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword. DISTINCT << correct answer >> The DISTINCT clause is used with a SELECT statement to return only unique or 'distinct' values. ORDER BY << correct answer >> The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. DESC << correct answer >> The DESC keyword with the ORDER BY clause orders rows in descending order. function / argument << correct answer >> A function operates on an expression enclosed in parentheses, called an argument, and returns a value. Usually, the argument is a simple expression, such as a column name or fixed value. Some functions have several arguments, separated by commas, and a few have no arguments at all. aggregate function << correct answer >> An aggregate function processes values from a set of rows and returns a summary value. Refers to a function that operates on a set of values, often within a column or group of columns, and produces a single aggregated result. These functions are used to perform calculations on data, summarizing information and providing insights into the dataset. Aggregate functions are commonly employed in database queries, especially when working with large datasets or when summarizing information for reporting purposes. COUNT() << correct answer >> COUNT() counts the number of rows in the set. MIN() << correct answer >> MIN() finds the minimum value in the set. MAX() << correct answer >> MAX() finds the maximum value in the set. SUM() << correct answer >> SUM() sums all the values in the set. AVG() << correct answer >> AVG() computes the arithmetic mean of all the values in the set. GROUP BY << correct answer >> The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group. HAVING << correct answer >> The ______ clause is used with the GROUP BY clause to filter group results. The ______ clause excludes the groups join / left table / right table << correct answer >> A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result. AS << correct answer >> To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword. The AS keyword follows a column or table name to create an alias. join clause << correct answer >> A join clause determines how a join query handles unmatched rows. Two common join clauses are: . INNER JOIN << correct answer >> INNER JOIN selects only matching left and right table rows. FULL JOIN << correct answer >> FULL JOIN selects all left and right table rows, regardless of match. ON << correct answer >> The ON clause specifies the join columns. LEFT JOIN << correct answer >> LEFT JOIN selects all left table rows, but only matching right table rows. RIGHT JOIN << correct answer >> RIGHT JOIN selects all right table rows, but only matching left table rows. outer join << correct answer >> An outer join is any join that selects unmatched rows, including left, right, and full joins. UNION << correct answer >> The UNION keyword combines the two results into one table. equijoin << correct answer >> An equijoin compares columns of two tables with the = operator. non-equijoin << correct answer >> A non-equijoin compares columns with an operator other than =, such as < and >. self-join << correct answer >> A self-join joins a table to itself. cross-join << correct answer >> A cross-join combines two tables without comparing columns. CROSS JOIN << correct answer >> A cross-join uses a CROSS JOIN clause without an ON clause. subquery / nested query / inner query << correct answer >> A subquery, sometimes called a nested query or inner query, is a query within another SQL query. correlated << correct answer >> A subquery is correlated when the subquery's WHERE clause references a column from the outer query. alias << correct answer >> An alias is a temporary name assigned to a column or table. EXISTS << correct answer >> Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. NOT EXISTS << correct answer >> The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected. flattening << correct answer >> Replacing a subquery with an equivalent join is called flattening a query. view table / view query << correct answer >> A view table is a table name associated with a SELECT statement, called the view query. The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table. CREATE VIEW << correct answer >> The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table. base table << correct answer >> A table specified in the view query's FROM clause is called a base table. materialized view << correct answer >> A materialized view is a view for which data is stored at all times. Whenever a base table changes, the corresponding view tables can also change, so materialized views must be refreshed. To avoid the overhead of refreshing views, MySQL and many other databases do not support materialized views. WITH CHECK OPTION << correct answer >> When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause. relational algebra << correct answer >> In his original paper on the relational model, E. F. Codd introduced formal operations for manipulating tables. Codd's operations, called relational algebra, have since been refined and are the theoretical foundation of SQL. Is a theoretical framework and a formalism that forms the foundation for working with relational databases. It provides a set of operations and rules for manipulating relational data. The relational algebra operations are applied to relational tables, which are two-dimensional structures composed of rows and columns. select operation << correct answer >> The select operation selects table rows based on a logical expression. The select operation is written as �expression(Table) and is equivalent to SELECT * FROM Table WHERE expression. project operation << correct answer >> The project operation selects table columns. The project operation is written as Π(Column1,Column2,...)(Table) and is equivalent to SELECT Column1, Column2, ... FROM Table. join << correct answer >> The join operation, denoted with a "bowtie" symbol, is written as Table1⋈expressionTable2 and is identical to a product followed by a select: �expression(Table1×Table2) The join operation is equivalent to SELECT * FROM Table1 INNER JOIN Table2 ON expression. theta join << correct answer >> Because of theta notation, the join operation is sometimes called a theta join. Is a type of join operation that combines rows from two tables based on a condition (predicate) that is not limited to equality. In contrast to the more common equi-join, where the join condition involves equality between values in specified columns, a theta join allows for a broader range of conditions, including inequalities and other comparisons. Compatible tables << correct answer >> Compatible tables have the same number of columns with the same data types. Column names may be different. set operations << correct answer >> Union, intersect, and difference operate on compatible tables and, collectively, are called set operations. union << correct answer >> The union operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table. The union operation is written as Table1∪Table2 and is equivalent to SELECT * FROM Table1 UNION SELECT * FROM Table2. Intersect << correct answer >> Intersect operates on two compatible tables and returns only rows that appear in both tables. The intersect operation is written as Table1∩Table2 and is equivalent to SELECT * FROM Table1 INTERSECT SELECT * FROM Table2. difference << correct answer >> The difference operation removes from a table all rows that appear in a second compatible table. The difference operation is written as Table1−Table2 and is equivalent to SELECT * FROM Table1 MINUS SELECT * FROM Table2. rename operation << correct answer >> The rename operation specifies new table and column names. The rename operation is written as �TableName(ColumnName1,ColumnName2,...)(Table) aggregate operation << correct answer >> The aggregate operation applies aggregate functions like SUM(), AVG(), MIN(), and MAX(). The aggregate operation is written as GroupColumn�Function(Column)(Table) and is equivalent to SELECT GroupColumn, Function(Column) FROM Table GROUP BY GroupColumn. equivalent << correct answer >> Relational algebra expressions are equivalent if the expressions operate on the same tables and generate the same result. query optimizer / query execution plan << correct answer >> A query optimizer converts an SQL query into a sequence of low-level database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement. cost << correct answer >> The cost of an operation is a numeric estimate of processing time. The cost estimate usually combines both storage media access and computation time in a single measure. product operation << correct answer >> The product operation combines two tables into one result. The result includes all columns and all combinations of rows from both tables. The product operation is written as Table1×Table2 and is equivalent to SELECT * FROM Table1 CROSS JOIN Table2. entity-relationship model << correct answer >> An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. Entity << correct answer >> An entity is a person, place, product, concept, or activity. Relationship << correct answer >> A relationship is a statement about two entities. Attribute << correct answer >> An attribute is a descriptive property of an entity. Reflexive Relationship << correct answer >> A reflexive relationship relates an entity to itself. Entity-relationship diagram / ER diagram << correct answer >> An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. glossary / data dictionary / repository << correct answer >> A glossary, also known as a data dictionary or repository, documents additional detail in text format. entity type << correct answer >> An entity type is a set of things. Ex: All employees in a company. relationship type << correct answer >> A relationship type is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department. attribute type << correct answer >> An attribute type is a set of values. Ex: All employee salaries. entity instance << correct answer >> An entity instance is an individual thing. Ex: The employee Sam Snead. relationship instance << correct answer >> A relationship instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales." . attribute instance << correct answer >> An attribute instance is an individual value. Ex: The salary $35,000. Analysis << correct answer >> Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details. Logical design << correct answer >> Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system. Physical design << correct answer >> Physical design adds indexes and specifies how tables are organized on storage media. cardinality << correct answer >> In entity-relationship modeling, cardinality refers to maxima and minima of relationships and attributes. Relationship maximum << correct answer >> Relationship maximum is the greatest number of instances of one entity that can relate to a single instance of another entity. singular / plural << correct answer >> A related entity is singular when the maximum is one and plural when the maximum is many. Relationship minimum << correct answer >> Relationship minimum is the least number of instances of one entity that can relate to a single instance of another entity. optional / required << correct answer >> A related entity is optional when the minimum is zero and required when the minimum is one. Attribute maximum << correct answer >> Attribute maximum is the greatest number of attribute values that can describe each entity instance. Attribute maximum is usually specified as one (singular) or many (plural). Attribute minimum << correct answer >> Attribute minimum is the least number of attribute values that can describe each entity instance. Attribute minimum is usually specified as zero (optional) or one (required). Unique Attribute << correct answer >> Each value of a unique attribute describes at most one entity instance. Identifying Attribute << correct answer >> An identifying attribute is unique, singular, and required. Identify << correct answer >> Identifying attribute values correspond one-to-one to, or identify, entity instances. Strong Entity << correct answer >> A strong entity has one or more identifying attributes. weak entity / identifying relationship / identifying entity << correct answer >> A weak entity does not have an identifying attribute. Instead, a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. Cardinality of the identifying entity is 1(1). subtype entity / supertype entity << correct answer >> A subtype entity is a subset of another entity type, called the supertype entity. IsA relationship << correct answer >> A supertype entity identifies its subtype entities. The identifying relationship is called an IsA relationship. Refers to a type of relationship between entities that represents an inheritance or specialization hierarchy. This relationship is often used in the design of relational databases, where one table (or entity) is considered a specialization or subtype of another table (or entity). The "IsA relationship" is also known as a "subtype-supertype" relationship or "generalization-specialization" relationship. It reflects the idea that one entity is a specific type or subtype of another more general entity. Similar entities << correct answer >> Similar entities are entities that have many common attributes and relationships. Partition << correct answer >> A partition of a supertype entity is a group of mutually exclusive subtype entities. Refers to a division or segmentation of a larger dataset or database table into smaller, more manageable subsets. Partitioning is a technique used to improve query performance, data management, and maintenance tasks. It involves organizing and storing data in a way that allows for efficient access and retrieval of information. Partition Attribute << correct answer >> Each partition corresponds to an optional partition attribute of the supertype entity. Refers to the specific column or set of columns used to define the partitions in a partitioned database table. The partition attribute determines how the data in the table is divided into distinct subsets, or partitions. Each partition corresponds to a unique value or range of values in the partition attribute. Crow's Foot Notation << correct answer >> Variations in cardinality conventions are common. One popular convention, called crow's foot notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird's foot, hence the name "crow's foot notation". Subject Area << correct answer >> Decompose a complex model into a group of related entities, called a subject area. Independent / Dependent << correct answer >> Refer to strong entities as independent and weak entities as dependent. Unified Modeling Language / UML << correct answer >> Unified Modeling Language, or UML, is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions. IDEF1X << correct answer >> IDEF1X stands for Information DEFinition version 1X. IDEF1X became popular, in part, due to early adoption by the United States Department of Defense. Chen notation << correct answer >> Chen notation appeared in an early ER modeling paper by Peter Chen. Chen notation is not standardized but often appears in literature and tools. Strong Table << correct answer >> A strong entity becomes a strong table. The primary key must be unique and non-NULL, and should be stable, simple, and meaningless. Single-column primary keys are best, but if no such column exists, a composite primary key may have the required properties. Artificial Key << correct answer >> An artificial key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists. Subtype Table << correct answer >> A subtype entity becomes a subtype table. Weak Table << correct answer >> A weak entity becomes a weak table. Depends On << correct answer >> Column A depends on column B means each B value is related to at most one A value. A and B may be simple or composite columns. Functional Dependence << correct answer >> Dependence of one column on another is called functional dependence. Multivalued Dependence / Join Dependence << correct answer >> Multivalued dependence and join dependence entail dependencies between three or more columns. Redundancy << correct answer >> Redundancy is the repetition of related values in a table. Normal forms << correct answer >> Normal forms are rules for designing tables with less redundancy. Are a set of guidelines or rules for organizing relational database tables to reduce redundancy and improve data integrity. The normalization process aims to structure a database in such a way that data dependencies and relationships between entities are properly represented, and updates or modifications to the data can be performed without introducing anomalies. First Normal Form << correct answer >> Every cell of a table contains exactly one value. A table is in first normal form when, in addition, the table has a primary key. Second Normal Form << correct answer >> A table is in second normal form when all non-key columns depend on the whole primary key. Third Normal Form << correct answer >> Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material. A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite. Candidate key / Minimal << correct answer >> A candidate key is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness. Non-Key << correct answer >> A non-key column is a column that is not contained in a candidate key. Boyce-Codd normal form << correct answer >> A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite. In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique. Normalization << correct answer >> Eliminates redundancy by decomposing a table into two or more tables in higher normal form. Denormalization << correct answer >> Means intentionally introducing redundancy by merging tables. Is a database design technique where certain aspects of a normalized database schema are intentionally relaxed or reversed. In a denormalized database, redundancy is introduced into the tables to improve query performance at the cost of some level of data integrity and redundancy. Access time << correct answer >> Is the time required to access the first byte in a read or write operation. Transfer Rate << correct answer >> Is the speed at which data is read or written, following initial access. Volatile Memory << correct answer >> Is memory that is lost when disconnected from power. Non-Volatile Memory << correct answer >> Data is retained without power. Main Memory / Random-Access Memory << correct answer >> Is the primary memory used when computer programs execute. Flash memory / Solid-State Drive (SSD) << correct answer >> Is less expensive and higher capacity than main memory. Magnetic disk / hard-disk drive (HDD) << correct answer >> Is used to store large amounts of data. Sectors << correct answer >> Magnetic disk groups data in sectors, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats. Pages << correct answer >> Flash memory groups data in pages, usually between 2 kilobytes and 16 kilobytes per page Block << correct answer >> Databases and file systems use a uniform size, called a block, when transferring data between main memory and storage media. Row-Oriented Storage << correct answer >> To minimize block transfers, relational databases usually store an entire row within one block, which is called row-oriented storage. column-oriented / columnar storage << correct answer >> In column-oriented storage, also called columnar storage, each block stores values for a single column only. Table Structure << correct answer >> Is a scheme for organizing rows in blocks on storage media. Heap Table << correct answer >> No order is imposed on rows sorted table / sort column << correct answer >> In a sorted table, the database designer identifies a sort column that determines physical row order. Hash Table << correct answer >> Rows are assigned to buckets. Bucket << correct answer >> Is a block or group of blocks containing rows. Is a block or group of blocks containing index entries. Hash Key << correct answer >> Is a column or group of columns, usually the primary key. Hash Function << correct answer >> Computes the bucket containing the row from the hash key. Modulo Function << correct answer >> Is a simple hash function with four steps. Dynamic Hash Function << correct answer >> Automatically allocates more blocks to the table, creates additional buckets, and distributes rows across all buckets. With more buckets, fewer rows are assigned to each bucket and, on average, buckets contain fewer linked blocks. Table clusters / multi-tables << correct answer >> Table clusters, also called multi-tables, interleave rows of two or more tables in the same storage area. Cluster Key << correct answer >> Table clusters have a cluster key, a column that is available in all interleaved tables. Single-Level Index << correct answer >> Is a file containing column values, along with pointers to rows containing the column value. Multi-Column Index << correct answer >> Each index entry is a composite of values from all indexed columns. In all other respects, multi-column indexes behave exactly like indexes on a single column. Table Scan << correct answer >> Is a database operation that reads table blocks directly, without accessing an index. Index Scan << correct answer >> Is a database operation that reads index blocks sequentially, in order to locate the needed table blocks. Hit ratio / filter factor / selectivity << correct answer >> Hit ratio, also called filter factor or selectivity, is the percentage of table rows selected by a query. Binary Search << correct answer >> The database repeatedly splits the index in two until it finds the entry containing the search value: . Primary index / clustering index << correct answer >> A primary index, also called a clustering index, is an index on a sort column. Secondary index / nonclustering index << correct answer >> A secondary index, also called a nonclustering index, is an index that is not on the sort column. Dense Index << correct answer >> Contains an entry for every table row. Sparse Index << correct answer >> Contains an entry for every table block. Multi-Level Index << correct answer >> Stores column values and row pointers in a hierarchy. Fan-Out << correct answer >> The number of index entries per block is called the fan-out of a multi-level index. Branch << correct answer >> Each path from the top-level block to a bottom-level block is called a branch. balanced / imbalanced << correct answer >> Multi-level indexes are called balanced when all branches are the same length and imbalanced when branches are different lengths. B+tree << correct answer >> 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. B-tree << correct answer >> 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. Hash Index << correct answer >> Index entries are assigned to buckets. hash function << correct answer >> The bucket containing each index entry is determined by a hash function, which computes a bucket number from the value of the indexed column. bitmap index << correct answer >> Is a grid of bits: . physical index << correct answer >> A single-level or multi-level index normally contains pointers to table blocks and is called a physical index. logical index << correct answer >> Is a single-level or multi-level index in which pointers to table blocks are replaced with primary key values function index << correct answer >> The database designer specifies a function on the column value. Index entries contain the result of the function applied to column values, rather than the column values. tablespace << correct answer >> Is a database object that maps one or more tables to a single file. fragmented << correct answer >> As files are updated, blocks become scattered, or fragmented, across many tracks. partition << correct answer >> Is a subset of table data. One table has many partitions that do not overlap and, together, contain all table data. Horizontal Partitioning << correct answer >> Is a subset of table rows. Vertical Partition << correct answer >> Is a subset of table columns. Shard << correct answer >> Like a partition, a shard is a subset of table data, usually a subset of rows rather than columns. Unlike partitions, which are stored on different storage devices of a single computer, shards are stored on different computers of a distributed database. partition expression / partition columns << correct answer >> To partition a table, the database administrator specifies a partition expression based on one or more partition columns. The partition expression may be simple, such as the value of a single partition column, or a complex expression based on several partition columns. Rows are assigned to partitions in one of the following ways: . range partition << correct answer >> Associates each partition with a range of partition expression values. The VALUES LESS THAN keywords specify the upper bound of each range. The MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range. Each partition is explicitly named by the database administrator. list partition << correct answer >> Associates each partition with an explicit list of partition expression values using the VALUES IN keywords. Like a range partition, each partition is explicitly named. hash partition << correct answer >> Requires a partition expression with positive integer values. The database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(N-1). The partition number for each row is computed as: (partition expression value) modulo N. key partition << correct answer >> Is similar to a hash partition, except the partition expression is determined automatically by the database. storage engine / storage manager << correct answer >> A storage engine or storage manager translates instructions generated by a query processor into low-level commands that access data on storage media. Storage engines support different index and table structures, so physical design is dependent on a specific storage engine. The storage engine, also called a storage manager, executes instructions, manages indexes, and interacts with the file system. The storage manager translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data. CREATE INDEX << correct answer >> The ______ _____ statement creates an index by specifying the index name and table columns that compose the index. DROP INDEX << correct answer >> The ____ _____ statement deletes a table's index. SHOW INDEX << correct answer >> The ____ _____ statement displays a table's index. EXPLAIN << correct answer >> The _______ statement generates a result table that describes how a statement is executed by the storage engine. Slow Query Log << correct answer >> The MySQL slow query log is a file that records all long-running queries submitted to the database. Architecture << correct answer >> Describes the components of a computer system and the relationships between components. Tools << correct answer >> Interact directly with database users and administrators, and send queries to the query processor. query processor << correct answer >> Manages connections from multiple users and compiles queries into low-level instructions for the storage engine. File System << correct answer >> The ____ ______ accesses data on storage media. The ____