dbfinals Cheat Sheet by abirjepatil

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.

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.

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.

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.

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.

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

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 : ______­___­___­___­____.

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.

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.

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.

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

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.