Data Management OA

Database Application

Software that helps business users interact with database systems.

Database Administrator

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

Authorization

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.

Rules

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

Query Processor

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.

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.

Transaction 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.

Metadata

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

Relational Database

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.

MongoDB (NoSQL)

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

SQL Statements

INSERT inserts rows into a table.

SELECT retrieves data from a table.

UPDATE modifies data in a table.

DELETE deletes rows from a table.

CREATE TABLE (Statement)

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.

Data Type

INT stores integer values.

DECIMAL stores fractional numeric values.

VARCHAR stores textual values

DATE stores year, month, and day

Database Design

Analysis (Conceptual)

Logical Design

Physical Design

Analysis Phase, Conceptual Design, Entity-Relationship Modeling (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.

Logical Design (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.

Physical 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.

Data Independence

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

Application Programming Interface (API)

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

MySQL Command-Line Client

A text interface included in the MySQL Server download.

MySQL

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

Error Code

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

Database Model

Data Structures (relational database) that prescribe how data is organized.

Operations that manipulate data structures.

Rules that govern valid data.

Tuple

An ORDERED collection of elements enclosed in parentheses.

Table

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

Column

A ______ has a name and a data type.

Row

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

Data Type

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

Union

Selects all rows of two tables.

Aggregate

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

Relational Rules

Rules that are logical constraints and ensure data is valid.

The standard language for Relational Databases...

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

Literals (SQL Syntax)

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.

Keywords (SQL Syntax)

Words with special meaning.

ex. SELECT, FROM, WHERE

Identifiers (SQL Syntax)

Objects from the database like tables, columns, etc.

ex. City, Name, Population

SQL Sublanguages

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.

Cell

A single column of a single row.

Not Ordered

Rows

Data Independence

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

DROP TABLE

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

ALTER TABLE

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

INT

4 bytes

SMALLINT

2 bytes

BIGINT

8 bytes

TINYINT

1 byte

MEDIUMINT

3 bytes

% (Modulo)

Divides one numeric value by another and returns the integer remainder

^

Raises one numeric value to the power of another.

=

Compares two values for equality.

!=

Compares two values for inequality.

NULL

A special value that represents either unknown or inapplicable data.

INSERT Statement (Clauses)

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 (Syntax)

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

UPDATE Statement (Clauses)

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.

DELETE Statement (Keywords/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.

TRUNCATE Statement

Deletes all rows from a table.

Primary Key

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"

Composite Key

A key that consists of multiple columns and is denoted with parentheses.

Must be minimal, meaning only necessary primary keys may be involved. In its minimal stage, if one of these keys is removed, then it becomes no longer unique.

Simple Primary Key

A key that consists of a SINGLE column

Auto-Increment

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.

Foreign Key

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.

RESTRICT

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

CASCADE

Propagates primary key changes to foreign keys.

Constraint

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

UNIQUE (Constraint)

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

CHECK (Constraint)

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

Adding and Dropping Constraints

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

BETWEEN (Operator)

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

LIKE (Operator)

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

ORDER BY (Clause)

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.

ABS(n) (Function)

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

LOWER(s) (Function)

Returns the lowercase "s". Or any specified characters.

TRIM(s) (Function)

Returns the string "s" without leading and trailing spaces

HOUR(t)

MINUTE(t)

(Function)

Returns the hour, minute, or second from time t

Aggregate (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.

HAVING (Clause)

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

JOIN

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.

AS (Alias)

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

INNER JOIN

Selects only matching left and right table rows.

FULL JOIN

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

LEFT JOIN

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

RIGHT JOIN

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

OUTER JOIN

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

UNION (Keyword)

The ______ keyword combines the two results into one table.

EQUIJOIN

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

NON-EQUIJOIN

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

CROSS-JOIN

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

SELF-JOIN

A ___________ joins a table to itself.

Subquery

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

Materialized View

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.

WITH CHECK OPTION

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

Intangible Entity

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.

Dependent Entities

Weak Entities

Subject Area

A group of related entities.

Unified Modeling Language (UML)

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

Logical Design Phase (First Step)

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

Third Normal Form

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

Second 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.