Project Overview & Q&A

1. Project Overview & Q&A

2. Announcements • Awesome talk from Google on Wednesday. • Please don’t miss it! • Next Monday is midterm review. • Send questions! • Wednesday office hours are canceled

3. Why study Constraints? • Practical: Understanding the role of constraints important for data quality and performance. • Immediately: you need it do a good job on the project! • Interesting: Can actual formalism rigorously some elements of good design of data. • Shocking you need such little information! • Deep connections to data quality and notions like entropy. • Connections: • Graphical Models: MVDs are Conditional Independence. • Used In data integration & Optimization.

4. Constraints in SQLi.e., Constraints for your project

5. Key constraints • A key is a minimal subset of attributes that acts as a unique identifier for tuples Again: If two tuples agree on the value of attributes in the key, then they must be the same tuple. • Students(sid: string, name: string, gpa: float) 1. Which would you select as a key? 2. Is a key always guaranteed to exist? 3. Can we have more than one key?

6. Foreign Key constraints • Students(sid: string, name: string, gpa: float) • Enrolled(studid: string, cid: string, grade: string “only bona fide students may enroll in courses”, i.e., students must appear in the course table. Studid is not a key. Why? What is? We say that Studid is a foreign key that refers to Students

7. Declaring Foreign Keys • Students(sid: string, name: string, gpa: float) • Enrolled(studid: string, cid: string, grade: string CREATE TABLE Enrolled ( studid CHAR(20), cid CHAR(20), grade char(10), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students )

8. Foreign Keys and update operations • Students(sid: string, name: string, gpa: float) • Enrolled(studid: string, cid: string, grade: string 1. What if we insert a tuple into Enrolled, but no corresponding student? 2. What if we delete a student? • Disallow the delete • Remove all of the courses for that student • SQL allows a third via NULL (not yet covered) DBA chooses (syntax on the web)

9. NULL and NOT NULL • To say “don’t know the value” NULL • NULL has (sometimes painful) semantics, more detail later • Students(sid: string, name: string, gpa: float) Say, Jim just enrolled in his first class. In SQL, we may constrain a column to be NOT NULL, e.g., name

10. General Constraints • We can actually specify arbitrary assertions • “There cannot be 25 people in the DB class” • We learned about triggers and check constraints. • In practice, we don’t specify many such constraints. Why? Performance! NB: Whenever we do something ugly, it’s for the sake of performance

11. Summary of Constraints • Constraints are how databases understand the semantics (meaning) of data • Assertion: they are also useful optimization • SQL supports general constraints, • Keys and foreign keys are most important

12. A Project Demo!