CS 4433 Database Systems Midterm Review Midterm Exam

CS 4433 Database Systems Midterm Review

Midterm Exam ■ Time: Monday 03/06/2020 1: 30 pm --- 2: 20 pm – Plan your time well ■ Venue: in-class exam ■ Closed book, closed note, but you can bring a one-page cheat sheet (A 4, double side) – Plan your strategy well ■ No calculators or other electronic devices – Laptops, ipads, smart phones, etc. are prohibited ■ Any form of cheating on the examination will result in a zero grade, and will be reported to the university 2

Midterm Exam ■ What will be covered? – From “Introduction” to “Relational design” – Materials in both the slides and the textbook ■ How to do well in the midterm exam? – Review the materials carefully and make sure you understand them – Reexamine the homework and make sure you can work out the solutions independently – Discuss with your peer students – Discuss with the TA and me – Relax 3

ER Model: Nothing but Drawing ■ Entities – Attributes ■ Relationships – One-one, one-many, many-many ■ Subclasses – ISA relation ■ Constraints – Keys, referential integrity, etc. ■ Weak Entity Set: Why & How? 4

Relational Model: A Step Further ■ Relation Schema ■ Primary key, Foreign key, Unique, NULL, Default ■ Translating ER to Relations – Entities – Relationships (many-many, many-one, one-one) – Weak entity set – Subclass 5

Relational Algebra: Let’s Do Math ■ Operators and operands – Set-based operators: intersection, subtraction, union… – Other basic operators: selection, projection, . . ■ Cartesian product, theta-join, equi-join, natural join ■ Relational algebra expressions and expression trees – How to answer queries using relational algebra? 6

Relational Design: Design it right ■ Motivation ■ Functional dependencies & keys – Armstrong axioms – Closures ■ Normal forms and decomposition algorithms – BCNF ■ Lossless or lossy? – 3 NF ■ Functional-dependency preserving or not? 7

Exam Questions ■ 1 (20)- ER diagram – Create an ER diagram for given database ■ 2 -(10) Relational Model – Convert ER model in question 1 to Relational Model-Write Schemas ■ 3 - (20) Relational Algebra – Write the given queries with Relational algebra ■ 4(20)- Relational Design – Check the given database with functional dependencies ■ Find closure set, ■ find keys, ■ check BCNF/3 NF, ■ decompose if necessary ■ 5(30) T/F questions (10) ■ 6 -(20) extra