Please enable JavaScript.
Coggle requires JavaScript to display documents.
COMP 421 Database Systems - Coggle Diagram
COMP 421 Database Systems
Internal structure of database
Indexing
Different cost of execution in ordered/unordered files
Indexing: build an index for any attribute that is frequently used in queries
B+ Tree
Query Evaluation
The cost (# of pages) of each query
Pipelining
Block nested loop join (and other joins)
Disk & Buffer
Heap File (unordered)
Sorted File
A File is: a collection of pages
Relational Database & SQL
DDL (data definition language)
Create table
Alter table
DML (.. manipulation ..)
Insert, delete, update
Query
Relation
Schema (set of attributes and their types)
Instance (set of tuples)
SQL SELECT :explode:
Implement aggregation using GROUP BY and HAVING
CREATE VIEW, WITH clause, and Derived Table
Implement JOIN using WHERE a.uid = b.uid
NULL values
Relational Algebra
Projection: project to a subset of attributes
Join: combination of cross product and selection
Selection: select a subset of tuples
Intersection, Union, Difference
Integrity Constraints
FOREIGN KEY constraint
Domain constraint: data type, not null
PRIMARY KEY constraint
ADD CONSTRAINT ratage CHECK (rating<=5 AND age>5)
JDBC
Connection Pooling
Allow SQL command to be called from within a host language. Because SQL language cannot do complex calculation
Entity Relationship (ER) Model
ISA ("is a ") hierarchies
Relationship
Participation constraint
Ternary Relationship
Key constraint
Weak entities (use another entity's primary key)
Entity sets
Advanced Topics
Transaction & Concurrency
Atomicity: log system
Isolation: Strict 2 phase locking
Graph DB
Neo4j software
Cypher
Big Data & Map-Reduce
map - shuffle - reduce
Pig Latin Language