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}