Chapter 3: Intro to SQL; Chapter 4: Intermediate SQL Flashcards
def: aggregate functions
operate on multiset of values of a column and return a value
aggregate functions (5)
- avg
- min
- max
- sum
- count
def: natural join
matches tuples with the same values for all common attributes, retains only one copy of each common column
def: outer join
def: join operations
takes two relations and returns another relations as result
def: join condition
defines which tuples in 2 relation match
def: join type
defines how tuples that do not match are treated based on join condition
def: view
a way to hide some data from view of users
def materialized view
physical copy of a view
view v1 depends directly on view v2 when…
v2 is used in expression defining v1
view v1 depends on view v2 when…
v1 depends directly on v2 or there is a path dependencies from v1 to v2
view v is recursive when….
it depends on itself
def: transaction
a sequence of query and/or update statements and is a ‘unit’ of work
transaction must end with one of the two statements (2)
- commit work
- rollback work
def: commit work
updates performed by transaction become permanent
def: rollback work
all updates performed in transaction are undone
def: atomic transaction
either fully executed or rolled back as if never occurred
purpose of integrity statements
guard against accidental damage to database by ensuring that authorized changes do not result in loss of data consistency
constrains on a single relation (4)
- not null
- primary key
- unique
- check(p) where p is predicate
def: assertion
predicate expressing condition that we wish the database would always satisfy
def: attribute index
a data structure that allows database system to find tuples that have specified value efficiently
def: grant command in SQL
used to give authorization
def: revoke command in SQL
remove authorization
def: role in SQL
a way to distinguish among various users and what they can access/update
