Final Exam Review

CSCI 311
Spring, 2020

Comprehensive part:

The Relational Model

• definitions and terminology
• keys
• superkey
• key (candidate key)
• primary key
• secondary key
• normalization theory
• functional dependencies
• normal forms
• first, second, and third normal forms
  
• BCNF
• fourth normal form
• normalization procedure
• the chase test

Relational Algebra

• basic operations
• set operations (union, intersection, difference)
• selection
• projection
• product
• natural join
• theta join
• renaming
• writing queries
• expressing constraints with relational algebra
• bags
• extensions
• duplicate removal
  
• extended projection
• aggregation
• grouping
• sorting
  
• outerjoin

Database design with the Entity-Relationship model

• E-R diagrams
• entities
• attributes
• relationships
• multiplicity:  1-1, 1-many, many-many
  
• is-a
• recursive
  
• constraints
• multiplicity of relationships
  
• keys
  
• domain
• referential integrity
• weak entity sets
• converting an E-R diagram to a relational schema

SQL

• select-from-where
• subqueries
• grouping and aggregation
  
• insert, delete, update
• data definition
• tables
• constraints
• indexes
• views

Since the midterm:

Procedural database programming

• SQL/PSM
• cursors
• triggers
• JDBC
• ResultSet class
• PHP and web access
  

Alternative data models

• Object-oriented databases
• ODL
• OQL
  
• Multimedia databases (blobs and clobs)
• Semi-structured data and XML
• XML syntax
  
• Data definition with DTDs
  
• Query languages
  
• XPath
• XQuery
• NoSQL databases
• redis
• mongoDB
  

Query processing and optimization

• Storage management
• Indexes
• dense vs. sparse
• primary vs. secondary
• unique vs. nonunique
  
• data structures
  
• B-trees
• Hash tables
• Bit maps
• Algorithms for relational algebra operations
• one pass
• two pass
• sort-based
• hash-based
• index-based
• Query optimization

System-level aspects of DBMSs

• Transactions
• Authorization -- granting and revoking privileges
• Data dictionary