CMSC 461 Midterm

Relational Query Language

For request information from the database

Reflexivity

If A is a set of attributes and B is a subset of A, then A holds B. { A-> B }

Sequential

Store records in sequential order

Aggregate function

avg - average min - minimum max - maximum sum - sum of values count - number of values select avg(salary)...from..where - Group by (dept_name)

canonical Cover

basically simplify down dependency

generalization

bottom-up approach in which two lower level entities combine to form a higher level entity

attribute

column of a relation

Natural join

column with the same name associated with tables will appear once

PHP

connection = mypysql.connect()

python connection

connection = pymysql.connect(lala) with connection as cursor: cursor.execute(sql)

Relationship sets

connection the relation between two entities (binary). more is something else

Sparse index

contains index records for only some search-key values

create database

create database database_name;

create table

create table table_name(name1 type1(domain1),...);

view

create view v as <query expression> v is the view name

Dynamic Hashing

data buckets are added and removed dynamically

Inner join

join operations that do not preserve non-matched tuples

Superkeys

key to identify a unique tuples, can be more than one

Concurrency control

mechanism to achieve isolation goal is to assure serializability

Third base form

no transitive functional dependency - This mean that if A depend on B, and B depend on C, then A dpend on A via B, this is a big NO NO

wait-die

older transaction may wait for younger one to release adta item. Younger are rolled back instead.

how to solve deadlock

one of the T have to rollback and unlock

Variable length records

- Storage of multiple record types in a file - Record types that allow variable lengths of one or more fields such as strings

Purpose of Database System

- Collection of data - Organized - Relevant information to enterprise

Schema diagrams

- Schema - Primary keys - Foreign keys - arrow

Fixed length records

- Simple access - Record could cross blocks - Free Lists

Static Hashing

- A bucket is a unit of storage contain one or more records

Structure of relational database

- A collection of tables - Each table have a unique name - Each row is an attribute

Nonprocedural

- User specified what data is required without how to get the data

Procedural

- What data is required and how to get it

Integrity constraints

- a column or combination of columns which unique identifies each row in the table

participtaion constraits

- if every entity set E participates at least 1 relationship , than it is total - else it is partial

manager

- mainting a log for recovery

Validation-Based Protocol

1. Read and execution phase: Transaction T writes only to temporary local variables 2. Validation phase: Transaction perform a validation test to determine if local variables can be written without violatin serializability 3. Write Phase

Two-Phase locking protocol

1. growing phase - Transaction may obtain locks - Transaction may not release locks 2. Shrinking phase - can release not obtain

Block

A contiguos sequence of sectors from a single track

Dependency Preservation

A decomposition is dependency preserving, if (F1 ∪ F2 ∪ ... ∪ Fn)+ = F +

DDL (Data Definition Language)

A formal language for defining a database scheme - generate a set of tables stored in a data dictionary - Require key

Hashing

A hash function computed on some attribute record

DML (Data Manipulation Language)

A language for accessing and manipulating the data organized by the appropriate data - Insert - Delete - Retrieve

Relation Operation

A procedural query language based on the mathematical theory of sets that is the foundation of commercial DBMS query languages

Heap

A recod can be place anywhere in the file where there is space

Timeout

A transaction waits for a lock only for a specified mount of time. After it is rolled back

Outer join

An extension of the join operation that avoids loss of information left - remember all left right - remember all right

ACID

Atomicity - either all transaction get executed or none Consistency - your data will be consistent isolation - one transaction cannot read data from another transaction that is not yet completed Durability - Once a transaction is complete, it is guranteed that all changes have been recorded.

Closure of Attribute sets

Basically finding all the letter that is a subset of of the original using strongman

Transaction

Commit - command used to save changes invoked by a transaction Rollback - used to undo transactoin that have not already been saved to the database

Data Abstraction

Create a simpler data form for something more complex.

Exclusive

Data item can be both read as well as written

shared lock

Data item can only be read

decompose

Employee(ID, name, street) to Employee1(ID, name) employee2(name, street)

Boyce-Codd Normal Form

Every partial key (prime attribute) can only depend on a superkey, - basically only the primarykey should be depend upon like the greedy bitch it is

Decomposition

If{A → BC} and {A → B}, then {A → C}

Union

If{A → B} and {A → C}, then {A → BC}

Pseudo Transitivity

If{A → B} and {BC → D}, then {AC → D}

Files

Individual files and folders can be accessed and manged by the storage system.

atomic

Indivisible - Can only contain one value., Cannot be divide any further

Multiversion

Keep old version of data item to increase concurrency

Candidate Key

More than one primary key essentially, primary key can be pick from one of the candidate key

wound-wait

Older transaction forces rollback of younger transaction

cardainality

One to many and shit like that

Hash indices

Search keys are distributed uniformly across "buckets"

Select

Select A1, A2, A3 from R1, R2, R3 where P A - attributes R - relation P - predicate

Rename

Select....from...(as)...where

Natural Join

Similar to inner join, except the repeated column will not be returned

Lossless

The decompositio of relation R into R1 and R2 is lossless when the join of R1 and R2 yield the same relation as in R.

Relational Model

The table thingy

lossy decomposition

When you split up into two relational and join them up in natural join

Precedence graph

a directed graph where the vertices are the transaction

tuple

a row

schedule

a sequence of instruction that specify the chronological order of which instruction transction are executed

Relation instance

a specific instance of a relation

Relation

a table

B+ Tree

advantage - automatically reorganize itself disadvantage - extra insertion and deletetion

weak entity

an entity that cannot be uniquely identified by its attributes alone. therefore must use a foreign key

non-clustering

an index who search specifies an order different from sequential

Multilevel index

where outer index leads to inner index leads to block of data

heterogeneous distributed databases

different sites have different operating systems

drop table

drop table table_name;

Keys

how tuples are distinguished

Relational Algebra

https://i.imgur.com/OW5j3XY.png - Project, union get rid of duplicate

Functional dependency trival

if A->B, then B is a subset of A example - ID, name -> ID name -> name

Lock manager

implemented as a separate process to which transaction send lock and unlock requests

Clustering

in a sequentially ordered file, the index whose search key specifies the sequential order

Dense index

index record appears on every search-key value in the file

insert

insert into table_name(att1, att2) values (v1, v2);

Thomas's Write Rule

instead of making Ti roled back, the write operation itself is ignored

Functional Dependency

relationship that exists when one attribute uniquely determines another attributes PersonID functionally determines Birthdate PersonID -> Birthdate

Ordered indiches

search keys are stored in sorted order

serialization

serial - ordered

domain

set of allowed values for each attribute

Entity set

set of entities of the same type that share the same properties "set of all people who are instructors at a given university"

ER Model

simpler version of Relational Model

coordinator

starting the execution of trsaction

First Normal Form

the domain of all attributes are atomic

update

update table_name set attribute = attribute * 5 where attribute2 > 1;

connect

use database_name;

Foreign key

value in one relation must appear in another

detecting deadlock

wait-for graph has a cycle

Transitivity

{A → B} and {B → C}, then {A → C}