Final Exam Review Fall 2014 CS 6360 Database Design Chris Irwin Davis Ph D Email chrisirwindavis utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Chapter 1 Nothing 2 Chapter 2 Nada 3 Chapter 3 Nichts 4 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 Join 5 Ch 4 5 SQL NOT COVERED UPDATE DELETE VIEW Admin functions DESCRIBE MODIFY ALTER DROP 6 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 Calculus 7 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 relationship 8 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 1 PARENT E1 E1 CHILD 2 N R 0 n 1 2 R 11 E2 E2 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 attributes 12 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 multivalued i e nested relations 1NF violations are based on violations of data 13 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 BCNF 14 Normalization Example 15 Normalization Example 16 Ch 15 Functional Dependencies and Normalization 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 17 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 Binary 19 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 Properties 20 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 Durability 23 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 T2 24