more constraints n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
More Constraints PowerPoint Presentation
Download Presentation
More Constraints

Loading in 2 Seconds...

play fullscreen
1 / 22

More Constraints - PowerPoint PPT Presentation


  • 75 Views
  • Uploaded on

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!

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'More Constraints' - jalene


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
today
Today
  • Status of the class
  • Review of MVDs
  • Piazza Refresher
  • Activity
midterm exam 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!
  • 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!
hw 1 poll results
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!
q a day
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.
multiple value dependencies mvds
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

formal definition of mvd
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.
connection fds
Connection FDs

If A  B does A↠ B ?

Hint: It’s sorta like multiplying

by one…

comments on mvds
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!)

in the slides
In the Slides

Constraints in SQL

for your reference*

*eligible for catch up lecture

key constraints
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?

foreign key constraints
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

declaring foreign keys
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

)

foreign keys and update operations
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)

null and not null
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

general constraints
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

summary of constraints
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