Review #1
Intro stuff
– What is a database, 4 parts, 3 users, etc.
Architecture
– Data independence
– Three levels, two mappings
– Jobs of the DBA
1
Review #1
Entity Relationship Model
– Entities, Relationships, E-R Diagram
– Relationship types
– Conversion to a set of tables.
– GRADUATE STUDENTS: Extended ER
Features, such as multivalued attributes, total
participation, cardinality limits, etc.
Relational Model (Informal)
– Primary and Foreign Keys
– Ability to add additional constraints
2
Review #1
ORACLE/SQL:
– Creating tables
– Inserting, deleting, updating (Lab 1)
– Querying
• Simple queries
• Joins
• Non-Relational Queries – Group By, etc.
• (Lab 2)
Something from the text.
3
Sample Question:
(5 pts) What is a database?
4
Sample (Bad) Answer #1:
(5 pts) What is a database?
– A set of tables.
5
Sample (Bad) Answer #2:
(5 pts) What is a database?
– A database is a collection of related data.
A Relational Database is one that stores
this data in a set of tables. Example 1: A
banks stores customer information, their
accounts and transactions. Example 2: A
university stores information about its
students, courses, and registration
information. Databases consist of data,
hardware, software, and end users. The
data is called persistent data; the software
is called the DataBase Management
System. There are 3 types of users: end
users, application programmers, and
database administrators.
6
Sample (Bad) Answer #2 (cont):
A database is designed in 3 levels: internal,
conceptual, and external. To do the conceptual
level design, we use the Entity Relationship Model.
This requires us to decide upon our entities,
relationships, draw an E-R Diagram, decide on the
type of relationships, and then develop a set of
tables from this completed diagram. There are 3
types of relationships: 1 to1, 1 to many, and many
to many. The external level is what the individual
users are permitted to see; the internal level
consists of the file structures, which are B-Trees,
Clustered B-Trees, Hashing, and Clustered Files.
7
Sample (Good) Answer #3:
(5 pts) What is a database?
Informally, a database is a collection of related
data. The most common model is the Relational
model, which groups the data into tables with
linkages (known as Foreign Keys) to model the
relationships.
8
Review #2
Relational Model
– Definitions
– Properties of relations
– Keys -- Candidate, Primary, Alternate, Foreign
– Integrity Constraints.
Relational Algebra
– Definitions
– English Relational Algebra Tables
– Optimization
Relational Calculus
– Definitions
– English Relational Calculus Tables
9
Review #2
Internal Level design/ External Level design
– File Structures and Analysis
– GRADUATE STUDENTS: Advanced File
Structures
GRADUATE STUDENTS: Extended Relational
Algebra
– English Extended RA
ORACLE/SQL
– Views (need to review Querying for this)
– Privileges
– Indexes
– Advanced CREATE TABLE options
– LAB 3
10
Final Review
(updated for Spring 2011)
Intro Stuff
Architecture
– Three levels, two mappings
– File Structures
E-R Model Design
Relational Model
– Keys, Integrity constraints, etc.
Query Optimization Using Relational Algebra.
Relational Algebra or Relational Calculus Queries
– GRADUATE STUDENTS – Know BOTH.
11
Final Review 2
GRADUATE STUDENTS:
– Extended Relational Algebra
Conversion from RC to RA (not on final)
Functional Dependencies
– Definitions, Axioms
– Spot them
– Proof of Candidate Keys
Normal Forms, 1NF
– Definition
– Spot violations
– Normalization
12
Final Review 3
Normal Forms, 2NF, 3NF, BCNF
– Definitions -- Formal and Informal
– Spot violations
– Normalization through decomposition
Multi-Value Dependencies
– Definitions, Axioms
– Find them, using quick & dirty method and t1,
t2, t3 method.
4NF
– Definition, find violations, normalization
GRADUATE STUDENTS:
– Join dependencies and 5NF
13
Final Review 4
Hierarchical Model – (Not on FINAL)
– Definition, Advantages, Disadvantages
– Type, Occurrence Trees, Virtual Links
– Design
Transaction Analysis
– Locks, Commit & Rollback
– Buffers, Checkpoints, Transaction Logs
– Recovery Algorithm
Oracle/SQL
– Labs 1, 2, 3
– Lab 4 – Code SEGMENT only
14