Relational Databases Flashcards
CONSTRAINT
Optional keyword followed by the constraint named and declaration
CHECK
Specifies an expression on one ore more columns of a table. Constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL
UNIQUE
Constraint that ensures values in a column, or group of columns, are unique
Table Constraint
governs values in one or more columns
Column Constraint
Govern values in a single column
Constraint
ruled that governs allowable values in a database. Constraints are based on relation and business rules, and implemented with special keywords in a CREATE TABLE statement.
ON UPDATE/ON DELETE
Actions are specified in the optional ON UPDATE and ON DELETE clauses of the FOREIGN KEY constraint
CASCADE
propagates primary key changes to foreign keys
SET DEFAULT
Sets invalid foreign keys to the foreign key default value
SET NULL
Sets invalid foreign keys to a NULL
RESTRICT
Rejects an insert, update or delete that violates referential integrity
Referential Integrity
Relational Rule that requires foreign key values are either fully NULL or match some primary key value
Fully NULL
Foreign key is a simple or composite foreign key in which all columns are NULL
Foreign Key/References
Constraint is added to a CREATE TABLE statement with the FORIEGN KEY and REFERENCES keywords.
Referential integrity
Requires a foreign key values must either be NULL or match some value of the referenced primary key.
Foreign Keys
A Column or group of columns that refer to a primary key
AUTO_INCREMENT
Defines an auto-increment column. Command follows the columns data type in a CREATE TABLE statement
Auto-increment Colum
Numeric column that is assigned an automatically incrementing value when a new row is inserted
PRIMARY KEY
Constraint in a CREATE TABLE statement names the tables primary key.
Composite Primary Key
Primary key consisting of multiple columns
Simple Primary Key
A primary key that consist of a single Column
Primary Key
A Column, or group of columns, used to identify a row
TRUNCATE
Statement that deletes all rows from a table
FROM
Keyword is followed by the table now whose rows are to be deletedT
DELETE
Statement that deletes existing rows in a table
SET
Statements use the SET clause to specify the new column values
Update
Modifies existing rows in a table
Default
Optional keyword that follow the column name and date type in CREATE TABLE statement. Column is assigned the default value, rather than NULL, when omitted form an INSERT statement
VALUES
Specifies the column values to be added
INSERT INTO
names the table and columns where data is to be added. INTO keyword is optional
INSERT
Statement adds a row to a table
Truth Tables
Value of logical expressions containing NULL Operands is defined in a truth table
IS NULL/IS NOT NULL
Operators must be used to select NULL values.
NOT NULL
Constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected. NOT NULL follows the column name and date type in a CREATE TABLE statement.
NULL
Special value that represents either unknown or inapplicable data
WHERE
SELECT statement has an option WHERE clause that specifies a condition for selecting rows. A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL
Condition
Expression that evaluates to a logical value
LIMIT
Clause that limits the number of rows returned by a SELECT statement.
Result table
SELECT statement returns a set of rows, called the result table
SELECT/FROM
Statements selects rows from a table. Statement has a SELECT clause and a FROM clause. FROM clause specifies the table from which rows are selected. SELECT clause specifies one or more expression, separated by commas, that determine what values are returned for each row.
Precedence of Operators
-(unary), ^, * / % , + - (binary), = != < > <= >= , NOT, AND, OR
Operator Precedence
Operators in an expression are evaluated in the order of operator precedence. Parentheses are always evaluated first
Expression
String of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values. Value of the expression may be any data type
Binary Operator
Has two operands
Unary Operator
Has more than one operand
Logical Operator
Computes logical values from logical operands
Comparison operator
Computes logical values TRUE or FALSE. Operands may be numeric, character, and other data types
Arithmetic operator
Computes numeric values from numeric operands
Operator/Operands
Symbol that computes a value from one or more other values
Unsigned
Number cannot be negative
Signed
Number may be negative
Document
Data types contains textual data in a structured format such as XML or JSON
Spatial
Data type that stores geometric information, such as lines, polygons, and map coordinates
Binary
Data type that stores data exactly as the data appears in memory or computer files, bit for bit
Date and time
data type that represents date, time, or both. Some date and time data types include a time zone or specify a time interval
Character
Data type that represents textual characters
Decimal
Data type represents numbers with fractional values
Integer
Represent positive and negative integers
Data Type
named set of values from which column values are drawn
ALTER TABLE
Statement adds, deletes, or modifies columns on an existing table
DROP TABLE
Statement that deletes a table, along with all the table’s rows, from a database
CREATE TABLE
Statement creates a new table by specifying the table name, column names, and column data types
Data Independence
Rule 4 is called Data indepenedence
Empty table
Table without rows is called an empty table
Cell
Single column of a single row
Row
Unnamed sequence of values. Each value corresponds to a column and belongs to the columns data type
Column
has a name and a data type
Table
named, fixed sequence of columns, and a varying set of rows
SHOW CREATE TABLE TableName
Shows the CREATE TABLE statement for the TableName table of the default database
SHOW COLUMNS FROM TableName
Lists all columns in the TableName table of the default database
SHOW TABLES
Lists all tables in the default database
SHOW DATABASES
Lists all databases in the database system instance
USE Databasename
Selects a default database for use in subsequent SQL statements
DROP DATABASE DatabaseName
Deletes a database, including all tables in the database
CREATE DATABASE DatabaseName
Creates a new Database with name of DatabaseName
Database System Instance
single executing copy of a database system. Personal Computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system.
Data Transaction Language
Manages database transactions
Data Control Langauge
Controls database user access
Data Manipulation Language
manipulates data stores in a database
