Lesson 2 Flashcards
relational database
Conceptual framework for a database systems with 3 parts:
-data structures: how data is organized
-operations: that manipulate data structures
-rules: logical constraints that ensure data is valid
set
unordered collection of elements enclosed in braces
Ex: {a,b,c} and {c,b,a}
tuple
ordered collection of elements enclosed in parentheses
table
has a name, a fixed tuple of columns, and a varying set of rows
Synonyms: Tables, File, Relation
column
has a name and a data type
Synonyms: Column, Field, Attribute
row
-unnamed tuple of values
-since rows are a set, rows have no inherit order
Synonyms: Row, Record, Tuple
relational data type
a data type is a named set of values, from which column values are drawn
relational algebra
theoretical foundation of the SQL language
relational rules
-rules are logical constraints that ensure data is valid
-govern data in every relational database
Relational Operations: SELECT
selects a subset of rows of a table
Relational Operations: JOIN
combines two tables by comparing related columns
Relational Operations: UNION
selects all rows of two tables
Relational Operations: AGGREGATE
computes functions over multiple table rows, such as sum and count
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
-high-level computer language for storing, manipulating, and retrieving data
-standard language for relational database
SQL statement Literals
Strings: must be surrounded by single or double quotes
Numeric: 123
Binary: represented with x’0’ where the 0 is any hex values
Data Definition Language (DDL)
defines the structure of the database
CREATE, ALTER, DROP
Data Manipulation Language (DML)
manipulates data stored in a database
INSERT, UPDATE, DELETE
Data Query Language (DQL)
retrieves data from database
SELECT
Data Control Language (DCL)
controls database user access
GRANT, REVOKE
Data Transaction Language (DTL)
manages database transactions
SAVEPOINT, ROLLBACK, COMMIT
table
has a name, fixed sequence of columns (tuples), and a varying set of rows
table rules
-one value per cel
-Unknown data is represented with NULL
-no duplicate column names in the same table
-no duplicate rows
-no row order
column
has a name and a data type
row
unnamed sequence of values
cell
single column of a single row
empty table
table without rows
ALTER TABLE
DDL
adds, delete, or modifies
Data types: INT
positive or negative integer values
Data types: VARCHAR(N)
values with 0 to N characters
Data types: DATE
Date values YYYY-MM-DD
Data types: CHAR(N)
fixed string value
Dat types: TIME
hh:mm:ss
Data types: DATETIME
YYYY-MM-DD HH:MM:SS
Data types: DECIMAL
numeric values of which digits follow the decimal point
Integer Storage
Operator
operator is a symbol that computes a value from one or more values, called operands
Arithmetic operators
compute numeric values from operands
Comparison operators
compute logic values TRUE or FALSE
Logical operators
compute of logical values from logical operand
Operator/Operand
Operator precedence
NULL
value that represents unknown or inapplicable data
INSERT
adds rows to a table
UPDATE
uses SET clause to specify new column values
optional WHERE clause specifies which rows are updated
DELETE
deletes existing rows in a table
TRUNCATE
deletes all rows from a table
MERGE
-selects data from one table, called the source, and inserts the data to another table, called the target
- MySQL does not support MERGE
Primary Keys
a column, or a group of columns, used to identify a row
Must be:
UNIQUE
NOT NULL
Simple primary key
consists of a single column
Composite primary key
consist of multiple columns
auto-increment
numeric column that is assigned an automatically incrementing value when a new row is inserted
foreign key
column, or group of columns, that refer to primary key
foreign key constraint
when specified, database rejects insert, update, and delete statements that violate referential integrity
fully NULL
simple or composite foreign key in which all columns are NULL
Referential integrity
relational rule that requires foreign key values are NULL or match primary key value
RESTRICT
rejects an insert update, or delete that violate referential integrity
RESTRIC, SET NULL, SET DEFAULT
Applies to primary key update and delete
Applies to foreign key insert and update
CASCADE
propagates primary key changes to foreign keys
applies to primary key update and delete only
Constraint
rule that governs allowable values in a database
UNIQUE
ensures that values in a column, or group of columns, are unique
CHECK
-specifies an expression on one or more columns of a table.
-constraint is violated when the expression is
FALSE and satisfied when the expression is either TRUE or NULL.
SQL CONSTRAINT
