1 / 22

More Constraints

More Constraints. October 8. Today. Status of the class Review of MVDs Piazza Refresher Activity. Logistics. Midterm Exam Logistics. The midterm exam moved to Wednesday night, 7pm, October 22nd . 1.5 hours. No room big enough for us. Firas has the req in week 1!

jalene
Download Presentation

More Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More Constraints October 8

  2. Today • Status of the class • Review of MVDs • Piazza Refresher • Activity

  3. Logistics

  4. Midterm Exam Logistics • The midterm exam moved to Wednesday night, 7pm, October 22nd. 1.5 hours. • No room big enough for us. Firas has the req in week 1! • IMPORANT: If you have a conflict with the exam, email Firas at fabuzaid@stanford.edu ASAP (within a week) to schedule an alternate exam for that day. • Alternate exam: held during Wednesday lecture (Lecture Canceled) • Room assignments on Piazza & course website. Stay tuned!

  5. HW#1 Poll Results • Thank you for polling data! • Most in the range we hoped (but not all!) • Longer than the others. • Gain: Familiar with SQL (amortized costfor projects!) • Pain: Some suboptimal Piazza & Some did not watch the videos? • HW#2 should take less time • Less setup, but start early!

  6. Q&A Day • Next lecture is for the project overview • After today, we will have covered the material needed for projects 1 & 2. • We can also have some Q&A. • If there are topics you’d like to hear again, hear more about please post to Piazza in Q&A section.

  7. MVDs

  8. Multiple Value Dependencies (MVDs) For each fixed course (CS145), every staff member in that course and every student in that course occur in a tuple in that table. Write: Course ↠ Staff or Course ↠ Student

  9. Formal Definition of MVD Course ↠ Staff t1 t3 t2 • We write A↠ B if for any tuples t1,t2 s,t. • t1[A] = t2[A] then there is a tuple t3s.t. • t3[A] = t1[A] • t3[B] = t1[B]and t3[C] = t2[C] • Where C are the attributes of R not in AUnion B.

  10. Does Course ↠ Staff hold now?

  11. Connection FDs If A  B does A↠ B ? Hint: It’s sorta like multiplying by one…

  12. Comments on MVDs MVDs have “rules” too! Experts: Axiomatizable 4th Normal Form is “non-trivial MVD” Ignore: MVD is conditional independence in graphical models (databases, first!)

  13. In the Slides Constraints in SQL for your reference* *eligible for catch up lecture

  14. Piazza!

  15. Constraints in SQL

  16. 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?

  17. 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

  18. 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 )

  19. 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 in the book)

  20. 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

  21. General Constraints • We can actually specify arbitrary assertions • “There cannot be 25 people in the DB class” • We will learn about triggers (more powerful) on Wednesday! • In practice, we don’t specify many such constraints. Why? Performance! NB: Whenever we do something ugly, it’s for the sake of performance

  22. 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 • Read: We want to see them on HW #1

More Related