Exam — Final

 

· Chapter 1 — Introduction

· 1.2 Purpose of a Database System

· 1.3 View of Data

· Chapter 2 — Relational Model

· Relational Model—Keys

· Chapters 3 & 4 — ALL Sections

· SQL

· DDL

· CREATE Tables & Views

· ALTER

· DROP

· DML

· Insert, Update, Delete

· SELECT statement

· Including GROUP BY ... HAVING ...

· Joins — ALL kinds

· Functions—scalar and aggregate

· Inner SELECTs

· Chapter 5  - NOT ON THE EXAM

· Chapter 6  - NOT ON THE EXAM

· Chapter 7 — ER Diagrams

· Entity Sets

· Attributes

· Primary Keys

· Relationship Sets

· Roles, Participation, Cardinality

· Weak Entity Sets

· Discriminators, Identifying Entity Sets

· ER Reduction to relational schemas

 

· Chapter 8—Database Design & Normalization

· Armstrong's Axioms & Extensions

· Closure of attributes

· Canonical cover

· 3NF

· BCNF

· Lossless joins

· Dependency preservation

· Find all candidate keys

 

· Chapter 9 — NOT ON THE EXAM

 

· CHAP 10 Storage         

· Storage

     By what 3 characteristics is it organized?

Hard drive

Anatomy

Access time

Concepts

Parallelism & Striping

Redundancy & Mirroring

RAID

     2 levels that are used, their characteristics, and what kinds of apps they are used with

· File Organization

          Heap /  Sequential    / Hashing

          How are records stored physically on disk for each organization

          Algorithms for inserting / deleting records

· Record organization

          fixed length records

          variable length records structure

               slotted page structure

                    insertion / deletion algorithms

 

· CHAP 11 Indexing and Hashing

· Differences between clustering and non-clustering indexes

*    What are they and what are they used for ?

· Indexed-sequential file

*    What are the problems with this file organization ?

· Dense vs Sparse indexes

*    When MUST an index be dense ?

· B+ TREES

*    What are the primary advantages ?

*    What does “balanced” mean ?

RULES

*    Key values

*    For leaf nodes

*    For internal nodes

*    For the root node

ALGORITHMS

*    Traversing the tree

*    Insertion / deletion

*    Splitting / merging of nodes

· HASHING

     hash index

     hash file

     2 characteristics of a good hash function

 

· CHAP 12 - Query Processing

· Formulas for join algorithms

     Know how to use them (do not memorize)

 

CHAP 14 Transactions

· ACID - definitions for each term

· What is a schedule?

· What is a serial schedule?

· What is conflict serializable?

· What causes conflicts in a schedule?

· Precedence graphs

· COMMIT / ROLLBACK