!" #Chris Irwin Davis, Ph.D. !Email: [email protected] Phone: (972) 883-3574 Office: ECSS 4.705Final Exam Review (Fall 2014)CS-6360 Database Design1!" #Chapter 12Nothing2!" #Chapter 23Nada3!" #Chapter 34Nichts4!" #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 Join55!" #Ch. 4 & 5: SQL■ NOT COVERED ■UPDATE, DELETE ■VIEW ■Admin functions: DESCRIBE, MODIFY, ALTER, DROP66!" #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 Calculus77!" #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 relationship88910!" #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 CHILDE2RR11!" #Chapter 9 - ER/EER Mapping to Relational Model■ Map ER/EER diagram onto relation schema using 7-step algorithm ■ NOTE!! These two mapping 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 attributes1212!" #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 data1313!" #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. ■ BCNF1414!" #Normalization Example1515!" #Normalization Example1616!" #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.1517!" #Ch. 17: Disk Storage and Hashing■ Type: Multiple Choice, Multiple Answer, Matching, T/F ■ 17.1 – Intro ■ 17.2 – Secondary Storage Devices ■ 17.3 – Buffering of Blocks ■ 17.4 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)1818!" #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 ⟺ Binary1919!" #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 Indexes2020!" #Ch. 18: Indexing Structures for Files■ Dynamic Multilevel Indexes: B-Trees and B+-Trees ■ Know Differences ■ Know Properties ■ Be able to answer Multiple Choice, T/F, etc. about both ■ B+-Trees ■ Be familiar with the settings used at the online link and be able to duplicate insert and delete behavior ■ https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html 2121!" #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)2222!" #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.2323!" #Chapter 19: Cost-based Optimization■ Given table meta-data, be able to calculate Cost-based query optimization. ■ Selectivity Cost S1 through S8 ■ Join Cost J1, J2, and J3 ■ Formulas will be provided, however ■Meaning of variables used in formulas will not be provided ■Which formula to use for a scenario will not be given2424!" #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 ■ Durability2525!" #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
View Full Document
Unlocking...