SQL Engine concepts

Physical and Logical layer

Query Execution plans $$ exact path the engine takes to step through creating new sub$-$relationships on its way to reaching a final result

ACID $$ Atomic, Consistent, Isolated & Durable

Relational Algebra

Projection IIs$($E$1)$ include only certain columns

Name of relation it relates to if duplicate attribute names

Can include calculations

Selection $\backslash$sigma c$($E$1)$ keep only certain rows

$^$ to have multiple selections

Joining relations

Natural join vs specifying the key

How to figure out which relations you need

Relation vs relation instance

Every relational algebra query needs a relation and can also have a projection and$/$or selection

Assume every result is a set with no duplicates

Rename with p newname$($newcol$1,$ newcol$2)($tablename$)-$ so newname is the name of tablename for the query. It is assuming there are only $2$ columns which also have new names newcol$1,$ newcol$2$

E$1/$ E$2$ : division

attribute$1$g function $($attribute$2)($E$1)$ : perform function on attribute$2$ whenever attribute$1$ changes

E$1$ U E$2$ : union

E$1-$ E$2$ : difference

E$1$ x E$2$ : cartesian product

Keys

Candidate Key $$ minimal combination of fields to uniquely identify a row of data

Primary key $$ one chosen candidate key to tell the database to use to uniquely identify a row of data. An index will be built on this key by the SQL engine.

Defined in the database

Super Key $$ Candidate key plus extra fields, which can uniquely identify a row

Foreign key $$ one or more fields pointing to another table $$ and matching up to its primary key

Defined in the database

E$-$R Diagrams

Entity is a thing or object $$ rectangle

Weak entity $$ cannot exist without a parent and uses the parent key as part of its key

Attribute is a field $$ either written inside rectangle under entity name OR in circles

Underline primary keys

Relationship is the association among entities $$ and can have its own attributes

Sometimes relationship is a diamond on a line

Can relate to self

Map cardinality

Always read entity name skip to end of line, interpret connection on the other entity and then read the other entity name

Exception: For the minimum, if using double line to show $$must have$,$ read entity name, then for closest double line say $$must have$$ and not double $$can have$$ and then follow the line to the related entity and say either $$one$$ for an arrow or $$many$$ for no arrow, and then the entity name.

Maximum : $1-1$; $1-$N; M$-$M;

Drawing choices

arrow $=1$ and no arrow $=$ M

Crows feet: $3$ prong $=$ many; $1$ cross $=1$

N$,$ M$,*=$ many; $1=$ only $1$; a number if some limit

Minimum : $0/1$

Drawing choices

Double line between entity and relationship $=$ that entity must have at least $1$ of the other related entity. Single lien means it can have.

Crows feet: cross before the line that touches the related object $=1$; $0$ before the line that touches the related object $=0$

N$,$ M$,*=$ many; $1=$ only $1$; $0=$ not required

Translating ER Diagram to tables

One table per entity

Determine key, hopefully underlined on ER$.$ Weak entities get parent$$s key

If $1$ required minimum, make the column mandatory

If $1$ to N$,$ add entity$$s key to the N side and make it a foreign key

If many to many, add a new association table with the key of both entities as the primary

If $1$:$1,$ put key field in either entity but not both

Visualize sample data and how it will work the new tables.

Functional Dependency

FD definition: X$->$ Y means Y$$s value is determined by X$.$ X is a determinant and Y is a dependent.

Functional Closure:

All the fields that can be determined by the key you are closing, such as AE$+.$

Close by working through functional dependency rules $$ if I have AE$,$ I get what, and then if I have these others new fields as well, what can I get next, until you get all fields $($close$)$ or are stopped $($partial$)$

Full functional closure of minimal combination of fields $=$ candidate key. EX if these are candidate keys: A$,$ B$,$ DE$,$ then AE and BE and DEF are all super keys, containing a candidate key.

Prime field $$ might be part of a candidate key because they determine something

Non$-$Prime fields $$ determinants only and cannot be part of a candidate key

Converting functional dependency rules:

Reflexive: A determinant always determines itself, including any subsets of itself. EX: A$->$ A ; AB$->$A$,$ AB$->$B

Transitive: You can chain dependencies: EX: A$->$B & B$->$C then A$->$C transitively

Decomposition: You can say a functional dependency of two elements means each of the elements is individually dependent. EX: A$->$BC then A$->$B & A$->$C

Trivial functional dependency: The dependent is a subset of the determinant, like the reflexive property. EX: A$->$ A ; AB$->$A$,$ AB$->$B

How to Mine for FD given a table of data:

When you are given a table with data, figure out which columns could possibly be dependent upon another column based on the data in the table.

For example if row $1$ had column a $=10$ and column b $=30$ while row$2$ had column a $=10$ and column b $=20,$ then you know that a$->$ b is not possible because $10$ cannot det