D426 Quizlet Flashcards

Software that helps business users interact with database systems.

Database Application

Responsible for securing the database system against unauthorized users. A ___ ___ enforces procedures for user access and database system availability.

Database Administrator

Many database users should have limited access to specific tables, columns, or rows of a database. Database systems authorize individual users to access specific data.

Authorization

Database systems ensure data is consistent with structural and business rules.

Rules

Interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. Performs query optimization to ensure the most efficient instructions are executed on the data.

Query Processor

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.

Storage Manager

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.

Transaction Manager

Data about the database, such as column names and the number of rows in each table.

Metadata

Stores data in tables, columns, and rows, similar to a spreadsheet.

Relational Database

All ___ ___ systems support the SQL query language.

Relational Database

Relational systems are ideal for databases that require an accurate record of every transaction, such as banking, airline reservation systems, and student records.

Relational Database

The newer non-relational systems are called NoSQL, for 'not only SQL', and are optimized for big data.

MongoDB (NoSQL)

INSERT inserts rows into a table. SELECT retrieves data from a table. UPDATE modifies data in a table. DELETE deletes rows from a table.

SQL Statements

A statement that 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.

CREATE TABLE (Statement)

INT stores integer values. DECIMAL stores fractional numeric values. VARCHAR stores textual values. DATE stores year, month, and day.

Data Type

Analysis Logical design Physical design

Database Design

This phase 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, and an attribute is a descriptive property of an entity.

Analysis Phase, Conceptual Design, Entity-Relationship Modeling (Database Design)

This phase implements database requirements in a specific database system. For relational database systems, ___ design converts entities, relationships, and attributes into tables, keys, and columns.

Logical Design (Database Design)

Specifies indexes, table structures, and partitions. This phase adds indexes and specifies how tables are organized on storage media. Affects query processing speed but never affects the query result.

Physical Design

Allows database designers to tune query performance without changes to application programs.

Data Independence

To simplify the use of SQL with a general-purpose language, database programs typically use an ___ ___ ___.

Application Programming Interface (API)

A text interface included in the MySQL Server download.

MySQL Command-Line Client

'World' database, a database that is usually installed with ___.

MySQL

MySQL Server returns an ___ ___ and description when an SQL statement is syntactically incorrect or the database cannot execute the statement.

Error Code

Data structures that prescribe how data is organized. Operations that manipulate data structures. Rules that govern valid data.

Database Model

An ORDERED collection of elements enclosed in parentheses.

Tuple

A ___ has a name, a fixed tuple of columns, and a varying set of rows.

Table

A ___ has a name and a data type.

Column

Is an unnamed tuple of values. Each value corresponds to a column and belongs to the column's data type.

Row

A ___ ___ is a named set of values, from which column values are drawn.

Data Type

Selects all rows of two tables.

Union

Computes functions over multiple table rows, such as sum and count.

Aggregate

Rules that are logical constraints and ensure data is valid.

Relational Rules

SQL is the standard language for relational databases, and is commonly supported in non-relational databases.

The standard language for Relational Databases…

Explicit values that are string, numeric, or binary.Strings must be surrounded by single quotes or double quotes.Binary values are represented with x'0' where the 0 is any hex value.

Literals (SQL Syntax)

Words with special meaning. ex. SELECT, FROM, WHERE

Keywords (SQL Syntax)

Objects from the database like tables, columns, etc. ex. City, Name, Population

Identifiers (SQL Syntax)

Data Definition Language (DDL) defines the structure of the database. Data Query Language (DQL) retrieves data from the database. Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language (DCL) controls database user access. Data Transaction Language (DTL) manages database transactions.

SQL Sublanguages

A single column of a single row.

Cell

Rows

Not Ordered

Rule 7. Allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.

Data Independence

Statement that deletes a table, along with all the table's rows, from a database.

DROP TABLE

Statement that adds, deletes, or modifies columns on an existing table.

ALTER TABLE

4 bytes

INT

2 bytes

SMALLINT

8 bytes

BIGINT

1 byte

TINYINT

3 bytes

MEDIUMINT

Divides one numeric value by another and returns the integer remainder

% (Modulo)

Raises one numeric value to the power of another.

^

Compares two values for equality.

=

Compares two values for inequality.

!=

A special value that represents either unknown or inapplicable data.

NULL

INTO clause names the table and columns where data is to be added. The keyword INTO is optional. VALUES clause specifies the column values to be added.

INSERT Statement (Clauses)

INSERT [INTO] TableName (Column1, Column2, …) VALUES (Value1, Value2, …)

INSERT Statement (Syntax)

Uses the SET clause to specify the new column values. An optional WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated.

UPDATE Statement (Clauses)

The FROM keyword is followed by the table name whose rows are to be deleted. An optional WHERE clause specifies which rows should be deleted. Omitting the WHERE clause results in all rows in the table being deleted.

DELETE Statement (Keywords/Clauses)

Deletes all rows from a table.

TRUNCATE Statement

A ___ ___ is a constraint on a column, or group of columns, used to identify a row. The ___ ___ is usually the table's first column and appears on the left of table diagrams, but the position is not significant to the database. A solid circle (●) notates these in table diagrams. MUST BE \Unique\ and \Not NULL\

Primary Key

A key that consists of a SINGLE column.

Simple Primary Key

A key that consists of multiple columns and is denoted with parenthesis. MUST BE Minimal, meaning only NECESSARY primary keys may be involved. In it's minimal stage, if one of these keys is removed it then becomes no longer unique.

Composite Key

Database users occasionally make the following errors when inserting primary keys: Inserting values for auto-increment primary keys. Omitting values for primary keys that are not auto-increment columns. MySQL allows insertion of a specific value to an auto-increment column. However, overriding auto-increment for a primary key is usually a mistake.

Auto-Increment

Is a column, or group of columns, that refer to a primary key. An empty circle (○) represents foreign keys in table diagrams, and an arrow leads to the referenced primary key. When a ___ ___ constraint is specified, the database rejects insert, update, and delete statements that violate referential integrity. Referential integrity requires ___ ___ values must either be NULL or match some value of the referenced primary key.

Foreign Key

___ rejects an insert, update, or delete that violates referential integrity.

RESTRICT

Propagates primary key changes to foreign keys.

CASCADE

A ___ is a rule that governs allowable values in a database based on relational and business rules.

Constraint

The ___ constraint ensures that values in a column, or group of columns, are NEVER REPEATED.

UNIQUE (Constraint)

The ___ constraint specifies an expression on one or more columns of a table.

CHECK (Constraint)

Constraints are added and dropped with the ALTER TABLE TableName followed by an ADD, DROP, or CHANGE clause.

Adding and Dropping Constraints

The ___ operator provides an alternative way to determine if a value is between two other values.

BETWEEN (Operator)

The ___ operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

LIKE (Operator)

The ___ ___ clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. The DESC keyword with the ORDER BY clause orders rows in descending order.

ORDER BY (Clause)

Returns the absolute value of n. Distance from zero. Always a positive number or zero.

ABS(n) (Function)

Returns the lowercase \s. Or any specified characters.

LOWER(s) (Function)

Returns the string \s\ without leading and trailing spaces

TRIM(s) (Function)

Returns the hour, minute, or second from time t

HOUR(t) MINUTE(t) SECOND(t) (Function)

COUNT() counts the number of rows in the set. MIN() finds the minimum value in the set. MAX() finds the maximum value in the set. SUM() sums all the values in the set. AVG() computes the arithmetic mean of all the values in the set.

Aggregate (Function)

The HAVING clause is used with the GROUP BY clause to filter group results.

HAVING (Clause)

A ___ is 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.

JOIN

A column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword.

AS (Alias)

Selects only matching left and right table rows.

INNER JOIN

Selects all left and right table rows, regardless of match.

FULL JOIN

Selects all left table rows, but only matching right table rows.

LEFT JOIN

Selects all right table rows, but only matching left table rows.

RIGHT JOIN

Any join that selects unmatched rows, including left, right, and full joins.

OUTER JOIN

The ___ keyword combines the two results into one table.

UNION (Keyword)

An ___ compares columns of two tables with the = operator. This is the most common type of join.

EQUIJOIN

A non-equijoin compares columns with an operator other than =, such as < and >.

NON-EQUIJOIN

A ___ combines two tables without comparing columns without an ON clause. This is the only JOIN that does not require a condition.

CROSS-JOIN

A ___ joins a table to itself.

SELF-JOIN

Sometimes called a nested query or inner query, is a query within another SQL query.

Subquery

In some databases, view data can be stored. A ___ ___ 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.

Materialized View

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

WITH CHECK OPTION

An ___ entity is documented in the data model, but not tracked with data in the database. In an ER diagram, intangible entities are distinguished with special notation, such as a dashed rectangle or distinct color.

Intangible Entity

Weak Entities

Dependent Entities

A group of related entities.

Subject Area

Commonly used for software development. Software data structures are similar to database structures

Unified Modeling Language (UML)

Each entity becomes a table and each attribute becomes a column. Tables and columns are revised in subsequent steps.

Logical Design Phase (First Step)

Informally, a table is in ___ ___ ___ when all non-key columns depend on the key, the whole key, and nothing but the key.

Third Normal Form

A table is in ___ ___ ___ when all non-key columns depend on the whole primary key. In other words, a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is in this form.

Second Normal Form