Itec 340 final exam Description
Concurrency and Database
Transactions
v
Transactions
·
what are they and why are they important
A logical unit of work that consists of one or more
SQL statements that moves the database from one consistent state to another
consistent state. Therefore, the transaction must
succeed or fail as a unit.
·
commit and rollback
beginning transaction:
either 1st SQL statement or BEGIN TRANSACTION statement
end of transaction:
(1) a COMMIT, SAVEPOINT or ROLLBACK transcation control statement is used.
(2) a DDL (CREATE, ALTER, DROP, RENAME or TRUNCATE) statement is executed (autocommit using set autocommit on).
(3) a DCL (GRANT or REVOKE) statement is executed (autocommit).
(4) a user properly exits (commit).
(5) system crashes (rollback).
v
Concurrent
Access
·
problems created by concurrent access
o
Lost updates
o
Uncommitted Data
o
Inconsistent retrievals
·
resource locking levels
o
Database
§
Locks the entire database
§
Not efficient
o
Table
§
Locks one table
§
Sometimes needed for batch updates
§
Not very efficient
o
Page
§
Usually contains multiple rows
§
Locks a unit corresponding to a the working unit of the OS
o
Row
§
Locks one row of a table
§
Most commonly used locking level
§
Very efficient
o
Column
§
Locks only certain attributes of a row.
·
shared vs exclusive locks
o
Exclusive
§
Will not allow another transaction to obtain a lock of any kind.
o
Shared
§
Allows multiple transactions to share a read only lock on the data.
§
Will not allow any transaction to write the locked rows.
§
Can potentially be upgraded to an Exclusive lock
·
optimistic vs pessimistic locking (including
steps)
·
Optimistic
o
No locking occurs until the transaction is ready for submission by the
client program
o
Can use several techniques to avoid business rule violations
§
Check row image before updating (timestamp, sequence number, or compare
fields)
§
Update only changed fields
§
Use incremental updates to balances
§
Use conditional transactions with a timer
§
Client refreshes (not typically
an option on web applications)
·
Pessimistic
o
Rows are locked at point of
client retrieval of data
o
Client application will not proceed if locks cannot be obtained
o
Locks are not released until client application submits completed
transaction
·
different optimistic strategies for dealing with mutation dead lock
·
solutions to dead lock
Two-phase locking with nowait
(1) growing phase: get one more
lock each time
(2) (2) shrinking phase: release
all locks
·
Oracle locking specifics (select
for update
no wait
wait
x
.)
Begin
Begin Transaction; -- assume we are going to get paper and pen
locks
Select * from accounts
where accountnum in (110, 401, 402) for update nowait; -- get pen lock, in growing phase
Select * from customers
where custid = 123 for update nowait;
-- get paper lock, in growing phase,
Update accounts set balance = balance + 1045
where accountnum = 110; -- complete the growing phasefrom here on, doing the drawing
Update accounts set balance = balance + 645 where accountnum
= 401;
Update accounts set balance = balance + 400 where accountnum
= 402;
Update customers set BalDue
= BalDue + 1045 where custid
= 123;
Insert into JournalEntries
(JENumber, JEDate,
Description)
values
(87543, 01-JAN-02, provided services on account);
Insert into DrCrDetail(JENumber, LineNumber, Account,
Amount)
values (87543, 1,
110, 1045.00);
Insert into DrCrDetail
(JENumber, LineNumber,
Account, Amount)
values (87543, 2,
401, 645.00);
Insert into DrCrDetail
(JENumber, LineNumber,
Account, Amount)
values (87543, 3,
402, 400.00);
Commit; -- the end of transaction, into shringking phase, release both pen and paper locks
Exception
rollback; -- if
dont get pen or paper lock, go back to
beginning state
End;
Access Control
v
privileges (select, update, insert, etc)
A privilege is a permission granted to an Oracle user to execute some action.
v
object vs system privileges
Object privilege: ability to perform some action on various
objects
Example: Alter, Select.Insert,Delete,Update,Execute,Index,References.
System Privilege: right to execute specific sets (data definition and data
control) of commands, such as to perform some action on
database data
database structure such as table, view or
index
one of Oracle system resources such as a tablespace
Example:
CREATE, ALTER and DROP privileges.
v
Grant and revoke (know the
syntax)
Grant syntax:
GRANT <priv> ON <object> TO <user>
[WITH GRANT OPTION / WITH ADMIN OPTION]
where <user> can be PUBLIC or a role,<priv>
can be ALL
Revoke Syntax:
REVOKE <priv> ON <object> FROM
<user>
v
How views help to lock specific
rows or columns in a table
A View can be created for certain columns and rows . When the view is locked, those rows and columns are locked automatically. You can restrict a role to access certain rows or columns and create a view for the role. Then just lock the view whenever needed.
v
Syntax to create
a view
CREATE VIEW AS
SELECT col1, col2, ... FROM tablename
WHERE clause;
v
with grant option
This option allows the recipient of that grant to pass along the privileges one has received to other uses. For example, if the user Susan grants privileges on her tables to the user John with grant option, then John can make grants on Susan's tables to other users. John cannot make grants on Susan's other tables (which were granted to John, but not with grant option) to other users.
v
with admin option
When granting a role to another role(or to a user), you may grant the role using the with admin option. If used, the grantee has the authority to grant the role to other users or roles. The grantee can also alter or drop the table.
v
Roles
Roles are similar to NT groups. DBA can grant Specific Object and System Privileges to roles. The roles have many-to-many relationship between user and role. When roles are assigned to a user, the user inherits all privileges of those roles.
Data Warehousing:
v
Explain the
difference between production database and data warehouse and what kind of
systems each supports (tps and dss)
(
production database: also called transactional
database or operational database, it is designed to support immediate response
transactions, it is under operation, design is not changed.
data warehouse: contains databases and other external data
sources, it contains decision support system, it often generates queries and
reports with variable structures.
v
Name and/or
explain key characteristics of a data warehouse that distinguish it from the
production database
data warehouse
integrated: unified view of data
subject-oriented: facilitates multiple views
time-variant: data has historical aspect
nonvolatile: data cannot be changed
v
Distinguish between a data warehouse and a data mart
Data
warehouse is general purpose, serves for many different functions
Data
mart used for specific function only
v
Define
replication.
data replication: storage of data copies at multiple sites
v
Explain the components of ETL
E:
extract
T:
transport and transform
L:
load
v
Distinguish between group by , rollup, and cube operations
Cube: provide time data and sub-totals and grand total, (e.g. 3 D data)
Slice: can look one level of dimension or time (e.g. 2D plane out of 3D cube)
Dice: look one level of a dimension within a 2D plane (e.g. one row of a 2D plane)
Drill Down: split up into detailed levels within a Dice
Roll Up: Combines detailed levels into a row (It is the reverse of Drill Down)
Pivot: interchange the order display of dimensions (e.g. instead of displaying State vs Item, we display Item vs State)
Example:
Create
table sales (
Item
varchar2(20),
State
varchar2(20),
Amount number(6),
Day
date);
Insert
into Sales
values('Soda','
Insert
into Sales
values('Diet
Soda','California',110,'01-Mar-2006');
1.
Select * from sales;
Total
3*4*3=36 elements shown.
2.
Select Item, State, sum(amount)
from
sales
group
by Item, State;
Total
3*4=12 elements shown.
3.
Select Item, State, sum(amount)
from
sales
group
by Rollup(Item, State);
Total
elements of subtotal of { Item, State }, {Item} and {}:
·
Subtotal for levels of {Item, State}- group by Item, State - 3*4=12 +
·
subtotal for levels of Item: 4 +
·
grand total: 1
= 17
elements shown.
4.
from
sales
group
by Rollup(State, Item);
Total
elements of subtotal of { State, Item }, {State} and {}:
·
Subtotal for levels of { State, Item }- group by State, Item - 3*4=12 +
·
subtotal for levels of State: 3 +
·
grand total: 1
= 16
elements shown.
5.
from
sales
group
by Cube(State, Item);
Total
elements of subtotal of { State, Item }, {State}, {Item} and {}:
·
Subtotal for levels of { State, Item }- group by State, Item - 3*4=12 +
·
subtotal for levels of State: 3 +
·
subtotal for levels of Item: 4 +
·
grand total: 1
= 20
elements shown.
6.
from
sales
group
by Cube(State, Item, Day);
Total
elements of subtotal of { Item, State,
Day }, { Item, State }, {Item, Day}, {State, Day}, {Item}, {State}, {Day} and
{}:
·
Subtotal for levels of {Item, State, Day: 3*4*3=36 +
·
Subtotal for levels of {Item, State}- group by Item, State - 3*4=12 +
·
Subtotal for levels of {Item, Day}- group by Item, State - 3*4=12 +
·
Subtotal for levels of {State, Day}- group by Item, State - 3*3=9 +
·
subtotal for levels of Item: 4 +
·
subtotal for levels of State: 3 +
·
subtotal for levels of Day: 3 +
·
grand total: 1
= 80
elements shown.
v
Distinguish between a view and a materialized view
View
is(when original table updated, view is changed
accordingly)
l
Derived table
l
Behaves like a base table (virtual)
l
Stored query
Materialized
views are schema objects that can be used to summarize, precompute,
replicate, and distribute data. They are suitable in various computing
environments such as data warehousing, decision support, and distributed or
mobile computing (When table changed, it needs to be updated).
v
Distinguish
between fast and complete refresh of a materialized view
v
Distinguish
between on demand, timed, or on commit refresh of a materialized view
Basics of Indexes
See
Find
Index (#3 to #8) in those Slides
v
What is an
index?
An index is an object used to speed up the retrieval of records in response to certain search condition.
v
Answer questions
about a binary tree index.
log N searches for using B* tree searches. If N=1 million, log N=20 searches.
v
What is gained by using an index?
Fast access (direct access) using Binary tree structure for index.
v
When/how are
indexes created?
index is used when table size is large and column selectivity (ratio of distinct columns selected to the total number of records) is high. Index is created using all columns and fields in WHERE clause in the SELECT statement.
v
Disadvantages of
creating an index?
It takes computer resources (cpu times and memory) to create index. When table is small, use index is impractical since the whole table is on one page and memory search is fast..
v
Sequential vs Direct (using rowed) table access
Sequential access: access table rows in sequence, good for
batch processing.
Direct access: access table rows using index, good for on-line processing.
v
Full Index scan,
Unique Index Scan, Index Range Scan (differences and when would they be used)
full index scan: create an index that contains all the
columns included in the select and where clauses of the query, used if a query
needs to access all or most of the rows in a table, but only a subset of the
columns
unique index scan: the one that prevents any duplicate values for the columns
that make up the index, used for primary key index
index range scan: an index comprising more than one column, it is more
selective(efficient) than unique index scan, used when you frequently query on
more than one column within a table
Backup and Recovery
v
oracle
files (datafiles,
logfiles, init files, control files)
v
importance of log files to point in time recovery
Captures all database transactions (changes) in order to roll a
database forward after a tape recovery.
v
archive log mode vs no archive log mode
v
No Archive Log Mode
1.
Also called circular logging
2.
When the final redo log file in a set fills up the first redo log file
is overwritten
3.
Prevents guaranteed point in time recovery
v
Archive Log Mode
1.
The on-line redo logs are archived and numbered before they overwritten
2.
Allows point-in-time recovery of the database
v
colds vs hot backups,
1.
Hot backup
1.
allows backup of the database while the database is
running and available to users.
2.
performance degrades during the backup period
3.
takes longer than a cold backup
2.
Cold backup
1.
requires database shutdown before backup begins
2.
physical files are backed up while shutdown
3.
database is unavailable to users during backup period
4.
faster than a hot backup
v
fault tolerance with RAID (understand mirror sets, volume sets, stripe
sets, and stripe sets with parity)
mirror sets (RAID 1) : data storage
50%
stripe sets with parity (RAID 5):
if 4 disks use 4 stripes each, data storage is 75% since 1 stripe per disk is
used for parity
Table space:
- a logical container for database objects
- related
to one or more data files