D426 Quick Reference
I. Aggregate Functions
a. Processes values from a set of rows and returns a summary
value
b. Appear in a SELECT clause
i. Process all rows that satisfy the WHERE clause condition
c. COUNT() = counts the number of rows in the set
d. MIN() finds the minimum value in the set
e. MAX() finds the maximum value in the set
f. SUM() sums all the values in the set
g. AVG() computes the arithmetic mean of all values in the set
II. Artificial Key
a. Single column primary key
b. Created by the database designer when no suitable single
column or composite primary key exists
c. Values are integers, generated automatically as new rows are
inserted to the table
i. Stable, simple, and meaningless
1. Stable = values should not change
2. Simple = easy to type and store
3. Meaningless = not contain descriptive information
III. Attributes
a. Individual value
b. Ex. Salary $35,000
IV. Cardinality
a. Refers to maxima and minima of relationships and attributes
V. Crows Foot Notation
a. Depicts cardinality as a circle (zero), a short line (one), or three
short lines (many)
VI. Data Normalization
a. Eliminates redundancy by decomposing a table into two or more
tables in higher normal form
VII. Data Types
a. Can be numeric, textual, or complex
b. Examples
i. INT = stores integer values
ii. DECIMAL = stores fractional numeric values
iii. VARCHAR = stores textual values
iv. DATE = stores year, month, and day
, v. Analysis
vi. Logical design
vii. Physical design
VIII. Design Stages
IX. Indexes
a. Speeds up query slowed down due to large data size
b. Allows compiler to run through data more efficiently
c. Dense Index = contains an entry for every table row
d. Sparse Index = contains an entry for every table block
e. Hash Index = index entries are assigned to buckets
f. Bitmap Index = grid of bits; contain ones and zeros
X. Joins
a. INNER JOIN = selects one matching left and right table rows
b. FULL JOIN = selects all left and right table rows, regardless of
match
c. LEFT JOIN = selects all left table rows, only matching right
table rows
d. RIGHT JOIN = selects all right table rows, only matching left
table rows
e. OUTER JOIN = any join that selects unmatched rows (includes
left, right, and full)
f. UNION = combines two results into one table
g. EQUIJOIN = compares columns of two tables with the =
operator
h. NON-EQUIJOIN = compares columns with another operator <
and >
i. SELF-JOINS = a table to itself
j. CROSS JOIN = combines two tables w/out comparing columns
i. No ON clause used
ii. All possible combinations of rows from both tables appear
in the result
XI. Key Types
a. Primary Key = column/columns used to identify a row
i. Table’s first column & appears on the left of table
diagrams
ii. Simple Primary = consists of a single column
iii. Composite Primary = consists of multiple columns
iv. Auto-Increment Column = numeric column that is
assigned an automatically incrementing value when a new
row is inserted
I. Aggregate Functions
a. Processes values from a set of rows and returns a summary
value
b. Appear in a SELECT clause
i. Process all rows that satisfy the WHERE clause condition
c. COUNT() = counts the number of rows in the set
d. MIN() finds the minimum value in the set
e. MAX() finds the maximum value in the set
f. SUM() sums all the values in the set
g. AVG() computes the arithmetic mean of all values in the set
II. Artificial Key
a. Single column primary key
b. Created by the database designer when no suitable single
column or composite primary key exists
c. Values are integers, generated automatically as new rows are
inserted to the table
i. Stable, simple, and meaningless
1. Stable = values should not change
2. Simple = easy to type and store
3. Meaningless = not contain descriptive information
III. Attributes
a. Individual value
b. Ex. Salary $35,000
IV. Cardinality
a. Refers to maxima and minima of relationships and attributes
V. Crows Foot Notation
a. Depicts cardinality as a circle (zero), a short line (one), or three
short lines (many)
VI. Data Normalization
a. Eliminates redundancy by decomposing a table into two or more
tables in higher normal form
VII. Data Types
a. Can be numeric, textual, or complex
b. Examples
i. INT = stores integer values
ii. DECIMAL = stores fractional numeric values
iii. VARCHAR = stores textual values
iv. DATE = stores year, month, and day
, v. Analysis
vi. Logical design
vii. Physical design
VIII. Design Stages
IX. Indexes
a. Speeds up query slowed down due to large data size
b. Allows compiler to run through data more efficiently
c. Dense Index = contains an entry for every table row
d. Sparse Index = contains an entry for every table block
e. Hash Index = index entries are assigned to buckets
f. Bitmap Index = grid of bits; contain ones and zeros
X. Joins
a. INNER JOIN = selects one matching left and right table rows
b. FULL JOIN = selects all left and right table rows, regardless of
match
c. LEFT JOIN = selects all left table rows, only matching right
table rows
d. RIGHT JOIN = selects all right table rows, only matching left
table rows
e. OUTER JOIN = any join that selects unmatched rows (includes
left, right, and full)
f. UNION = combines two results into one table
g. EQUIJOIN = compares columns of two tables with the =
operator
h. NON-EQUIJOIN = compares columns with another operator <
and >
i. SELF-JOINS = a table to itself
j. CROSS JOIN = combines two tables w/out comparing columns
i. No ON clause used
ii. All possible combinations of rows from both tables appear
in the result
XI. Key Types
a. Primary Key = column/columns used to identify a row
i. Table’s first column & appears on the left of table
diagrams
ii. Simple Primary = consists of a single column
iii. Composite Primary = consists of multiple columns
iv. Auto-Increment Column = numeric column that is
assigned an automatically incrementing value when a new
row is inserted
