Tallahassee, Florida, 2015
COP4710
Database Systems
Midterm Review
Fall 2015
Midterm Exam
• Time: Wednesday 10/15/2014 3:35pm --- 4:50am– Plan your time well
• Venue: HWC 2400, in-class exam
• Closed book, closed note, but you can bring a one-page 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 a zero grade, and will be reported to the university
2
Midterm Exam
• 15% of your final score• Format
1. 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?
2. Relational algebra
3. Relational design
4. SQL
3
Midterm Exam
• What will be covered?– From “introduction” to “SQL-basics” (the end of
today’s class)
– 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 4
DB: What and Why?
• Besides file systems and OS, why bother yet another so-called DBMS?
• Schemas & Data• DDL & DML
5
ER Model: Nothing but Drawing
• Entities – Attributes
• Relationships– One-one, one-many, many-many– Roles
• Subclasses• Constraints
– Keys, referential integrity, etc.
• Weak Entity Set: Why & How?
6
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
7
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– How to answer queries using relational algebra?
8
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
9
SQL: Let’s Get Hands Dirty
• What is SQL?• Why SQL?
– DDL & DML in SQL
• Select-From-Where– String pattern matching– 3-value logic– How to answer queries using SQL?
10
Break a Leg!