Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Databases Cheat Sheet (DRAFT) by

cis 450

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Basic Info

Collection of data elements stored in a computer in a systematic way
Reliable storage and recovery of data
Support for concurrent users
Programmer shouldn’t need to know how its stored
Only needs to interact with logical model
Building a database
Start with conceptual model-> Build schemas from this -> Write applic­ations using DBMS -> DBMS takes care of query optimi­zation

SQL

Null rules:
If all are null then its a null result
If only some are null then they are discarded
For count, nulls are counted

Normal Forms

BCNF: For every FD, X->A, A is in X, or X is a superkey for R
Sometimes not dependency preserving
3NF: For every FD, X->A, A is in X, or X is a superkey, or A is a member of some key
1NF: Each entry is a discrete value
3NF decomp­osition guarantees both lossless and dependency preser­vation

Transa­ctions

 
 

E-R Diagrams

Attributes are only connected to entities and relati­onships
Keys are attributes that uniquely identify entries
Number of columns in a relati­onship is the arity
Domain is where the values of an attribute: string, int, etc..
Candidate key: May have several
Primary key: The one key that is chosen to represent a tuple
Super key: A set of fields that include a key
Foreign key: Set of fields in one table that refer to the key in another relation
Entries can only be connected by relati­onships
Arrow is placed from many to 1 direction
Weak entities: Can only be identified by the primary key of another entity

Functional Depend­encies

Saying one attribute determines another
Armstr­ong’s axioms
Reflex­ivity, if Y is in X, X->Y
Augmen­tation, if X->Y, WX->WY
Transi­tivity, if X->Y and Y->Z, X->Z
Extra’s to armstr­ong’s axioms (can be deduced from the axioms)
Union, if X->Y and X->Z, X->YZ
Psuedo­-tr­ans­iti­vity, if X->Y and WY->Z, XW->Z
Decomp­osi­tion, if X->Y and Z is in Y, X->Z
Closure of F: Set of all FD’s derivable from F
Two FD’s are equivalent if their closures are equivalent
Minimal cover, F is minimal if
Every FD in F is of the form X->A where A is a single attribute
For no X->A, is F - {X->A} equivalent to F
For no X->A and subset Z of X is (F - {X->A}) U {X->Z} equivalent to F