kind-of-a-syllabus.md (3661B)
1 +++ 2 title = "Kind of a syllabus" 3 +++ 4 5 # Kind of a syllabus 6 7 Here\'s what you should be able to do for each chapter (directly from 8 slides). 9 10 Data modelling 11 12 - three phases of database design: conceptual, logical, physical 13 - basic E/R concepts 14 - entities, attributes, relationships, \'is a\', weak entity sets, 15 aggregation 16 - cardinality/participation constraints 17 - how UML corresponds with and differs from E/R 18 - differences: basic syntax, aggregation, key specifications 19 - how to make a conceptual model given a scenario (UML and ER) 20 21 Conceptual to relational model 22 23 - how to translate a conceptual model to a relational model 24 - identifying keys 25 - internal/external keys 26 - (foreign) key constraints 27 - multi-valued attributes 28 - weak entity sets vs. composition 29 - \'is a\' 30 - representing cardinalities 31 - recursive relationships 32 - optimisation: removing relationship tables 33 34 Relational model: 35 36 - explain concepts of relational model: 37 - schemas, state, domains 38 - read various notations for relational schema 39 - explain applications and problems of null values 40 - explain integrity constraints and their importance 41 - explain meaning of keys and foreign keys 42 - develop simple relational schemas 43 44 SQL: 45 46 - write advanced SQL queries (nested, multiple variables) 47 - aggregation, grouping, union 48 - be comfortable with various join variants 49 - evaluated correctness and equivalence of SQL queries (including 50 possible presence of duplicate result tuples) 51 52 Relational normal forms: 53 54 - work with functional dependencies: 55 - define what they are 56 - detect them in database schemas 57 - decide implication, determine keys 58 - explain insert, update, delete anomalies 59 - understand, explain, and use BCNF 60 - test a given relation for BCNF 61 - transform a relation into BCNF 62 - understand, explain, and use 3NF 63 - test a given relation for 3NF 64 - transform a relation into 3NF 65 - understand, explain MVDs and 4NF 66 - detect normal form violations on the level of ER 67 - explain when and how to denormalise a database schema 68 - probably on exam: 69 - compute cover {attribute}+ \-- needed everywhere 70 - determine all minimal keys 71 - find \'canonical\' (minimal) set of FDs: 72 - use for BCNF decomposition \-- splitting 73 - 3NF synthesis algorithm 74 - definitions of 4NF and multivalued FDs 75 76 Transactions: 77 78 - ACID properties, transactions 79 - anomalies: lost update, dirty read, unrepeatable read, phantoms 80 - transaction schedules, serializability, conflicts (rw/wr/ww) 81 - conflict equivalent, conflict serializability 82 - lock based concurrency control: 2 PL (strict, preclaiming) 83 - cascading rollbacks, deadlocks, deadlock detection 84 - cascadeless, recoverable 85 - granularity of locking, intention locks 86 - SQL isolation levels: `READ UNCOMMITTED`, `READ COMMITTED`, 87 `READ STBILITY`, `SERIALIZABLE` 88 - optimistic concurrency approach 89 - multiversion concurrency control, snapshot isolation 90 91 Database APIs: 92 93 - explain problem of impedance mismatch 94 - be able to classify database app interfaces: static, dynamic, 95 object-relational mapping 96 - discuss advantages/disadvantages of API in terms of object 97 navigation and complex query execution 98 - understand object-relational mappings: Hibernate for Java, entity 99 framework for .NET (but you don\'t have to program on the exam) 100 - relate these to the ANSI SPARC 3-layer model and concepts of 101 logical/physical data independence 102 - explain advantages of LinQ and how it relates to impedance mismatch