WGU D427 Data Management Applications Exam Graded A+
Database Model - ANSWER-is a conceptual framework for database systems, with three parts
- Parts of the Database Model - ANSWER-Data structures, operations, rules
Data structures - ANSWER-prescribe how data is organized
Operations - ANSWER-manipulate data structures
Rules - ANSWER-govern valid data
Big Data - ANSWER-characterized by unprecedented data volumes and rapidly changing data structures.
Set - ANSWER-is an unordered collection of elements enclosed in braces.
Ex: {a, b, c} and {c, b, a} are the same, since sets are not ordered.
Tuple - ANSWER-is an ordered collection of elements enclosed in parentheses.
Ex: (a, b, c) and (c, b, a) are different, since tuples are ordered.
Database Terminology - ANSWER-Table Column Row Data Type
Math Terminology - ANSWER-Relation Attribute Tuple Domain
File Terminology - ANSWER-File Field Record Data Type
Select (Relational) - ANSWER-selects a subset of rows of a table
Project (Relational) - ANSWER-eliminates one or more columns of a table
Product (Relational) - ANSWER-lists all combinations of rows of 2 tables
Join (Relational) - ANSWER-combines 2 tables by comparing related columns
Union (Relational) - ANSWER-selects all rows of 2 tables
Intersect (Relational) - ANSWER-selects rows common to 2 tables
Difference (Relational) - ANSWER-selects rows that appear in one table but not another
Rename (Relational) - ANSWER-changes table name
Aggregate (Relational) - ANSWER-computes functions over multiple table rows, such as sum and count
relational algebra - ANSWER-Theoretical way of manipulating table contents using relational operators
ex. select, project, product, join...
Relational Rules - ANSWER-logical constraints that ensure data is valid
Examples of Rules - ANSWER-Unique Primary Key - all tables have a primary key column, or group of columns, in which the values may not repeat
Unique Column Names - different columns of the same table have different names
No Duplicate Rows - no 2 rows of the same table have identical values in all columns
Business Rule - ANSWER-based on business policy and specific to a particular database
Example of a Business Rule - ANSWER-all rows of the Employee table must have a valid entry in the DepartCode column
-or-
PassportNumber values may not repeat in different Employee rows
SQL Constraints - ANSWER-relational rules that are implemented and enforced by the database system.
Business rules are discovered during database design and often implemented as SQL Constraints
Structured Query Language (SQL) - ANSWER-is a high-level computer language for storing, manipulating and retrieving data
SQL Statement - ANSWER-is a complete command composed of one or more clauses
Clause - ANSWER-groups SQL keywords like SELECT, FROM, and WHERE
ex. with tables names like City, column names like Name, and conditions like Population > 100,000
SQL Language's 5 Sublanguage - ANSWER-Data Definition Language (DDL) Data Query Language (DQL) Data Manipulation Language (DML) Data Control Language (DCL) Data Transaction Language (DTL)
Data Definition Language (DDL) - ANSWER-defines the structure of the database
Data Query Language (DQL) - ANSWER-retrieves data from the database
Data Manipulation Language (DML) - ANSWER-manipulates data stored in a database
Data Control Language (DCL) - ANSWER-controls database user access
Data Transaction Language (DTL) - ANSWER-manages database transactions
Database System Instance - ANSWER-is a single executing copy of a database system
CREATE DATABASE DatabaseName - ANSWER-creates a new database
DROP DATABASE DatabaseName - ANSWER-deletes a database, including all tables in the database
USE DatabaseName - ANSWER-selects a default database for use in subsequent SQL statements
SHOW DATABASES - ANSWER-lists all databases in the database system instance
SHOW TABLES - ANSWER-lists all tables in the default database
SHOW COLUMNS FROM TableName - ANSWER-lists all columns in the TableName table of the default database
SHOW CREATE TABLE TableName - ANSWER-shows the CREATE TABLE statement for the TableName table of the default database
Table - ANSWER-has a name, a fixed sequence of columns and varying set of rows
Column - ANSWER-has a name and data type
Row - ANSWER-is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type
Cell - ANSWER-is a single column of a single now
Empty Table - ANSWER-a table without rows
Exactly one value per cell (Relational Rule) - ANSWER-a cell may not contain multiple values. Unknown data is represented with a special NULL value
No duplicate column names (Relational Rule) - ANSWER-duplicate column names are allowed in different tables, but not in the same table
No duplicate rows (Relational Rule) - ANSWER-no 2 rows may have identical values in all columns
No row order (Relational Rule) - ANSWER-rows are not ordered. the organization of rows on a storage device, such as a disk drive, never affects query results
is called data independence, allows database administrators to improve query performance by changing the organization of a data on storage devices without affecting query results
CREATE TABLE - ANSWER-statement creates a new table by specifying the table name, column names, and column data types
DROP TABLE - ANSWER-statement deletes a table, along with all the table's rows from a database
ALTER TABLE - ANSWER-statement adds, deletes or modifies columns on an existing table
Data Type - ANSWER-is a named set of values from which column values are drawn
Signed - ANSWER-number may be negative
Unsigned - ANSWER-number cannot be negative
Data Type storage requirements - ANSWER-char - 1 or 2 bytes per character
int- fixed number of bytes per character
unsigned - can store larger numbers than signed version of the same data type
Operator - ANSWER-a symbol that computes a value from one or more other values called operands
Unary - ANSWER-operator has 1 operand
Binary - ANSWER-operator has 2 operands. Most common
Expression - ANSWER-a string of operators, operands, and parentheses that evaluates to a single value
the order or operator evaluation can effect the value of the expression
SELECT Statement - ANSWER-selects rows from a table.
SELECT Clause - ANSWER-specifies one or more expressions separated by commas that determine what values are returned for each row
FROM Clause - ANSWER-specifies the table from which rows are selected
WHERE Clause - ANSWER-specifies a condition for selecting rows
NULL - ANSWER-is a special value that represents either unknown or inapplicable data
*is not the same as zero for numeric data types or blanks for character types*
NOT NULL - ANSWER-prevents a column from having a NULL value
When arithmetic or comparison operands have 1 or more NULL operands... - ANSWER-the result is NULL
When a WHERE clause evaluates to NULL for values in a row... - ANSWER-the row is not selected
IS NULL - ANSWER-returns TRUE when the value is NULL
IS NOT NULL - ANSWER-returns TRUE when the value is not NULL
NULL Logic - ANSWER-TRUE AND NULL is NULL.TRUE OR NULL is TRUE.
------------------------------------------
FALSE AND NULL is FALSE FALSE OR NULL is NULL
------------------------------------------
NULL AND NULL is NULL
NULL OR NULL is NULL
INSERT Statement - ANSWER-adds rows to a table
INSERT / INSERT INTO - ANSWER-names the table and columns where data is to be added
VALUES Clause - ANSWER-specifies the column values to be added *can list multiple rows at a time*
DEFAULT Keyword - ANSWER-keyword and value follow column name and data type
UPDATE Statement - ANSWER-modifies existing rows in a table
SET Clause - ANSWER-specify the column values
WHERE Clause - ANSWER-specifies which rows are updated *omitting the WHERE clause results in all rows being updated*
DELETE Statement - ANSWER-deletes existing rows in a table
FROM Keyword - ANSWER-is followed by the table name whose rows are to be deleted
*optional* WHERE Clause - ANSWER-specifies which rows are to be deleted *omitting the WHERE results in all rows being deleted*
TRUNCATE Statement - ANSWER-deletes all rows from a table *almost the same as delete but doesn't have a where clause*
Primary Key - ANSWER-is a column, or group of columns used to identify a row *often used in the WHERE clause to select a specific row*
Primary Keys must be: - ANSWER-- unique: this rule ensures that each value identifies at most one row
-Not NULL: this rule ensures that each value identifies at least one row
Simple Primary Key - ANSWER-consists of a single column
Composite Primary Key - ANSWER-consists of multiple columns *denoted by parentheses*
Composite Primary Keys must be: - ANSWER--Unique: vlaues of primary key columns when grouped together must be unique.
-Not NULL: no column of a composite primary key may contain a NULL value
-Minimal: all primary key columns are necessary for uniqueness
PRIMARY KEY Constraint - ANSWER-in a CREATE TABLE statement names the table's primary key *a PRIMARY KEY Constraint ensures that a column or group of columns is always unique & not NULL*
AUTO_INCREMENT Column - ANSWER-is a numeric column that is assigned an automatically incrementing value when a new row is inserted
Foreign Key - ANSWER-is a column or group of columns that refer to a primary key
Referential Integrity - ANSWER-requires foreign key values must be either NULL or match some value of the referenced primary key
Fully NULL Foreign Key - ANSWER-is a simple or composite foreign key in which all columns are NULL
Referential integrity can be violated in 4 ways: - ANSWER-1. a primary key is updated
- a foreign key is updated
- a row containing a primary key is updated
- a row containing a foreign key is updated
*primary key inserts & foreign key deletes NEVER violate referential integrity*
Databases automatically correct referential integrity violations with any of four actions,
specified as SQL constraints: - ANSWER-RESTRICT, SET NULL, SET DEFAULT and
CASCADE
RESTRICT - ANSWER-rejects an insert, update, or delete that violates referential integrity.
*applies to primary key update and delete, and foreign key insert and update.*
SET NULL - ANSWER-sets invalid foreign keys to NULL.
*applies to primary key update and delete, and foreign key insert and update.*
SET DEFAULT - ANSWER-sets invalid foreign keys to the foreign key default value.
*applies to primary key update and delete, and foreign key insert and update.*
CASCADE - ANSWER-propagates primary key changes to foreign keys.
*behaves differently for primary key updates and deletes.-If a primary key is deleted, rows containing matching foreign keys are deleted.-If a primary key is updated, matching foreign keys are updated to the same value.*
MySQL has several limitations on primary key updates and deletes: - ANSWER--
RESTRICT is applied when the ON UPDATE or ON DELETE clause is omitted.
- SET NULL cannot be used when a foreign key is not allowed NULL values.
- SET DEFAULT is not supported in some MySQL configurations.
Constraint - ANSWER-is a rule that governs allowable values in a database
*can be given a name*
Column Constraint - ANSWER-appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column
Ex: NOT NULL is a column constraint.
Table Constraint - ANSWER-appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns.
Ex: FOREIGN KEY is a table constraint.
*A PRIMARY KEY constraint on a composite column must be defined as a table constraint.*
Column or Table Constraint - ANSWER-Some constraint types can be defined as either constraints.
Ex: A PRIMARY KEY constraint on a single column can appear either in the column
declaration or a separate CREATE TABLE clause.
DEFAULT Constraint - ANSWER-does not actually limit allowable values in a column.Instead, DEFAULT specifies a value that is inserted when a column is omitted from an INSERT statement
*For this reason, DEFAULT is not always considered a constraint.*
UNIQUE Constraint - ANSWER-constraint ensures that values in a column, or group of columns, are unique.
CHECK Constraint - ANSWER-constraint specifies an expression on one or more columns of a table.
IN Operator - ANSWER-is used in a WHERE clause to determine if a value matches one of several values.
BETWEEN Operator - ANSWER-provides an alternative way to determine if a value is between two other values.
ex.value >= minValue AND value <= maxValue.-or- ex. value >= minValue AND value <= maxValue.
LIKE Operator - ANSWER-when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.
% - ANSWER-matches any number of characters.
Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".
_ - ANSWER-matches exactly one character.
Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot".
DISTINCT Clause - ANSWER-is used with a SELECT statement to return only unique or 'distinct' values.
ORDER BY Clause - ANSWER-orders selected rows by one or more columns in ascending (alphabetic or increasing) order.
DESC Keyword - ANSWER-with the ORDER BY clause orders rows in descending order.
Join - ANSWER-is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result.
Join Clause - ANSWER-determines how a join query handles unmatched rows.
Two common join clauses are: - ANSWER-Inner Join & Full Join
INNER JOIN - ANSWER-selects only matching left and right table rows.
FULL JOIN - ANSWER-selects all left and right table rows, regardless of match.
LEFT JOIN - ANSWER-selects all left table rows, but only matching right table rows.
RIGHT JOIN - ANSWER-selects all right table rows, but only matching left table rows.
Outer Join - ANSWER-is any join that selects unmatched rows, including left, right, and full joins.
equijoin - ANSWER-compares columns of two tables with the = operator.Most joins are equijoins.
non-equijoin - ANSWER-compares columns with an operator other than =,
such as < and >.
Self Join - ANSWER-joins a table to itself.
Cross Join - ANSWER-combines two tables without comparing columns.
*uses a clause without an ON clause. As a result, all possible combinations of rows from both tables appear in the result.*
Subquery / nested query / inner query - ANSWER-is a query within another SQL query.Is typically used in a SELECT statement's WHERE clause to return data to the outer query and restrict the selected results. Is placed inside parentheses ().
Correlated Subquery - ANSWER-when the WHERE clause references a column from the outer query.The rows selected depend on what row is currently being examined by the outer query.
EXISTS operator - ANSWER-returns TRUE if a subquery selects at least one row and FALSE if no rows are selected.
NOT EXISTS operator - ANSWER-returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.
Flattening a query - ANSWER-Replacing a subquery with an equivalent join
View Table - ANSWER-restructure table columns and data types without changes to the underlying database design.
*not normally stored*
Base Table - ANSWER-A table specified in the view query's FROM clause
*is stored*
Materialized View - ANSWER-is a view for which data is stored at all times.Whenever a base table changes, the corresponding view tables can also change, so this view must be refreshed. To avoid the overhead of refreshing views, MySQL and many other databases do not support this view.
Advantages of Views - ANSWER-Protect sensitive data Save complex queries Save optimized queries
Using views in INSERT, UPDATE, and DELETE statements is problematic: - ANSWER-
Primary keys. If a base table primary key does not appear in a view, an insert to the
view generates a NULL primary key value. Since primary keys may not be NULL, the insert is not allowed.Aggregate values. A view query may contain aggregate functions such as AVG() or SUM(). One aggregate value corresponds to many base table values. An update or insert to the view may create a new aggregate value, which must be converted to many base table values. The conversion is undefined, so the insert or update is not allowed.Join views. In a join view, foreign keys of one base table may match primary keys of another. A delete from a view might delete foreign key rows only, or primary key rows only, or both the primary and foreign key rows. The effect of the join view delete is undefined and therefore not allowed.
Cardinality - ANSWER-refers to maxima and minima of relationships and attributes.
Relationship Maximum - ANSWER-is the greatest number of instances of one entity that can relate to a single instance of another entity.
Relationship minimum - ANSWER-is the least number of instances of one entity that can relate to a single instance of another entity.
Attribute Maximum - ANSWER-is the greatest number of attribute values that can describe each entity instance.
Attribute Minimum - ANSWER-is the least number of attribute values that can describe each entity instance.
unique attribute - ANSWER-describes at most one entity instance.
artificial key - ANSWER-is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.
Functional Dependence - ANSWER-Dependence of one column on another.Reflects business rules.
Ex: "Each student receives one letter grade in a course" indicates the Grade column depends on the composite column (StudentID, CourseCode).
redundancy - ANSWER-is the repetition of related values in a table
normal forms - ANSWER-are rules for designing tables with less redundancy.
logical design - ANSWER-specifies tables, columns, and keys.
Physical Design - ANSWER-specifies indexes, table structures, and partitions.
Storage Engine / Storage Manager - ANSWER-translates instructions generated by a query processor into low-level commands that access data on storage media.
MySQL can be configured with several different storage engines, including: - ANSWER- InnoDB is the default storage engine installed with the MySQL download. InnoDB has full support for transaction management, foreign keys, referential integrity, and locking.MyISAM has limited transaction management and locking capabilities. MyISAM is commonly used for analytic applications with limited data updates.MEMORY stores all data in main memory. MEMORY is used for fast access with databases small enough to fit in main memory.
Different databases and storage engines support different table structures and index types. Ex: - ANSWER-Table structure. Oracle Database supports heap, sorted, hash, and cluster tables. MySQL with InnoDB supports only heap and sorted tables.Index type. MySQL with InnoDB or MyISAM supports only B+tree indexes. MySQL with MEMORY supports both B+tree and hash indexes.
In MySQL with InnoDB: - ANSWER-- Indexes are always B+tree indexes.
- A primary index is automatically created on every primary key.
- A secondary index is automatically created on every foreign key.
- Additional secondary indexes are created manually with the CREATE INDEX
- Tables with a primary key have sorted structure. Tables with no primary key have a
statement.
heap structure.
EXPLAIN Statement - ANSWER-generates a result table that describes how a statement is executed by the storage engine.
DDL (Data Definition Language) - ANSWER-Creates, alters & drops tables
DQL (Data Query Language) - ANSWER-selects from a table
DML (Data Manipulation Language) - ANSWER-inserts, updates & deletes data in a table
DCL (Data Control Language) - ANSWER-grants & revokes permissions to and from users
DTL (Data Transaction Language) - ANSWER-commits data to a database, rolls back data from a database and creates save points
Insert a row - ANSWER-INSERT [INTO] Table Name (column 1, column 2...) VALUES (value1, value2...);
Update a row - ANSWER-UPDATE Table name SET column1 = value1 WHERE condition;
Delete Row - ANSWER-DELETE FROM table name WHERE condition;
Create Table - ANSWER-CREATE TABLE tablename
Delete Table - ANSWER-DROP TABLE tablename
add a column to a table - ANSWER-ALTER TABLE table_name ADD column_name datatype;
modify a column in a table - ANSWER-ALTER TABLE table_name CHANGE currentColumn_name newColumn_name Newdatatype;
delete a column - ANSWER-ALTER TABLE table_name DROP ColumnName;