CS Resources

Computer Science Resources and Links from Avi Parshan

View project on GitHub

Intro to Database Systems

ACID Transactions

Atomicity: All-or-nothing transactions. Ensures data integrity by completing everything or reverting all changes.

Consistency: Predictable data changes. Transactions maintain valid table states and prevent corruption.

Isolation: Concurrent access without conflicts. Transactions appear to happen one at a time, even when happening simultaneously.

Durability: Permanent data changes. Committed transactions survive system failures.

CRUD - Actions that the database should support

Create - add entry

Read - get entry

Update - modify entry

Delete - remove entry

Keys and entities

Weak Entities: Entities needing a foreign key (often a primary key from another table) to be uniquely identified.

Foreign Keys: Link columns between tables (e.g., table1.colA -> table2.colB).

Primary Key - The actual key for the data in the database, Non null and unique (minimal set of identifying information to still be unique)

Composite key

  • Key based on 2 (or more) items, and make up the primary key

  • Example: street name and address or name and address

  • Each independently are not unique

  • Not really a foreign key as not composite

  • Not a weak entity either as uses parent key

Arrows and quantity

  • —- n

  • —> 0 or 1

    • Filled in arrow

    • ——-) exactly once

    • Closed Arrow - Limiting factor


  • One to one

  • One to many

  • Many to many

Data models (levels)

  • Logic

  • Physical

  • Conceptual


  • Layout of relationship

  • How it is structured and represented

Relational Algebra Symbols

Type Operator Symbol Min rows Max rows (R first has n Tuples) Duplications Notes Returns  
Unary Project π pi 1, can be 0 if relation is empty n When done projection won’t give duplicate of same information Subscript of projection are some of attributes in relation’s schema, results in relation containing only the columns specified (1 field/column)  
Select σ sigma 0 n   Give me this , when Returns tuples from Relation that match the Condition, schema of output is same as schema of input    
Auxillary Rename ρ roe       break up long expressions, resolve conflicts in attribute names, same arg name order as title header returns relation with new schema but same tuples  
Binary Cartesian product X   m*n (rxt)   tuple for each pair of tuples from two input relations, # of tuples is product of # of tuples in R and in S.    
Union U max(m,n) n+m no duplicated values in end result Combine 2 tables, only works for comptible relations (with same schema), # of rows doesn’t matter Contains all tuples that are in at least one of the relations    
Difference - n-m, unless m > n then = 0 n (ie if 2nd set is empty)   Only for compatible relations (same schema) Result contains all tuples in R that are not in S    
Intersection 0 (disjoint) min(n,m)   Needs to be forcompatible relations R - (R-S) = R ∩ S returns all touples in both R and S    
Conditional Join ⋈c       Boolean condition over pairs of attributes from R and S - cartesian product with condition…. Selection of … join - CJ      
Natural Join ⋈ bowtime 0 - nothing matches n*m   condition is there automatically - if one col = same it looks at it .same schema , same columns      
Division ÷ 0 - can be empty table (no results)   Scema of 2nd relation must be subset of the first R/S Result contains tuples (a1,….,an) st for all (b1,…bm) in S, (a1,..an,b1,..,bm) is in R      
  Equivelence       For all instances of the relations in E1and E2, they return the same result    
Relational algebra SQL
set of tuples multiset of tupils
no null - no unknown values Can have null values
2 valued logic for conditions true/false (logic) unknown, true, falae
not turing complete - limited turing complete (many additional features)

Nulls and Unknowns

Truth table for unknowns

A Not A
Unknown Unknown

More conclusive - I skipped entries ie where B is true and A is false etc (it’s repetitive)

A B A and B A or B
TRUE Unknown Unknown TRUE
FALSE Unknown FALSE Unknown
Unknown Unknown Unknown Unknown

Actions with or about Nulls and return values

Action Return Example
Operating on Null Null Null+1, Null*7
Comparing with Null Unknown Null > 10, Null <> Null, Null = Null
Is Null True if Null x IS NULL
Is Not Null False if Null X IS NOT NULL

        -- INIT database
        CREATE TABLE Parents (
        Child VARCHAR(20),
        Parent VARCHAR(20)

        INSERT INTO Parents VALUES ('avraham', 'yitz');
        -- QUERY database
        SELECT * FROM Parents;

Updated on July 3, 2024