!" #Chris Irwin Davis, Ph.D. !Email: [email protected] Phone: (972) 883-3574 Office: ECSS 4.705Final Exam Review (Fall 2014)CS-6360 Database Design!" #Chapter 12Nothing!" #Chapter 23Nada!" #Chapter 34Nichts!" #Ch. 4 & 5: SQL ■ Type: Short answer, Multiple Choice, True/False ■Required Syntax: SELECT FROM ■ Optional Syntax ■WHERE ■GROUP BY (after WHERE) ■HAVING (after GROUP BY) ■Aggregate Functions: COUNT, SUM, MIN, MAX, AVG ■Aggregate functions never appear in WHERE clause ■JOIN: Natural Join, Inner Join, Outer Join5!" #Ch. 4 & 5: SQL■ NOT COVERED ■UPDATE, DELETE ■VIEW ■Admin functions: DESCRIBE, MODIFY, ALTER, DROP6!" #Ch. 6: Relational Algebra■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ Relational Algebra (Set functions) !■ NOT Covered ■ Relational Calculus (Boolean ops and Quantifiers) ■ Tuple Relational Calculus ■ Domain Relational Calculus7!" #Ch. 7 & 8: ER & EER Models■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ Answer questions about ER/EER diagram ■Participation and Cardinality ⟺ (min, max) ■ Participation and Cardinality – property of an entity ■ (min, max) – range of an entity’s interaction with a relationship □A (min, max) pair is required for each side of a relationship8!" #Cardinality, Participation and (min,max)■ Cardinality + Participation notation is mutually exclusive with (min, max) notation ■ Cardinality ≈ max and Participation ≈ min ■ If there is one number on each side, then it is Cardinality (i.e. max) ■ Participation has only two options □single line: min = 0 □double line: min = 111E2E1N2E1(0,n) (1,2)PARENT CHILDE2RR!" #Chapter 9 - ER/EER Mapping to Relational Model■ Map ER/EER diagram onto relation schema using 7-step algorithm ■ NOTE!! These two steps are commonly missed ■ ER Step 7: Mapping of N-ary Relationship Types (p.292) ■ EER Step 8: Four different options □Superclass and subclasses □Subclass relations only □Single relation (superclass) with one type attribute □Single relation (superclass) with multiple type attributes12!" #Ch. 15: Functional Dependencies and Normalization■ 1NF – The only attribute values permitted by 1NF are single atomic (or indivisible) values. That is, no attribute for a given tuple is multi-valued, i.e. “nested relations” ■ 1NF violations are based on violations of data13!" #Ch. 15: Functional Dependencies and Normalization■ Normal Forms Based on Primary Keys (Based on Schema) ■ 2NF – A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R ■ 3NF – Relation should not have a non-key attribute functionally determined by another non-key attribute (or by a set of non-key attributes). That is, there should be no transitive dependency of a non- key attribute on the primary key. ■ BCNF14!" #Normalization Example15!" #Normalization Example16!" #Ch. 15: Functional Dependencies and Normalization17■ Like 1NF, 4NF and 5NF normal forms are based on data ■ If proper Chapter 9 schema design principles are observed, 4NF and 5NF violations will not occur ■ In practice, schemas evolve over time ■ Later DBAs may not have access to original design requirements ■ 15.6 – Multivalued Dependency and 4NF ■ Figure 15.15 ■ 15.7 – Join Dependencies and 5NF ■ Figure 15.15!" #Ch. 17: Disk Storage and Hashing■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ 17.1 – Intro ■ 17.2 – Secondary Storage Devices ■ 17.3 – Placing File Records on Disk ■ 17.5 – Operations on files ■ 17.6 – Files of Unordered Records (Heap Files) ■ 17.7 – Files of Ordered Records (Sorted Files)18!" #Ch. 17: Disk Storage and Hashing■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ 17.8 Hashing Techniques ■ Extendible hashing (p.612-614) ■ Dynamic hashing (p. 614) ■ Linear hashing (p. 614-616) ■ 17.9 – Other Primary File Organizations ■ 17.10 – RAID ■ Types of RAID ■Parity: Hex ⟺ Binary19!" #Ch. 18: Indexing Structures for Files■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ Single-level Ordered Indexes ■ Primary Indexes ■ Clustering Indexes ■ Secondary Indexes ■ Multilevel Indexes ■ Dynamic Multilevel Indexes: B-Trees and B+-Trees ■ Know Differences ■ Know Properties20!" #Chapter 19: Query Optimization■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ Selection ■ Projection ■ Query Optimization (Problems) ■ 19.7 - Heuristic Optimization (schema-based) (starting p.704) ■ Cost-based Optimization (data-based)21!" #Chapter 19: Heuristic Optimization■ Steps in converting a query tree during heuristic optimization. ■ (a) Initial (canonical) query tree for SQL query Q. ■ (b) Moving SELECT operations down the query tree. ■ (c) Applying the more restrictive SELECT operation first. ■ (d) Replacing CARTESIAN PRODUCT and SELECT with JOIN operations. ■ (e) Moving PROJECT operations down the query tree.22!" #Ch. 21:!Transaction Processing■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ 21.1 – Introduction to Transaction Processing ■ 21.1.3 – Four Potential Concurrency Issues ■ 21.1.4 – Six Types of Failures (pp. 750-1) ■ 21.2 Transaction and System Concepts ■ 21.3 Desirable Properties of Transactions (ACID) ■ Atomicity ■ Consistency Preservation ■ Isolation ■ Durability23!" #Ch. 21:!Transaction Processing■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ 21.4 – Characterizing Schedules Based on Recoverability ■ 21.5 – Characterizing Schedules Based on Serializability ■ Algorithm 21.1 (Problems involving Serializability graphs for transactions) ■ Serializable? Show ALL equivalent serial schedules, e.g. □T1 → T4 → T3 □T1 → T3 → T4 ■ Non-serializable? Show ALL cycles □X(T1 → T2), Y, Z(T2 → T1) □Y(T2 → T3); Z(T3 → T4); , X,Z(T4 →
View Full Document