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