Show Menu
Cheatography

db finals

Spatial Database

1.offers spatial data types:- points lines,­reg­ions.
2.Coll­ection of objects: partition.
operators that evaluate to boolean 1. inside 2.inte­rsect 3. adjacent
operat­ors­->s­patial data types 1.inte­rse­ction, plus,m­inu­s,c­ontour.
operators returning numbers 1.dist­,pe­rim­eter.
operators on set of objects1.sum,­clo­sest.
Relati­onships disjoint 1, in46,t­ouch9 equal10, cover1214, overlap16.
Requir­ements for Graphical Repres­ent­ati­on.1.g­rap­hical display of query results.graphical combin­ation.display of context on an image.query to select a part of an image.L­eg­end.Label Placem­ent.Scale Selection.
NEW THINGS REQUIRED IN DBMSspatial data types.P­rocs for data types.S­patial index struct­ure.filter and refine techni­que­s.s­patial join operat­ions.

XML And Web

Sql is set orient­ed.p­re­pared statem­ents. SQL Inject­ions.JSON Values. true,f­als­e,n­ull­,ob­jec­t,arrayeval and parse XML:- attribute name value pair. root,e­lem­ent­,at­tri­but­e,i­nst­ruc­tio­ns,­tex­t,n­ame­spa­ce,­con­tent.
DTD:- IDREFS : reference to IDS. <!A­TTL­IST­><!­ELE­MEN­T> ?optional star optional repeatable + required repeat­able.
Drawbacks of DTD: 1.inco­nvi­nient to build tools using them. don't capture main domain­s.ids not a good implem­ent­ation. no way of OO inheri­tance.
XSD: using Xpath to for keys.
Simple element : no attrib­ute­s,e­lements and not empty.

Encryption and decryp­tion.

Symmetric Encryp­tion: all users know the key.
RSA: public and private key.L=P*Q;
e random number. s=d ^emod L;
decryp­tion: de=lmod­((p-l)(q-l))
Certifying Servers : SSL.ce­rti­fic­ation authority. public key stored in browser. temp session key browser -> encodes it from amazon pub key.and sends to amazon.
Statis­tical DB Security:- inform­ation about indivi­dua­ls:each query must involve n number of rows.
 

Spatial Indexing

approx­imation of geomet­rie­s.M­inimum bounding rectan­gles.
Two step operat­ion­:1.S­elects all objects whose MBR satisfy the query. A superset of objects is returned. In the next step this superset is refined to return the object that is required.

R Tree

Based on MBR. Used for 2d Object­s.MBR of objects form the leaves of the tree.
R tree is a depth balanc­ed.The root has atleast 2 entries.
Two types of querie­s:1.point query.2.w­indow query.

Quad tree based Indexing

Space based struct­ures. Division of each space into 4 quadrants. the subdiv­ision can be equal or unequal.
SPACE DRIVEN
Based on partit­ioning of the embedding 2D space into rectan­gular cells, indepe­ndently of the object distri­bution. Objects are mapped to cells according to some pre-de­fined criterion.
Example: Quadtrees
DATA DRIVEN
Based on subdiv­ision of the set of objects and not the embedding space. The subdiv­ision adapts to the object distri­bution in the space.
Example: R-trees

Sorting.

Data required in sorted order. grouping operat­ion­s.sort merger join. duplicate removal. Bulk loading B+. External Merge Sort:- 2N ([log n ]+1)
Cost of merge sort:- ______­___­______.
cost : ______­___­___­___­____.
 

Storage

commun­ication via page reques­ts.q­ue­ry-> page requests.RAID 1.Level 0:- break a file into blocks­,di­str­ibute amongst disks.s­imple to implement, no redundancy or error correc­tion. Low MTTF. 2.Level 1 : Exact replica. Read perfor­mance can be improved. Write perf suffers. Expens­ive.twice space.3. Level 2:- Stripes data. bit interl­eav­ed.ECC. Multi disk record ECC, Parity disk.more disks requir­ed.[­er­fpr­,ac­e/ECC code .one read at one time.
Raid Level 3:-Parity Disk is added. ECC codes with each sector.
Raid Level 4:- interl­eaves file blocks.allows small IO operations to be done at once.reads are simple to unders­tand. write requires two reads and two writes.
Raid level 5:- stripes file data and check data over all the disks. write perfor­mance improv­es.high perfor­mance. most expens­ive.mirror each strip.

Two phase locking

restrict access in some way. Balance between safety and effici­ency. release the lock only after the entire transa­ction has completed.
Transa­ction must hold a lock. if another transa­ction conflicts then it will wait for the previous transa­ction to release the lock. @ phase locking blocki­ng.l­eads to deadlock.
Deadlo­cks:- ostrich : ctrl+a­lt+del, timeou­t,d­ete­ction and recovery, avoida­nce­,pr­eve­ntion.
When deadlock is detected some transa­ction is rolled back.s­tar­vat­ion:- same transa­ction may be aborte­d.s­olu­tion: count no of times roll back.
Avoida­nce:- timest­amping.
Lock manager:- process to send lock and unlock.re­spo­nds­-> lock grant.lock table-> hash table.new req end of table.u­nlock results in the entry being deleted and other entries taken care of.
 

Transa­ctions.

Concurrent access.Is­ola­tion: interl­eaved transa­cti­ons.se­ria­lizable transa­cti­ons.Co­nfl­icts:- RW,WR,WW. READ Write transa­cti­ons.:-SET TRANSA­CTION READ ONLY;.WR Conflicts: Dirty Reads: uncomm­itted transa­ctions..RW Conflict :-unre­pea­table read.t­ran­saction reads the same data and gets different ans.Overwr­iting uncomm­itted data..Phantom Problem.
Specifying Isolation levels:- default:- serial­izable.
Eg: Set transa­ction Read Write; Isolation level read uncomm­itted.
ST; Isolation level read commit­ted­;ST­-> Isolation level repeatable read.
Read commit­ted:- does not allow reading uncomm­itted data.
Repeatable Read:-if a tuple is retrieved once then once then it will be repeatable again.
Implem­enting Isolation Levels:- Using locks.s­hared lock and exclusive lock.
Read Uncomm­itted. allows to read without acquiring any lock. Read committed requires a lock.

Theory of Serial­iza­bility.

serial schedule. all steps occur consec­uti­vely. Well Formed Two phased transa­ction. acquires shared lock before reading and exclusive lock before writing.

Security and author­ization

Secrecy:- not see things, Integr­ity:- modify that supposed to. Availa­bility.
Access control:- security policy and security mechan­ism.:- Discre­tionary and Mandatory Access control.
GRANT privilege ON object TO users [WITH GRANT OPTION].
If the creator of the view loses the SELECT then the view is dropped.
RBAC:-­Man­datory Access Control. db object is given a class. The subject is also given a class. Rules based on object and subject.
Bell -La Padula Model.
TS>­S>C­>U top secret­,se­cre­t,c­onf­ide­nti­al,­unc­las­sified.
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          MySQL Cheat Sheet
          SQL Server Cheat Sheet

          More Cheat Sheets by abirjepatil

          db Cheat Sheet