Data Management Foundations

Data Structures

prescribe how data is organized

Operations

manipulate data structures

Rules

govern valid data

Relational Model

a way to structure data based on relations between data entities, typically using tables in a database.

Big data

unprecedented data volumes and rapidly changing data structures

set

unordered collection of elements enclosed in braces Ex: {a, b, c}

tuple

an ordered collection of elements, enclosed in parentheses Ex (a,b,c) and (c,b,a) are different

Table

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

column

has a name and a data type

row

unnamed tuple of values. Each value corresponds to a column in the table and belongs to the columns data type

data type

named set of values, from which column values are drawn

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 2 tables

Aggregate

computes functions over multiple table rows, such as sum and count

Difference

selects rows that appear in one table but not another

Rename

changes a table name

Relational rules

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

based on business policy and specific to a particular database. Ex: All rows of the Employee table must have a valid entry in the DepartCode column.

Structured Query Language (SQL)

a high-level computer language for storing, manipulating, and retrieving data. SQL is the standard language for relational databases

statement

a complete command composed of one or more clauses

clause

groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000

Literals

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

words with special meaning

Identifiers

Objects from the database like tables, columns, etc

Comments

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

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 stores in a database

Data Control Language (DCL)

controls database user access

Data Transaction Language (DTL)

manages database transactions

automated script

a series of SQL statements that is executed repeatedly. Statements are prepared in advance and saved in a file or as a database stored procedure

Database system instance

a single executing copy of a database system

CREATE DATABASE DatabaseName

creates new database

DROP DATABASE DatabaseName

deletes database, including all tables in database

SHOW

provides database users and administrators with info about databases, the database contents (tables, columns, etc.) and server status info

SHOW DATABASES

lists databases available in the database system.

SHOW TABLES

lists tables available in the currently selected database

SHOW COLUMNS

lists columns available in a specific table named by a FROM clause.

SHOW CREATE TABLE

shows the CREATE TABLE statement for a given table.

USE

selects a database and is required to show info about tables within a specific database

cell

a single column of a single row

empty table

a table without rows

Exactly one value per cell

A cell may not contain multiple values. Unknown data is represented with a special NULL value.

No duplicate column names

Duplicate column names are allowed in different tables, but not in the same table.

No duplicate rows

No two rows may have identical values in all columns

No row order

Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results.

data independence

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

CREATE TABLE

creates a new table by specifying the table name, column names, and column data types.

INT or INTEGER

integer values

VARCHARN(N)

values with 0 to N characters

DATE

data values

DECIMAL (M,D)

numeric values with M digits, of which D digits follow the decimal point

DROP TABLE

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

ID

Integer

Name

Variable-length string with maximum 40 characters

ProductType

Variable-length string with maximum characters

OriginDate

Year, month, and day

Weight

Decimal number with six significant digits and one digit after the decimal point

ALTER TABLE

statement adds, deletes, or modifies columns on an existing table

Transpose

an operation on a table in which rows become columns, and columns become rows

data type

a named set of values from which column values are drawn

Integer

data types that represent positive and negative integers

Decimal

data types that represent numbers with fractional values

Character

data types that represent textual characters

Date and time

data types that represent date, time, or both

Binary

data types store data exactly as the data appears in memory or computer files, bit for bit

Spatial

data types store geometric information, such as lines, polygons, and map coordinates

Document

data types contain textual data in a structured format such as XML or JSON

signed number

negative

unsigned number

cannot be negative

Unary operator

has one operand, ex: -(-2)

expression

a string of operators, operands, and parentheses that evaluates to a single value

SELECT clause

selects rows from a table

FROM clause

specifies the table from which rows are selected

result table

The SELECT statement returns a set of rows, called the result table

condition

an expression that evaluates to a logical value

WHERE clause

clause that specifies a condition for selecting rows

IN operator

is used in a WHERE clause to determine if a value matches one of several values

Between operator

provides an alternative way to determine if a value is between two other values

LIKE operator

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

DISTINCT clause

used with a SELECT statement to return only unique or 'distinct' values. Ex: The first SELECT statement in the figure below results in two 'Spanish' rows, but the second SELECT statement returns only unique languages, resulting in only one 'Spanish' row

ORDER BY clause

orders selected rows by one or more columns in ascending (alphabetic or increasing) order

DESC keyword

orders rows in descending order with the ORDER BY clause

function and argument

operates on an expression enclosed in parentheses, called an argument, and returns a value

Aggregate function

processes values from a set of rows and returns a summary value. Appear in a SELECT clause and process all rows that satisfy the WHERE clause condition

What are the common aggregate functions?

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

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

HAVING clause

used with the GROUP BY clause to filter group results. The optional HAVING clause follows the GROUP BY clause and precedes the optional ORDER BY clause