• 110 likes • 269 Views
COP4710 Database Systems. Midterm Review. Fall 2013. Midterm Exam. Time : Friday 10/18/2012 9:05am --- 9:55am Plan your time well Venue : HCB 315, in-class exam Closed book, closed note, but you can bring a piece of cheat sheet (A4, double side) Plan your strategy well
E N D
COP4710Database Systems Midterm Review Fall 2013
Midterm Exam • Time: Friday 10/18/2012 9:05am --- 9:55am • Plan your time well • Venue: HCB 315, in-class exam • Closed book, closed note, but you can bring a piece of cheat sheet (A4, 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 zero grade, and will be reported to the university
Midterm Exam • 15% of your final score • Format • Short-answer questions: testing for basic concepts • Make your answers clear and succinct • Example 1: What is the difference between Cartesian product and natural join? • Example 2: Given a relation R in 3NF, is R in BCNF? Why? • Relational algebra • Address queries using relational algebra • Example: Assignment 2 • SQL • Address queries using SQL • Example: Assignment 2
Midterm Exam • What will be covered? • From “Introduction” to “SQL-basics” • 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
DB: What and Why? • Besides file systems and OS, why bother yet another so-called DB? • Schemas & Data • DDL & DML
ER Model: Nothing but Drawing • Entities • Attributes • Relationships • Key constraints • Participation constraints • Roles • Subclasses • Constraints • Keys, referential integrity • Weak Entity Set: Why & How?
Relational Model: A Step Further • Relation Schema: revisited • Primary key, Foreign key, Unique, NULL, Default • Translating ER to Relations • Entities • Relationships (many-many, many-one, one-one) • Weak entity set • Subclass
Relational Algebra: Let’s Do Math • Operators and operands • Set-based operators • Other basic operators • Derived operators • Cartesian product, theta-join, equi-join, natural join • Relational algebra expressions and expression trees
Relational Design: Design it right • Motivation • Functional dependencies & keys • Armstrong axioms • Closures • Normal forms and decomposition algorithms • BCNF • Lossless or lossy? • 3NF • Functional-dependency preserving or not? • Don’t overdo it
SQL: Let’s Get Hands Dirty • What is SQL? • Why SQL? • DDL & DML in SQL • Select-From-Where • String pattern matching • 3-value logic • Subqueries • Aggregation • Select-From-Where-Groupby-Having