Chapter 2 - Relational Model
Operator Precedence
operators of same precedence are evaluated from left to right, but operators within parenthesis are evaluated first before anything
row
unnamed sequence of values. each value corresponds to a column and belongs to the column's data type
set
unordered collection of elements enclosed in braces. Ex: {a, b, c} and {c, b, a} are part of same set
keywords
words with special meaning(SELECT, WHERE, FROM)
VALUES
INSERT clause that specifies the column values to be added listed in parentheses to insert multiple rows matching the column order exactly as defined in INTO clause
Intersect
operation that selects rows common to two tables
Difference
operation that selects rows that appear in one table but not another
unary operator
operator with only one operand
data query language (DQL)
sublanguage that retrieves data from the DB
operator
symbol that computes a value from one or more other values called operands
ADD
ALTER TABLE clause that adds a column
DROP
ALTER TABLE clause that deletes a column
CHANGE
ALTER TABLE clause that modifies a column
SQL Statement
a complete command composed of one or more clauses
database model
a conceptual framework for db systems with 3 parts
expression
a string of operators, operands, and parenthesis that evaluates to a single value. Ex: Salary < 34000 AND Department = 'Marketing';
CHAR
data type which is a fixed string of characters
relational operations
based on set theory where each operation generates a results table from one or two input tables collectively known as relational algebra
signed number
can be negative
unsigned number
cannot be negative and can store larger numbers than the signed version
LIMIT
clause that limits the number of rows returned by a SELECT statement. Ex: SELECT * FROM City LIMIT 100;
INSERT INTO
clause that names the table and columns where data is to be added(INTO is optional)
CREATE DATABASE
command to create a new database
DROP DATABASE
command to delete a database, including all tables
comparison operator
computes logical values TRUE or FALSE
Logical operator
computes logical values from logical operands
arithmetic operator
computes numeric values from numeric operands
NOT NULL
constraint that prevents a column from having a NULL value. Must be listed after column name and data type.
Character
data types that represent textual characters
VARCHAR
data type that is a string of variable length up to a specified max size
decimal
data type that represents numbers with fractional values(FLOAT and DECIMAL)
integer
data type that represents positive and negative numbers
relational model
database model based on a tabular data structure, first published in 1970 at IBM and originally designed for transactional data like banking and airline reservations
rules
db model part that governs valid data
operations
db model part that manipulates data structures
data structures
db model part that prescribes how data is organized
literal
explicit values that are string, numeric or binary
clause
groups SQL keywords like SELECT, FROM, and WHERE with name like City, columns like Name and condition like Population > 100000.
column
has a name and data type
table
has a name, a fixed sequence of columns and varying set of rows. Must have at least one column, but any number of rows and is considered empty when no rows are present
SHOW DATABASES
lists all databases available in the database system
SHOW TABLES
lists all tables available in the currently selected DB
SHOW COLUMNS
lists columns available in a specific table named by a FROM clause
data type
named set of values, from which column values are drawn
identifiers
objects from the database like tables, columns, etc.(City, Name, Population, etc.)
binary operator
only had two operands and covers most operators
Rename
operation that changes a table name
Join
operation that combines two tables by comparing related columns. Ex: SELECT * FROM Employee, Department WHERE Employee.DeptCode = Department.DeptCode;
Aggregate
operation that computes functions over multiple table rows such as sum and count
Project
operation that eliminates one or more columns of a table. Ex: SELECT Name FROM Employee;
Product
operation that lists all combinations rows of two tables. Ex: SELECT * FROM Employee, Department;
Select
operation that selects a subset of rows of a table. Ex: SELECT * FROM employee WHERE Salary > 50000;
Union
operation that selects all rows of two tables
DEFAULT
optional keyword and default value that follow the column name and data type in a CREATE TABLE statement so that the column is assigned a default value rather than NULL in case omitted from INSERT statement
tuple
ordered collection of elements enclosed in parenthesis. Ex: (a, b, c) and (c, b, a) are different since orders matters.
Relational Rules
part of the relational model that governs data in every relational database with logical constraints that ensures data is valid. Implemented as SQL constraints
Business Rules
rules based on business policy and are specific to a particular DB
SHOW CREATE TABLE
shows the CREATE TABLE statement for a given table
cell
single column of a single row
NULL
special value that represents either unkown or inapplicable data; Not the same as Zero
INSERT
statement that adds rows to a table with 2 clauses
ALTER TABLE
statement that adds, deletes, or modifies columns on an existing table.
CREATE TABLE
statement that creates a new table by specifying the table name, column names, and column data types
DROP TABLE
statement that deletes a table, along with all the table's rows
TRUNCATE
statement that deletes all rows from a table, nearly identical to DELETE statement with minor difference that depend on DB system being used
DELETE
statement that deletes existing rows in a table with FROM keyword specifying the table's name whose rows are to be deleted. Optional WHERE clause specifies which rows should be deleted and if omitted, all rows will be deleted
UPDATE
statement that modifies existing rows in a table using the SET clause to specify new column values
USE
statement that selects a database and is required to show information about tables within a specific database
MERGE
statement that selects data from one table, called the source and inserts the data to another table, called the target
data transaction language (DTL)
sublanguage that commits data to DB, rolls back data, and creates save points
data control language (DCL)
sublanguage that controls DB user access
data definition language (DDL)
sublanguage that defines the structure of the database
data manipulation language (DML)
sublanguage that manipulates data stored in a DB
