RDBMS Must Know

Concepts

key

• 1 attribute (col)
• more than 1 attributes (col)
  • = composite/compound key

composite/compound key

• a key that has more than 1 attribute

super key

• set of 1 or more attribute which can uniquely identify a row
• sets has redundant attributes
• sets don’t have redundant attributes
  • = candidate key

candidate key

• a super key w no redundant attribute
• primary key * 1 + alternate key * n

primary key

• determined by DBA (DB Admin)
• is a candidate key
• unique identifier of rows
• no duplicate values
• not NULL
• can be 1 or more attributes

alternate key

• all non-primary candidate keys

foreign key

• cross-reference between tables
• points to (same attribute name) primary/candidate keys of another table

Indexing

• Primary Index
  • Dense Index
    • 1 key : 1 row
  • Sparse Index
    • 1 key : n rows
• Secondary Index
  • 1 1st key : n 2nd key
  • 1 2nd key : n rows
• Multilevel Index
  • 2nd index -> nth indexes
• Clustering Index
• B+ Tree Index

Dense Index

Sparse Index

Multilevel Index

Clustering Index

Comparison

B+ Tree Index

• fast search
  • tree height is less than BST, less hard disk random access operation
  • each level sibling nodes are linked
    

DBMS Architecture

3-tier architecture

• User/Presentation Tier (client)
• App/Middle Tier (server)
• Database/Data Tier (DB)

DBMS Data Model

• Entity-Relationship Model (ER Diagram)
  • DBMS Generalization
  • DBMS Specialization
• Relational Model

DBMS Schema/Data Abstraction

• Physical
  • DB actual hardware storage schema
• Logical
  • DB schema, i.e. table schema
• View
  • DBMS UI

Joins

Transaction & Concurrency