final

Question 1

SQL DDL

Answer 1

sequel data definition language: define relations, integrity constraints, domains,

Question 2

types in SQL

Answer 2

char(n) fixed len string
varchar(n) variabe len string
int integer
float(n) n digit float

Question 3

create table example

Answer 3

create tableinstructor(ID char(5),name varchar(20),salary numeric(8,2))

Question 4

integrity constraints

Answer 4

ensure certain logical things do not happen, ie not null, primary key, foreign key

Question 5

integrity constraints example

Answer 5

create tableinstructor(ID char(5),name varchar(20) not null,dept_namevarchar(20),salary numeric(8,2),primary key (ID),foreign key (dept_name) referencesdepartment)

Question 6

not null

Answer 6

can be a constraint: used in create to ensure not null

can be a test: ie select all * where x is not null

Question 7

insert example

Answer 7

insert into instructor values (‘10211’, ‘Smith’, ’Bio’, 66000);

Question 8

delete example

Answer 8

delete from r where conditions

Question 9

drop table

Answer 9

drop table r

Question 10

alter example

Answer 10

Alter table – add/remove attributes●Structure:
alter table r add A D
alter table r drop A

Question 11

basic query

Answer 11

select x
from y
where z

Question 12

search for a substring

Answer 12

select name
from instructor
where name like ‘%dar%’;

Question 13

more substring

Answer 13

intro% (intro followed by anything)
%mid%
‘___’ 3 characters
‘___%’ 3 or more chars

Question 14

list people alphabetically

Answer 14

select distinct name
from instructor
order by name asc/desc

Question 15

between exmp

Answer 15

… where att between 100 and 200

Question 16

union

Answer 16

combine 2 relations
select …
union
select …

Question 17

intersect

Answer 17

combine things in common

see union

Question 18

except

Answer 18

remove items in common

Question 19

aggregate functions

Answer 19

select x count(y)
from z
group by x

Question 20

conditions with aggro func

Answer 20

select ...
group by x
having  count(y) > a

Question 21

nested subqueries

Answer 21

queries where a select-from-where exists in another query

used for membership, comparisons, cardinality

Question 22

NS for membership

Answer 22

select from

where att in (select from where)

Question 23

rename

Answer 23

x as y

Question 24

some

Answer 24

search thru a NS for any member
select from
where x > some NS

Question 25

all

Answer 25

some but for all in the set returned by a query

Question 26

unique

Answer 26

returns tuples from an NS where there are no duplicates

Question 27

delete

Answer 27

delete tuples from relation delete from r where ...

Question 28

update

Answer 28

change values of atts on a where cond update instructor set att = att + 4 where att > x

Question 29

update in one statement

Answer 29

``` update r set att = case when att > then att = x else att = y end ```

Question 30

join

Answer 30

takes 2 relations and if atts with the same name hold the same value then that relation gets its atts appended and returned course on r.att = s.att

Question 31

views

Answer 31

``` basically macros for expressions create view v as select from where ```

Question 32

check

Answer 32

added to creates, create(... check (x in ("x","y")));

Question 33

create type

Answer 33

same utility as classes | create type binary as numberic(1,0)

Question 34

domain

Answer 34

``` assert things like not null or create domain x constraint check(value in (domain)) ```

Question 35

create index

Answer 35

create index on r(att)

Question 36

large object types

Answer 36

blobs and clobs, binary or character, returns pointers

Question 37

functions

Answer 37

doubt it

Question 38

triggers

Answer 38

``` given an event, can insert, delete, or update create trigger setnull_trigger before update of takes referencing new row as nrow for each row when (nrow.grade = ‘ ‘) begin atomic set nrow.grade = null; end; go back to this maybe ```

Question 39

types of NoSQL dbs

Answer 39

key value graph database document oriented column family

Question 40

sharding

Answer 40

distribute a database accross many machines, as opposed to a single server

Question 41

replica sets

Answer 41

other machines ensure redundancy

Question 42

master slave replication

Answer 42

operations on a master server automatically correspond to slaves, scalable redundancy, quicker to access a nearby slave

Question 43

noSQL vs risk based data management system

Answer 43

loose schema definition, distributed, less organization, support

Question 44

beneits noSQL

Answer 44

``` elastic scaling: easily distributed big data: high data volumes DBA spec: designed to automate DBA out flexible model: not very strict with schema economics: scalable is cheaper ```

Question 45

drawbacks noSQL

Answer 45

minimal support

Question 46

mongoDB

Answer 46

use hashing to relate to JSON objects

Question 47

mongoDB why use it

Answer 47

is simple, flexible, small, lightweight, complete, very useful

Question 48

MDB hierarchy of objects

Answer 48

databases > collections > documents

Question 49

mongoDB is for

Answer 49

small transactions light on joins

Question 50

mongoDB data definition

Answer 50

none given, no nulls bc nothing expected

Question 51

index

Answer 51

small pointer telling you where to go in the database to find something

Question 52

kinds of indices

Answer 52

ordered: stored in sorted order hash: use a hash function

Question 53

indexing serves to

Answer 53

speed up operations

Question 54

dense index

Answer 54

record appears for every search-key value

Question 55

sparse index

Answer 55

index records for only some search-key values

Question 56

sparse indexes

Answer 56

slower, but with less work + overhead, entry points for "blocks"

Question 57

secondary indices

Answer 57

index points to another index, which points to entry, must be dense

Question 58

nodes composed of

Answer 58

key search values, and pointers to children

Question 59

find record with KS value V

Answer 59

C is root while C is not a leaf move from K0...Kn until V <= K if V > all K, then follow the last pointer else, if V = Ki, then move to Pi+1, else Pi (now we are at a leaf) find i such that V = Ki follow Pi to record if the find fails, no record exists

Question 60

insert record into B+ tree

Answer 60

``` find leaf where SK val would appear if SK val is present add record to the file add a pointer to the bucket else add record to the file add a pointer to the bucket if there's room, insert SK value into node if no room, split the node ```

Question 61

splitting nodes

Answer 61

take the ceiling(n/2) nodes and put em in the first, rest go in the second insert the lowest kp into the parent, split that too if needed can propogate upwards

Question 62

remove nodes

Answer 62

find node, remove from bucket if present if the node now has too few eles, merge take our node and add its members to the left node delete the pointer to our old node, merge up if needed

Question 63

issues with B+ indexing

Answer 63

if a record moves, all pointers have to be updated

Question 64

efficient entry

Answer 64

bulk entry: adding several entries to a B+ tree at once sorted entry: sort items and entrer them n in sorted order bottom up: sort, then add them layer by layer with prefab parts

Question 65

B- tree

Answer 65

no duplicates

Question 66

B- comp with B+

Answer 66

very minor speed advantages, hader to maintain

Question 67

hashing

Answer 67

we obtain SK value via hash function | ie numchars mod 10 returns one of 10 buckets

Question 68

overflow bucket

Answer 68

when a bucket fills you add a pointer to another bucket at the end

Question 69

problems with static hashing

Answer 69

- too many buckets - space allocated but can fail to be filled - periodic reorganization requires new hash function

Question 70

dynamic hashign

Answer 70

grows and shrinks in size

Question 71

delete from extendable hash

Answer 71

locate KV and remove it remove bucket if empty coalesce buckets if possible

Question 72

benefits of extendable hash

Answer 72

doesn't degrade performance as file grows, bucket addressing, overhead can become problematic