D426 OA Study Guide

Flashcards based on the study guide for WGU's D426: Database Management Fundamentals

database application

software that helps business users interact with database systems

database administrator

responsible for securing the database system against unauthorized users by enforcing procedures for user access and database availability

What does “authorization” mean in terms of a database?

“Authorization” refers to the process by which a database restricts individual user access to data in specific tables, columns, or rows of the database.

What do the rules of a database do?

They ensure that the data is consistent with structural and business rules.

query processor

the component of a database that interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application

query optimization

a process that the query processor performs to ensure the most efficient instructions are executed on the data

storage manager

the component of a database that translates the query processor instructions into low-level file-system commands that modify or retrieve data

transaction manager

the component of a database that ensures transactions are properly executed, including preventing conflicts between concurrent transactions and restoring the database to a consistent state in the event of a transaction or system failure

What are the four SQL keywords associated with Data Manipulation Language?

DELETE

UPDATE

INSERT

SELECT

What does the SQL CREATE TABLE statement do?

It creates a new table by specifying the table and column names and assigning each column a data type.

What is analysis in terms of database design?

The phase that specifies database requirements without regard to a specific database system through identification of entities, relationships, and attributes.

What are some alternate names for the Analysis Phase of database design?

conceptual design

entity-relationship modeling

requirements definition

logical design

implements database requirements in a specific database system, which (for relational databases) means converting entities, relationships, and attributes into tables, keys, and columns

physical design

the phase of database design that adds indexes and specifies how tables are organized on storage media

Which phase of database design impacts query performance?

physical design

data independence

the principle that physical design never affects query results

What is an API used for?

To simplify the use of SQL with general-purpose languages.

MySQL Command-Line Client

a text interface included in the MySQL Server download that will return an error code and description when an SQL statement is syntactically incorrect or the database cannot execute the statement

tuple

an ordered collection of elements enclosed in parentheses

table

a data structure with a name, a fixed tuple of columns, and a varying set of rows

data type

a named set of values, from which column values are drawn

Name two synonyms for “table.”

File

Relation

Name two synonyms for “row.”

Record

Tuple

Name two synonyms for “column.”

Field

Attribute

empty table

a table with no rows

A table must have at least one __.

column

What does the DROP TABLE statement do?

delete a table, along with all the table’s rows, from a database

What does the ALTER TABLE statement do?

adds, deletes, or modifies COLUMNS on an existing table

literals

explicit values that are string, numeric, or binary

keywords

words with special meaning in SQL

identifies

objects from the database, like tables, columns, etc.

comments

statements intended only for humans and ignored when by the database when parsing an SQL statement

What are the 5 sublanguages of SQL?

1. Data Definition Language

2. Data Query Language

3. Data Manipulation Language

4. Data Control Language

5. Data Transaction Language

What statements are associated with DDL?

ALTER TABLE (CHANGE/ADD/DROP CAD columns)

CREATE VIEW

CREATE INDEX

Data Query Language (DQL)

retrieves data from the database

Data Control Language

controls database user access

Data Transaction Language

manages database transactions

INNER JOIN

returns a dataset that has any matching values in both tables

LEFT JOIN

returns all records from the left table and any matching records from the right table, with NULL values for any missing right table matches

FULL JOIN

returns all records that from both the left and right tables with NULL values for any cells where a row from one table does not have a corresponding value in the other table

If a column or set of columns can be used to join two tables together, then the columns are most likely __.

primary or foreign keys

TINYINT

• 1 byte

• Unsigned range: 0 - 255

• Signed range: -128 - 127

SMALLINT

• 2 bytes

• Signed range: -32,768 to 32,767

• Unsigned range: 0 to 65,535

MEDIUMINT

• 3 bytes

• Signed range: -8,388,608 to 8,388,607

  • Unsigned range: 0 to 16,777,215

INTEGER or INT

• 4 bytes

• Signed range: -2,147,483,648 to 2,147,483,648

• Unsigned range: 0 to 4,294,967,295

When do one-to-one relationships happen?

Only when there is a constraint or rule in place, such as if an employee can only be assigned to one cubicle and each cubicle can only be assigned to one employee.

UPDATE statement

modifies existing rows in a table

The UPDATE statement uses the __ clause to specify the new column values.

SET

What happens if no WHERE clause is included in an UPDATE statement?

All rows are updated.

DELETE statement

deletes existing rows in a table

In a DELETE statement, the __ keyword is followed by the table name whose rows are to be deleted.

FROM

TRUNCATE

deletes all rows from a table without deleting the table structure

What happens if the WHERE clause is omitted from a DELETE statement?

All table rows are deleted.

What is the difference between a MERGE and a JOIN?

A MERGE can only be used to add rows onto a target table from a source table, whereas a JOIN statement uses columns in two or more tables with matching values to create a new table with columns from multiple tables.

primary key

a column, or group of columns, used to identify a row

candidate key

a simple or composite column that is unique and minimal

What does minimal mean in terms of table keys?

That all columns are necessary for uniqueness.

What does the project operation do?

select table columns

What does the product operation do?

combine two tables into one result in the manner of a cross join

auto-increment column

a numeric column that is assigned an automatically incrementing value when a new row is inserted

What are two common errors that database users make when inserting records that include the primary key column?

1. Manually inserting values for auto-increment primary keys.

2. Omitting values for primary keys that are not auto-increment columns.

foreign key

a column, or group of columns, that refer to a primary key on the same or another table

What keywords are used to add a foreign key constraint to a CREATE TABLE statement?

FOREIGN KEY and REFERENCES

When a foreign key constraint is specified, the database __ insert, update, and delete statements that violate referential integrity.

rejects

What are the four referential integrity actions?

1. RESTRICT

2. SET NULL

3. SET DEFAULT

4. CASCADE

RESTRICT

the default referential integrity action for MySQL, which rejects an insert, update, or delete that violates referential integrity

SET NULL

sets any invalid foreign key values to NULL

What makes a foreign key value invalid?

If there is no matching primary key value.

SET DEFAULT

sets invalid foreign keys to a specified foreign key default value

CASCADE

propagates primary key changes to foreign keys in order to maintain referential integrity

constraint

a rule that governs allowable values in a database

What are constraints based on?

relational and business rules

What does a database do with insert, update, or delete statements that violate constraints?

automatic reject

How are constraints added and dropped?

With ALTER TABLE TableName followed by an ADD, DROP or CHANGE clause and values.

BETWEEN operator

an alternative to using “value >= minValue AND value <= maxValue” to determine if a value is between two other values

The __ operator is used to match text against a pattern using a wildcard character in the WHERE clause.

LIKE

Which wildcard character can stand in for any number of missing characters?

%

Which wildcard character is used to represent a single missing character?

_

alias

a temporary name assigned to a column or table

materialized view

a view for which data is stored at all times and must be refreshed whenever the data in the base table changes

When __ is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.

WITH CHECK OPTION

entity-relationship model

a high-level representation of data requirements, ignoring implementation details

entity

a person, place, product, concept, or activity

relationship

a statement about two entities

attribute

a descriptive property of an entity

reflexive relationship

a statement about how an entity relates to itself

What shape is used to represent entities on an ER diagram?

rectangle

What are the eight steps of analysis/conceptual design?

1. Discover entities, relationships, and attributes.

2. Determine cardinality

3. Distinguish strong and weak entities

4. Create supertype and subtype entities.

5. Implement entities.

6. Implement relationships.

7. Implement attributes.

8. Apply normal form.

cardinality

the maxima and minima of relationship attributes

relationship maximum

the greatest number of instances of one entity that can relate to a single instance of another entity

relationship minimum

the least number of instances of one entity that can relate to a single instance of another entity

In ER diagrams, attribute maximum and minimum follow the attribute name. The __ appears in parentheses.

minimum

If an attribute minimum is 1, then the attribute is __.

required

subtype entity

an entity that is a subset of another entity type, such as managers being a subset of employees, or squares being a subset of rectangles

supertype entity

an entity type that has subsets within the database

IsA relationship

the identifying relationship between a subtype and supertype entity

partition

a group of mutually exclusive subtype entities within a supertype

How does crow’s foot notation depict cardinalities?

• 0 = a circle

• 1 = a short line

• many = three short lines

intangible entity

an entity that is documented in the data model but not tracked with data in the database

weak entity

an entity with no identifying attribute but rather an identifying relationship