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