CIS Exam 1

database

collection of data managed by a database management system

database management system

software that interacts with users, applications, database to capture and analyze data

dbms aspects

efficient, convenient, reliable, safe multi-user, massive, persistent

query parser

builds the tree structure from text query

query preprocessor

semantic checks and tree transformation

query optimizer

transform query plan to best sequence of operations

query parser, query preprocessor, query optimizer

query compiler steps in order

atomicity, consistency, isolation, durability

acid properties of DMBS

atomicity DBMS

allows no partial actions

consistency DBMS

always be in a consistent state or fail

isolation DBMS

multi-user with concurrent execution transparent to single user

durability DBMS

recovery from failures, errors, misuses (with help of logging)

view layer DBMS

users interact with database

show different parts of the database to different users based on needs and permissions

schema

types of variables in programming language

set up structure in database

redundancy

represents the same thing in 2 or more ways

good entity set

at least 1 nonkey attribute or

many in many-one/many-many relationship if 0 nonkey attributes

combine relations

if for many-one relation, E is many

do not convert

weak entity relationship without attributes

weak entity set conversion

gets the keys from reliant entity set

objected oriented method

one relation per subset of subclasses, including all attributes

er style

one relationship for each subclass, containing

• key attributes

• attributes of the specific subclass

use nulls

one relation

NULL is placed in attribute locations where the subclass fields do not exist

either side

ER to RD conversion

where is the key for one-one relationship

many side

ER to RD conversion

where is the key for many-one relationship

both sides

ER to RD conversion

where is the key for many-many relationship

many-many

—

many-one

—>

one-many

←-

one-one

←→

to exactly one

—-)

tuples

the individual records in the table

domains

types for attributes

sql

data definition language for declaring DB schema

data manipulation language for querying and modifying the DB

case insensitive outside of string literals

tables, views, temporary tables

3 sql relations

tables

relations stored in the DB

views

relations generated by computation in the DB

not stored

constructed when needed

temporary tables

relations generated by the sql processor and not the user

made and disposed as needed by the DBMS

YYYY-MM-DD

date format

HH:MM:SS.S

time format

primary key

attribute unique and not NULL

unique

attribute unique except for NULL

execution engine

take an optimized query plan from the query optimizer and physically carry out the steps to process data and return the final results

index, file and record requests

issues a sequence of request for small pieces of data to a resource manager

index/file/record manager

locate data and page commands

manage, organize, and provide fast access to data stored on physical disk blocks

buffer manager

read/write pages

to minimize slow disk input/output (I/O) operations by caching frequently accessed data pages in the main memory (RAM)

database manager path

data-definition language

DDL commands parsed by DDL processor and passed to execution engine

index/file/record manager to alter the metadata

user/application path

data-manipulation language

query compiler- query parsed + optimized

query plan passed to execution engine

execution engine issues requests for data to resource manager

requests passed to buffer manager to get data from secondary storage to main buffer

buffer manager asks storage manager to get data from disk

data definition language

create

alter

drop

• deal with schema

• from database manager

data manipulation language

1. query (select, from, where)

2. modification (insert, update, delete)

• extract/modify data

transaction processing

1. logging

2. concurrency

storage manager

controls placement of data on disk

controls data movement between disk and main memory

buffer manager

partitions available main memory into buffers

execution engine

execute each step of query plan

conceptual level

defines what data is stored and how its related

physical level

how its physically stored

selection

picking rows (where)

projection

picking columns (select)

product and joins

comparison of relations

create table

create a new relation

insert into <table> values

insert values into the new relation

alter table <table> rename column <old> to <new

alter the name of the second attribute in the R relation

alter table <table> add <column> int default 0

alter the R relation and add a new column then update it with a constant value

select from where phone like “352%”

write a query to find the phone numbers of people in the database with a 352 area code

select from where address like “%Main Street%”

write a query to find people living on Main Street even if you dont know the building number