MIS 421 Final

ALTER Table

Changes table according to instructions within ALTER statements, instead of attempting to CREATING the table again

Data Control Language

Used to grant/revoke database permissions to/from users and groups

Data Definition Language

Used for creating tables, relationships, and other structures

Data Manipulation Language

Used for queries and data modification

SQL persistent stored modules (SQL PSM)

Programming code that may include SQL statements in modules for reuse
ANSI/ISO standard embedding procedural programming functionality into SQL

Transaction control language

Used to mark transaction boundaries and control transaction behaivior

Non-clustered index

Seperation structure is created for index values
Each value points to an actual records storage location of table data

Clustered index

Sort and store data rows based on index key value
One per table that is physically stored in file

Cursors

Extension to result sets that allow processing one row at a time from result set

SQL injection attack

When data from a user is used to modify a sql statement
Inserting SQL data into form fields rather than "clean" values in the aim of gaining access/adversely modifying data inside the database.
Ex: User inputs into "First Name" field in web application th

Object name

Server_Name.DB_Name.Schema.Object_Name
ServerName, DBName, Schema, Object separated with periods.
Ex: Yorktown.SCA_Bird.dbo.tblPerson

Authorizing

Grant user permissions to database objects

Authenticating

Create login for a user

Database security

Ensures that only authorized users can perform authorized activities at authorized times
Limits certain actions on certain users

Database administation

Person or office specific to DBs and their apps

Data administration

Function managing all data assets in an organization

Checkpoint

Point of synchromization between database and changes in memory

Rollback/rollforward recovery

Periodically save database and keep DB change log since last save

Reprocessing recovery

DB goes back to known point, then manually reprocesses workload from there using application (NOT DBMS)

Incrimental backup

Backup changes since previous backups of any type

Differental backup

Backup changes since last full backup

Full backup

Complete backup of everything

How is a function called?

By name from another sql statement or stored procedure

Table valued funtions

Return a table
- can be used in FROM/WHERE/HAVING clauses
Create function definition defines what table columns will be returned

Aggregate functions

Performs a calculation on set of values and returns a single value

Scalar functions

Returns a single value

What may stored procedures invoke?

Triggers

Stored procedures can be called by

User or DB admins

Stored procedures are assigned to

DBs

Stored procedures can be called FROM

Programs written in standard languages
Scripting languages
SQL command prompt

Advantages of stored porcedures

Greater security because they are always stored on the DB server
Decreased network traffic
SQL can be optimized in DMBS compiler
Code sharing

Stored procedures can issue

INSERT, UPDATE, DELETE, and MERGE commands

Triggers can call

Stored procedures

Trigger is called

When INSERT, UPDATE, or DELETE commands are issued

Trigger is assigned to

Table or view`

AFTER trigger

Perform other tasks after standard actio9ns of triggering statement are performed
May not be assigned to views

INSTEAD OF triggers

Override standard actions of triggering statement: INSERT UPDATE or DELETE

Triggers can be used for

Providing default values
Enforcing data constraints
Updating views
Performing referential integrity actions

When trigger is fired, DBMS supplies

Old and new value for update
New values for inserts
Old values for deletions

Triggers can issue

INSERT, UPDATE, and DELETE commands

Concurrent transactions

2+ transactions that appear to users as they are being processed against a database at the same time
In reality, CPU can execute only one instruction at a time

Concurrency control

Ensures that one user's work does not inappropriately influence another user's work

Concurrency problems

Lost updates
Inconsistent reads that leads to lost updates

Serialized transactions

No lost update problems
Locking (ISOLATION)
Transactions are executed sequentially instead of concurrently
transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained

Resource locking

Prevents multiple applications from obtaining copies of same record when record is about to be changed
Way to stop an inconsistent read

Implicit locks

Placed by DBMS
Almost all locking is implicit

Explicit locks

Issued by application program

Lock granularity

Size of a locked resource
- Large granularity is easy to manage but frequently causes conflicts

Exclusive lock (write lock)

Prohibits other users from reading locked resource

Shared lock (read lock)

Allows other users to read locked resource, but they cant make changes to it

Range lock

Applied to set of records
Can prevent inserting, deleting, or updating to set of records

Deadlock (deadly embrace)

When two transactions are waiting on resource that transaction holds

Breaking a deadlock

There are usually algorithms in place to detect a deadlock
Timeouts
DBMS aborts one of the transactions and rolls back partially completed work

Optimistic locking

Assumes that no transaction conflict will occur

Pessimistic locking

Assumes that a conflict will occur
- Locks are issued before transaction is processed, and then the locks are released

Transaction boundary markers

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

Consistent transaction

Transaction either creates new and valid state of data or returns to previous state

Isolation

Achieved by locks and is used to ensure read consistency

Read uncommitted

Specifies that statememnts can read rows that have been modified by other transactiuons but not yet committed
Dirty read may occur

Repeatable read

Species that statements cant read data that has been modified but not yet committed by other transactions
Prevents dirty and non-repeatable reads

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

Dirty read issue

Read a record that has not been committed

Non-repeatable read issue

2 reads of a row have different values

Phantom read issue

2 reads of a set of records are different in terms of number of rows returned
-User A runs the same query twice.
-In between, User B runs a transaction and commits.
-All the rows in the query have the same value before and after, but different rows are be

INSERT with IdentityID

Inserting into a table with an auto-incrementing identity field (i.e. CustomerID) don't include insert into ID field because the identity will auto increment

INSERT without IdentityID

When inserting into a table with an auto-incrementing identity field (i.e. CustomerID), you can override ID field insert by using WITHOUT IdentityID

Truncate vs. Delete

Truncate is used to remove all data from a table, while leaving
the table structure itself in the database. The SQL TRUNCATE TABLE statement does not use
an SQL WHERE clause to specify conditions for the data deletion�all the data in the table is
always r

ON UPDATE

Trigger constraint determining when the trigger should fire.
Would fire on update of relevant table

ON DELETE

Trigger constraint determining when the trigger should fire.
Would fire on delete of data from relevant table

Global variables

Variables with scope outside of a function or stored procedure, can be accessed from outside.
Examples include @@ROWCOUNT

Local variables

Variables with scope inside a function, trigger, or stored procedure, can only be accessed from within their relevant module.
Examples include @X, @Y, variable declared by a user.

How to call stored procedure/function/trigger

Stored Procedure: EXEC __________
Function: SELECT function_name(_________,_________,______ etc.)
Trigger: SELECT, DELETE, UPDATE

CHECK constraint

Ensures that the value in a column meets a certain expectation

What are the three module types of programs?

User-defined functions, stored procedures, triggers

Server Roles (Security)

dbcreator
securityadmin
serveradmin
sysadmin

Database Roles (Security)

db_datareader
db_datawriter
db_securityadmin
db_owner

Atomic transactions

Either all the actions in a transaction occur or none of them do

Domain

In SQL, describes and constrains values in column
Examples include VARCHAR, CHAR, INT, DECIMAL, MONEY, DATETIME, DATE, etc.

Range

In SQL, explicitly constrains values in column in addition to any datatype domain constraints.
Example: CHECK value on INT column limiting values from 1 to 1000

ALTER Table

Changes table according to instructions within ALTER statements, instead of attempting to CREATING the table again

Data Control Language

Used to grant/revoke database permissions to/from users and groups

Data Definition Language

Used for creating tables, relationships, and other structures

Data Manipulation Language

Used for queries and data modification

SQL persistent stored modules (SQL PSM)

Programming code that may include SQL statements in modules for reuse
ANSI/ISO standard embedding procedural programming functionality into SQL

Transaction control language

Used to mark transaction boundaries and control transaction behaivior

Non-clustered index

Seperation structure is created for index values
Each value points to an actual records storage location of table data

Clustered index

Sort and store data rows based on index key value
One per table that is physically stored in file

Cursors

Extension to result sets that allow processing one row at a time from result set

SQL injection attack

When data from a user is used to modify a sql statement
Inserting SQL data into form fields rather than "clean" values in the aim of gaining access/adversely modifying data inside the database.
Ex: User inputs into "First Name" field in web application th

Object name

Server_Name.DB_Name.Schema.Object_Name
ServerName, DBName, Schema, Object separated with periods.
Ex: Yorktown.SCA_Bird.dbo.tblPerson

Authorizing

Grant user permissions to database objects

Authenticating

Create login for a user

Database security

Ensures that only authorized users can perform authorized activities at authorized times
Limits certain actions on certain users

Database administation

Person or office specific to DBs and their apps

Data administration

Function managing all data assets in an organization

Checkpoint

Point of synchromization between database and changes in memory

Rollback/rollforward recovery

Periodically save database and keep DB change log since last save

Reprocessing recovery

DB goes back to known point, then manually reprocesses workload from there using application (NOT DBMS)

Incrimental backup

Backup changes since previous backups of any type

Differental backup

Backup changes since last full backup

Full backup

Complete backup of everything

How is a function called?

By name from another sql statement or stored procedure

Table valued funtions

Return a table
- can be used in FROM/WHERE/HAVING clauses
Create function definition defines what table columns will be returned

Aggregate functions

Performs a calculation on set of values and returns a single value

Scalar functions

Returns a single value

What may stored procedures invoke?

Triggers

Stored procedures can be called by

User or DB admins

Stored procedures are assigned to

DBs

Stored procedures can be called FROM

Programs written in standard languages
Scripting languages
SQL command prompt

Advantages of stored porcedures

Greater security because they are always stored on the DB server
Decreased network traffic
SQL can be optimized in DMBS compiler
Code sharing

Stored procedures can issue

INSERT, UPDATE, DELETE, and MERGE commands

Triggers can call

Stored procedures

Trigger is called

When INSERT, UPDATE, or DELETE commands are issued

Trigger is assigned to

Table or view`

AFTER trigger

Perform other tasks after standard actio9ns of triggering statement are performed
May not be assigned to views

INSTEAD OF triggers

Override standard actions of triggering statement: INSERT UPDATE or DELETE

Triggers can be used for

Providing default values
Enforcing data constraints
Updating views
Performing referential integrity actions

When trigger is fired, DBMS supplies

Old and new value for update
New values for inserts
Old values for deletions

Triggers can issue

INSERT, UPDATE, and DELETE commands

Concurrent transactions

2+ transactions that appear to users as they are being processed against a database at the same time
In reality, CPU can execute only one instruction at a time

Concurrency control

Ensures that one user's work does not inappropriately influence another user's work

Concurrency problems

Lost updates
Inconsistent reads that leads to lost updates

Serialized transactions

No lost update problems
Locking (ISOLATION)
Transactions are executed sequentially instead of concurrently
transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained

Resource locking

Prevents multiple applications from obtaining copies of same record when record is about to be changed
Way to stop an inconsistent read

Implicit locks

Placed by DBMS
Almost all locking is implicit

Explicit locks

Issued by application program

Lock granularity

Size of a locked resource
- Large granularity is easy to manage but frequently causes conflicts

Exclusive lock (write lock)

Prohibits other users from reading locked resource

Shared lock (read lock)

Allows other users to read locked resource, but they cant make changes to it

Range lock

Applied to set of records
Can prevent inserting, deleting, or updating to set of records

Deadlock (deadly embrace)

When two transactions are waiting on resource that transaction holds

Breaking a deadlock

There are usually algorithms in place to detect a deadlock
Timeouts
DBMS aborts one of the transactions and rolls back partially completed work

Optimistic locking

Assumes that no transaction conflict will occur

Pessimistic locking

Assumes that a conflict will occur
- Locks are issued before transaction is processed, and then the locks are released

Transaction boundary markers

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

Consistent transaction

Transaction either creates new and valid state of data or returns to previous state

Isolation

Achieved by locks and is used to ensure read consistency

Read uncommitted

Specifies that statememnts can read rows that have been modified by other transactiuons but not yet committed
Dirty read may occur

Repeatable read

Species that statements cant read data that has been modified but not yet committed by other transactions
Prevents dirty and non-repeatable reads

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

Dirty read issue

Read a record that has not been committed

Non-repeatable read issue

2 reads of a row have different values

Phantom read issue

2 reads of a set of records are different in terms of number of rows returned
-User A runs the same query twice.
-In between, User B runs a transaction and commits.
-All the rows in the query have the same value before and after, but different rows are be

INSERT with IdentityID

Inserting into a table with an auto-incrementing identity field (i.e. CustomerID) don't include insert into ID field because the identity will auto increment

INSERT without IdentityID

When inserting into a table with an auto-incrementing identity field (i.e. CustomerID), you can override ID field insert by using WITHOUT IdentityID

Truncate vs. Delete

Truncate is used to remove all data from a table, while leaving
the table structure itself in the database. The SQL TRUNCATE TABLE statement does not use
an SQL WHERE clause to specify conditions for the data deletion�all the data in the table is
always r

ON UPDATE

Trigger constraint determining when the trigger should fire.
Would fire on update of relevant table

ON DELETE

Trigger constraint determining when the trigger should fire.
Would fire on delete of data from relevant table

Global variables

Variables with scope outside of a function or stored procedure, can be accessed from outside.
Examples include @@ROWCOUNT

Local variables

Variables with scope inside a function, trigger, or stored procedure, can only be accessed from within their relevant module.
Examples include @X, @Y, variable declared by a user.

How to call stored procedure/function/trigger

Stored Procedure: EXEC __________
Function: SELECT function_name(_________,_________,______ etc.)
Trigger: SELECT, DELETE, UPDATE

CHECK constraint

Ensures that the value in a column meets a certain expectation

What are the three module types of programs?

User-defined functions, stored procedures, triggers

Server Roles (Security)

dbcreator
securityadmin
serveradmin
sysadmin

Database Roles (Security)

db_datareader
db_datawriter
db_securityadmin
db_owner

Atomic transactions

Either all the actions in a transaction occur or none of them do

Domain

In SQL, describes and constrains values in column
Examples include VARCHAR, CHAR, INT, DECIMAL, MONEY, DATETIME, DATE, etc.

Range

In SQL, explicitly constrains values in column in addition to any datatype domain constraints.
Example: CHECK value on INT column limiting values from 1 to 1000