CS 474
Things to know for the exam:
Components of DBMS
Users of DBMS
Everything on slides
Everything on Webpage
Everything in textbook chapters covered: Chapters 1-9, 11 and appendix E
Review homeworks and quizzes and midterm exam
Focus of course is DESIGN
What is designed are tables
determinants
Normalization
First
second
third
fourth
boyce-codd normal forms
primary keys
foreign keys
Design is aided by
Entity relationship models
ER-Win Diagrams
Semantic Object Models
Different Object Types
Simple, composite, compound, hybrid
Cardinalities
Minimum and maximum to the right of the attributes
Attributes
Simple
group attributes
Establish relationships by dragging one object into another
Easy to convert to SQL code to create Database
Information retrieved from databases is based on Relational Algebra theory
Operations
Select
Project
Join
Cartesion Product (Multiplication)
Intersect
Difference
Bold: requires union compatibility
SQL is the standard language used to communicate with DBMS
SQL statements
- query database - retrieving
- update database data
- modify
- insert
- delete
- add constraints
- create database components/table
- update database structure
- create/execute:
- procedures
- triggers
- indexes
- views
Multi-User Databases
transaction properties : ACID
Atomicity
Consistency
Isolation
Durability
transaction logs
what they contain
how they are used
Steps and notation used for transaction
logs
<T starts>
<T aborts>
<T commits>
<T, X, 10> : <TransactionName, Variable, Value>
concurrency problems
Lost Updates
Uncommitted
update problem-
Inconsistent
analysis problem-
Non-repeatabLe reads
phantom reads
locks
shared
exclusive
granularity
two-phase locking
growing phase
shrinking phase
serializability – of transactions